如何使用GAS改進這個嵌套的for回圈？

我想學習另一種方法來運行這個 for 回圈，因為目前，它通過每次迭代設定值，并且由于它可能會變大，所以最好性能更高。

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

謝謝！！！

uj5u.com熱心網友回復：

描述

如果使用 setValues() 不會覆寫任何公式，您可以使用 getValues() 來獲取舊值陣列，替換任何需要更新的值，然后使用 setValues() 簡單地放回值陣列。背景和數字格式也是如此。

spcSheet請注意，它是從其他地方定義的 OP 中假設的。

腳本

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

參考

• Range.getBackgrounds()
• Range.setBackgrounds()
• Range.getNumberFormats()
• Range.setNumberFormats()

標籤：javascript 谷歌应用脚​​本

上一篇：Google電子表格-創建增量單元格的值按鈕

下一篇：在已完成的行中添加“完成”或“添加”之類的詞