How to pause App Scripts until spreadsheet finishe

问题:

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.

回答1:

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.

回答2:

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.

回答3:

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

回答4:

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).