“服务电子表格失败"；调用insertCells方法时出错 [英] "Service Spreadsheets failed" error when calling insertCells method

问题描述

这可能是一个简单的问题，但是我在一段代码上遇到了麻烦.

This may be a simple issue, but I am having some trouble with a section of code.

这是代码:

    function CreateSheet() {
      
      function toTitleCase(str) {
        return str.replace(/\w\S*/g, function (txt) {
            return txt.charAt(0)
                .toUpperCase() + txt.substr(1)
                .toLowerCase();
        });
      }
      var ss = SpreadsheetApp.getActive();
      var templatesheet = ss.getSheetByName('Template');
      //var fieldName = Browser.inputBox('Field Name', 'Insert Field Name', Browser.Buttons.OK_CANCEL);
      var ui = SpreadsheetApp.getUi();
      var fieldResult = 
          ui.prompt(
            'FIELD NAME',
            'Please type in the Field Name',
             ui.ButtonSet.OK
          )
      
      var fieldName = toTitleCase(fieldResult.getResponseText());
      var acreResult = ui.prompt('ACRES',
      'Please type in the # of acres, if the # is not known just leave it blank',
       ui.ButtonSet.OK
      )
      var acres = acreResult.getResponseText();
      var url = '';
      ss.insertSheet(fieldName,3,{template: templatesheet});
      ss.getRange('B3:D3').activate();
      ss.getCurrentCell().setValue(fieldName);
      ss.getRange('E3').activate();
      ss.getCurrentCell().setValue(acres);
      url += '#gid=';
      url += ss.getSheetId();
      ss.setActiveSheet(ss.getSheetByName('Summary'));
      SpreadsheetApp.setActiveSpreadsheet(ss);
      ss.getRange('A5:J5').activate();
      ss.getRange('A5:J5').insertCells(SpreadsheetApp.Dimension.ROWS);

最后一行是引发异常消息的地方:

The last line is where it is throwing the exception message:

访问ID为ID的文档时，服务电子表格失败."

"Service Spreadsheets failed while accessing document with id.."

如果我进入工作表本身并手动插入范围"A5:J5"，然后运行代码，它将起作用.我在这里想念什么?

If I go into the sheet itself and manually insert range 'A5:J5' then run the code it will work. What am I missing here?

推荐答案

访问具有ID的文档时服务电子表格失败

Service Spreadsheets failed while accessing document with id

通常是一种错误消息，当达到电子表格的最大大小限制时

• 此限制是5000000个单元格，因此这不应该成为问题为您的电子表格
• 但是，几个用户遇到了与您相同的问题，并在Google的公共问题跟踪器中报告了此问题.此处或此处
• 这可能是一个错误，目前正在由Google调查
• 一些用户报告说，他们仅在V8运行时遇到问题
• 尝试将其禁用(Run > Disable new Apps Script runtime powered by V8)，希望它将暂时解决该问题
• 否则，尝试找到一种替代方法来替换导致错误的行
• 当我重现您的脚本时，这对我来说并没有错，这就是为什么我只能给您一些建议的原因
• 如果insertCells给您带来麻烦，请尝试 insertRowAfter() 或类似的
• 如果问题来自ss.getRange('A5:J5')，请尝试ss.getActiveSheet().getRange('A5:J5')或ss.getSheetByName('Summary').getRange('A5:J5')
• 此外，尝试删除行SpreadsheetApp.setActiveSpreadsheet(ss);-您已经将ss定义为上面的活动电子表格，无需将其重新设置为活动

is usually an error message one obtains when hitting the maximum size limit for a spreadsheet

• This limit is 5 000 000 cells, so it should not be the issue for your spreadsheet
• However, several users experienced the same problem as you and have reported it on Google's Public Issue Tracker here or here
• It is likely to be a bug and it currently being investigated by Google
• Some users reported that they experience the problem only with V8 runtime
• Try to disable it (Run > Disable new Apps Script runtime powered by V8), hopefully it will solve the issue temporarily
• Otherwise, try to find a workaround replacing the line leading to the error
• When I reproduce your script it does not error for me, this is why I can only give you some suggestions
• If insertCells gives you trouble, try insertRowAfter() or similar instead
• If the problem comes from ss.getRange('A5:J5'), try ss.getActiveSheet().getRange('A5:J5') or ss.getSheetByName('Summary').getRange('A5:J5')
• Also, try removing the line SpreadsheetApp.setActiveSpreadsheet(ss); - you already defined ss as the active spreadsheet above, there is no need to set it to active again

这篇关于“服务电子表格失败"；调用insertCells方法时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！