1

In Google Sheets I'm trying to use importhtml to grab data from a website, the problem is I'm trying to do it across 5000 cells.

Google Sheets is trying to do this all at once, and I've been experimenting with making it so that a cell waits for the cell above it to finish importing before doing so itself.

However I've not quite been able to get this to work.

I've tried using the following two formula:

=IF(OR(ISERR(A1),A1="Waiting..."),"Waiting...",IMPORTHTML("....")

To explain what my thinking here is:

Every time a cell tries to import something in Google Sheets, if that takes a while, it can throw up an error: "Loading..."

So - the behavior I'm trying to create is:

If A1 is currently importing (Loading...) OR is waiting itself (Waiting...) - then A2 is supposed to not try to import but rather fill itself with a text to say: "Waiting..." (this isn't necessarily a requirement but its my way of trying to force it to stop trying to import)

I've also tried this behavior with the following code:

=IF(OR(ISERR(A1),ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN())))),"Waiting...",IMPORTHTML("....")

The problem I had with this code is that A2 successfully waits for A1 to finish, however A3 doesn't recognise that A2 is waiting.

Any ideas here? I'm not tied to these formulas - I'm open to completely different solutions.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17
Vendrium
  • 55
  • 6
  • What you are intending to import ? Is the table too large ? Take a look at this answer https://stackoverflow.com/questions/67790221/replacing-importhtml-with-urlfetchapp/67807779#67807779 – Mike Steelson Jul 17 '21 at 09:59

1 Answers1

1

Move the evaluation to another cell instead.

Assuming B1 is the 1st IMPORTHTML("...."), and in B2 is the 2nd.. & so on.

In A2, put :

=iferror(if(B1="","",1),0)

and B2 :

=IF(A2=1,IMPORTHTML("...."),"")

and drag all downwards.

Idea : The helper cell assist to control the importhtml() calls. If the previous B1 returns error, the current Import shall stop. If the B1 evaluation is not yet done, the cell value will be either "" or error. In both case, A2 will display "" or 0 . If B1 evaluation is success, A2 will be 1. Then only B2 import will run. same for the next A3/B3.

p/s : this solution is tested on googlesheet, not excel.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17