How to improve this nested for loop using GAS?-CodePudding

I'd like to learn an alternative way to run this for loop, because currently, it sets the value through each iteration and as this can get large, it better be more performant.

function calculate() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const settingSheet = ss.getSheetByName('settings'); 
const dataSheet= ss.getSheetByName('Data'); 

let settingData = settingSheet.getRange(1, 1, settingSheet.getLastRow(), settingSheet.getLastColumn()).getValues();
let spcData = dataSheet.getRange(1, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getValues();

let recordTypesAccepted = ['strategy1', 'goal2'];

var settingPar1 = settingData.filter(e => e[0] == 'default').map(e => e[1]);
var settingPar2 = settingData.filter(e => e[0] == 'default').map(e => e[2]);

for (let a = 0; a < spcData.length; a  ) {
  for (let r = 0; r < settingData .length; r  ) {

      let settingSearchTerm = settingData[r][4];
      let spcSearchTerm = spcData[a][10];

      let settingMatchType = settingData[r][5];
      let spcMatchType = spcData[a][9];

      let spcRecordType = spcData[a][8];

      if (recordTypesAccepted.indexOf(spcRecordType) > -1 && settingMatchType === spcMatchType) {
        if (settingSearchTerm == spcSearchTerm) {
          settingPar1 = settingData[r][7];
          settingPar2 = settingData[r][6];
        }

        let spcClicks = spcData[a][10];
        let spcOrders = spcData[a][11];

        let row = a   1;

        if (spcClicks >= settingsPar1 && spcOrders >= settingsPar2) {
          let newValue = 10;
          spcSheet.getRange(row, 20).setValue(newBid).setBackground("#D8E271");
        }
      }
    }
  }
}

Thank you!!!

CodePudding user response：

Description

Provided using setValues() doesn't overwrite any formulas you could use getValues() to get an array of the old values, replace any that need to be updated and simply put back the array of values using setValues(). The same for back grounds and number formats.

Note it is assumed from the OP that spcSheet is defined elsewhere.

Script

function calculate() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingSheet = ss.getSheetByName('settings'); 
  const dataSheet= ss.getSheetByName('Data'); 

  let settingData = settingSheet.getRange(1, 1, settingSheet.getLastRow(), settingSheet.getLastColumn()).getValues();
  let spcData = dataSheet.getRange(1, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getValues();

  let recordTypesAccepted = ['strategy1', 'goal2'];

  var settingPar1 = settingData.filter(e => e[0] == 'default').map(e => e[1]);
  var settingPar2 = settingData.filter(e => e[0] == 'default').map(e => e[2]);

  let spcRange = spcSheet.getRange(1,20,spcSheet.getLastRow(),1)
  var spcValues = spcRange.getValues();
  var backgrounds = spcRange.getBackgrounds();
  var numberformats = spcRange.getNumberFormats();

  for (let a = 0; a < spcData.length; a  ) {
    for (let r = 0; r < settingData .length; r  ) {

      let settingSearchTerm = settingData[r][4];
      let spcSearchTerm = spcData[a][10];

      let settingMatchType = settingData[r][5];
      let spcMatchType = spcData[a][9];

      let spcRecordType = spcData[a][8];

      if (recordTypesAccepted.indexOf(spcRecordType) > -1 && settingMatchType === spcMatchType) {
        if (settingSearchTerm == spcSearchTerm) {
          settingPar1 = settingData[r][7];
          settingPar2 = settingData[r][6];
        }

        let spcClicks = spcData[a][10];
        let spcOrders = spcData[a][11];

        if (spcClicks >= settingsPar1 && spcOrders >= settingsPar2) {
          let newValue = 10;
          spcValues[a][0] = newBid;
          backgrounds[a][0] = "#D8E271";
          numberformats[a][0] = "€##0.00";
        }
      }
    }
  }
  spcRange.setValues(spcValues);
  spcRange.setBackgrounds(backgrounds);
  spcRange.setNumberFormats(numberformats);
}

Reference