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.