The Data We Need
For this demonstration, the data needed for the report is in a file named “HR Information” which has two tabs: “HR 2010” and “HR 2020”.
“HR 2010” has people with entries dated till the end of 2010 while “HR 2020” has people with entries dated till the end of 2020.
The objective is to bring the data from these two sheets into a new Sheets file. The goal is to have the data from these two sheets appended (i.e., stacked) atop one another, forming a single table.
Before we tackle the appending process, we need to step back and solve the problem of importing a single data source.
Featured Course
Google Sheets – The Comprehensive Masterclass
Importing Data with IMPORTRANGE
The IMPORTRANGE function allows you to point to another file and extract data from a defined range.
The syntax for the IMPORTRANGE function is:
IMPORTRANGE(spreadsheet_url, range_string)- spreadsheet_url – The URL of the spreadsheet from where data will be imported. The value for spreadsheet_url must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
- range_string – A string, of the format “[sheet_name!]range” (e.g., “Sheet1!A2:B6” or “A2:B6”) specifying the range to import. The sheet_name component of range_string is optional; by default, IMPORTRANGE will import from the given range of the first sheet. The value for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
NOTES:
Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE.
The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet. The access remains in effect until the user who is granted access is removed from the source.
If the data you are trying to import is too large, you may get an error.
Acquiring the Source File’s URL
When working with other Google Sheets files, it is necessary to obtain the URL (Uniform Resource Locator) of the source files to inform IMPORTRANGE as to the location of the data.
With the source file open, highlight and copy the ULR visible in the Address Bar at the top of the browser window.
NOTE: The “edit…” information after the final “/” is not needed by the IMPORTRANGE function.
Writing the IMPORTRANGE Formula
To write the IMPORTRANGE formula, select a cell and type…
=IMPORTRANGE(The first argument is the URL copied from the previous step. Remember, the URL must be enclosed within a set of double-quotes.
=IMPORTRANGE(“copied URL goes here”,The second argument is the source sheet’s data location. This information must also be enclosed in double-quotes.
If you do not mention a sheet name, the default is to select the data from the first encountered sheet in the file.
If we wished to import all data from columns A through C, we would write the following…
=IMPORTRANGE(“copied URL goes here”, “A:C”)By leaving off the row number references we ensure we return all rows with data. If you needed to start at a row deeper in the sheet and stop at a specific row, you could write the formula as follows.
=IMPORTRANGE(“copied URL goes here”, “A10:C100”)Asking for Permission
If this is the first time connecting to the data source, you are likely to be prompted to ask for permission to extract the data.
Select the cell and click “Allow Access”. The data is imported and ready for further processing.
Selecting a Specific Sheet
If the needed data is not on the first sheet, or if the target sheet is subject to movement within the workbook, it will be necessary to list the sheet’s name along with the cell range reference.
=IMPORTRANGE(“copied URL goes here”, “HR 2010!A:C”)
If we were to change the sheet name reference from “HR 2010” to “HR 2020”, we see different data returned.
Improving the Sheet Selection Step
To make this process a bit more dynamic, we could add a Data Validation drop-down list that allows the user to select the desired sheet in the source file.
For this, select a cell (ex: cell E2) and click Data -> Data Validation.
In the Data Validation dialog box, for the Criteria select “List of Items” and enter “HR 2010,HR2020” as the list items. We will also disallow the user from entering their information by selecting “Reject Input”.
Updating the IMPORTRANGE Formula
To use the user’s sheet selection, we modify the formula to read as follows.
=IMPORTRANGE(“copied URL goes here”, E2 & “!A:C”)While we’re at it, let’s make the formula a bit simpler by placing the source file’s URL in a cell which we will reference. We’ll place the URL in cell E1.
=IMPORTRANGE(E1, E2 & “!A:C”)
By referencing the URL you can make your source file dynamic. You could allow users to select from a list of files, at which point the needed file URL is located (probably by way of a lookup function.)
Testing the Dynamic Drop-down
If we change the drop-down from “HR 2010” to HR 2020” we see the new data returned based on the desired sheet.
Appending the Results of Both Sheets
Suppose we want to append/stack the results of both sheet’s data into a single results list?
Using a bit of array notation, we can combine the results of two IMPORTRANGE formulas.
With array notation, two important characters help define array dimensions:
- Semi-colon – this moves to the next row
- Comma – this moves to the next column
By writing two IMPORTRANGE formulas and connecting them with a semi-colon, we can have the results of the second formula placed below the results of the first formula.
To keep things simple, we won’t import the entire columns-worth of data, only rows 1 through 20.
=IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”)
What happened?
We can’t just enter formulas like above when creating an array of formulas, we need to enclose the formulas in a set of curly-braces.
={ IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”) }
Just for Fun
To demonstrate the array characters more completely, if we update the formula by replacing the semi-colon with a comma, we see the results placed side-by-side.
Why the Gap?
Notice the gap between the result sets.
This is because we have indicated that we wish to return rows 1 through 20 for each data set regardless of whether all rows contain data.
We could refine the formula to only return data from the occupied range, but this would limit our flexibility when adding rows to the source tables. We would need to constantly refine our formulas to reflect the current dimensions of the data.
To give us this flexibility, we will combine our forces with the QUERY function.
Using QUERY to Eliminate Empty Rows
Although the QUERY function is simple to use, it does require a bit familiarity of the QUERY notation to operate properly.
I have a post about the QUERY function that you can read or watch to gain deeper insight into the QUERY function, but for now, we will just write what we need without diving too deeply into the notation’s meaning and uses.
Google Sheets QUERY Function Explained
We start by preceding the previous formula with a reference to the QUERY function and defining the source of the data.
=QUERY{ IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”) },Next, we add the query logic.
“Select * where Col1 is not null”A high-level explanation of this is…
“Select all columns where the rows of the first column are not empty.”
NOTE: We can’t say col “A” when using query notation; we must reference the column’s index position within the data. In our case, column “A” is referenced as “Col1”.
The update formula appears as follows.
=QUERY( { IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”) }, “Select * where Col1 is not null”)
The results are the two ranges of data stacked atop one another without the blank rows.
Because we captured data starting on row 1 for both IMPORTRANGE functions, we have unfortunately created duplicate headings in the data.
We can solve this problem in a couple of different ways.
- Rewrite the formula to only start on row 1 of the first IMPORTRANGE function, but start on row 2 for all remaining IMPORTRANGE functions.
=QUERY( { IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A2:C20”) }, “Select * where Col1 is not null”)- Manually create a heading row and start all IMPORTRANGE functions at row 2.
=QUERY( { IMPORTRANGE(E1, “HR 2010!A2:C20”); IMPORTRANGE(E1, “HR 2020!A2:C20”) }, “Select * where Col1 is not null”)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
