How to determine when all Loading Data operations are finished?

Question

I have a Sheets spreadsheet that has many cells that use a custom function which calls IMPORTHTML and parses the results. Other calculations in the sheet use the returned values from these custom formula cells. As expected, when the custom formula cells are calculating they show a "Loading Data..." error message. I can't trust the results of the dependent calculations until the Loading Data operations are complete. Is there any good way to know when all Loading Data operations within a range are complete?

Tupiguarani

Answer 1

I had the same issue.

cell1 : url

cell2 : uses url in importdata

cell3 : uses cell2 in importdata

A mess of chained results, I know.

So I tested all the options I could find around.

Judge by yourself. On the animation, I change the url so it causes a new query and you can see how the different checkers are affected while the data is retrieved.

Here are the queries for your convenience.

=IFNA(E27,"Waiting for data")
=IFERROR(E27,"Waiting for data")
=If(ISERROR(E27),"Waiting for data",E27)
=IF(E27<>"",E27,"Waiting for data")
=COUNTIF(E27,"<>*")

If you have many of these, you may want to avoid recalculations (tons of http requests in parallel). For that look into https://mcmap.net/q/1622686/-how-to-make-google-sheet-formula-just-calculate-once

Heritor

Answer 2

This worked for me:

=COUNTIF(B1:B565,"<>*")

to count the number of cells exhibiting "Loading..." when using the =IMPORTXML() google sheet function. For example, in one particular situation I had 565 links in the first column and the formula =IMPORTXML(A1,"//h2[@class='margin-bottom-50']") in column 2 and placed the formula above into D1. I double-checked by dragging =countif(B1,"<>*") down column 3 to visually check, and then using =SUM() to add all the 1's in that column.

Parapodium

Answer 3

Let's say the "Loading Data" errors can be found in Sheet1, which is made up of columns A-Z and rows 1-1000. In Sheet2!A1 you could put =IF(COUNTIF(Sheet1!A:Z,"#Loading Data..."),"Loading","Done"). (Replace Z in A:Z with the last column.) If any cells in Sheet1 are returning "#Loading Data..." then the countif will count them, and return "Loading"; else it will return "Done".

(Note: Is the error message exactly what I used? I can't test this at the moment and I'm not sure that's exactly the output)

Trelu

Thanks for the help, but it's not working for me. The error message is "Loading Data..." but the cell displays "Loading...". I've tried both but neither is returning true regardless of state. Since my data is numeric, I've used a countif <> 0 function and compared it to the number of cells in the range. – Tupiguarani

Answer 4

try IF(IsError(a:Z),"Not Ready Yet","Ready")

Magnetite

Answer 5

With a Query function it is possible. Assuming the importhtml functions in column E and a data model that returns e.g. URLs, a counter could be constructed that would be set to 0 when all the scraping functions have been completed:

=Count(E1:E)-query(E1:E; "Select count(E) where E contains 'http' label count(E)''")

In case it is another model of retrieved data, you could combine the Query function with regular expressions that match the text or string pattern of the cells.

With a Google Apps Script you could create a rule that alerts you with an email when the target cell equals 0, so you already have a warning of scraping completion.

Loaded

Answer 6

You can use ERROR.TYPE(cell with import formula) now.

ERROR.TYPE(cell with import formula)=10 means it's still loading. If it returns N/A, that means it's done.

Mohave

Answer 7

Use this, it will accommodate multiple columns/rows in the range. =IF(SUMPRODUCT(ISERROR(range))>0,"Loading","Ready")

Unfortunately, =COUNTIF(range,"<>*") will count the blank cells in the range after it has loaded.

Lolita