It would be quite a hassle if we have to combine sheets upon sheets of data into a single sheet just to run queries to it, right? Fortunately, it is possible to query multiple sheets in a single line in Google Sheets. This tutorial will teach us a simple trick to do so. Before we start, take note of this advice:
Make sure that these sheets contain the same types of columns so we can take advantage of this trick.
Are you ready? Let’s go!
Again, let us go back to the basic format of a QUERY command in Google Sheets:
=QUERY(source_sheet, "sql_query_commands", headers)
To combine two or more sheets as the source, list the sheet ranges, separated by semicolons without spaces, enclosed in curly braces {}. For our example, we want to combine week27 and week28:
=QUERY({week27!A1:H633;week28!A2:H634})
Running this command alone will combine the two sheets together, which is another trick in combining the contents of sheets without the manual copy-paste method. Google Sheets will automatically insert more rows at the bottom when needed, but it takes some time.
If the sheets to be queried are located in a different Google Sheets workbook, you will need to use the IMPORTRANGE function. It has the following function:
=importrange("url_of_workbook","data_range")
Note that both the URL and the data range should be enclosed by quotation marks. You can insert this function inside a query function, so the formula will look like this:
=QUERY( {importrange("url_of_sheet1","data_range");importrange("url_of_sheet2","data_range");importrange("url_of_sheet3","data_range")}, "sql_query_commands")
The workbook must be either a public workbook or that you have access to it. If the workbook you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access.
Afterward, the data will be loaded.
If the sheets are located in the same workbook (thus having the same URL), they still need to be added through the IMPORTRANGE function individually.
How about inserting queries? Queries will still work, with a little change in how we call the columns. Instead of referring to the column letters, we will use Col1, Col2, Col3, and so on. Let’s say we want to list the date, total sales, and profit. These are located in columns 1, 5, and 8, so we will call on Col1, Col5, and Col8:
=QUERY({week27!A1:H633;week28!A1:H634}, "select Col1, Col5, Col8")
The result will look like this:
While this lets us analyze large chunks of data much easier, it will take us a lot of time to do so. Maybe there is a better way so that all the analysis is automated and we will just have to make important decisions for our business...
If that’s what you fancy, consider trying Lido. With a few clicks, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!
