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

CodePudding user response：

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:

• getRangeList(a1Notations)

CodePudding user response：

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