Need to make Loop more efficient in google appscript-CodePudding

I am comparing values in google sheet and based on the result adding a result in the one column.

But sheet have over 3000 rows which make code slow as I am looping through each line.

function UpdateReadyLINE() {
         var ss = SpreadsheetApp.getActiveSpreadsheet();
         var sheet = ss.getSheetByName("ORDER SHEET");
         var LastRow = ss.getLastRow();

             for(var i = 2; i <= LastRow ; i  ){
                var PendingTotal = sheet.getRange(i,10,1,1).getValue();
                var StockAginstLine = sheet.getRange(i,20,1,1).getValue();
                   if(PendingTotal==StockAginstLine){
                     const time = Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy");
                     sheet.getRange(i,27,1,1).setValue("Ready");
                     sheet.getRange(i,33,1,1).setValue(time);
  } 
  }

}

CodePudding user response：

Try this:

function UpdateReadyLINE() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("ORDER SHEET");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  const ts = Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy");
  vs.forEach((r,i) => {
    if(r[9] == r[19]) {
      sh.getRange(i   2,27).setValue("Ready");
      sh.getRange(i   2,33).setValue(ts);
    }
  })
}

While being more efficient the following script has the potential for messing up formulas

function UpdateReadyLINE() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("ORDER SHEET");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  const ts = Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy");
  let aa = sh.getRange( 2,27,sh.getLastRow() -1).getValues();
  let ag = sh.getRange( 2,33,sh.getLastRow() - 1).getValues();
  vs.forEach((r,i) => {
    if(r[9] == r[19]) {
      aa[i] = "Ready";
      ag[1] = ts;
    }
  });
  sh.getRange(2,27,aa.length,1).setValues(aa);
  sh.getRange(2, 33, ag.length,1).setValues(ag);
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

Modification points:

When getValue and setValue is used in a loop, the process cost becomes high. Ref

When your script is modified, how about the following patterns?

Pattern 1:

In this pattern, only Google Spreadsheet service is used.

function UpdateReadyLINE() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ORDER SHEET");
  var values = sheet.getRange("J2:T"   sheet.getLastRow()).getValues();
  var { aa, ag } = values.reduce((o, r, i) => {
    if (r[0] == r.pop()) {
      var row = i   2;
      o.aa.push(`AA${row}`);
      o.ag.push(`AG${row}`);
    }
    return o;
  }, { aa: [], ag: [] });
  if (aa.length == 0) return;
  sheet.getRangeList(aa).setValue("Ready");
  sheet.getRangeList(ag).setValue(Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy"));
}

When this script is run, first, the values are retrieved from columns "J" to "T" by one call. And, the range list is created. Using the range list, Ready and Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy") are put.

Pattern 2:

In this pattern, Sheets API is used. So, please enable Sheets API at Advanced Google services.

function UpdateReadyLINE() {
  var sheetName = "ORDER SHEET";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = ss.getId();
  var sheet = ss.getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var date = Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy");
  var { values } = Sheets.Spreadsheets.Values.get(spreadsheetId, `'${sheetName}'!J2:T${lastRow}`);
  var data = values.reduce((ar, r, i) => {
    if (r[0] == r[10]) {
      var row = i   2;
      ar.push({ range: `'${sheetName}'!AA${row}`, values: [["Ready"]] }, { range: `'${sheetName}'!AG${row}`, values: [[date]] });
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, spreadsheetId);
}

When this script is run, first, the values are retrieved from columns "J" to "T" by one API call. And, the request body is created. Using the request body, Ready and Utilities.formatDate(new Date(), "GMT 5.30", "dd/MMM/yyyy") are put.
When the Spreadsheet is large, when Sheets API is used, the process cost can be reduced rather than the Spreadsheet service is used a little. Ref

Modification points:

Pattern 1:

Pattern 2:

References: