Google apps script Exceeded maximum execution time issue or 29k rows

Question

I built some code to update dates from a Dump file. I am commonly running into an "Exceeded maximum execution time" message. Otherwise the code works fine. It imports & updates statuses for 16.5 thousand rows from a sheet that has 29 thousand rows.

Can I make the code faster?

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();
}

Answer 1

According to a benchmark report by Tanaike

Methods of Sheets API can reduce the process costs from those of Spreadsheet Service by about 19 %.

But in the case of this script the impact of replacing the Google Sheets Basic Service by the Google Sheets Advanced Service will be marginal as the number of read / write tasks are minimal.

If you are using the old new runtime (Mozilla Rhino), try the new one (Chrome V8)

Also you could try look for more efficient JavaScript techniques to join two "tables"

In any case it's very likely that the performance improvements will not be enough so you will have to find a way to do process your data by batches. Among other possibilities, you could add a "timer" to save the progress and stop the script before it fails then create a time drive trigger to call a new instance. Another possibility is to use client-side code to orchestrate parallel executions.

Related

• Transforming an array of arrays into an array with joins
• SQL-style join on arrays using ECMA6