Function that loops through the sheet-CodePudding

I'm trying to make Apps Script macro that will do following:

Check if checkmark (cell value = TRUE/FALSE) is ticked in column A
If it's ticked, increase value of cell in column H
Repeat until reached the last row.

After a little bit of digging around in documentation, I came up with this, and from my understanding it should be working, but since I'm asking a question here, it obviously does not. Script does get executed without errors, but it changes nothing on the sheet.

function increment() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var EndRow = ss.getLastRow();
  for (var i = 2; i <= EndRow; i  ) {
      var Cell = ss.getRange(i,8);
      var CheckmarkCell = ss.getRange(i,1);
      if (CheckmarkCell.getValue() == 'TRUE') {
          ss.Cell.setValue(ss.Cell.getValue()   1);
      }
  }
}

Anyone has any idea, what's wrong here?

CodePudding user response：

See if this helps?

function increMentWhenTrue() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Sheet1').getDataRange().offset(2, 0)
  const values = sh.getValues().map(r => {
    r[7] = (r[0]) ? (r[7]) ? r[7] 1 : 1 : r[7];
    return r; 
    })
  sh.setValues(values);
}

Change the sheet name in the first line to suit your requirements.

Same code with if-else statements

function increMentWhenTrue() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Blad1').getDataRange().offset(2, 0)
  const values = sh.getValues().map(r => {
    if (r[0]) {
      if (r[7]) {
        r[7] = r[7]   1;
      } else {
        r[7] = 1;
      }
    } else {
      r[7] = r[7];
    }

    return r;
  })
  sh.setValues(values);
}