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.

Alfeus

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();
}

Rumple

In my case just calling SpreadsheetApp.flush(); was enough. Thanks anyway! – Achromatize

I solved with SpreadsheetApp.flush(); as well – Payee

SpreadsheetApp.flush() worked also for me – Sergius

Answer 2

Scripts timeout after about 6 minutes to prevent infinite loops and constantly running programs. I don't think there's a manual way to stop a script.

Edit: Oops, forgot to answer your first question: I thought onEdit ran after values were recalculated, but apparently I don't use enough formulas to see this. If it's not waiting, then the best way is to do something like this:

while(value === "Thinking..." || value === "#NA") {
  Utilities.sleep(10000);
}

It pauses the script for a few seconds and then checks again.

Sent

Just for what it's worth, for anyone looking at this sort of setup for production code outside of Sheets then it's worth adding a sanity check counter (exit the while loop if the counter hits some largeish value) since if for some reason this never completes it locks up a thread forever. I've seen such code bring down servers when they've slowly locked out every thread... – Creight

Answer 3

I also write a bit in Excel VBA, where the native Calculation functions come in handy. I've run across the same problem in Google Apps/Docs several times, wanting to execute code whenever calculations are complete. I wonder why there's no native function in Google Apps/Docs to handle this. Anyhow, I wrote this code to solve the problem. Hope it helps.

function onEdit() {

Refresh();

};

function Refresh () {

     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
     var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

     // set the range wherever you want to make sure loading is done    

     var range = sheet.getRange('A:A')
     var values = range.getValues();

     var string = values.toString();

     var loading = "Loading";

     do{
       var randomWait = Math.floor(Math.random()*1+0); randomWait;
     } while (string.search(loading) ==! 0);
     range.copyTo(sheet2.getRange('A1'), {contentsOnly:true});
     customMsgBox();
};

function customMsgBox() {
  Browser.msgBox("Data refreshed.");
};

Here's an example in action:
https://docs.google.com/spreadsheet/ccc?key=0AkK50_KKCI_pdHJvQXdnTmpiOWM4Rk5PV2k5OUNudVE#gid=0

Make a copy if you want to play around with it.

Darned

That looks like a fairly intensive busy wait to me. en.wikipedia.org/wiki/Busy_waiting Surely it would be better to use a Utilities.sleep(X) in the loop? Also the example sheet doesn't use the code shown above any more but uses a SpreadsheetApp.flush(); (which doesn't actually work). – Creight

Thanks for the answer. Unfortunately, over the past 10 years, the Google Sheets UI must have changed in ways that make this answer not relevant anymore. When I click into the example and look for MENU BAR > ACTIONS > REFRESH, I don't see what you're talking about. – Quaky

Answer 4

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

Buckish

Answer 5

Several months ago, Google Sheets introduced a new set of built-in functions, I.E., LAMBDA, MAP, etc., as well as new features, i.e., named functions, that might be handy for handling loops. Still, Google Apps Script might be used to handle iterations.

As of June 2024, there is still no general way to pause the script execution to wait for a spreadsheet calculation to be finished.

In some cases, it might be helpful to use SpreadsheetApp.flush() to force a change done by the script to be applied to the spreadsheet before continuing with the next part of the script, but if the calculation is started by other means, i.e., by the settings for volatile functions like NOW, TODAY, RAND and RANDBETWEEN to recalculate every specific time or by one of the import functions like IMPORTRANGE.

It's crucial to review your spreadsheet for 'code smells' like the use of volatile functions (NOW, TODAY, RAND, RANDBETWEEN) and functions using large range references that might lead to unnecessary formula recalculation, such as VLOOKUP and QUERY. By taking this proactive step, you can ensure optimal performance and efficiency in your spreadsheet operations.

Also, you might have to review your "environment" like the sharing settings of your spreadsheet and the editors' activity.

One strategy that might be considered is to handle all the calculations related to the iterations on the script side.

Another strategy is to take a "snapshot" of the spreadsheet values and use a second spreadsheet to hold the intermediate steps results. Once the script job is finished, update the original spreadsheet.

Regarding the second question about how to interrupt a script execution, the options depend on how the script was called. If you run it from the script editor, click the Stop button. For any other case, go to https://script.google.com, locate the project and open Executions. Then click the three-dot button and select Terminate.

Another option is to use the Apps Script API.

References

• Adding more flexibility to functions in Sheets
  Date: Wednesday, August 24, 2022.
  Announce the launch of named functions and new built-in Google Sheets functions like LAMBDA.

Este

Answer 6

You could put the recursive structure in the code.js file, rather than on the spreadsheet. Javascript can handle recursion really well. You could then have the script update the spreadsheet on each iteration (or each 100).

Luteal

Answer 7

now you can use the SpreadsheetApp.flush() I discovered this in this link here

Yoko