Can I pass an array into a Google App Script method from a Google Spreadsheet?

Question

Can I pass an array into a Google App Script method from a Google Spreadsheet?

Suppose I have an App Script function that expects a list with two elements (note: this example is just an example so please do not tell me that my problem would be solved if I simply passed each element as a separate argument). How do I call this function from a Google Spreadsheet cell?

I've tried both: '=myFunc([1,2])' and '=myFunc((1,2))' and both give me a Parse Error.

Answer 1

In a spreadsheet formula, you can construct an embedded array using curly braces. Semi-colons are row delimiters; commas (or backslashes in locales that use a comma for the decimal separator) are column delimiters.

Now when such embedded arrays are passed to Google Apps Script, they are converted to a 2-dimensional Javascript array, just like referenced ranges are.

So:

=myFunc({1,2})

=myFunc({1;2})

function myFunc(value)
{
}

In the first formula, value will be [[1, 2]]. In the second, it will be [[1], [2]].

Answer 2

One option is to pass a string of text and then transform it into an array inside the function myFunc:

function myFunc(value) {
  var arr = value.split(',');
}

=myFunc("1,2")

or

function myFunc(value) {
  /* remember you have to be careful with this function: eval() */
  var arr = eval(value);
}

=myFunc("[1,2]")

Answer 3

You can pass a selected range of cells into your Javascript function like so:

The Javascript function will be invoked, and the values will be passed to a singe parameter as the following:

[[""],[""],["E3"],[""],["E1"],[""],[""],[""]]

This array of arrays can be normalized, using a simple map and filter chaining:

function calculateSalaryMultiple(values) {

  // values == [[""],[""],["E3"],[""],["E1"],[""],[""],[""]]
  values = values.map(x=>x[0]).filter(x=>x);

  // values == ["E3", "E1"]
  // Rest of your logic
  ...