Custom function doesn't work with ArrayFormula

Question

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

Was my answer useful for you? If you have problems for my answer yet, feel free to tell me. I would like to study to solve your problems. — Tanaike
– Tanaike, Commented Jan 31, 2018 at 22:33

Tanaike · Accepted Answer · 2018-01-31 08:45:26Z

6

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.

edited Jan 31, 2018 at 8:45

answered Jan 31, 2018 at 8:33

Tanaike

204k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Weilies Over a year ago

Thanks @Tanaike and that's except what I want!

Tanaike Over a year ago

@Weilies I'm glad your problem was solved. Thank you, too.

Collectives™ on Stack Overflow

Custom function doesn't work with ArrayFormula

1 Answer 1

From :

To :

Note :

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From :

To :

Note :

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related