CODEWITHSUNDEEP
jquerygoogle-sheetsrow-number
Sam Nichols
Sam Nichols

Reputation: 11

Returning the row number for a query to a different Google sheet

I am trying to retrieve the row number along with multiple cell data from a different sheet than the one I am in. I have the query that returns all of the column values I need but I can't seem to figure out how to get the row number to come with it.

This is my equation

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abunchofnumbersandsuch/","'2018 Data Log'!B2:O"), "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 where Col4 contains '"&'Data Entry'!C4&"'", 0)

it queries a different sheet based on the value from the "Data Entry" sheet in the current "workbook?" I'm an excel guy so not sure if that's the correct syntax. I have tried placing row() different spots in the equation but I just can't seem to get it to work.

example =ArrayFormula(QUERY(ROW(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abunchofnumbersandsuch/","'2018 Data Log'!B2:O"), "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 where Col4 contains '"&'Data Entry'!C4&"'", 0)

Upvotes: 0

Views: 381

Answers (1)

doubleunary
doubleunary

Reputation: 18784

The importrange() function gets an array rather than a range. Row numbers can only be retrieved from a range. In order to copy data with row numbers, you must store those row numbers next to the data you are importing before you import, or recreate the row numbers using some logic.

If you cannot modify the source spreadsheet, one easy workaround would be to import all of the data to a new 'Import' sheet in the target spreadsheet, add row numbers there, and then use query() or filter() to aggregate the data the way you need. You can import the data with this formula in cell Import!A1:

=importdata("abunchofnumbersandsuch", "2018 Data Log!A1:O")

Then point query(), filter(), row() at the 'Import' sheet. To more easily access row numbers, use filter() instead of query(), like this:

=filter( 
  { Import!B2:O, row(Import!B2:O) }, 
  Import!E2:E = 'Data Entry'!C4 
)

In the event you are copying data from one sheet to another within the same spreadsheet file, you should not use importrange() in the first place.

See filter().

Upvotes: 0

Related Questions