在为Google表格编写Google Apps脚本以删除已删除其引用的目标时已成为孤立的命名范围时，会发生意外的“服务器错误” . 有人可以告诉我如何避免它吗？
编辑：请注意，除了解决附加的演示脚本之外，我每次调用例程SpreadsheetApp.getNamedRanges（）时都会寻求一般解决方案 . 我将部署一个命令，以从任何Google电子表格中删除孤立范围名称 . 它将成为dlbTools中NamedRanges例程的一部分，该例程已在Google附加组件商店https://chrome.google.com/webstore/detail/dlbtools./ochdleihpppeoboanknpbmabbdjfihjj中提供 .
我写了一个演示它的Google电子表格，产生了这样的信息： - "We're sorry, a server error occurred. Please wait a bit and try again. (line 8, file " Code ")" . 此电子表格可在https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing公开发布
发生错误：
-
调用方法SpreadsheetApp.getNamedRanges（）时 .
-
仅在使用完整行样式引用删除已在命名范围中引用的行之后 .
-
仅当命名范围引用类似于"1:1"而不是"A1:B1"时 .
-
仅在删除引用的行后导致命名范围引用"#REF!" .
使用Google电子表格演示此意外服务器错误的步骤如下：
-
选择菜单项工具>脚本编辑器...
-
切换到标签"Find Orphans Scripts"
-
选择菜单项Run> findOrphans
-
观察红栏说，"We're sorry, a server error occurred. Please wait a bit and try again. (line 8, file "代码")"
-
选择菜单项视图>日志
-
选择菜单项视图>执行记录
以下是演示服务器错误的测试脚本：function findOrphans（）{
// Helper function logs Named Range names and references.
var logNamedRanges = function (scenario) {
Logger.log('Logging named ranges for scenario: ' + scenario);
Logger.log('In logNamedRanges() before call to getNamedRanges()');
var namedRanges = ss.getNamedRanges();
Logger.log('In logNamedRanges() after call to getNamedRanges()');
var names = namedRanges.map(function (nr){return nr.getName() + ' --> ' + nr.getRange().getA1Notation();});
Logger.log('\n' + names.join('\n'));
}
// Helper function alters Named Ranges to full row references.
// That is, "A1:B1" becomes "1:1".
var changeRangeReferencesToFullRow = function () {
Logger.log('In changeRangeReferencesToFullRow() before call to getNamedRanges()');
var namedRanges = shtTest.getNamedRanges();
Logger.log('In changeRangeReferencesToFullRow() after call to getNamedRanges()');
for (var i = 0; i < namedRanges.length; i++) {
var n = namedRanges[i].getRange().getA1Notation();
var num = n.charAt(1);
var fullRowA1Notation = num + ':' + num;
var fullRowNameRange = shtTest.getRange(fullRowA1Notation);
namedRanges[i].setRange(fullRowNameRange);
}
Logger.log('Successfully changed Named Ranges to full row references');
}
// Helper function creates test sheet by copying sheet Static and
// creates Named Ranges for each of the first two rows.
var setupTestSheet = function () {
if (ss.getSheetByName('Copy of Static') != null) {
// Test sheet already exists, so delete it.
ss.deleteSheet(ss.getSheetByName('Copy of Static'));
Logger.log('Deleted existing copy of test sheet');
}
// Activate sheet Static, duplicate it, and grab reference to it.
shtStatic.activate();
ss.duplicateActiveSheet();
shtTest = ss.getSheetByName('Copy of Static');
// Create or update Named Nanges pointing to the first two rows.
ss.setNamedRange('row1Name', shtTest.getRange('1:1'));
ss.setNamedRange('row2Name', shtTest.getRange('2:2'));
Logger.log('Successfully set up test sheet');
}
// Helper function runs test sequence.
function doTest (whichPass) {
Logger.log('\n\nStarting test using ' + whichPass + '\n\n');
setupTestSheet();
if (whichPass.slice(0,3) == '1:1') {
// next line is only difference between the passes
changeRangeReferencesToFullRow();
}
logNamedRanges('Before row delete')
shtTest.deleteRow(1);
if (whichPass.slice(-5) == 'error') {
// next line stops test before error occurs
Logger.log('Halted test to avoid error');
return;
}
logNamedRanges('After row delete')
Logger.log('Finished test using ' + whichPass );
}
// Create variables that will be available to helper functions.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shtStatic = ss.getSheetByName('Static');
var shtTest;
// Execute test sequence three times.
doTest('A1:B1 style references');
doTest('1:1 style references with stop before server error');
doTest('1:1 style references without stopping');
}
使用Sheets API进行测试
@ Tanaike建议使用Sheets API有帮助，因为它成功检索SpreadsheetAPP调用失败的命名范围列表 . 不幸的是，它不包括最感兴趣的人 - 孤儿 .
https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing上的演示电子表格现在包含一个名为 trySheetsAPI.gs 的附加脚本文件 . 顶部的注释告诉我们如何识别测试Sheets API的其他代码 . 虽然我很欣赏这个有用且有趣的建议，但它仍然不允许我编写一个通用的孤儿删除例程 .
1 回答
这个考虑怎么样？我不知道这是否是最好的 . 对不起 .
修改点：
发现当更新named范围时，会发生错误 . 脚本流程如下 .
运行
setupTestSheet()时，命名范围由ss.setNamedRange('row1Name', shtTest.getRange('1:1'))和ss.setNamedRange('row2Name', shtTest.getRange('2:2'))定义，如下所示 . 使用Sheet API检索以下值 .此时，各个命名范围在电子表格上定义如下 . 可以使用您的浏览器在电子表格中看到这一点 .
这里，当运行
shtTest.deleteRow(1)时，以上命名范围会发生如下变化 .在这种情况下， no error occurs 在
var namedRanges = shtTest.getNamedRanges().在
setupTestSheet()，ss.setNamedRange('row1Name', shtTest.getRange('1:1'));和ss.setNamedRange('row2Name', shtTest.getRange('2:2'));都可以正常工作 .但是，
changeRangeReferencesToFullRow()并不完全有效 . 运行changeRangeReferencesToFullRow()时，命名范围如下 . 发现列数据丢失 .此时，各个命名范围在电子表格上定义如下 .
在这种情况下， no error occurs 在
var namedRanges = shtTest.getNamedRanges().这里，当运行
shtTest.deleteRow(1)时，上面的命名范围变为如下 .在这种情况下， the error occurs 在
var namedRanges = shtTest.getNamedRanges(). 因此，当使用shtTest.deleteRow(1)时，必须删除或更新row1Name #REF.这些表示
1:1和2:2之类的范围可能不适合SpreadsheetApp的命名范围 .解决方法：
上述反映的解决方法如下 .
在
deleteRow()删除行后，使用ss.setNamedRange('row1Name', shtTest.getRange('1:1'))和ss.setNamedRange('row2Name', shtTest.getRange('2:2'))重新定义行的命名范围 .使用以下修改后的脚本删除
deleteRow()删除的行的命名范围 . 仅修改doTest()作为以下脚本 .脚本：
在我的环境中，上面的脚本运行正常 . 如果这些不适合您的环境 . 随意告诉我 .
有关其他问题
如何使用Sheet API？
要从任何Google电子表格中删除孤立范围名称，您可以使用
namedRangeId.namedRangeId是每个命名范围的个人ID . 通过使用它，可以删除孤立的范围名称 .为了使用
namedRangeId，它使用Sheet API . 因此，请为高级Google服务和Google API控制台启用Sheet API .Google表格API v4 https://developers.google.com/sheets/api/高级Google服务https://developers.google.com/apps-script/guides/services/advanced
示例脚本：
流程如下 .
使用
Sheets.Spreadsheets.get()，检索名称和namedrangeID .删除与名称匹配的namedrangeID .
在使用Sheet API的情况下，如果范围数据丢失，虽然
getNamedRanges()出现错误，但Sheet API可以检索命名范围 .如果我误解了你的其他问题，我很抱歉 .