Search code examples
google-sheetslambdasplitgoogle-sheets-formula

Passing QUERY() as a parameter to a Google Sheets LAMBDA function

Google Sheets offers passing in parameters to lambdas as such: =LAMBDA(x, y, x + y)(100, 200)

I was thinking of taking 2 columns from another Sheet, filter it with QUERY and then pass those 2 columns into the LAMBDA. Basically the 2 columns were a key and a CSV text that I wanted to split in one go.

  1. =lambda(a, b, split(b, ","))(query('Alias Key Raw'!A1:B, "select * where A starts with 'X'"))

This gives the following ERROR Wrong number of arguments to call following LAMBDA function. Expected 2 arguments, but got 1 arguments.. Given that QUERY provides 2 columns of actual values, I thought this would be possible.

  1. =byrow(query('Alias Key Raw'!A1:B, "select * where A starts with 'X'"), lambda(row, split(row, ",")))

This gives me only column A. No error otherwise. All of column B is ignored it appears

  1. I've tried using BYCOL, BYROW, etc, and a lot of errors are ERROR Wrong number of arguments to call following LAMBDA function. Expected 2 arguments, but got 1 arguments.

Data

Input into the lambda

Key Lineages
CU B.1.1.529.5.1.26
CV B.1.1.529.2.75.3.1.1.3
XA B.1.1.7,B.1.177
XB B.1.634,B.1.631
XC AY.29,B.1.1.7
XAZ BA.2.5,BA.5,BA.2.5
XBC BA.2*,B.1.617.2*,BA.2*,B.1.617.2*

Expected

Output from the lambda

Key Lineages
XA B.1.1.7 B.1.177
XB B.1.634 B.1.631
XC AY.29 B.1.1.7
XAZ BA.2.5 BA.5 BA.2.5
XBC BA.2* B.1.617.2* BA.2* B.1.617.2*

Note: There can be any number of lineages in the CSV cell

Solution

  • Updated

    =ArrayFormula(
 LAMBDA(a, {QUERY({a},"Select Col1"),SPLIT(QUERY({a},"Select Col2"),",")})
 (QUERY('Alias Key Raw'!A1:B, "select * where A starts with 'X'",1)))

    enter image description here

    Explanaition:

    using an Array {} to return:
    Col1: {QUERY({a},"Select Col1"),...}
    Col2: {...,SPLIT(QUERY({a},"Select Col2"),",")}

    Of the Query QUERY('Alias Key Raw'!A1:B, "select * where A starts with 'X'",1) found in the Lambda call named a

    Used formulas help
    ARRAYFORMULA - LAMBDA - QUERY - SPLIT