TransWikia.com

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

Code Review Asked by Mohammed Ziara on December 19, 2021

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

One Answer

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

Answered by Rubén on December 19, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP