CODEWITHSUNDEEP
google-docsgoogle-appsgoogle-apps-script
rtyshyk
rtyshyk

Reputation: 971

Google spreadsheets Query Language get data from many other sheet

How I can get data from many sheets? In documentation Reference data from other sheets is an example

=Sheet1!A1

I wrote test query and it's works

=Query(otherSheet!A1:P999, "select A, B, C")

But I want get data with one query from many tables. is it possible? How I can?

---UPDATE---

For example I have 2 sheet as clients and orders, and I want get username who buy some product last month. I found solutions, for example I get cient_id from order table, and then I get username from clients table

=Unique(Query(orders!A1:P999, "select D WHERE I = 'paid' and J > date '2010-01-15' LABEL D 'client_id' "))

=vlookup(A2,contacts!B:C,2,false)

You can write it with single query?

Upvotes: 1

Views: 5028

Answers (1)

Guy
Guy

Reputation: 12891

You need to be more specific to be able to get a good answer, but I will try to give you the directions that I can think of.

You can join your data physically and create a single sheet with all the data. You don't have neither to copy the data nor refer it (using =), as you can use EXPAND function:

=EXPAND(SORT(Sheet1!A:C))

Another option is to join the criteria that you want to have on each sheet using ARRAYFORMULA; something like:

=ARRAYFORMULA((SheetX!A2:C3>5)*(SheetY!A2:C3="Category A")*(A2:C3))

--- UPDATED ---

For your updated function try something like:

=ARRAYFORMULA(VLOOKUP(
  (orders!I:I="paid")*(orders!J:J>"2010-01-15")*(orders!D:D),
  contacts!B:C,2
 ))

You can add the UNIQUE function as well right inside the VLOOKUP, as well as other functions as SORT.

Note that you can also replace the values of the query ("2010-01-15", for example) with values in cells (=E2, for example), instead of having hardcoded values in the function.

Upvotes: 3

Related Questions