Google Spreadsheet Script问题-错误:服务超时:Apps脚本 [英] Google Spreadsheet Script problem - Error: Service Times Out: Apps Script

问题描述

我一直在尝试制作一个快速的Google脚本来为婚礼的邀请响应电子表格计算rsvps.在将新条目添加到电子表格后，该脚本可以完美运行一周，然后突然停止在每个单元格中显示以下错误消息:

I've been trying to whip up a quick google script to count rsvps for the invite response spreadsheet for a wedding. The script worked perfectly for a week as new entries were added to the spreadsheet, then suddenly stopped working with the following error message in each cell:

错误:服务超时:应用脚本

Error: Service Times Out: Apps Script

脚本本身很简单.它查询相关列(有多个事件)，然后检查是否存在用户指定的某些响应-通常为是"，否"或空白.

The script itself is simple. It queries the relevant column (there are multiple events) and then checks to see whether there is some response spefied by the user - "YES", "NO", or a blank, typically.

此错误是什么意思，有人对修复有任何建议吗?

What does this error mean, and does anyone have any suggestions for fixes?

function sumRSVP(response, rsvpType) {
  var rsvpCol = 7;
  if (rsvpType == "rehearsal") rsvpCol = 8;  
  if (rsvpType == "brunch") rsvpCol = 9;

  var mySum = 0;

  var sh = SpreadsheetApp.getActiveSheet();
  for( i=2; i<177; i++){

    var rsvp = sh.getRange(i, rsvpCol).getValue();
    var nguests = sh.getRange(i, 6).getValue();
    if(nguests != "" && rsvp == response){
      mySum = mySum + parseFloat(nguests);
    }
  }

  return mySum;
}

推荐答案

希望婚礼进行得很顺利.这个问题是前一段时间提出的，但在这篇文章中已经被浏览了300多次，我认为这很重要:

Hopefully the wedding went well. This was asked some time ago but has been viewed over 300 times at this post and I believe is important:

不应循环地从电子表格中提取数据.所需的数据应批量提取到数组中，并在循环中评估该数组.

Data should not be extracted from a spreadsheet in a loop. The data needed should be extracted in a batch to an array and the array evaluated in the loop.

请参阅以下文档参考: https://developers.google.com/apps-script/guide_common_tasks#OptimizeScripts

See docs reference at: https://developers.google.com/apps-script/guide_common_tasks#OptimizeScripts

通过减少读写次数，您可以编写脚本以最大程度地利用内置缓存.交替进行读写命令很慢.要加快脚本运行速度，请使用一个命令将所有数据读入数组，对数组中的数据执行任何操作，然后使用一个命令将数据写出.

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.

function sumRSVP(response, rsvpType) {
  var rsvpCol = 7;
  if (rsvpType == "rehearsal") rsvpCol = 8;  
  if (rsvpType == "brunch") rsvpCol = 9;

  var mySum = 0;

  var sh = SpreadsheetApp.getActiveSheet();
  // start at row 2 - uses columns 6-9
  var data = sh.getRange(2, 6, 177 - 1 , 4).getValues();
  for(var i=0; i<data.length; i++){

    var rsvp = data[i][rsvpCol - 6];
    var nguests = data[i][0];
    if(nguests != "" && rsvp == response){
      mySum = mySum + parseFloat(nguests);
    }
  }

  return mySum;
}

这篇关于Google Spreadsheet Script问题-错误:服务超时:Apps脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！