google-apps-script - getNamedRanges() 上的 Google 表格脚本服务器错误

Question

在为 Google 表格编写 Google Apps 脚本以删除已通过删除它们引用的目标而孤立的命名范围时，发生了意外的“服务器错误”。有人可以告诉我如何避免吗？

编辑:请注意，除了解决附加的演示脚本之外，我还在每次调用例程 SpreadsheetApp.getNamedRanges() 时寻求通用解决方案。我将部署一个命令以从任何 Google 电子表格中删除孤立的范围名称。它将成为 dlbTools 中 NamedRanges 例程的一部分，该例程已在 Google 附加组件商店中提供，地址为 https://chrome.google.com/webstore/detail/dlbtools./ochdleihpppeoboanknpbmabbdjfihjj。 .

我写了一个 Google 电子表格来演示它，生成消息: - “很抱歉，发生服务器错误。请稍等一下，然后重试。(第 8 行，文件“代码”)”。此电子表格可在 https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing 上公开获得。

出现错误:

• 当调用 SpreadsheetApp.getNamedRanges() 方法时。
• 仅在使用整行样式引用删除已在命名范围中引用的行之后。
• 仅当命名范围引用类似于“1:1”而不是“A1:B1”时。
• 仅在删除引用行后导致命名范围引用“#REF!”。

使用 Google 电子表格演示此意外服务器错误的步骤是:

• 选择菜单项工具 > 脚本编辑器...
• 切换到“查找孤儿脚本”标签
• 选择菜单项 Run > findOrphans
• 观察红色条，“我们很抱歉，发生服务器错误。请稍等一下，然后重试。(第 8 行，文件“代码”)”
• 选择菜单项查看 > 日志
• 选择菜单项 View > 执行记录

这是演示服务器错误的测试脚本: 函数 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');
}

使用表格 API 进行测试

@Tanaike 关于使用 Sheets API 的建议很有帮助，因为它成功地检索了 SpreadsheetAPP 调用失败的命名范围列表。不幸的是，它不包括最感兴趣的人——孤儿。

演示电子表格位于 https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing现在包含一个名为 trySheetsAPI.gs 的附加脚本文件。顶部的注释说明了如何识别测试 Sheets API 的附加代码。虽然我很欣赏这个有用且有趣的建议，但它仍然不允许我编写通用的孤立删除例程。

Answer 1

这个考虑如何？我不知道这是否是最好的。对不起。

修改点:

发现是在更新namedrange的时候，出现了这个错误。您的脚本流程如下。

运行 setupTestSheet() 时，命名范围由 ss.setNamedRange('row1Name', shtTest.getRange('1:1')) 和 ss.setNamedRange('row2Name', shtTest.getRange('2:2')) 如下。使用 Sheet API 检索了以下值。

{
  "namedRanges": [
    {
      "namedRangeId": "#####",
      "name": "row1Name",
      "range": {
        "endColumnIndex": 26,
        "startRowIndex": 0,
        "endRowIndex": 1,
        "sheetId": #####,
        "startColumnIndex": 0
      }
    },
    {
      "namedRangeId": "#####",
      "name": "row2Name",
      "range": {
        "endColumnIndex": 26,
        "startRowIndex": 1,
        "endRowIndex": 2,
        "sheetId": #####,
        "startColumnIndex": 0
      }
    }
  ]
}

此时，各个命名范围在 Spreadsheet 上定义如下。这可以使用您的浏览器在电子表格中看到。

row1Name 'Copy of Static'!A1:Z1
row2Name 'Copy of Static'!A2:Z2

这里，当 shtTest.deleteRow(1) 运行时，上面的命名范围会发生如下变化。

row1Name #REF
row2Name 'Copy of Static'!A2:Z2

在这种情况下，不会发生错误 var namedRanges = shtTest.getNamedRanges()。

在 setupTestSheet() 处，ss.setNamedRange('row1Name', shtTest.getRange('1:1')); 和 ss.setNamedRange ('row2Name', shtTest.getRange('2:2')); 工作正常。

但是，changeRangeReferencesToFullRow() 并不完全有效。当 changeRangeReferencesToFullRow() 运行时，命名范围如下。发现列数据丢失。

{
  "namedRanges": [
    {
      "namedRangeId": "#####",
      "name": "row1Name",
      "range": {
        "startRowIndex": 0,
        "endRowIndex": 1,
        "sheetId": #####
      }
    },
    {
      "namedRangeId": "#####",
      "name": "row2Name",
      "range": {
        "startRowIndex": 1,
        "endRowIndex": 2,
        "sheetId": #####
      }
    }
  ]
}

此时，各个namedranges在Spreadsheet上的定义如下。

row1Name 'Copy of Static'!1:1
row2Name 'Copy of Static'!2:2

在这种情况下，不会发生错误 var namedRanges = shtTest.getNamedRanges()。

这里，当shtTest.deleteRow(1)运行时，上面的namedranges变成如下。

row1Name #REF
row2Name 'Copy of Static'!2:2

在这种情况下，错误发生在var namedRanges = shtTest.getNamedRanges()。因此，当使用 shtTest.deleteRow(1) 时，必须删除或更新 row1Name #REF。

这些表示 1:1 和 2:2 等范围可能不适合 SpreadsheetApp 的命名范围。

解决方法:

反射(reflect)上述考虑的解决方法如下。

1. 在 deleteRow() 删除该行后，使用 ss.setNamedRange('row1Name', shtTest.getRange('1:1'))< 重新定义该行的命名范围 和 ss.setNamedRange('row2Name', shtTest.getRange('2:2'))。
2. 使用以下修改后的脚本删除由 deleteRow() 删除的行的命名范围。只修改 doTest() 如下脚本。

脚本:

function doTest (whichPass) {
  Logger.log('\n\nStarting test using ' + whichPass + '\n\n');
  setupTestSheet();

  var r = shtTest.getNamedRanges(); // <--- Added

  if (whichPass.slice(0,3) == '1:1') {
  // next line is only difference between the passes
    changeRangeReferencesToFullRow();
  }
  logNamedRanges('Before row delete')

  var dummy = [i.remove() for each (i in r) if (i.getName() == "row1Name")]; // <--- Added

  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 );
}

在我的环境中，上述脚本运行良好。如果这些在您的环境中不起作用。请随时告诉我。

其他问题

Sheet API 的使用怎么样？

为了从任何 Google 电子表格中删除孤立的范围名称，您可以使用 namedRangeId。 namedRangeId 是每个命名范围的单独 ID。通过使用它，可以删除孤立的范围名称。

为了使用namedRangeId，它使用了Sheet API。因此，请为高级 Google 服务和 Google API 控制台启用 Sheet API。

Google 表格 API v4 https://developers.google.com/sheets/api/

高级谷歌服务 https://developers.google.com/apps-script/guides/services/advanced

示例脚本:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = Sheets.Spreadsheets.get(ss.getId(), {fields: "namedRanges(name,namedRangeId)"});
Sheets.Spreadsheets.batchUpdate(
  {"requests": [{
      "deleteNamedRange": {
        "namedRangeId": [i.namedRangeId for each (i in r.namedRanges) if (i.name == "### name ###")][0]
  }}]},
  ss.getId()
);

流程如下。

1. 使用 Sheets.Spreadsheets.get()，检索名称和命名范围 ID。
2. 删除与名称匹配的namedrangeID。

在使用 Sheet API 的情况下，如果范围数据丢失，虽然 getNamedRanges() 发生错误，但 Sheet API 可以检索命名范围。

如果我误解了你的附加问题，我很抱歉。