google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-sheets-custom-function

How to determine when all Loading Data operations are finished?

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?

Solution

  • 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.

    enter image description here

    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