“How do I get the numbers off this spreadsheet onto this other spreadsheet?” she gave me with a quizzical look. “If I copy and paste, I get the weird #REF! error.” Jessica had spent a lot of time working on several critical Google Sheets. Unfortunately, because of who she had to share each Google Sheet document with, she couldn’t put all the data together in ONE document to share. She wanted to have all the data flow into ONE Google Sheet so she could do some spreadsheet magic!
As you may know, Google Sheets is a free, online, collaborative spreadsheet. It features functions, formulas, mail merge, and allows you to easily insert images, GANTT charts, and more. In this blog entry, please allow me to share a few common challenges of Sheets and tips for overcoming them. In spite of Google Sheets being around for several years, these questions continue to come up.
Why Use Import Range?
Have you ever had to pull data from two or more spreadsheets into another? You may wonder why this would be useful. One reason is that you can share your third spreadsheet with View or Edit rights with another person. This allows them to manipulate the data on the third spreadsheet. However, the original data from the first two or more spreadsheets is protected and data cannot be changed. One way I have used it involves publishing the link to the third spreadsheet to the Web. This means I can then give that link to anyone for viewing without having to grant access. And, when I update the data in the spreadsheets where the original data is saved, it will be updated in the published sheet. Isn’t that amazing?
The process to accomplish this is straightforward but can trip folks up. Be sure to take the actions listed below to successfully complete the process. You’ll also find a short video tutorial that illustrates the import range action.
Note: You can access the Google Sheets used in this blog entry online.
The Import Range Process
To solve Jessica’s problem, we need a quick way to import data from another Google Sheet into the current one. One way to achieve this is to use the IMPORT RANGE function. Before we get to that, though, let’s take a few actions.
Action #1 – Take the time to name your Google Sheets document with a single word or two words put together.
Use a one word label for your sheet, avoiding spaces if you must use more than one word. This may help eliminate confusion later. In the example (blue highlighted text) below, you see I have combined two words into one, Inventory Costs.
Action #2 – Take the time to rename your Google Sheets sheets.
That is, instead of relying on Google’s default naming scheme of “Sheet 1,” then “Sheet 2,” etc., rename them. Use a one-word label for your sheet, avoiding spaces in the name. Below is the screenshot of the traditional way Google Sheets names the sheets. Note the yellow highlighted section indicates the sheets that need to be renamed.
Here’s the same Google Sheet with sheets renamed (highlighted in yellow):
Action #3: Make a note of the document ID # for each of your Google Sheets documents.
This document ID # is an identifier code for the Google Sheet. You can find it in the web address (a.k.a. URL) bar, as shown below. Since the whole address is usable as a part of the ImportRange function we will be using, I’ve highlighted (yellow) the entire URL from http:// up to the end of the identifier code.
Our goal will be to grab data from both the InventoryCosts spreadsheet and the CapitalOutlay spreadsheet, then put them into the Reports Google Sheet. One way to map this out is to get it to look like the image below. Note that both spreadsheets will contribute data to the Report Google Sheet.
Action #4: Grant permission for ImportRange to work.
The Import Range function is straightforward except for one additional step. This additional step will only happen the first time you pull data from one Sheet to another. Google support describes it in this way:
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 granted access is removed from the source.
The video tutorial in Action #5 will highlight the process of granting one-time permission.
Action #5: Use the ImportRange function to combine the data from two or more Google Sheets into one.
Ready to combine the data from two Google Sheets into one? Watch this video to see how to do it.
Action #6 – Share the link (optional).
Now that your source spreadsheets (in this example, InventoryCosts and CapitalOutlay) are feeding their respective totals into a third spreadsheet (Report), you may want to take one more step. That step is to publish your Report Google Sheet to the web so anyone can see it. This is for view only access, of course. In the highlighted image below, you can see that Link Sharing is turned on, and “Anyone with the link can view.”
Bonus Tip:
Give Alice Keeler’s AnyoneCanView Chrome extension a try. It makes sharing easier. “Instantly change the sharing permissions of a Google Docs/Sheets/Slides/Drawing to “Anyone with the link can view.”
Ready to Give It a Try on Your Own?
Here’s a quick overview of the steps.
- Login to G Suites for Education (GS).
- Create Folders in Google Drive.
- Rename spreadsheet tabs.
- Find the unique code that identifies each GS. You will need the code for your spreadsheets.
- Use the formula to import data from multiple Google Sheets into one. Items in red must be present, while you supply the items in green.
=importrange(“unique_webaddress_with_documentIDcode“,”GoogleSheet_tabname!A1:S36“) - Place the formula in a new Google Sheet.
- Make sure to allow access for each Google Sheet you are connecting. One time permission.
- Publish your spreadsheet to the web as a link.
- Share your spreadsheet (all tabs or just one) as an embedded document on a web page.
Wasn’t that easy?