2

In the QUERY "select" clause it states that you can use a scalar function in place of a column name, for example "year(A)".

And in another part of the documentation it states that you can call a script function by coding "=function_name(...)" into the cell.

My question is: can you use a script function as a "scalar function" in a QUERY statement?

If possible, it would greatly simplify code and might improve performance, for some cases, for example "copying data from one sheet to another".

Here is my example

Assume "test" is a function that issues a "return" statement that modifies
its input parameter.

I have tried the code 

=test(A1)

in a cell and it works.

But when I try this QUERY 

=QUERY(A1:A10, "select test(A)" )

it fails. My guess it does not like the "test(A)" value. Is there some way to do this.

Rubén
  • 34,714
  • 9
  • 70
  • 166
just.a.guy
  • 192
  • 1
  • 2
  • 12

1 Answers1

3

My question is: can you use a script function as a "scalar function" in a QUERY statement?

No you can't, unfortunately.

Reading between the lines, I think what you would need to do is to modify your script such that it accepts an array as an argument, do what it needs to do over that array, and return a result array. Then you would be able to invoke something like:

=test(A1:A10)

or you could include that as your first argument in a QUERY statement:

=QUERY(test(A1:A10);"select Col1 where Col1 contains 'x'")
AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Adani, thank you for your suggestion. I like using the function as a source for the query. It is a good solution. – just.a.guy Jun 26 '12 at 13:09