How can I combine a Google Sheets query formula with arrayFormula formulas?

Question

Code from @doubleunary's answer to: How can I improve the performance of this Google Sheets custom function?

My 'query' sheet has a query formula to filter an 'INPUT' sheet. The query is:

=query(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1)

The 'INPUT' sheet values are:

Instance Id	Group name	Group Id	Field name	Field Id	Type	Value	File Id	Role	Concatenated Id	VLookup value
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	100	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1A	100
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	200	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1A	200
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	300	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1A	300
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	110	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1B	110
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	220	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1B	220
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	330	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1B	330
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	1000	Co-PIlot_File	Co-Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance2A	1000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	2000	Co-PIlot_File	Co-Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance2A	2000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	3000	Co-PIlot_File	Co-Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance2A	3000

Which gives the following result on the 'query' sheet:

Instance Id	Field 1	Field 2	Field 3	File Id
Instance1A	100	200	300	Pilot_File
Instance1B	110	220	330	Pilot_File

Another 'RESULTS_PILOT' sheet uses two arrayFormula formulas to format the 'query' sheet values into a table with values placed in specific columns according to a 'TABLE_CONFIG' sheet. The formulas are:

cell A1:

=arrayformula( 
  { 
    "Instance Id", 
    iferror( 
      vlookup( 
        sequence(1, max(TABLE_CONFIG!C2:C4) - 1, 2), 
        { TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 
        2, false 
      ) 
    ), 
    "File Id" 
  } 
)

cell A2:

=arrayformula( 
  iferror( 
    hlookup( 
      A1:I1, 
      query!A1:E, 
      sequence(counta(query!A2:A), 1, 2), 
      false 
    ) 
  ) 
)

The 'TABLE_CONFIG' sheet is:

Field Id	Description	Desired table field column	Group Id
09456c1a-abb4-4e81-94bd-7ce4c88afffc	Field 1	1	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
474f6395-83a7-4c2b-aa5a-ceb00e200f8e	Field 2	3	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
ac64e001-fe85-400a-92e4-69cebf1c260d	Field 3	5	91c7db0a-c52a-407d-869a-af8ba8bf8ba7

So the table on the 'RESULTS_PILOT' sheet appears like:

Instance Id	Field 1		Field 2		Field 3	File Id
Instance1A	100		200		300	Pilot_File
Instance1B	110		220		330	Pilot_File

Is there a way to combine the query with the arrayFormula formulas on the 'RESULTS_PILOT' sheet, so that the 'query' sheet is not required?

It looks like you copied the code from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, you can [edit], include a [link](/editing-help#links) to the source, mention the author's name, and [quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Oct 06 '22 at 23:50

score 1 · Accepted Answer · answered Oct 07 '22 at 00:56

1

try:

=INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, 
 SEQUENCE(COUNTA(QUERY(x, "offset 1", )), 1, 2), ))}) 
 (QUERY(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1), 
 {"Instance Id", IFERROR(VLOOKUP(SEQUENCE(1, MAX(TABLE_CONFIG!C2:C4)-1, 2), 
 {TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 2, )), "File Id"}))

answered Oct 07 '22 at 00:56

player0

124,011
12
67
124

This works perfectly on the sample 'INPUT' data. However I should have explained that my real world 'INPUT' data is mixed format (Text, Number, Date, etc.). It is written to the 'INPUT' sheet via the API with valueInputOption set as "USER_ENTERED". The query formula seems to convert numbers to strings, which breaks subsequent calculations on the values. – blade_runner Oct 07 '22 at 14:05
@blade_runner https://i.stack.imgur.com/mG0hY.png – player0 Oct 07 '22 at 14:11

How can I combine a Google Sheets query formula with arrayFormula formulas?

1 Answers1

Linked