1

I'm looking to reset all TRUE checkboxes to FALSE using a script. However, this column also has formulas I want to keep in certain cells (they are NOT checkboxes).

Is it possible to alter so that only TRUE/FALSE values are reset to FALSE? I've searched around, but can't seem to find anyone that had this similar issue.

This option currently resets all checkboxes, but includes all cells & switches my formulas to the word "FALSE" & thus, not working.

Script:

function Checks2() {
    var spreadsheet = SpreadsheetApp.getActive();
  
  
  spreadsheet.getRangeList(['a1:a1000']).activate()
  spreadsheet.getRange('a1:a1000').setValue(false);
  
   
  spreadsheet.getRange('a1').activate();
  
  
};

This script keeps the non-checkbox values I have, but for some reason replaces my FORMULAS within those cells with plain text. I want these formulas to stay in the cells. Any ideas?

function myFunction() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Test')
var dataRange = sheet.getRange('a3:I');
var values = dataRange.getValues();
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[i].length; j++) {
    if (values[i][j] == true) {
      values[i][j] = false; // Modified
    }
  }

}
dataRange.setValues(values); // Added
}
  • Hi Anthony. You included some code - does it work, not work, best effort... what? Also, the images included in your question don't reflect the equivalent fields in your spreadsheet. You need to update one or the other. – Tedinoz Jun 16 at 4:59
  • Just to clarify, please correct me if I'm wrong. Your data is in column A. The number of rows is large, but variable. If a cell in Column A contains text, the cell value should be not be changed. If the cell contains a "checked" checkbox then you want to "uncheck" the checkbox. If the cell contains an "unchecked" checkbox, then no action is required. – Tedinoz Jun 16 at 5:24
  • That is exactly what I'm looking for @Tedinoz - my code works in that it resets all checkboxes, but it also resets the text that should not be changed to the word "FALSE". That is the piece I'm looking to fix. – Anthony Madle Jun 16 at 13:11
  • @Ruben thanks for the advice. I added my new script to the post, but unfortunately I realized there is still an issue. It is replacing my FORMULAS with the plain text result of the formula. I need to find a way to just skip those values entirely....hmm..... – Anthony Madle Jun 16 at 16:19
  • @Tedinoz - my new script now keeps the correct word, but my formulas are lost in the process. It runs slower, but I think it is closer to my end goal. Any thoughts? – Anthony Madle Jun 16 at 17:29
1

Your data is in column A. The number of rows is large, but variable. If a cell in Column A contains text, the cell value should be changed to a boolean value of "FALSE". If the cell contains a ticked checkbox then you want to "uncheck" the checkbox. If the cell contains an "unticked" checkbox, then no action is required.

Your script fails because it not only sets checked checkboxes to true, but also overwrites text values that should not be changed.

uncheck Doc Ref seems to be a method made-to-order. It 'changes the state of the checkboxes in the range to “unchecked”. Ignores the cells in the range which currently do not contain either the checked or unchecked value configured.' It has the added benefits that there is no need to getValues() or setValues().


function wa14319703() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "test";
  var sheet = ss.getSheetByName(sheetname);
  var LR = sheet.getLastRow();
  var range = sheet.getRange(1,1,LR);
  range.uncheck();
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.