Removing / working around google sheets run-time limit

Question

I recently picked up a google sheets file that would allow me to use the ImportXML more than 50 times in a spreadsheet. https://docs.google.com/spreadsheets/d/1VriaQ6QMq69Ya3734DKRBZ1eUBRd3B1jCD-P6DUHKLA/edit?usp=sharing

I ran into a problem where the script times out because it has to run through so many links (it can't go over 250 at the moment) that it reaches the script run-time limit.

I was wondering if there was a way to turn off the script run-time limit or have it break and restart every 5 minutes. I'm not very good at app script at the moment as I just started learning it, and was hoping someone might be able to point me in the right direction.

I linked the spreadsheet above (with example links for protection reasons). But here is the code im running for the sheet if you would like a look:

function bulkXml() {

    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = sheet.getLastRow()
    var Num = sheet.getRange(2, 1, lastRow, 1).getValues();

    Num = Num.filter(function(u) {
        return u[0].length > 2
    })

    //  Logger.log(a)
    // return false

    for (var y = 0; y < 2; y++) {
        for (var x = 2; x - 2 < Num.length; x++) {
            var url = sheet.getRange(x, 1).getValue();
            sheet.getRange(2, 6).setValue(url);
            var xpathResult = sheet.getRange(3, 6).getValue();
            var counter = x - 1;
            sheet.getRange("C4").setValue(" PLEASE WAIT...CURRENTLY FETCHING " + counter + " OUT OF " + Num.length);

            if (y === 1) {
                sheet.getRange(x, 2).setValue(xpathResult);
                sheet.getRange("C4").setValue("PROCESSED " + counter + " OUT OF " + Num.length);
                SpreadsheetApp.flush();
            }

        }

    }

}

How often do you need to update the set of importXML or is it a batch thing you only need to run once? You can handle this without scripts, just check if the previous value is finished in an if statement before running the next importXML etc. — CodeCamper
Did you try the If method I suggested yet? I don't have your test data but it was working for me. — CodeCamper

Iamblichus Iamblichus · Accepted Answer · 2019-11-14T15:20:32

I think a good way to get around the 6 minute time limit is to split the actions into several parts, and create a timed-based trigger at the end of each part, which will call the following part after a specified amount of time.

The method to notice here is after(durationMilliseconds). Thanks to this, you can run whatever function you specify after the amount of milliseconds you indicate.

In this case, this workaround gets a further complication because you're inside a loop and there is no clear place where you could split your function into several parts.

(1) One option would be to calculate the execution time in the script after each iteration (or after a specified number of iterations) and if this execution time is close to the 6 minute time limit, stop the script and create a trigger that will call the function again.

(2) Another option would be to know more or less how many iterations you can do with 6 minutes, and stop the function and call it again after that many iterations.

The idea would be that each time the function is trigged, it would resume the loop from where it was left. In this case, the next time the function is called (using the time-based trigger specified above) it would need information on which part of the loop are we in: that is, the values of the x and y indexes.

Below is a sample of a code that basically does all these things. Several points have to be taken into account regarding this sample, though:

I removed the outer for, because I don't understand its purpose. If you need it, you would have to modify this code a bit to include it.
The information about the indexes (or in this sample, the index, x) could be stored using the Properties Service, or using any other tool you prefer. But I used the sheet itself to store and retrieve the values each time, in order to keep track of it more easily.
I used the method (2) in this example (I defined the number of iterations to do before the script stops and gets called again, in this case, 25). Change accordingly.

function bulkXml() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow()  
  var Num = sheet.getRange(2, 1, lastRow, 1).getValues();  
  Num = Num.filter(function(u) { 
   return u[0].length > 2
  });

  // Delete all triggers to avoid duplication:
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  // Define the index to start with loop (information coming from sheet):
  var x_range = sheet.getRange(20, 8);
  var x_old = x_range.getValue();  
  if (x_old == "") {
    x_old = 2;
    x_range.setValue(x_old);
  }

  // Looping through each row (from the index it stopped last time till 25 rows below that):
  for (var x = x_old; x - 2 < Num.length && x < x_old + 25; x++) {
    var url = sheet.getRange(x, 1).getValue();
    sheet.getRange(2, 6).setValue(url);
    var xpathResult = sheet.getRange(3,6).getValue();    
    var counter = x - 1;  
    sheet.getRange(x, 2).setValue(xpathResult);
    sheet.getRange("C4").setValue("PROCESSED " + counter + " OUT OF " + Num.length);
    SpreadsheetApp.flush();
  }
  x_range.setValue(x); // Set index to start from next time

  // Calling the script again after some time:
  ScriptApp.newTrigger("bulkXml")
  .timeBased()
  .after(1000)
  .create();
}

I hope this is of any help.

Removing / working around google sheets run-time limit

2 Answers