0

First of all, I am continuing an old thread at this link that I am unable to comment on due to being a newbie.

I have a situation that an answer in that thread given by user Br. Sayan would really improve my Spreadsheet Google App Script. I am making calls to Google Url Shortener API, which puts quotas at 1 call per user per second. I have slowed my script down enough to accommodate this quota, but I then I run over the MAX_RUNNING_TIME for App Scripts execution due to the extended number of calls I need to make, so I need to break the loop when the execution time is exceeded and pick up where I left off.

Here is the code of his answer:

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

 }

My Questions:

Is MAX_RUNNING_TIME a global variable with a value set by Apps Script that I can leave that reference as-is, or must I replace it with a value equalling the 6 minutes listed as the quota for run time on the Google API Console?

How can I place the bulk of my function within this script so that a loop that runs inside my function (say var i = 0; i < data.length; i++) will be synchronized with the loop in the portion given in the above code?

Clarification: when i is incremented up by 1, I need ii to increment by 1. Does this happen automatically? Do I need one loop nested inside the other? Does the bulk of my function go in the first '//do some work here' or the second '//do some work here' or possibly even doSomeWork()?

@tehhowch agreed! However, HOW I need to adapt my code depends on where I need to put it in the above snippet.

Here is what I have so far:

'function short() {
  var = startTime = (new Date()).getTime();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var run = 0;
  var finc = 50;
  var istart = run * finc;
  var iLen = (run + 1) * finc;
  var startRow = 2 + istart;
  var endRow = startRow + finc;
  var data = sheet.getSheetValues(startRow,2,endRow,1);
  var shortUrl = new Array();
  for (var i=istart; i < iLen; i++) {
       Utilities.sleep(1100);
       var url = UrlShortener.Url.insert({longUrl: data[i][0]});
       shortUrl.push([url.id]);
       Logger.log([url.id]);
  }
  var t = ss.setActiveSheet(ss.getSheets()[0]);
  t.getRange(startRow,4,finc,data[0].length).clearContent();
  t.getRange(startRow,4,finc,data[0].length).setValues(shortUrl);'

So if I update the code after each subsequent run to manually increase the variable 'run' by 1, and manually run the code again, this works.

I have also tried break it down into multiple functions by updating the i= and i < parts for each subsequent function, which also works, but requires much more manual work.

I have also tried, unsuccessfully, to use a prompt with a button press that continues the function, which would be better than the other attempts, but would still require a button press to resume the code after each run.

I want to automate the function as much as possible.

  • You should adapt the techniques, not necessarily copy - paste your functions into the ones there. A much better place to start would be to post *your* code and how you have tried to make it resumable. – tehhowch Mar 08 '18 at 22:07
  • @tehhowch agreed! I will definitely adapt my code rather than copy - pasting. However, how I need to adapt my code depends on where it should be placed in the given snippet. Here is what I have done so far: – Nancy Sukup Mar 08 '18 at 22:13
  • You could replace your `for` loop with a `while` loop that checks `i` vs the data length and also how much time has elapsed relative to a duration of your choosing (the "MAX_E..." value in the linked code). When you exit the while loop, it will be due to reaching that time, or because all data has been processed. If there is data remaining, cache/store the current data index. Otherwise, remove the index from the storage. When you start the function, try first to load the index into data from the storage location. If it is there, you resume. Otherwise, begin anew. – tehhowch Mar 08 '18 at 23:00

0 Answers0