CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Weilies
Weilies

Reputation: 530

Custom function doesn't work with ArrayFormula

i followed the post https://developers.google.com/apps-script/guides/sheets/functions

function DOUBLE(input) {
    return input * 2;
}

and copied the very simple DOUBLE function into my app-script.

=double(A1)

is working and double my integer in 'A1' But when i filled column A with values, like

A1 = 2
A2 = 3
A3 = 4
....

And in B1 cell, i run 

=arrayformula(double(A1:A))

it returned error "Result was not a number", #NUM!

I am not sure what goes wrong. Could any app-script gurus helps?

Thanks

Upvotes: 2

Views: 2592

Answers (1)

Tanaike
Tanaike

Reputation: 201473

How about this answer?

  • For example, when =DOUBLE(A1) is used, the value of input of DOUBLE(input) is retrieved as a number or a string (in your case, it's a number.).
  • For example, when =DOUBLE(A1:B1) is used, the values of input of DOUBLE(input) are retrieved as 2 dimensional array.

It is required to calculate after it confirmed whether input is array. The modification which reflected above is as follows.

From :

return input * 2;

To :

return Array.isArray(input) ? input.map(function(e){return e.map(function(f){return f * 2})}) : input * 2;

Note :

When the above modified sample is written using "if" and "for loop", it becomes as follows.

if (Array.isArray(input)) {
  var result = [];
  for (var i = 0; i < input.length; i++) {
    var temp = [];
    for (var j = 0; j < input[i].length; j++) {
      temp.push(input[i][j] * 2);
    }
    result.push(temp);
  }
  return result;
} else {
  return input * 2;
}

If I misunderstand your question, I'm sorry.

Upvotes: 6

Related Questions