javascript 服务错误：Google Scripts 上的电子表格

Service Error: Spreadsheets on Google Scripts

提问by Marcello Grechi Lins

Scope

范围

I started to write an script that will make chained calls to an API (with a JSON response) and write the result into a Spreadsheet.

我开始编写一个脚本，该脚本将对 API（带有 JSON 响应）进行链式调用并将结果写入电子表格。

What Happens:

怎么了：

Once i debug the script code, it runs just fine, with no major problem, but once i run it from the Spreadsheet button itself (from the menu i created) it runs some steps of the script and than, pops a : Service Error: Spreadsheetwith no other error details.

一旦我调试了脚本代码，它就会运行得很好，没有大问题，但是一旦我从电子表格按钮本身（从我创建的菜单）运行它，它就会运行脚本的一些步骤，然后弹出一个：Service Error: Spreadsheet没有其他错误详情。

Weirdness

怪异

I Started to "Log" the current step of the process to a Spreadsheet cell, so that i can monitor its progress while running the script out of the debugger.

我开始将进程的当前步骤“记录”到电子表格单元格中，这样我就可以在从调试器中运行脚本时监视其进度。

The problem is, once i move some "random" pieces such as :

问题是，一旦我移动了一些“随机”部分，例如：

sheet.getRange("F2").setValue(currentPage);

the code tends to break in different points.

代码往往会在不同的地方中断。

Code Sample :

代码示例：

You can find a code to reproduce the issue here : http://pastebin.com/HjmSwEYZ

您可以在此处找到重现该问题的代码：http: //pastebin.com/HjmSwEYZ

All you have to do is :

您所要做的就是：

1 - Create a new Spreadsheet on Google Drive

1 - 在 Google Drive 上创建一个新的电子表格

2 - Hit Tools -> Script Editor

2 - 点击工具 -> 脚本编辑器

3 - Create a new Script, paste the code in, and save

3 - 创建一个新脚本，粘贴代码，然后保存

4 - Reload the Spreadsheet (F5) so that the custom menu will now appear "Guild Wars 2 Tracker"

4 - 重新加载电子表格（F5），以便自定义菜单现在将显示“Guild Wars 2 Tracker”

5 - Click the button and hit "List All"

5 - 单击按钮并点击“全部列出”

Desired Output:

期望输出：

