Reset Checkboxes and Delete Notes on Defined sheet

问题:

I need to reset checkboxes to FALSE (Unchecked) and also delete all notes from defined sheets.

Also need a script that deletes all notes from a Google sheet (all sheets)

I tried combining numerous different scripts.

https://webapps.stackexchange.com/questions/115076/how-to-run-script-on-multiple-google-sheet-tabs

Re-set checkboxes to false - Google Apps Script -- The problem with this script is that is turns cells with a value of 1 into cells that say "FALSE"

function ResetCheckBoxesAndClearNotesOnDefinedSheets(){
    var tabs = [
        'Checkboxes 1',
        'Checkboxes 2',
        'Checkboxes 3',
        'Another Checkbox tab',
        'Yet another checkbox tab'
    ];


  //LOOP THROUGH TABS LISTED ABOVE
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    for (var i = 0; i < tabs.length; i++) {
        var sheet=ss.getSheetByName(tabs[i]);

  //CLEAR NOTES ON TABS LISTED ABOVE    
      sheet.clearNotes();

 // CLEAR CHECKBOXES ON TABS LISTED ABOVE

var dataRange = sheet.getRange('A: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);


}//end of  sheets loop.
  }

ACTUAL RESULTS: -It does not loop through the pages and delete the notes. It also does not loop through and change all checkboxes in the tabs to unchecked.. and it sometimes replaces a "1" with a FALSE

EXPECTED RESULTS: -Reset all checkboxes on defined sheets -Clear all notes on Defined sheets (or entire Spreadsheet)

回答1:

How about this modification?

Modification points:

• In your script, the variable of i is used at both for (var i = 0; i < tabs.length; i++) { and for (var i = 0; i < values.length; i++) {.
  • By this, the value of i of for (var i = 0; i < tabs.length; i++) { is not increased every 1.
  • I think that this is one of your issues of It does not loop through the pages and delete the notes..
  • For example, please modify to for (var k = 0; k < tabs.length; k++) {var sheet=ss.getSheetByName(tabs[k]);.
• Equality comparison == is used for comparing the boolean.
  • I think that this is another issue of it sometimes replaces a "1" with a FALSE.
  • Please modify this to the strict equality comparison ===.

Modified script:

Please modify as follows.

From:

for (var i = 0; i < tabs.length; i++) {
    var sheet = ss.getSheetByName(tabs[i]);

To:

for (var k = 0; k < tabs.length; k++) {
    var sheet = ss.getSheetByName(tabs[k]);

And

From:

if (values[i][j] == true) {

To:

if (values[i][j] === true) {

Reference:

• Equality comparisons and sameness

If I misunderstood your question, please tell me. I would like to modify it.