Google Spreadsheet App Script will not wait for re

问题:

I have a small script and what I'm trying to do is to write one value from 'Sheet 1' to 'Sheet 2'. Wait for the results to load and compare the cells to see if it is above 10% or not. I have some =importhtml functions in the spreadsheet and it takes along time to load. I've tried sleep, utilities sleep, and flush. None have been working, maybe because I might be putting it in the wrong area..

function compareCells() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var listSheet = ss.getSheetByName('Stocks');
    var dataSheet = ss.getSheetByName('Summary');
    var listSheetLastRow = listSheet.getLastRow();
    var currRow = 1;
    for (i = 1; i <= listSheetLastRow; i++) {
        if (listSheet.getRange(1, 3).getValue() == 'asd') {
            var ticker = listSheet.getRange(currRow, 1).getValue();
            dataSheet.getRange(5, 4).setValue(ticker);
            var value1 = dataSheet.getRange(15, 4).getValue();
            var value2 = dataSheet.getRange(22, 4).getValue();
            SpreadsheetApp.flush();
            if (value1 > 0.10 && value2 > 0.10) {
                listSheet.getRange(currRow, 8).setValue('True');
                listSheet.getRange(currRow, 9).setValue(value1);
                listSheet.getRange(currRow, 10).setValue(value2);
            } else {
                listSheet.getRange(currRow, 8).setValue('False');
            }
        } else {
            Browser.msgBox('Script aborted');
            return null;
        }
        currRow++;
    }
}

回答1:

If it is not important that you use the =IMPORTHTML() function in your sheet, the easiest way to do this will be to use UrlFetchApp within Apps Script. Getting the data this way will cause your script to block until the HTML response is returned. You can also create a time-based trigger so your data is always fresh, and the user will not have to wait for the URL fetch when looking at your sheet.

Once you get the HTML response, you can do all of the same processing you'd do in Sheet1 within your script. If that won't work because you have complex processing in Sheet1, you can:

1. use UrlFetchpApp.fetch('http://sample.com/data.html') to retrieve your data
2. write the data to Sheet1
3. call SpreadsheetApp.flush() to force the write and whatever subsequent processing
4. proceed as per your example above

By handling these steps sequentially in your script you guarantee that your later steps don't happen before the data is present.

回答2:

In case if you are getting these two values (

var value1 = dataSheet.getRange(15, 4).getValue();
var value2 = dataSheet.getRange(22, 4).getValue();

) after the =importhtml call, you have to add sleep function before these two lines of code.

You also can have a loop until you get some values into the range from =importhtml call and add some sleep in the loop. Also note that as of April 2014 the limitation of script runtime is 6 minutes.

I also found this link which might be helpful.

Hope that helps!

回答3:

I had a similar problem but came up with a solution which uses a while loop which forces the script to wait until at least 1 extra column or 1 extra row has been added. So for this to work the formula needs to add data to at least one extra cell other than the one containing the formula, and it needs to extend the sheet's data range (number of rows or columns), for example by adding the formula to the end of the sheet, which looks like what you are doing. Every 0.5 seconds for 10 seconds it checks if extra cells have been added.

 dataSheet.getRange(5, 4).setValue(ticker);            
 var wait = 0;
 var timebetween = 500;
 var timeout = 10000; 
 var lastRow = dataSheet.getLastRow();
 var lastColumn = dataSheet.getLastColumn();
 while (dataSheet.getLastColumn() <= lastColumn && dataSheet.getLastRow() <= lastRow){
   Utilities.sleep(timebetween);
   wait += timebetween;
   if (wait >= timeout){
     Logger.log('ERROR: Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
     throw new Error('Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
   }
 }