Script to fetch data ; not all data is returned

Question

I am making a google sheet script that loops trough all rows and fetches API data, then pastes all the results in a column all at once. All went well ( script returned all row data) until I started using below snippet instead of referring to a sheet name.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

Now it returns all chunks it could fetch within the 5 mins it can run, then restarts with next trigger and returns a chunk again. The script takes much longer now.

First solution I could think of is making nested functions, so that not the whole script (including the loading of the active sheet) is reran, but only the fetch part. But now it gives me 'data is not defined' error. I think possibly because every time the script terminates due to google time limit, data gets wiped and has to be fetched again.

How can I solve this, activesheet is ideal as I want to be able to run this script with a button within two different tabs. But then it should work properly.

Code that goes wrong(returns a chunk(e.g. 80 rows), not all 400 rows):

/** 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 procesed 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]);

  }

}

Answer 1

I have found a simple solution. Using activesheet apparantly does not work well with generating triggers. So the trigger to rerun the script after timeout does not get executed.

I have stored the name of the active sheet in a variable and passed this on to getSheetByName like this and now script works, now I can run script on several sheets:

var name = ss.getActiveSheet().getName();
var sheet = ss.getSheetByName(name);