CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
d-ron
d-ron

Reputation: 71

How to set a cell value as TRUE in Google apps script

Column A in my sheet has checkboxes. I'm writing a simple script to find the checkbox that is checked (cell value = TRUE), make it unchecked (change it to FALSE), and then check the next checkbox in the column (make that cell value = TRUE).

Here's my code:

function nextCheckbox() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()

  var checkmarks = ss.getRangeByName("update_checkmarks").getValues().flat(); //this range is column A
  Logger.log(checkmarks.indexOf(true)); //this logs 8.0, which is the correct row for the checked box in column A

  var rowNum = checkmarks.indexOf(true); 

  Logger.log(rowNum); // this logs 8.0, as expected

  var cell = sheet.getRange(rowNum,1);
  cell.setValue(false); //nothing happens here...
  var cell = sheet.getRange(rowNum + 1,1);
  cell.setValue(true); //nothing happens here...

}

Logging logs the expected row number (8.0). But nothing happens when I use setValue. What am I doing incorrectly?

Upvotes: 0

Views: 4772

Answers (3)

Cooper
Cooper

Reputation: 64062

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet1' && e.range.rowStart > 1 && e.range.rowStart < 12 && e.range.columnStart == 1 && e.value == "TRUE") {
    e.range.setValue("FALSE");//reset
    e.source.toast(e.range.getA1Notation());
  }
}

Demo:

enter image description here

Upvotes: 1

Wicket
Wicket

Reputation: 38160

  1. Regarding getting/setting values for checkboxes

    Instead of using setValue consider to use check() / uncheck()

    The above because checkboxes might use custom values for checked / unchecked states.

  2. Regarding the use of indexOf / getRange

    • indexOf will return the index using 0 based numbering, this means 0 corresponds to the first value, 1 for the second and so on.
    • SpreadsheetApp.Sheet.Range.getRange(row,column) requires 1 based indexes, this means that the first row is 1, the second is 2.

    Considering the above replace

    var rowNum = checkmarks.indexOf(true);

    by

    var rowNum = checkmarks.indexOf(true) + 1;

Resources

Upvotes: 2

pgSystemTester
pgSystemTester

Reputation: 9917

Your array starts at zero while your rows start at one. Currently your setting row 7 to be false(which it already is) and row 8 to be true, which it also already is.

Change this line in your code:

var rowNum = checkmarks.indexOf(true);

to be

var rowNum = checkmarks.indexOf(true)+1;

and you should get your expected results.

Upvotes: 1

Related Questions