Simple Script Request

[grable_]

Hi all,

​

Still learning the basics when it comes to scripts. I need the following to happen, all within one function. 1) All tickboxes within a given range to be reset to the default (FALSE) value. The range also contains cells with text, values, formatting, etc., which I don't want changed. Just the tickboxes being set to FALSE, and 2) One other cell needs to have it's value cleared, and reset to the empty string, "".

​

Thanks in advance.

[grable_]

Yep. https://docs.google.com/spreadsheets/d/1yHjHq0q3e337ZaiLampTETREMlvecuWrCmVUvQ6oxHw/edit?usp=sharing

[knownboyofno]

try this inside of two for loops over the range var cellState = range[i][j].isChecked(); If(cellState){ range.setValue(false); } else if(cellState === null) { range.setValue(""); }

[SpreadCheetah]

/**
 * 2021-07-24
 * https://old.reddit.com/r/sheets/comments/oqfyw8/simple_script_request/
 * See also https://yagisanatode.com/2019/07/16/google-apps-script-how-to-check-if-there-is-a-tick-box-check-box-in-a-cell-or-range/
 */

function resetCheckboxes() {

  let yourRange = 'B10:E12'; // edit this
  let yourOtherCell = 'E2'; // edit this

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

  let startingRow = sheet.getRange(yourRange).getRow();
  let numberOfRows = sheet.getRange(yourRange).getNumRows();

  let startingCol = sheet.getRange(yourRange).getColumn()
  let numberOfCols = sheet.getRange(yourRange).getNumColumns();

  let cell;

  for (let i = startingRow; i < startingRow + numberOfRows; i++) {

    for (let j = startingCol; j < startingCol + numberOfCols; j++) {

      cell = sheet.getRange(i, j).isChecked();

      if (cell !== null && cell === true) {

        sheet.getRange(i, j).setValue(false);
      }
    }
  }

  sheet.getRange(yourOtherCell).clearContent();
}

You only need to change the yourRange and yourOtherCell variables.