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
– Mateo Randwolf, Commented Mar 20, 2020 at 14:36

player0 · Accepted Answer · 2020-03-20 16:19:33Z

2

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

edited Mar 20, 2020 at 16:19

answered Mar 20, 2020 at 14:42

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tarik Hodzic Over a year ago

I changed Z4:Z to AB4:AB to match the first empty column. I am getting formula parse error.

player0 Over a year ago

@TarikHodzic fixed, try now

Collectives™ on Stack Overflow

adding sheet name when importing multiple sheets using importrange

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related