I created a Google Sheets script that iterates through all rows and retrieves API data, then pastes the results in a column all at once. Everything was working fine until I started using the following code snippet instead of referencing a sheet name.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
Now, the script returns all the chunks it could fetch within the 5-minute runtime limit, then restarts with the next trigger and retrieves another chunk. This has significantly increased the duration of the script.
The initial solution I thought of was to create nested functions so that only the fetch part reruns and not the entire script (including loading the active sheet). However, I encountered an 'data is not defined' error which I believe might be due to the data being wiped every time the script terminates due to the Google time limit.
How can I solve this issue? Using `getActiveSheet()` is preferred as I want to be able to run this script from a button on two different tabs.
The section of code that is returning incomplete chunks (e.g., 80 rows instead of all 400 rows) is:
/** GLOBAL PARAMETERS */
// Serp API key
var API_KEY = '12345';
// key columns
var COL_DOMAIN = 2;
var COL_QUERY = 3;
var COL_RANK = 5;
var COL_GL = 8;
/** END: GLOBAL PARAMETERS */
/**
* Main function: gets search ranks using the Serp API
*/
function getRank() {
// start timer and delete active triggers
var timeStart = new Date();
deleteTriggers();
// Spreadsheet and sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// get data from all rows in a 2-d array
// array indices start at "0" instead of "1"
// so to get row 1 it would be element 0 in the array and so on...
var LR = sheet.getLastRow();
var data = sheet.getRange(1, 1, LR, 18).getValues();
// loop through all rows
// starting at row = 3 (array element index = 2)
for (var i = 2; i < data.length; i++) {
console.log(i);
// skip if processed before
var row = data[i];
if (row[COL_RANK - 1] != '') {
continue;
}
// process row
try {
// get parameters
var domain = row[COL_DOMAIN - 1];
var query = row[COL_QUERY - 1];
var goog = row[COL_GL - 1];
var gl = goog;
var hl = goog;
if (gl == 'co.uk') {
gl = 'uk'
hl = 'en'
}
else if (gl == 'com') {
gl = 'us'
hl = 'en'
}
// send API request
var url = 'https://serpapi.com/search'
+ '?q=' + encodeURIComponent(query)
+ '&gl=' + gl
+ '&hl=' + hl
+ '&output=rank:' + encodeURIComponent(domain)
+ '&google_domain=google.' + goog
+ '&num=100'
+ '&api_key=' + API_KEY;
var rank = UrlFetchApp.fetch(
url,
{
'muteHttpExceptions': false
}
).getContentText();
// update sheet with result
sheet.getRange(i + 1, COL_RANK).setValue(rank);
sheet.getRange(1,2).setValue(Date());
// sleep for X milli-seconds
Utilities.sleep(1);
// check timer
if (isTimeUp(timeStart)) {
addTriggers();
return;
}
}
catch (error) {
sheet.getRange(i + 1, COL_RANK).setValue("Check of url(kolom B), afgesproken positie(D) of land(H) ontbreekt");
continue;
}
}
}
/**
* Checks if script execution time has hit the limit
* default = 5 minutes if the minutes parameter is not passed to the function
*/
function isTimeUp(timeStart, minutes=5) {
var now = new Date();
return now.getTime() - timeStart.getTime() > minutes * 60 * 1000;
}
/**
* Sets up a time trigger for the function to run in one minute
*/
function addTriggers() {
//delete active triggers
deleteTriggers();
//create new trigger
ScriptApp.newTrigger('getRank')
.timeBased()
.after(60 * 1000)
.create();
}
/**
* Deletes active triggers
*/
function deleteTriggers() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}