Google Scripts forEach and setValue not working

user_19341602 0
Viewed 28

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++
  });
}```
google-apps-script
2 Answers

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

Reference:

user_71086530

Try it this way:

function lfunko() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Repairs")
  const vs = sh.getDataRange().getValues();
  vs.forEach((r, i) => {
    if (r[3].includes("Complete") && (!r[4].includes("Posted"))) {
      let currentMessage = r[0] + "\n" + r[1] + "\n" + r[2]
      RepairsCompleted(currentMessage)
      sh.getRange(i + 1, 4).setValue("Posted");
    }
  });
}
user_72150910
Related