0

I am using the Spreadsheet Services in Google app script to retrieve some data from the Internet and then mess a bit with it. The problem is when I set the ImportHtml value if the data set is larger than say a few rows I do not have access right away to the imported range and thus an error is thrown in the script:

example:

// create tmp sheet and import some data.
var sheet = this.createTmpSheet(); // custom method to create a temp sheet.
sheet.getRange('A1').setValue('=ImportHtml("someUrl","table",1)');
// at this point usually I can access the range
var range_to_copy = sheet.getDataRange();
// However if the data is more than 10-15 rows I get invalid dimention for range..

Any ideas how to wait for the 'readiness' of the import? None of the usuall triggers seemd like an appropriate choice. All I need is to have flow control in such a way as to be notified once the import completes, usually under 10 seconds.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Peter StJ
  • 2,297
  • 3
  • 21
  • 29

2 Answers2

0

As you noted, there's no trigger that will tell you when a spreadsheet recalculation has completed. (That's what is going on after you update a formula in a cell.)

You can induce your script to wait 10 seconds by using Utilities.sleep(10000);. Generally it's bad programming practice to rely on delays, but it's almost your only option here.

The other option would be to perform the html query yourself, parse the table of data into an array, and write it to the new sheet. If you're interested in that, have a look at html div nesting? using google fetchurl, to see how you could obtain your table of information.

Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
0

Dont do it like that. Use urlFetch to get the data and write it yourself to the spreadsheet.

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36