google sheets - How to set a cell value based on the value of two other cells in a for loop? - Stack Overflow

admin管理员组

文章数量:1346338

I'm trying to update the value of a cell based on if the value of two other cells in a For loop in Google Apps Script. Here's what I got so far:

function myFunction() {

  var s = SpreadsheetApp.getActive().getSheetByName("Salary Tracker");

  var rank = s.getRange("F2:F").getValues();
  var service_length = s.getRange("G2:F").getValues();
  var data_len = rank.length;

  for(var i=0; i<data_len; i++) {
    if(rank[i][0] == "O-4" && service_length[i][0] == 24) {
      s.getRange(i+2,12).activate();
      s.getCurrentCell().setFormula('=MULTIPLY(9689.10*12');
    } else {
    }
  }

}

Here is the link to an example of the data I'm working with: Google Sheet Example

I want the salary to be calculated in the M column. However, when I run the function, nothing happens.

I'm trying to update the value of a cell based on if the value of two other cells in a For loop in Google Apps Script. Here's what I got so far:

function myFunction() {

  var s = SpreadsheetApp.getActive().getSheetByName("Salary Tracker");

  var rank = s.getRange("F2:F").getValues();
  var service_length = s.getRange("G2:F").getValues();
  var data_len = rank.length;

  for(var i=0; i<data_len; i++) {
    if(rank[i][0] == "O-4" && service_length[i][0] == 24) {
      s.getRange(i+2,12).activate();
      s.getCurrentCell().setFormula('=MULTIPLY(9689.10*12');
    } else {
    }
  }

}

Here is the link to an example of the data I'm working with: Google Sheet Example

I want the salary to be calculated in the M column. However, when I run the function, nothing happens.

• google-sheets
• google-apps-script

Share Improve this question Follow edited 2 days ago Filburt 18.1k1313 gold badges8888 silver badges147147 bronze badges asked 2 days ago rpgbear95rpgbear95 3544 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

=MULTIPLY(9689.10*12

That's an invalid syntax, because the multiply() function expects two arguments. You can fix it like this:

      s.getCurrentCell().setFormula('=multiply(9689.1, 12)');

...or, more simply:

      s.getCurrentCell().setFormula('=9689.1 * 12');

...or, since this is a hard-coded value, like this:

      s.getCurrentCell().setValue(9689.1 * 12);

There's an issue with .getRange('G2:F) as well. That will get columns F and G, and since you're only looking at the first column, the if() will always always get false and do nothing. You should use .getRange('G2:G).

The function is pretty inefficient, because it calls four API methods per row. You may want to use Range.setValues() instead, or trash the script and use this array formula directly in cell M1 instead:

=vstack(
  "Starting Salary",
  arrayformula(if(
    (F2:F = "O-4") * (G2:G = 24),
    9689.1 * 12,
    iferror(ø)
  ))
)

This array formula will fill all of the column in one go, so you should clear column M1:M before adding the formula to make room for the results.

For completeness, here's the code quoted in the question rewritten in modern Apps Script V8:

function setStartSalaries() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Salary Tracker');
  const ranks = sheet.getRange('F2:F').getValues().flat();
  const serviceLengths = sheet.getRange('G2:G').getValues().flat();
  const result = ranks.map((rank, i) =>
    rank === 'O-4' && (serviceLengths[i] == 24)
      ? [9689.10 * 12]
      : [null]
  );
  sheet.getRange('M2:M').setValues(result);
}

The array formula will offer the best performance, but the modern Apps Script version will also run orders of magnitude faster than the original code.

本文标签： google sheetsHow to set a cell value based on the value of two other cells in a for loopStack Overflow