Importing Range Data From One Google Sheet to Another

One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google Sheet that you have imported the data to.

Everything starts with the IMPORTRANGE Google Sheets function.

But before we get started on the “How to’s,” you might be wondering why you might need to import live Google Sheet data from one sheet to another.

Or check out the table of contents to dive straight into what you need right now!

Why is importing live range data into another Google Sheet useful?

Consistency

Manually, duplicating the same data in different locations is really a gateway to creating extra work and more errors in your Google Sheets.

I have used IMPORTRANGE to import a list of staff members into a tonne of other Google Sheets projects. When a staff member is promoted or moves to a different section of the company or if the company gets a new hire or the company loses a staff member, then we would have to update all the locations where we have a list of staff.

Or we could create a core staff list Google Sheet and reference that sheet in all of our other related documents with IMPORTRANGE. Then we only have to update the core staff sheet and all the other connected sheets will update with it.

Specificity

Let’s say you have a Google Sheets master workbook containing all the data and analysis for your project. This might be fine to share with head office but, you might not want to share everything in your master spreadsheet with every team.

For example, imagine you have created a sales profile for a national company where you have inputted all the sales data for all regions. This is updated regularly and you want your regional teams to only see how they are progressing and not the other teams.

You can use IMPORTRANGE combined with a function like VLOOKUP, QUERY or FILTER to provide a specific version of your data for each of those regions. As the master sheet is updated, so too are the regional sheets. Without any extra work.

In a recent example, I have used IMPORTRANGE to get ‘hours worked’ data from a master Project Task Manager while keeping other notes and data from my Project Task Manager private.

Fluidity^*

I’ve alluded to fluidity already in the above examples and introduction. I think the is really the powerhouse of using IMPORTRANGE in Google Sheets. Whenever that master sheet changes all the child sheets with your import range data will change along with it.

When I was working as a university admin, I used this to create a Gradebook sheet similar to the one you can find in my course here for each of the classes my lecturers would teach. I would then use FILTER to reduce the child sheet for each course for teachers to review the grades of only their students for their course. As new grades were added to the master sheet, so too were the class sheets were updated automatically.

* Really had to wrack my brain for an ‘-ity’ word to explain the last one.

Now we have a few practical uses in mind, let’s get into how it is all done.

Basic import range

Sample Import Data Sheet

It’s usually a good idea to follow along with new concepts to help reinforce your learning. If this is the plan for you, click on the link below to access the sample Google sheet to import data from.

We will be using sample data from the Project Task Manager Sheet below:

Project Tasks Manager – Google Sheet

Simply go to File > Make a copy. For your very own copy of the Google Sheet.

IMPORTRANGE

What we will do here is import all of the data in the Google Sheet tab labelled ‘Task List’ in our sample Project Task Manager Sheet into a new Google Sheet.

We can do this with the IMPORTRANGE function.

You can import data from any Google Sheet you have View, Comment or Edit access to. However, if you are removed from this sheet by the owner, any sheet that you have imported data into will lose its access also.

Open up a new tab in your browser (Ctrl + t) and create a new Google Sheet and name it something like ‘IMPORTRANGE example’.

IMPORTRANGE takes two arguments:

1. The URL or Google Sheet ID of the spreadsheet you wish to import.
2. The Sheet tab and range that you wish to import.

Grab the google sheet reference

In cell A1, type in =IMPORTRANGE(. Don’t worry about closing the braces yet. Head over to the Project Task Manager example sheet and select the URL (Note! Your copy of the sheet will have a different URL if you are using your own copy).

Click on the URL and then Ctrl (Cmd for Mac) + a to select all and Ctrl + c to copy the URL.

Head back to your IMPORTRANGE formula. Click on the formula cell and then in the formula bar click just after the left brace and add a double quotation mark ". Next, use Ctrl + v to paste in the URL. Finally, close the URL with another double quotation mark, ".

You can alternatively just add in the Google Sheets ID instead of the full formula. You can find the ID in the URL after the https://docs.google.com/spreadsheets/d/ and before /edit.

Selecting the range

Next, add a comma to insert our range argument.

We want to grab all the data including the header from the ‘Task List’ sheet tab. This data starts in cell A2 and across to column G. Enter the following:

"'Task List'!A2:G"

Note that when a sheet tab title has a space between words in it we need to enclose it in single quotation marks. At the end of the sheet tab name, we add an exclamation mark and then our range.

You can see that the end range does not have a row number. This is a handy way in Google Sheets to keep the row depth flexible. If more data is added to the sheet then the range will expand to accommodate it.

Your new Google Sheet should now look like this:

Using the following complete formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Av1N4H1MKJIJXD88Ia7F-uEm3ZXN1HZEhmq8zpk_fRA/","'Task List'!A2:G")

The IMPORTRANGE #REF error

If when you have entered the IMPORTRANGE function into your Google Sheet cell and you get a #REF error, hover over the cell and select ‘Allow access’. This will give the current sheet permission to share the data from your original source sheet in the current sheet.

Why do I have to do this? Why does this only occur sometimes?

This occurs when you are importing data from a Google Sheet that has only permission access to a select few users by email account rather than selecting anyone in your domain or on the internet can ‘Edit’, ‘Comment’ or ‘View’.

You can  find out more about sharing Google Sheets here:

Google Sheets Beginners: Sharing your Sheet (20)

Note! When you allow access you are not giving actual access permission to the originally imported document. You are only giving permission for its data to be shared on the current Google Sheet.

Formatting

You can now format the Google Sheet range how you like.

Alternatively, if you want to maintain the formatting of the imported data we can head back to our source data Google Sheet, in our case ‘IMPORTRANGE – Example’ and select from A2 across to G2 and all the way down to the bottom. Ctrl (Cmd for Mac) + c to copy .

Then head back to your new Google Sheet and right-click cell A1 > Paste special > Format only. Now you have duplicated the formatting in your new sheet.

You can learn more about specialised copying and pasting in Google Sheets here.

The video tutorial

Check out my video tutorial on IMPORTRANGE here:

Conclusion

IMPORTRANE is an extremely useful tool to add live data from one sheet into another.

You can combine other Google Sheets functions along with import range to create some impressive things to extract specific information from your source data to be used in your current Google Sheet.

There are, however, some pitfalls. IMPORTRANGE will not prevent clever editors from accessing other ranges of your imported document. Well, unless you have a little secret workaround (see below😉). Also, importing huge amounts of data will start to cause problems with your IMPORTRANGE function and it is not encouraged.

We will be covering these points in the next chapters of this series. Subscribe (Top right) to get a notification when the next chapter is available.

Up Next!

• Google Sheets IMPORTRANGE: How to prevent clever editors from accessing other ranges of your imported document.
• Google Sheets IMPORTRANGE: Using VLOOKUP on imported Google Sheets data.
• Google Sheets IMPORTRANGE: Using FILTER and QUERY on imported Google Sheets data.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.