What this code should (if it wasn't for this error)do is :

这段代码应该（如果不是因为这个错误）做的是：

1 - Execute a request on this url : http://www.gw2spidy.com/api/v0.9/json/items/all/1(which will return the first page of Guild Wars 2 itens)

1 - 在这个 url 上执行一个请求：http: //www.gw2spidy.com/api/v0.9/json/items/all/1（这将返回激战 2 itens 的第一页）

2 - Iterates over each page, parsing the json and writing the returned values into the Spreadsheet

2 - 遍历每个页面，解析 json 并将返回的值写入电子表格

Disclaimer:

免责声明：

Sorry about all the "Log" messages in the sheet. This was a desperate attempt to track my progress, i know i should't do this.

对工作表中的所有“日志”消息感到抱歉。这是跟踪我进度的绝望尝试，我知道我不应该这样做。

Thanks in advance

提前致谢

Update 1:

更新 1：

After creating another Spreadsheet and pasting the pastebin code in its own Script Project i could run it for on interaction, but that was it. This time thus, it raised a different error : We're sorry, a server error occurred. Please wait a bit and try again.

在创建另一个电子表格并将 pastebin 代码粘贴到它自己的脚本项目中后，我可以运行它进行交互，但就是这样。这一次，它引发了一个不同的错误：We're sorry, a server error occurred. Please wait a bit and try again.

回答by Dave Ghidiu

I like what ellockie said - I was (unwittingly) having the same problem. I was trying to range.sort(8), but to collect the range, I used:

我喜欢 ellockie 所说的 - 我（无意中）遇到了同样的问题。我试图 range.sort(8)，但为了收集范围，我使用了：

sheet.getRange(2,1,sheet.getMaxRows(), sheet.getMaxColumns());

But what I shouldhave used was:

但我应该使用的是：

sheet.getRange(2, 1, sheet.getMaxRows()-1, sheet.getMaxColumns());

The error message, as of 5/1/2015, still is very cryptic and offers no further details than the "Service error: Spreadsheets".

截至 2015 年 5 月 1 日，该错误消息仍然非常神秘，除了“服务错误：电子表格”之外没有提供更多详细信息。

回答by tehhowch

You can get service errors like this when you access improper ranges, and the error may not be raised until a subsequent access. For example, if you get a Rangethat references columns that don't exist (like H if you only have A-E), or rows that don't exist (like row 10001 when you only have 10000 rows). This is noted in the Apps Script Issue Tracker: https://issuetracker.google.com/issues/68062620

当您访问不正确的范围时，您可能会遇到这样的服务错误，并且在后续访问之前可能不会引发该错误。例如，如果您得到一个Range引用不存在的列（如 H，如果您只有 AE），或不存在的行（如只有 10000 行时的第 10001 行）。这在 Apps 脚本问题跟踪器中注明：https: //issuetracker.google.com/issues/68062620

With regards to the source of your issue, your script is highly unoptimized and does not follow Apps Script "Best Practices"regarding use of the Spreadsheet Service. Namely, you should use batch operations such as Range#setValuesoperation to write whole blocks, or at least appendRowto append each row (instead of sheet.getRange(rowIndex, someColumn).setValue(oneValue)). These methods will add relevant rows to hold the data if they need to.

关于您的问题的根源，您的脚本高度未优化，并且没有遵循有关使用电子表格服务的Apps 脚本“最佳实践”。即，您应该使用批处理操作，例如Range#setValues操作写入整个块，或者至少appendRow附加每一行（而不是sheet.getRange(rowIndex, someColumn).setValue(oneValue)）。如果需要，这些方法将添加相关行以保存数据。

An example modification of your code:

代码的示例修改：

var itemFields = [ "name",
                   "rarity",
                   "price_last_changed",
                   "max_offer_unit_price",
                   "min_sale_unit_price",
                   "offer_availability",
                   "sale_availability" ];
function addResultPage_(sheet, results) {
  const imgs = [],
  const data = results.map(function (result, index) {
    if (result.img)
      imgs.push({row: index, url: result.img});
    return itemFields.map(function (field) { return result[field] || ""; });
  });
  if (!data.length) return;

  const startRow = sheet.getLastRow() + 1;
  sheet.getRange(startRow, 2, data.length, data[0].length).setValues(data);
  if (imgs.length)
    imgs.forEach(function (imgInfo) {
      sheet.insertImage(imgInfo.url, 1, startRow + imgInfo.row);
    });
}
function listAllItems() {
  const sheet = SpreadsheetApp.getActiveSheet(),
        totalPages = updateStartStatus("List All Items");

  for (var page = 1; page <= totalPages; ++page) {
    var pageResults = getItemsByPage(page);
    if (pageResults.results)
      addResultPage_(sheet, pageResults.results);
    else
      console.warn({message: "No results for page '" + page + "'", resp: pageResults});
  }
}

回答by a-change

Marchello, I've run into the same problem today and have just found an answer to get round it here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3815(see #4). The idea is to add rows to the bottom of the sheet that would let the scripts start working again. It does work in my case.

Marchello，我今天遇到了同样的问题，刚刚在这里找到了解决方法：https: //code.google.com/p/google-apps-script-issues/issues/detail?id=3815（见#4）。这个想法是在工作表的底部添加行，让脚本重新开始工作。在我的情况下它确实有效。

回答by ellockie

In my case I had formulas with 'dynamic' ranges, i.e. =sum(b2:b), which I remember was mentioned as a casuse of issues in the new google spreadsheets.

在我的例子中，我有一个带有“动态”范围的公式，即=sum(b2:b)，我记得在新的谷歌电子表格中提到它是导致问题的一个原因。

Correcting to sum(b2:b22)(making sure the range doesn't exceed the sheet's last row) solved the problem.

更正sum(b2:b22)（确保范围不超过工作表的最后一行）解决了问题。

回答by user3775095

In my case I get this error because I was asking another spreadsheet to sort it's data with a range of 25 columns when there were only 19 on the spreadsheet.

在我的情况下，我收到此错误是因为我要求另一个电子表格在电子表格上只有 19 列时对 25 列范围内的数据进行排序。

In my wisdom I thought I would tidy up the spreadsheet by deleting un-needed columns after I had written the script to sort 25 columns instead of using getLastColumn - don't ask me why.

在我的智慧中，我认为我会在编写脚本以对 25 列进行排序而不是使用 getLastColumn 之后删除不需要的列来整理电子表格 - 不要问我为什么。

回答by Niesteszeck

I have the same problem reason as ellockie mentioned: The dynamic range in a formula. In my case I was using a =INDEX(B7:B,1)to get the first cell after my column title, the cell B7. I was using this, because I use a sript to insert a new cell, shifting all the actual data down, so I need to get the first row value in other cell, no matter I move the cell. I change it for a =INDIRECT(CONCATENATE(CHARACTER(COLUMN()+64);ROW()+4))to reference it dynamical

我有与 ellockie 提到的相同问题原因：公式中的动态范围。就我而言，我使用 a=INDEX(B7:B,1)来获取列标题后的第一个单元格，即单元格 B7。我正在使用它，因为我使用 sript 插入一个新单元格，将所有实际数据向下移动，因此无论我移动单元格，我都需要获取其他单元格中的第一行值。我将其更改为=INDIRECT(CONCATENATE(CHARACTER(COLUMN()+64);ROW()+4))动态引用它

• CHARACTER(COLUMN()+64)give the column letter. Replacing =INDEX(B7:B,1)means B
• ROW()+4give me the row number I want, 4 rows under I put this formula. replacing =INDEX(B7:B,1)means 7

• CHARACTER(COLUMN()+64)给列字母。更换=INDEX(B7:B,1)装置B
• ROW()+4给我我想要的行号，我把这个公式放在下面的 4 行。替代=INDEX(B7:B,1)手段7

回答by Rafe

I had the same. But figured out the root cause, which was trying to copy a single cell... CAN YOU BELIEVE IT?

我有同样的。但是找出了根本原因，即试图复制单个单元格……您能相信吗？

part of script was trying to clear a single row, and that damn cell was there. I was trying to copy a single row and the whole Spreadsheet crashes. restarted and continued by copying cells one by one and figured out that an empty cell causes the crash. Deleted the entire column and all other empty columns... then crossed through the heaven's gate!

脚本的一部分试图清除一行，而那个该死的单元格就在那里。我试图复制一行，整个电子表格崩溃了。通过逐个复制单元格重新启动并继续，并发现空单元格导致崩溃。删除了整列和所有其他空列……然后穿过了天堂之门！

now, can copy entire row and script works fine!!!!!

现在，可以复制整行并且脚本工作正常！！！！！