Google Sheets Functions – IMPORTRANGE

Google Sheets has a wonderfully useful function called IMPORTRANGE. So what does it do? It allows you to connect different spreadsheets and import data from one to another. The most basic example would be to connect one sheet with another sheet and import some data from the first sheet to the second.

functions1-40

What’s important is that if you change the data in the first sheet, it’s automatically updates in the second sheet.

Other uses

Not only can you connect 2 spreadsheets, but you can connect multiple spreadsheets with a master sheet. This can be useful if, for example, you only want someone to see part of the data you have on the master sheet, so you share the individual sheet or range with them.

Either the master is updated and the information is sent out to the individual sheets.

functions1-42

Or you can have the opposite, users update the individual sheets and the information is sent to the master sheet.

functions1-41


How to use it

Let’s start with a simple example showing how you can connect different spreadsheets.

I have a sheet, linked to a questionnaire form, where the responses from our student questionnaires are stored.

functions1-43

The students are asked questions about the teacher and also about the administration of their course and the service we provided during sign up, etc. I want to share the information about the admin process with reception but I don’t want to show them the personal information about the teacher. So, what I can do is, create and share a sheet with reception and import the relevant information for them.

First I create the sheet and share it with reception.

Then in the shared file, I type the IMPORTRANGE function. As with all functions, it starts with the equals sign, then the function name. Then open the brackets.

At this point you will see the useful help box appear, which gives the content of the brackets it’s expecting, an example of the function filled out, a summary of what the function does, plus some extra information, for example, in this case, what is the “spreadsheet key”.

Note, if you click on “Learn more about IMPORTRANGE” this takes you to the Google help page for that function, which will give you more information on how the function is used. In here it states that you should use the full URL and not the spreadsheet key. However, in my experience both work.

functions1-1

As the help box states, you need to add the ‘spreadsheet key’. This is found in the URL of the file. It’s the mixture of numbers of letters after the /d/  and before /edit. This is telling the Google Sheet which file it is, as every file has a unique key.

functions1-3

Copy this and then back on the shared file, add speech marks then paste the key in and add speech marks again. It won’t work without the speech marks.

functions1-2

Now we need to tell it where the data we want is in that file. So in our function, we first add the sheet name then the range. The sheet in this case is called “Questionnaire”.

functions1-8

So, we start with a comma after the key we’ve just added, then speech marks again, then the name of the sheet followed by an exclamation mark, which tells Sheets this is a reference to a sheet.

functions1-5

Looking back at the original file, we can see we want the first 3 columns A to C and down to row 8. Columns D and E are about the course and the teacher.

functions1-4

Finally, we add the range we want, A1:C8 and end with speech marks. then press Enter.

functions1-5

The first time you try to link the sheet, the #REF! error will appear. Don’t worry, it’s just reminding you to do something. Hover your mouse over the cell, and you’ll see the box below appear asking you to “allow access”. This is a security measure and is always necessary the first time you share the file. Just click the blue button to link the files.

functions1-9

The contents of the range above will appear in the shared file. Note, that this only imports the content, i.e. the values of the cells, and not the formatting.

functions1-6

So, sometimes you’ll have to adjust the cells accordingly.

functions1-7

And that’s it! The two sheets are linked and if I make a change on the original sheet, it’ll update the shared one automatically.

Depending on the amount of information being imported, sometimes there is a slight delay in the second sheet updating. That’s normal.

I of course do the same for the teachers and share just the ranges that apply to them.


Open-ended ranges

In the example above, we are only sharing a fixed range (A1:G8), but what happens if we receive some more questionnaire feedback and have data that is in rows 9 and above? The range we used above won’t show us that data, so here we will need to use an open-ended range.

Let’s slightly change our function at the end. Change the range from A1:C8 to A:C. As we don’t state the row numbers, the range is in effect going from row 1 to infinity (well until the maximum number of rows).

functions1-9-1

Here we’ve received two more pieces of feedback in row 9 and 10 in the original file.

