Google Script to clear Checkboxes and Notes in cel

问题:

EXPECTED BEHAVIOR: Clear all cell notes and checkboxes (convert Checkboxes from TRUE to FALSE)

ACTUAL BEHAVIOR: Clears all cell notes, checkboxes, and hyperlinks. It leaves behind text formatted to appear like a hyperlink... but no actual link.

Before:

After (only text & formatting remains -- link is gone)

function ResetCheckBoxesAndClearNotesOnDefinedSheets(){
  var tabs = [
    'Checkout',
    'Reception'
  ];

  //LOOP THROUGH TABS LISTED ABOVE
  var ss=SpreadsheetApp.getActiveSpreadsheet();

  for (var k = 0; k < tabs.length; k++) {
    var sheet = ss.getSheetByName(tabs[k]);
    //CLEAR NOTES ON TABS LISTED ABOVE    
    sheet.clearNotes();  

    // CLEAR CHECKBOXES ON TABS LISTED ABOVE
    var dataRange = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
    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.
}

How do I stop the removal of hyperlinks?

回答1:

Figured it out with the script in the links. THANKS!

function ResetCheckboxesOnSheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getDataValidations();
  var cbA=[];
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      var rule=vA[i][j];
      if(rule!=null) {
        var criteria = rule.getCriteriaType();
        if(criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
          sh.getRange(i+1,j+1).setValue(false)

        }
      }
    }
  }
}