Google Scripts forEach and setValue not working

August 12, 2022

I am trying to have Sheets iterate through the data range, and start another function if two conditions are met. If the other function is started I want a cell to have the value set. If the function is not started, it would continue iterating through and increasing the value of i each time.

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Repairs")
  var rows = ws.getDataRange().getValues();
  
  rows.forEach(function(eachRow) {
    var i = 1
    var p = 4
    if (eachRow[3].includes("Complete") && (!eachRow[4].includes("Posted"))) {
    var currentMessage = eachRow[0] + "\n" + eachRow[1] + "\n" + eachRow[2]
    RepairsCompleted(currentMessage)
    var cell = ws.getRange(i,4);
    cell.setValue("Posted");
    }
    i++
  });
}```

>Solution :

Modification points:

In your script, var i = 1 is declared in the loop. By this, i is always 1 in every loop. I thought that this might be the reason for your issue.

When this is reflected in your script, it becomes as follows.

From:

rows.forEach(function(eachRow) {
  var i = 1

To:

var i = 1
rows.forEach(function(eachRow) {

Note:

In your script, I thought that the process cost can be reduced a little. So, how about the following modification?

  function sample() {
    const ss = SpreadsheetApp.getActiveSpreadsheet()
    const ws = ss.getSheetByName("Repairs")
    var rows = ws.getDataRange().getValues();

    // I modified the below script.
    const ranges = rows.flatMap(function (eachRow, i) {
      if (eachRow[3].includes("Complete") && (!eachRow[4].includes("Posted"))) {
        var currentMessage = eachRow[0] + "\n" + eachRow[1] + "\n" + eachRow[2]
        RepairsCompleted(currentMessage);
        return ["D" + (i + 1)];
      }
      return [];
    });
    if (ranges.length == 0) return;
    ws.getRangeList(ranges).setValue("Posted");
  }