Spreadsheet.getNamedRanges()不返回已删除工作表中定义的NamedRanges

Question

我有一个复杂的电子表格，每个选项卡都有许多在不同的工作种子中定义的命名范围(比如Tab_A，Tab_B，.Tab_X)。我试图将此电子表格分解为更小的独立电子表格(假设独立电子表格1只有Tab_A、Tab_B)，方法是首先复制原始的大型电子表格，然后删除我不需要的工作表，然后尝试清理命名的Range。我正在尝试清除在现在删除的工作表中定义的所有现在无效的命名范围。

我试着运行这个脚本..。

function ListInvalidNamedRanges() { 
  var spreadsheet = SpreadsheetApp.getActive();
    
  /* remove named range if match or partial match with Tab Name reference */
  var namedRangeList = spreadsheet.getNamedRanges();
  for (var j=0; j<namedRangeList.length; j++) {
    var namedRangeName = namedRangeList[j].getName();
    // Do not use includes - ECMA-6 not supported in AppScript
    try {  
      var namedRange = namedRangeList[j].getRange();
      var valueToTriggerErrorIfApplicable = namedRange.getValue(); // force error if necessary
      Logger.log("Try: Valid named range: %s; Range: %s; Value: %s; j: %s", namedRangeName,namedRange, valueToTriggerErrorIfApplicable, j  );
    }
    catch(e) {
      Logger.log("Catch: Invalid named range: %s; Range: %s; Value: %s", namedRangeName,namedRange, valueToTriggerErrorIfApplicable  );
      // namedRangeList[j].remove();
    }
  }                        
}

问题是，spreadsheet.getNamedRanges()不返回在现在删除的选项卡中定义的命名范围列表，但是它们确实显示在命名范围UI中，如您在下面的图像中所看到的那样。

使用UI查看命名图像

​

是否有返回完整列表的对spreadsheet.getNamedRanges()的替代调用？

有太多的垃圾NamedRanges无法一次从UI中删除一个(甚至录制一个宏也不起作用，因为这些垃圾NamedRanges似乎也无法通过宏中的appscript访问)。我有这个用例，不止这一次，所以我尝试以脚本驱动的方式来完成这个任务。

这个简单的测试用例只需两个工作表就可以很容易地看出这一点。

这是测试电子表格的“删除前”版本。为自己创建一个本地副本。https://docs.google.com/spreadsheets/d/1XVTmOMROWuCO640eBnNDF2WpVZFU4UA854pabvYd1ZE/edit?usp=sharing。

在删除第一个工作表之前运行AppScript实用程序的输出

​

现在，删除第一个工作表，W1待删除。

这就是在命名范围用户界面中显示的内容。

​

这就是运行AppScript函数时显示的内容。

工作表删除后运行Appscript函数的输出

​

Answer 1

问题和解决办法：

不幸的是，在目前阶段，似乎#REF的命名范围无法被(SpreadsheetApp)和Sheets检索。这样，当前规范不能直接删除#REF的命名范围。而且，这似乎已经被报告给谷歌的问题跟踪器。参考

从上述情况出发，在本例中，我想提出一个消除#REF命名范围的解决方案。此解决方案的流程如下。

1. 将谷歌电子表格转换为XLSX数据。
2. 删除XLSX数据中#REF的命名范围。
   • 幸运的是，Microsoft的详细规范被发布为Open。这个解决办法就是用它。当Google电子表格转换为XLSX数据时，可以将XLSX数据编辑为XML数据。

3. 将XLSX数据转换为Google电子表格。

通过这个流，可以删除#REF的命名范围。

重要信息：

当Google电子表格被转换为XLSX数据时，通过Google电子表格中的函数，完全转换可能无法实现。例如，无法转换复选框。所以，请小心点。因此，首先，请测试此解决方案，并确认您的电子表格是否可以正常用于实际情况。

示例脚本：

在这个示例脚本中，为了将XLSX转换为Google电子表格，使用了Drive。那么，请在高级谷歌服务上启用驱动器API.

function removeInvalidNamedRanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=" + ss.getId();
  const name = "xl/workbook.xml";
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob().setContentType(MimeType.ZIP);
  const blobs = Utilities.unzip(blob);
  const xml = blobs.find(b => b.getName() == name).getDataAsString();
  const root = XmlService.parse(xml).getRootElement();
  root.getChild("definedNames", root.getNamespace()).getChildren().forEach(e => {
    if (e.getValue() == "#REF!") e.detach();
  });
  const newBlobs = [...blobs.filter(b => b.getName() != name), Utilities.newBlob(XmlService.getRawFormat().format(root), MimeType.XML, name)];
  Drive.Files.insert({ title: `Modified_${ss.getName()}`, mimeType: MimeType.GOOGLE_SHEETS }, Utilities.zip(newBlobs).setContentType(MimeType.MICROSOFT_EXCEL));
}

• 当这个脚本运行时，上面的流就完成了。这样，您就可以看到创建的根文件夹的新电子表格。打开新的电子表格时，可以看到删除了#REF的无效命名范围。

注意：

• 当然，活动电子表格可以被转换的XLSX数据覆盖。但是，在这种情况下，我提议创建它作为一个新的谷歌电子表格。因为当原始电子表格被覆盖时，它可能不是您期望的结果。

参考文献：

• 档案:插入
• 类XmlService
• DefinedName类

Answer 2

，这似乎是一个bug.

谷歌的问题跟踪器在几年前就有关于这个问题的报道。谷歌的一位代表在一个评论中回复了这个帖子，声明如下：

当前，方法Sheet.getNamedRanges()返回工作表上的每个命名范围(有效和无效)(.)为了举例说明这种行为，我创建了一个包含三个范围的新工作表，并删除了其中两个范围的列，使其无效。然后，我根据描述的解决方案编写了这段代码： //(一些代码和一些日志) 无效的命名范围很容易与有效的范围区分开来。请尝试复制这种方法，如果它无助于分享您如何创建无效范围和使用的代码，那么我可以继续研究这个问题。

您可以转到代码示例的线程，但从本质上讲，他展示了getNamedRanges()确实返回了一个#REF错误的“无效”范围。这并不是完全错误的，只是他通过删除列而不是工作表来测试它。考虑到他的解释，看来该方法应该列出所有无效的范围以便可以处理，因此它没有列出已删除的工作表中的范围这一事实似乎是一个疏忽或错误。不幸的是，线程死了，因为之后几乎没有活动。

我还尝试了其他一些可能的解决办法，但都没有成功：

• getSheets()方法似乎没有跟踪已删除的工作表，看我们是否可以直接引用它们来搜索范围。
• 即使您想直接按名称进行搜索，getRangeByName()也会返回null。
• 正如TheMaster的评论所建议的那样，我通过运行Sheets.Spreadsheets.get()来尝试高级服务，它也只列出了未删除的表单中的namedRanges。考虑到这与“纯”API调用几乎相同，这似乎不仅仅是一个应用程序脚本问题。你也可以在谷歌的API资源管理器上测试它。

我的建议是去问题跟踪器尝试重新打开bug，或者仅仅是提交一份新报告，这可能会得到更快的关注。无论哪种方式，修复都可能需要一段时间，因此在删除工作表之前，您可能需要在代码中说明这个问题，并删除指定的范围。