我有一个复杂的电子表格，其中每个选项卡都有许多在不同工作集中定义的命名范围（例如 Tab_A、Tab_B、... Tab_X）。我试图将此电子表格分解为较小的独立电子表格（假设独立电子表格 1 只有 Tab_A、Tab_B），方法是首先复制原始大型电子表格并剪掉我不需要的工作表，然后尝试清理命名范围。我试图摆脱现在已删除的工作表中定义的所有现在无效的命名范围。
我尝试运行这个脚本...
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 中，如下图所示。
View of named images using the UI
有没有替代的电话spreadsheet.getNamedRanges()返回完整列表？
有太多垃圾 NamedRanges 无法从 UI 中一次删除一个（甚至录制宏也不起作用，因为这些垃圾 NamedRanges 似乎也无法通过宏中的 appscript 访问）。我不止一次有这个用例，所以我尝试以脚本驱动的方式来完成此操作。
通过这个仅包含 2 个工作表的更简单的测试用例，可以轻松看出这一点。
这是测试电子表格的“删除之前”版本。为自己创建一个本地副本。https://docs.google.com/spreadsheets/d/1XVTmOMROWuCO640eBnNDF2WpVZFU4UA854pabvYd1ZE/edit?usp=sharing https://docs.google.com/spreadsheets/d/1XVTmOMROWuCO640eBnNDF2WpVZFU4UA854pabvYd1ZE/edit?usp=sharing.
Output of running the AppScript utility BEFORE deleting the 1st-worksheet
现在，删除第一个工作表 W1-to-be-deleted。
This is what shows up in the Named Range UI
这是运行 AppScript 函数时显示的内容
Output of running the Appscript function AFTER the worksheet delete
不幸的是，在现阶段，命名范围似乎#REF无法通过 Google 电子表格服务 (SpreadsheetApp) 和 Sheets API 检索。由此，命名范围#REF当前规范无法直接删除。而且，这似乎已经报告给谷歌问题跟踪器。Ref https://issuetracker.google.com/issues/126262167
从上述情况来看，在这种情况下，我想提出一种解决方法来删除命名范围#REF。此解决方法的流程如下。
#REF in XLSX data.
通过这个流程，命名范围#REF可以删除。
当Google Spreadsheet转换为XLSX数据时，通过Google Spreadsheet中的函数，可能无法实现完整的转换。例如，复选框无法转换。所以，请小心这一点。所以，首先请测试一下这个解决方法，并确认您的电子表格是否可以根据您的实际情况正常使用。
在此示例脚本中，为了将 XLSX 转换为 Google 电子表格，使用了 Drive API。所以，请在高级 Google 服务中启用 Drive API https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services.
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被删除。