清除单元格中的复选框和便笺的Google脚本意外也清除了超链接 [英] Google Script to clear Checkboxes and Notes in cells unexpectedly clears HYPERLINKS as well

问题描述

预期的行为::清除所有单元格注释和复选框(将复选框从TRUE转换为FALSE)

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.

之前:

之后(仅保留文本和格式-链接消失了)

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?

推荐答案

使用链接中的脚本进行查找.谢谢！

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)

        }
      }
    }
  }
}

这篇关于清除单元格中的复选框和便笺的Google脚本意外也清除了超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！