functions1-12

And automatically it appears on the shared admin file, without having to update the range. Note, I added the green colour just to highlight the new data added.

functions1-13


Multiple IMPORTRANGES on the same sheet

So far, we’ve seen one range from one spreadsheet being imported into another spreadsheet. We’re not limited to just one, if we wanted we could import hundreds on the same sheet.

Let’s look at another example where I have some details about two teachers on two different spreadsheets. They have access to their specific file and I want to combine this information in a master sheet, so the information for both teachers appears on one page.

First, we have the information in one file, on a page called Teacher 1.

functions1-15functions1-16

Second, we have the information about the other teacher in another file, on a page called Teacher 2.

functions1-17functions1-18

Each teacher only has access to their particular file to ensure privacy.

Now, I want to add them to a master sheet. In cell A1 I type in the first IMPORTRANGE function as below. So, exactly the same as above, I get the spreadsheet key of the first spreadsheet, then I refer to the ‘Teacher 1’ sheet and the range ‘A1:B5’.

functions1-19

As always I may need to tidy up the formatting, for example, here the column width and the alignment.

functions1-20

Now in cell C1, I add the second IMPORTRANGE function. I include the key from the second spreadsheet, refer to the page Teacher 2 and this time, I only need the column B.

functions1-21

I could continue adding teachers like this by adding another IMPORTRANGE function to D1, E1, etc.

functions1-22

If I’m expecting further information to be added, for example, qualifications to be updated, and therefore, extra rows added, I could of course use an open-ended range for each of the functions. For example, in the case of the first one the range would be A:B not A1:B5.

One important thing to note, is that you cannot write anything within the imported range. If you do, the data disappears and you get the #REF! error as you can see below. Here I typed the word “text” in cell C5 and that has caused the second teacher’s details to disappear. so, you cannot edit the imported information, only the original data can be edited.

functions1-23


Pre-formatting an imported range

In the above examples, we just had text and the imported range didn’t look that good, and we had to format the cells afterwards. Let’s look at how we can maintain the format of the original data in our new imported range.

Here I have the attendance for two classes (A & B) in two separate files and I want to add them into one file but with a tab for each class. This time the data is formatted in various ways, e.g. background colours, font size, bolding, conditional formatting, percentages, alignment. To reformat this for each imported class, would be a real pain. So, it’s far better to set up the destination file with the same formatting, so when the data arrives you don’t need to do anything.

functions1-29

functions1-36

Here we’re going to do two key things:

  1. Use “Copy to…” to copy one of the original sheets to the destination one.
  2. Use “duplicate” to create a copy of a blank formatted sheet.

First, from the Class A file, click on the sheet tab arrow and select “Copy to…”.

functions1-25

You then need to find and select the file you want to copy to. I often use click on “Recent” as usually the file I want has been worked on recently, so will appear near the top of the list.

functions1-26

Click on the file and click “Select”.

functions1-27

You’ll get a confirmation message and if you want to go to the destination file, click on “Open target workbook”.

functions1-28

Here it’s added a new tab called “Copy of ClassASheet”.

functions1-37

As you can see, it’s identical to the original sheet.

functions1-29

Now select all the cells and press delete.

functions1-30

This will leave you with a pre-formatted blank.

functions1-31

Now as we have another class we want to import and I want to put it on a different sheet, click the sheet tab arrow and this time select “Duplicate”. This will make a copy of the blank formatted sheet.

functions1-32

Now all we need to do is add our IMPORTRANGE functions. I add class A to the first sheet typing the function in cell A1 (although it could in fact be in any cell).

functions1-33

As you can see it formats it perfectly automatically.

functions1-34

Then I add class B.

functions1-35

functions1-36

So, now I have both class A and B in the same file, which saves me opening different files to see the information. It also allows me to analyse the different sets of data within the same file, for example, maybe I want to produce a report for the attendance of all students, this can easily be done within the same file.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s