javascript - 循环行，检查两个单元格的值是否相等，然后更改单元格值

Question

我想检查第 2 列是否具有值“foo”并且第 28 列具有值“bar”，如果为 true，则将第 2 列中的值设置为“fizz”。

这就是我到目前为止所拥有的。我以为我检查的值正确，但也许我没有将值正确设置为嘶嘶声。

function functionName() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('sheetname');

  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 2; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[2] == 'foo' && row[28] == 'bar'){
      row[2] == 'fizz';
    }
  }
}

Answer 1

如果 foo 和 bar 则嘶嘶作响

function functionName() {
  var ss=SpreadsheetApp.getActive();//typically I find it more useful not to chain too much in the beginning so that you can reuse these assignments later.
  var sh=ss.getActiveSheet();
  var rg2=sh.getRange(2,2,sh.getLastRow()-1,1);//since you began your loop at two I assume you have a header so rows will be one less than getLastRow() to make up for the header
  var vA2=rg2.getValues();
  var rg28=sh.getRange(2,28,sh.getLastRow()-1,1);//You could use getDataRange but then you'd have to reload the whole sheet rather that just one column which could be a  problem if you have formulas in your sheet.
  var vA28=rg28.getValues();
  for(var i=0;i<vA2.length;i++) {
    if(vA2[i][0]=='foo' && vA28[i][0]=='bar')  {
      vA2[i][0]='fizz';
    }
  }
  rg2.setValues(vA2);//Dont forget this.  Its what actually loads the data into the spreadsheet.
}