Allow the window to calculate before continuing Javascript (GAS)

Question

I have seen a lot of duplicates on this subject, but I don't see how to actually get done what needs to be done.

I have a list of URLs in one sheet tab and an IMPORTXML() function in another. I'm writing a script to copy each URL to the second tab then perform an action based on the output of the IMPORTXML(). For this to work, I need a slight delay in the script to ensure the IMPORTXML() has calculated before continuing. setTimeout() doesn't seem appropriate here, because I need the other parameters of the script (which row it's checking, etc) to be calculated based on outputs. Help!

function test(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var list = sh.getSheetByName("Dec 2018").getRange(row,3,sh.getSheetByName("Dec 2018").getLastRow()-row).getValues();
  var check = sh.getSheetByName("Check");
  for(var row = 2;row<500;row++){
  check.getRange(1,1).setValue(list[row-2][0]);
  //wait right here
  //other code to run based on the output of the =IMPORTXML() formula on the Check sheet
  }
}

`SpreadsheetApp.flush();` might just help. Alternatively, if you know how to test to see if the IMPORTXML() has completed maybe put that test in a loop with `Utilities.sleep()`? — P Burke, Jan 16 '19 at 19:04
That's an idea. So after each iteration, clear out the data from the last run, set the new run, and check to see if it's performed. — Spencer, Jan 16 '19 at 19:31
@PBurke Actually `SpresheetApp.flush()` updates applies changes by the script, it doesn't wait for spreadsheet recalculation but `Utilities.sleep(milliseconds)` should work. — Rubén, Jan 16 '19 at 19:32
What is the most relevante duplicate that you found and why the answer there do not work for you? — Rubén, Jan 16 '19 at 19:33
@Rubén I looked at https://stackoverflow.com/questions/7854820/is-there-a-sleep-pause-wait-function-in-javascript, https://stackoverflow.com/questions/951021/what-is-the-javascript-version-of-sleep, https://stackoverflow.com/questions/19389200/javascript-sleep-delay-wait-function, and https://stackoverflow.com/questions/1141302/is-there-a-sleep-function-in-javascript. They all use setTimeout() which, while cool, continues processing the function, but I need everything to wait for the formula calculation. — Spencer, Jan 16 '19 at 19:48

Rubén · Accepted Answer · 2019-01-16T20:00:05.460

0

To insert a slight delay use Utilities.sleep(milliseconds) with a milliseconds value big enough to wait the slowest recalculation time (I think that it's 30000 ms for a single formula because it's the execution time limit for custom functions). If you want to optimize this time, maybe you will want to use a technique like exponential back-off

Note: The Window object isn't available on Google Apps Script server side code execution, so setTimeout() can't be used.

edited Jan 16 '19 at 20:00

answered Jan 16 '19 at 19:47

Rubén

34,714
9
70
166

Ah, yes! I was searching off of Javascript since GAS is based on it, didn't even think to check for a GAS-specific function. Thank you! – Spencer Jan 16 '19 at 19:50

Allow the window to calculate before continuing Javascript (GAS)

1 Answers1