Google脚本 - 15秒后发生内部错误 [英] Google Script - Internal Error after 15 seconds

问题描述

所以我写了一个脚本，我们给出了在同一行中具有特定标签的所有数据的总和。

  Col 1 | Col 2 
 ------- + --------- 
 grp1 | 2 
 grp1 | 1 
 grp2 | 1 
 ------- + --------- 
  

如果我要传递此函数grp1，结果将为3.

当我在1000行以上使用此脚本时，出现错误Internal Error Executing the custom function 很短时间后（如15秒）。我认为这可能是暂停，但它发生在30秒之前。任何想法？

 函数collectgrpdata（group，startrow）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getActiveSheet（）; 
 var lastrow = sheet.getLastRow（）; 
 var currentcell = sheet.getActiveCell（）; 
 var col = currentcell.getColumn（）; 
 var total = 0; 
 
 for（var x = startrow; x <= lastrow; x ++）{
 var v = sheet.getRange（x，col）.getValue（）; 
 if（v！=）{
 if（sheet.getRange（x，2）.getValue（）== group）{
 total + = v; 
 
 
 
返回总额
 
 code $ <$ pre> 
 
 
您可能正在打你的通话限额。 
 
 
，而不是做一个大的调用来获取所有数据，然后使用它：
  function collectgrpdata2（group， startrow）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getActiveSheet（）; 
 var currentcell = sheet.getActiveCell（）; 
 var col = currentcell.getColumn（）; 
 var range = sheet 
 .getRange（startrow，
 col，
 sheet.getLastRow（） -  startrow + 1，
 sheet.getLastColumn（） -  col + 1 ）
 
 var data = range.getValues（）; 
 
返回数据
 .filter（function（row）{return row [0] === group;}）
 .map（function（row）{return row [1 ];}）
 .concat（0）
 .reduce（function（x，y）{return x + y;}）; 
} 
  
 
So I am writing a script that we give the sum of all the data that has a specific tag in the same row. 
Col 1  |  Col 2
-------+---------
grp1   |   2
grp1   |   1 
grp2   |   1
-------+---------
If I was to pass this function grp1 the result would be 3. 

When I use this script over 1000 rows, I get an error "Internal Error Executing the custom function" after a short time (like 15 seconds). I thought it might be the timeout but it happens well before 30 seconds. Any ideas?
function collectgrpdata(group, startrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastrow = sheet.getLastRow();
  var currentcell = sheet.getActiveCell();
  var col = currentcell.getColumn();
  var total = 0;

  for(var x = startrow;  x <= lastrow; x++) {
    var v = sheet.getRange(x, col).getValue();
    if(v != "" ) { 
      if (sheet.getRange(x, 2).getValue() == group) {
           total += v;
      }    
    }
  }
  return total
}

解决方案
Your problem is most likely due to the fact that you do so many calls to getRange and getValue.

You are probably hitting your quota limit of calls.

instead of doing that do one big call to get all the data and then work with that:
function collectgrpdata2(group, startrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var currentcell = sheet.getActiveCell();
  var col = currentcell.getColumn();
  var range = sheet
      .getRange(startrow, 
                col, 
                sheet.getLastRow() - startrow + 1, 
                sheet.getLastColumn() - col + 1)

  var data = range.getValues();

  return data
      .filter(function(row) {return row[0] === group;})
      .map(function(row) {return row[1];})
      .concat(0)
      .reduce(function(x,y) {return x+y;});
}


                        
这篇关于Google脚本 -  15秒后发生内部错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！