AInternal Error executing custom function

Question

Is there any limitation in Apps Script? I'm getting "internal Error" if I set variable "lLen" more then 18 - http://prntscr.com/j60kxb

Actually I need to have this string as var lLen = CIDlist.length; but I'm getting the Error above. In some cases CIDlist.length value can be 160+. When I played to understand the reason of the issue I found that it works if lLen <= 18. Any ideas why it happens?

function myFunction(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets(); 
  var sheet = sheets[3]; 
  var lastrow = sheet.getLastRow();
  var CIDlist = [];

  for(var i =16; i<=lastrow; i++){
    var firstval = sheet.getRange("B"+i).getValue();
    var secondval = sheet.getRange("C"+i).getValue();
    if (firstval == input[0][1] && secondval == input[0][0]) {
      var CID = sheet.getRange("A"+i).getValue();
      if (CIDlist.indexOf(CID) == -1) {
        CIDlist.push(CID);
      } 
    }
  }
  console.log(input);
  console.log(CIDlist.length);
  var lLen = 19;
  var TotalRevenue = 0;
  for (var i=0; i< lLen; i++){
    var CIDvalue = CIDlist[i];
    for (var j=16; j<=lastrow; j++){
      var cid = sheet.getRange("A"+j).getValue();
      var revenue = sheet.getRange("D"+j).getValue();
      if (cid == CIDvalue) {
        TotalRevenue = TotalRevenue + revenue;
      }
    }
  }
  return TotalRevenue;
}

You didn't read the documentation, where it clearly states the limitations on custom functions. You have written a horrendously slow custom function, which violates best practices regarding the use of APIs (by not using batch methods `getValues()`), and thus requires more than 30 seconds to execute. — tehhowch, Apr 16 '18 at 16:05
Thanks, so what should I use instead of getValues()? Sorry if my question too dummy. I'm marketer (not coder) so didn't have chance to read the documentation. — oxygene, Apr 16 '18 at 18:33
you need to *use* `getValues()` on the range you are inspecting, instead of repeatedly calling `getRange` with a different target cell. Think of it like sending a single multi-page PDF to your distributor instead of hundreds of single-page PDFs. — tehhowch, Apr 16 '18 at 18:48
Thanks @tehhowch, now it's working fine after I used one getValues() instead of bunch of getValue()! — oxygene, Apr 18 '18 at 08:06

score 1 · Answer 1 · answered Apr 16 '18 at 16:16

1

The function on the question makes use of two for loops that makes several calls to the SpreadsheetApp classes.

As it fails when is increased a value that control the number of iterations of one of the for loops, which makes that the time execution of the custom function be increased, it's very likely that it's exceeding the 30 second limit for custom functions.

answered Apr 16 '18 at 16:16

Rubén

34,714
9
70
166

Thanks Ruben, is there any way to avoid using two loops for this specific case? – oxygene Apr 16 '18 at 18:34
@oxygene Please edit your question to add a description of what your custom function should give as result. Usually including input/output sample data is helpful to understand that. It's worth to note that maybe the number of loops aren't the problem, it could be the number of calls to the Google Apps Script services instead. – Rubén Apr 16 '18 at 18:39

AInternal Error executing custom function

1 Answers1