How to pause App Scripts until spreadsheet finishes calculation

Question

Because google spreadsheets does not support iterations, I wrote my own simple app script to adjust an input based upon the calculation of the spreadsheet output. However, after I change the input variable, the spreadsheet recalculates but app scripts does not seem to wait for that recalculation so I end up retrieving values such as "Thinking..." or "#NA". Is there a way to pause a script and wait for the calculation to complete before moving to the next line in the script?

Currently, I am just using a loop to watch the cell but I wanted to find out if there was a more elegant way to pause the execution until the sheet was done calculating.

I write a lot of Excel Macros and Excel VBA always waits for the calculation to complete before moving to the next line in the code. Apps Script does not seem to do this so I am hoping there is an easy way to do this.

A second question: Because this iteration can take some time, how does one interrupt and terminate a script from running? I can't seem to find a way to do this.

Answer 1

Here is a very simple way of preventing the next script from starting until the current script completes in google apps scripts. Just add a call for testWait() after each script you are processing successively. The SpreadsheetApp.flush() also seems to reset the timeout timer on the spreadsheet back to the default 5min so you have more time to process multiple scripts in one go.

//holds processing of next script till last one has completed
function testWait(){
  var lock = LockService.getScriptLock(); lock.waitLock(300000); 
  SpreadsheetApp.flush(); lock.releaseLock();
}

Answer 2

I had the same problem. I resolved it by using 2 scripts as I needed to be sure the spreadsheet has the data I need.

The first script provides a seed value to populate the spreadsheet through a IMPORTXML function.

The second script processes this data.

I used time based triggers to run the scripts allowing for sufficient time for the first script to complete