adding sheet name when importing multiple sheets using importrange

Question

I am importing multiple sheets into one using importrange and query function. Each sheet belongs to a different person. Is it possible to show which person the imported row belongs to?

I have:

=QUERY({importrange({"https://docs.google.com/spreadsheets/d/dsafdsf33/edit#gid=0"},"Original!A4:V");importrange("https://docs.google.com/spreadsheets/d/2sdfsad34543/edit#gid=0","Original!A4:V")},"SELECT Col2,Col5,Col4,Col7,Col9,Col12 Where Col8='AU'")

This is importing data from Bob and Steve. I would like to have the row (first column maybe) to say whether the row is being imported from Bob or Steve. Is this possible?

Are Bob and Steve two different Spreadsheets or are they two different sheets of the same spreadsheet? Most importantly, how would you want to show which person the imported row belongs to? Writing it in the cell next to the imported range? Thanks for the clarification. Also, if you could share a sample of your sheets that does not contain any sensitive information it could be really useful to clarify your question. — Mateo Randwolf

player0 player0 · Accepted Answer · 2020-03-20T14:42:59

without scripts it could be only hardcoded like this:

=ARRAYFORMULA(QUERY({
 {IMPORTRANGE("abc", "Original!A4:V"), IMPORTRANGE("abc", "Original!Z4:Z")&"Bob"}; 
 {IMPORTRANGE("xyz", "Original!A4:V"), IMPORTRANGE("xyz", "Original!Z4:Z")&"Steve"}},
 "select Col2,Col5,Col4,Col7,Col9,Col12,Col19
  where Col8='AU'"))

where Z4:Z range is an empty column

adding sheet name when importing multiple sheets using importrange

1 Answers