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?

CodePudding user response：

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