Google apps script Exceeded maximum execution time issue

Question

I build my own app script code to update date from Dump file. I'm facing issue "Exceeded maximum execution time" most of the time. But the code was working fine. it imports & updates status for data (16.5 K rows) from sheet has (29 k rows ) can you support to make the code faster / working fine?

function update_main_master() {
  var xngSs = SpreadsheetApp.openById('XXXX');
  var xngSh = xngSs.getSheetByName('XNG Clean Data');
  var MasterSs = SpreadsheetApp.openById('YYY');
  var MasterSh = MasterSs.getSheetByName('Master Sheet');
  var MasterData = MasterSh.getDataRange().getValues();
  var xngData = xngSh.getDataRange().getValues();

  //  clearFilter()
  if (MasterSh.getFilter() != null) {
    MasterSh.getFilter().remove();
  }

  xngData.splice(0, 1);
  MasterData.splice(0, 1);
  var OrderNumberMasterSh = [];
  var PathNameMasterSh = [];
  for (var i = 0; i < MasterData.length; i++) {
    OrderNumberMasterSh.push(MasterData[i][1]);
    PathNameMasterSh.push(MasterData[i][2]);
  }
  var i = 0;
  for (var x = 0; x < xngData.length && xngData[x][3] != undefined; x++) {
    var OrderNumber = xngData[x][3];
    var OrderDate = xngData[x][2];
    var PathName = xngData[x][4];
    var CustomerName = xngData[x][5];
    var MW_contractor = xngData[x][8];
    var OrderStatus = xngData[x][9];
    var OrderStage = xngData[x][10];
    var ProjectID = xngData[x][14];
    var OrderType = xngData[x][41];
    var StageDate = xngData[x][11];
    var InService = xngData[x][28];
    var RejectedReason = xngData[x][31];
    var District = xngData[x][15];
    var LinkID = xngData[x][24];
    var NewOrder = 'New Order';

    if (MW_contractor == 'A' || MW_contractor == 'B' || MW_contractor == 'C') {
      if (
        OrderType == 'New' ||
        OrderType == 'Repeater' ||
        OrderType == 'Visibility'
      ) {
        //        if(OrderType == "New" || OrderType == 'Repeater')

        var index = OrderNumberMasterSh.indexOf(OrderNumber);

        if (index == -1) {
          MasterData.push([
            OrderDate,
            OrderNumber,
            PathName,
            CustomerName,
            ProjectID,
            MW_contractor,
            OrderStatus,
            OrderStatus,
            OrderStage,
            OrderType,
            StageDate,
            InService,
            '',
            District,
            '',
            NewOrder,
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            RejectedReason,
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
          ]);
        } else {
          MasterData[index][4] = ProjectID;
          MasterData[index][5] = MW_contractor;
          MasterData[index][7] = OrderStatus;
          MasterData[index][8] = OrderStage;
          MasterData[index][10] = StageDate;
          MasterData[index][11] = InService;

          if (MasterData[index][51] == '') {
            MasterData[index][51] = LinkID;
          }

          if (
            OrderStatus == 'IN-PROCESS' ||
            OrderStatus == 'CANCELLED' ||
            OrderStatus == 'REJECTED'
          ) {
            MasterData[index][6] = OrderStatus;
          }

          if (OrderStatus == 'COMPLETED') {
            MasterData[index][6] = 'LIVE';
          }
          if (OrderStatus == 'REJECTED' && MasterData[index][48] == '') {
            MasterData[index][48] = RejectedReason;
          }
        }
      }
    }
  }

  var ContorlSS = SpreadsheetApp.openById('ZZZZ');
  var ContorlSh = ContorlSS.getSheetByName('Setup');
  ContorlSh.getRange('F6').setValue('Updated');
  ContorlSh.getRange('G6').setValue(new Date());

  MasterSh.getRange(2, 1, MasterData.length, MasterData[0].length).setValues(
    MasterData
  );
  SpreadsheetApp.flush();
}

I build my own app script code to update date from Dump file. I'm facing issue "Exceeded maximum execution time" most of the time. But the code was working fine. it imports & updates status for data (16.5 K rows) from sheet has (29 k rows ) can you support to make the code faster / working fine?

Answer 1

You are reaching execution time limit, which is 6 minutes for most accounts. To avoid that, you can split your loop into different executions by setting a time-based trigger that will fire each successive execution after the previous one has finished. You would have to do the following:

• Check execution time after each iteration. If this time is close to the time limit, stop the execution. You can use the Date object for this.

• Create the following time-based trigger at the end of your function: after(durationMilliseconds). Thanks to this, you can run whatever function you specify after the amount of milliseconds you indicate. After each execution, a trigger will be created to fire the next one.

• Because you want to split the loop, you have to store the loop counter (x) somewhere (you could use PropertiesService at the end of each execution, or write it in the spreadsheet) and retrieve it at the beginning of the next, so that each in successive execution, the script knows where to resume the loop. See, for example, this answer if you don't know how to store and retrieve script properties.

• Right now, you are storing all the data you want in a 2D array MasterData, and writing this data to the spreadsheet with setValues at the end of your code. Since this action only happens at after your loop is finished, this would not be appropriate if you split the loop into several executions. I'd suggest you to modify your code so that the writing process is made inside the loop, and remove the setValues from the end of your code. You would have to change your code as in the following examples.

For example, try changing this:

MasterData[index][48] = RejectedReason;

With this:

MasterSh.getRange(index + 1, 49).setValue(RejectedReason);

And this:

MasterData.push([OrderDate, OrderNumber, ...]);

With this:

MasterSh.appendRow([OrderDate, OrderNumber, ...]);

Sample code (check inline comments):

function update_main_master() {
  var begin = new Date(); // Time when execution begins
  // Your code before loop
  var x_old = // Retrieve x stored in previous execution (from PropertiesService? Spreadsheet?) (should be 0 if first execution)
  var timeLimit = 1000 * 60 * 5; // 5 minutes (in milliseconds)
  while (new Date() - begin < timeLimit) { // Check if 5 minutes passed since execution start
    for (var x = 0; x < xngData.length && xngData[x][3] != undefined; x++) {
      // Your code inside loop
    }
  }
  // Store current x index (using PropertiesService? Or write to the spreadsheet itself?
  var ContorlSS = SpreadsheetApp.openById('ZZZZ');
  var ContorlSh = ContorlSS.getSheetByName('Setup');
  ContorlSh.getRange('F6').setValue('Updated');
  ContorlSh.getRange('G6').setValue(new Date());
  if (x < xngData.length) { // Create trigger if x hasn't reach the total number of iteration
    ScriptApp.newTrigger("update_main_master")
    .timeBased()
    .after(1000 * 60) // This fires the function 1 minute after the current execution ends. Change this time according to your preferences
    .create();  
  }
}

Reference:

• Apps Script: Current limitations
• PropertiesService
• after(durationMilliseconds)