Google Scripts - How can I break my code into segments due to avoid the exceeding maximum run-time issue

Question

big noob here with javascript, appreciate any help.

I have a script that will likely run about 30 minutes - it loops through a bunch of stock tickers on one sheet (queries using GOOGLEFINANCE) and copies the results to a master data sheet.

function loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var historical = ss.getSheetByName('Historical');
  var data = ss.getSheetByName('Data');
  var tickers = ss.getSheetByName('Tickers').getRange('B2:B607').getValues();
  var copyRange = historical.getRange(12, 1, historical.getLastRow()-11, 7);
  for (var i = 0; i < tickers.length; i++) {
    historical.getRange('C2').setValue(tickers[i][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(2000);
    if (historical.getRange('C7').getValue() == 'YES') {
      data.getRange(data.getLastRow()+1, 1,historical.getLastRow()-11,7).setValues(copyRange.getValues()); 
    }
  }
}

The sleep above is so that the formulas have a chance to refresh.

I found the following code snippet which I think is the answer from (https://stackoverflow.com/a/8608327)

function runMe() {
  var startTime= (new Date()).getTime();
  
  //do some work here
  
  var scriptProperties = PropertiesService.getScriptProperties();
  var startRow= scriptProperties.getProperty('start_row');
  for(var ii = startRow; ii <= size; ii++) {
    var currTime = (new Date()).getTime();
    if(currTime - startTime >= MAX_RUNNING_TIME) {
      scriptProperties.setProperty("start_row", ii);
      ScriptApp.newTrigger("runMe")
               .timeBased()
               .at(new Date(currTime+REASONABLE_TIME_TO_WAIT))
               .create();
      break;
    } else {
      doSomeWork();
    }
  }
  
  //do some more work here
  
}

But I can't figure out how to integrate the two... how do I merge these two together? Or is there a better solution? Open to other ideas on how to approach.

Any advice appreciated. Thx

score 0 · Accepted Answer · answered Mar 01 '21 at 22:33

Instead of processing all the tickers (B2:B607) in a single execution you have to process them in batches. One way to do this is by parametrizing your loop function, i.e. instead of getRange(A1Notation) you might use getRange(row,column,numRows,numColumns) setting row and numRows as function parameters.

function loop(start, howMany) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var historical = ss.getSheetByName('Historical');
  var data = ss.getSheetByName('Data');
  var tickers = ss.getSheetByName('Tickers').getRange(start,2,howMany,1).getValues();
  var copyRange = historical.getRange(12, 1, historical.getLastRow()-11, 7);
  for (var i = 0; i < tickers.length; i++) {
    historical.getRange('C2').setValue(tickers[i][0]);
    SpreadsheetApp.flush();
    if (historical.getRange('C7').getValue() == 'YES') {
      data.getRange(data.getLastRow()+1, 1,historical.getLastRow()-11,7).setValues(copyRange.getValues()); 
    }
  }
}

If you don't understand how the runMe function works, then you might find better to call the above function in following way:

function batch1(){
  loop(2, 304);
}

function batch2(){
  loop(305, 607 - 304);
}

Run batch1
Wait it to finish
Run batch2

(you might have to use smallers howMany values and more batchN functions)

Very helpful, thanks for taking the time to answer. I've implemented this and assigned a trigger to launch these batches one after the other. Works great! I also noticed you removed the Sleep timer and it still works (so that's an added bonus as even faster now so can have larger batches). — Adam Parker, Mar 03 '21 at 02:12

Google Scripts - How can I break my code into segments due to avoid the exceeding maximum run-time issue

1 Answers1