Room For Optimization?

Question

My code works fine as a single array custom function but when I try to run

Arrayformula(if A2:A = true, nextyear(A2:A),nextmonth(A2:A)

It doesn't work, it says internal error.

From what I've seen looks like it could be because my function is taking too long?

function NextMonth(input) {

   if(input.map) {
    return input.map(NextMonth);}

  else {
  var month = Utilities.formatDate(new Date(input), "GMT+0","MM")-1;
  var day = Utilities.formatDate(new Date(input), "GMT+0","dd");
  var year = Utilities.formatDate(new Date(input), "GMT+0","yyyy");

  var output = new Date(year,month,day,0,0,0,0);
  var now = new Date();

  while (output < new Date()) {
  var month = Utilities.formatDate(new Date(output), "GMT+0","MM")-1+1;
  var day = Utilities.formatDate(new Date(output), "GMT+0","dd");
  var year = Utilities.formatDate(new Date(output), "GMT+0","yyyy");
  var output = new Date(year,month,day,0,0,0,0);

  }

  return (output)
  }
}

Have read [Guidelines for Custom Functions](https://developers.google.com/apps-script/guides/sheets/functions#guidelines_for_custom_functions). — Cooper, Nov 24 '19 at 16:50
Take a look at [this question](https://stackoverflow.com/questions/2706125/javascript-function-to-add-x-months-to-a-date) and [this one](https://stackoverflow.com/questions/59014418/use-arrayformula-in-a-custom-script-function-google-sheets-gas) as well. — ZektorH, Nov 25 '19 at 09:59

Rubén · Answer 1 · 2019-11-25T17:43:56.840

0

Custom functions can't be used in ARRAYFORMULAS in the same way that built-in functions could be used. Usually it's better to build a custom function that does what the whole formula does.

Why?

Custom functions have a 30 seconds execution time limit while built-in functions doesn't have this function.

Another limitation is hat custom functions can't use volatile functions like NOW(), TODAY(), RAND(), RANDBETWEEN() and others as arguments.

answered Nov 24 '19 at 19:22

Rubén

34,714
9
70
166

To confirm what do you mean can't be used? It works when I do a single array, but in the longer if statements it doesn't end up working. – Patrick Dufresne Nov 25 '19 at 17:15
@PatrickDufresne I edited my answer (changed the wording and adding few "whys" and links to related questions. – Rubén Nov 25 '19 at 17:44

Room For Optimization?

1 Answers1

Linked