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?
5 Answers
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://stackoverflow.com/a/60871673/4752223
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)
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.
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.
Related