How to Add Row Index of each data row using Query() function in Google Sheets?

Question

Is there a way to get the row index using Query function like using row() function but inside a query?

Here is my query SQL. The query source are from 2 different sheets. So the row index should restart to 1 when the next source is being selected into the query.

={
                query(IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$3),
                        "select Col" &  ArrayFormula(textjoin(", Col",true,column('Static Data'!$A$22:$AC$22))) & ", 'BAU'
                        where 
                        Col"& 'Static Data'!$B$22 &" is not null
                        label 'BAU' 'SOURCE'", 
                1);
                
                query(IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$5),
                        "select Col" &  ArrayFormula(textjoin(", Col",true,column('Static Data'!$A$22:$AC$22))) & ", 'ICS'
                        where 
                        Col"& 'Static Data'!$B$22 &" is not null
                        label 'ICS' ''", 
                0)`}

EDIT (24/Aug/2021):

Final code that works looks like this after the suggestions from the comments below. I moved the rowId to the end of the table coz that's where I want it to be positioned.

=INDEX({QUERY({IMPORTRANGE('Static Data'!$B$2, 'Static Data'!$B$3),SEQUENCE(ROWS(
 IMPORTRANGE('Static Data'!$B$2, 'Static Data'!$B$3)))},
 "select Col1,Col"&TEXTJOIN(",Col", 1, 1+COLUMN('Static Data'!$A$22:$AC$22))&",'BAU'
  where Col"&1+'Static Data'!$B$22&" is not null
  label 'BAU''SOURCE'", 1);
 QUERY({IMPORTRANGE('Static Data'!$B$2, 'Static Data'!$B$5),SEQUENCE(ROWS(
 IMPORTRANGE('Static Data'!$B$2, 'Static Data'!$B$5)))+1},
 "select Col1,Col"&TEXTJOIN(",Col", 1, 1+COLUMN('Static Data'!$A$22:$AC$22))&",'ICS'
  where Col"&1+'Static Data'!$B$22&" is not null
  label 'ICS'''", )})

Does this answer your question? [Get row number with Google spreadsheet query language](https://stackoverflow.com/questions/38297864/get-row-number-with-google-spreadsheet-query-language) — Martí, Aug 23 '21 at 13:49

score 2 · Answer 1 · answered Aug 23 '21 at 21:08

={query({sequence(ROWS(IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$3))),IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$3)},
                        "select Col1, Col" &  ArrayFormula(textjoin(", Col",true,1+column('Static Data'!$A$22:$AC$22))) & ", 'BAU'
                        where 
                        Col"& (1+'Static Data'!$B$22) &" is not null
                        label 'BAU' 'SOURCE'", 
                1);
 query({sequence(rows(IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$5))),IMPORTRANGE('Static Data'!$B$2,'Static Data'!$B$5)},
                        "select Col1, Col" &  ArrayFormula(textjoin(", Col",true,1+column('Static Data'!$A$22:$AC$22))) & ", 'ICS'
                        where 
                        Col"& (1+'Static Data'!$B$22) &" is not null
                        label 'ICS' ''", 
                0)}

@plaridel1 I'd appreciate if you mark this one as the answer — GoranK, Aug 24 '21 at 19:02

score 1 · Accepted Answer · answered Aug 23 '21 at 21:21

try more compact:

=INDEX({QUERY({SEQUENCE(ROWS(
 IMPORTRANGE('Static Data'!B2, 'Static Data'!B3))), 
 IMPORTRANGE('Static Data'!B2, 'Static Data'!B3)},
 "select Col1,Col"&TEXTJOIN(",Col", 1, 1+COLUMN('Static Data'!A22:AC22))&",'BAU'
  where Col"&1+'Static Data'!B22&" is not null
  label 'BAU''SOURCE'", 1);
 QUERY({SEQUENCE(ROWS(
 IMPORTRANGE('Static Data'!B2, 'Static Data'!B5))),
 IMPORTRANGE('Static Data'!B2, 'Static Data'!B5)},
 "select Col1,Col"&TEXTJOIN(",Col", 1, 1+COLUMN('Static Data'!A22:AC22))&",'ICS'
  where Col"&1+'Static Data'!B22&" is not null
  label 'ICS'''", )})

thank you for the additional compact code! – plaridel1 Aug 24 '21 at 09:44 — plaridel1, Aug 24 '21 at 09:44

How to Add Row Index of each data row using Query() function in Google Sheets?

2 Answers2