Search code examples
google-sheetsgoogle-query-language

Is it possible to stack Google Sheet columns from multiple sheets into one report?

My company isn't allotting funds for a strategy planning tool like Aha! so I'm making my own using google sheets. One of the requirements I need is to aggregate progress estimations from child initiatives, and include it in the parent initiative estimation, and the easiest way I've found to do this is to import all workspace progress percentages into a single data dump on the parent sheet since AVERAGE() doesn't work with IMPORTRANGE(). I'm working with 3 sheets, and will expand to include more. Sheet1 is the Parent Line, Sheet2 and Sheet3 are workspaces and aggregate up to the parent line.

I'm trying this Query(IMPORTRANGE()) combination, but it's only giving me the column from the first sheet, and not included the column from any subsequent sheet that I've identified in Query():

=QUERY(
{
    IMPORTRANGE("[sheet2 URL]","Meta Data!$E$2:$F$900"),
    IMPORTRANGE("[sheet3 URL]","Meta Data!$E$2:$F$900")
},
"select Col1, Col2
where Col2 is not null",
-1)

The expected result is:

Expected

But the actual result is:

Actual

All information from sheet2 and sheet3 are supposed to stack in those columns as one might expect with a SQL query, but it seems google query is only giving me the first table in my query and ignoring the second table.

Is it possible to query two tables and stack the column information?

Solution

  • in array {} use semicolon ; to stack stuff under each other:

    =QUERY(
 {IMPORTRANGE("[sheet2 URL]","Meta Data!$E$2:$F$900");
  IMPORTRANGE("[sheet3 URL]","Meta Data!$E$2:$F$900")},
 "select Col1, Col2
  where Col2 is not null", -1)