This post originally appeared on the Coalmarch blog in 2015 (Coalmarch is a former employer of mine). They were going to remove it, but asked if I wanted to move the post to my site. So I did! The following is the original blog post, with one new section at the end if you want to get extra fancy.
———————————————————————————————
We use the “IMPORT RANGE” function in Google Sheets frequently. It’s crucial when trying to work with the behemoth of admin documents we have. Luckily, it’s a pretty straightforward function. All it does is pull in a cell from another sheet into the sheet we’re working in. But, there’s one problem with it: if you’re working with a big document and you want to fill the function down or across, it doesn’t work. Luckily, I found a solution!
First, in case you don’t use it, let me show you how it works.
The IMPORT RANGE Function
Google will tell you that the function looks like this:
=IMPORTRANGE(spreadsheet_key, range_string)
Here’s what that means:
spreadsheet_key: Every Google Sheet has a URL that looks something like this (“https://docs.google.com/spreadsheets/d/1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_…“). The spreadsheet key is that string of gibberish after the /d/. So, in the case above, 1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0.
range_string: This is just the cell from the target sheet that we’re looking to import into our working sheet. We’ll need to include the sheet name and the cell name. So, if we want to pull what’s in cell A1 from our target sheet called “New Document”, we’d just use the standard notation of New Document!A1.
Putting it together, this is what our function would look like (notice that both parts of the function have to be surrounded with double quotes):
=IMPORTRANGE(“1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0”, “New Document!A1”)
That’s it! So here’s an example. I have three documents I’m working on. Test Doc 1, 2, and 3. Doc 2 has client contact name information in it, Doc 3 has client location information in it, and I want Doc 1 to be a hub that has both contact name and location information in it.
We could manually copy and paste the info from 2 and 3, but if we change those frequently, we may want to have it dynamically pull that information. So, we use IMPORT RANGE.
So let’s grab the “Contact Name” info from Test Doc 2. To do so, I went to Test Doc 2 and grabbed the spreadsheet key from the URL, the sheet name, and note that I want to pull the first contact name, which is at cell B2.
So my formula in cell B2 of Test Doc 1 looks like this:
=IMPORTRANGE(“1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0”, “Contact Info!B2”)
Then, hit enter. The first time a spreadsheet tries to communicate with another spreadsheet, we’ll have to authorize access between them:
Once we do that, we should see the data from our other sheet!
So here’s the tricky part. Normally with a function, we’d be able to “fill” it down and it would change the function to match the new area. I’ll show you an example.
Let’s say instead of referencing a different sheet, the client names were in the same sheet but just in a different column and we were just referencing them there. In that case, the formula would just refer to the other column, or in this case, it would be “=D2”:
If we fill that down, it will fill the rows correctly:
Notice how the new formula changed from “=D2” in row 2 to “=D4” in row 4. But if we try to do that with the IMPORT RANGE function, nothing changes. It imports the same cell every time:
The reason is in the name of that part of the function: range string. A string is text–it’s not a row or column or number like a function normally has. A spreadsheet can tell what you’re doing when you try to fill a formula down that is referencing a cell–you probably want the formula to reference the cells below the original. If you’re filling it to the right, you probably want it to reference the cells to the right of the original.
If it’s a string, it can’t tell that you’re referencing a cell, it just sees the letters and numbers that make up “Contact Info!B2”.
So. How do we dynamically reference that cell so that it fills correctly?
The ADDRESS, COLUMN, and ROW Functions
The answer is a function called ADDRESS. It’s a pretty meta function. Google says it “returns a cell reference as a string” when you input the row and column. So say you tell it row 1, column 1, it will return “A1”, but it will return it as a string and not the actual cell. Sound familiar? That’s exactly what we need for the “range string” part of the IMPORT RANGE function to work.
Here are the full details of the address function. It looks like this:
=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
That means:
row: Simply, the row number you’re referencing.
column: The column you’re referencing. Keep in mind this is a number not a letter, so A would be 1, B would be 2, etc.
[absolute_relative_mode]: This is optional. You can tell the function whether you want it to use absolute or relative references. I leave this out.
[use_a1_notation]: Also optional. You can use A1 or standard (R1C1) notation. I leave this out as well.
[sheet]: Also optional, but this one is useful. You can tell it the name of the sheet you’re looking for in case there are multiple sheets in the doc you’re pointing to.
So if we want the B2 cell in Test Doc 1 to grab the B2 cell in Test Doc 2, we just need the range string in our IMPORT RANGE function to reference the current row and column. Luckily, there’s a simple way to do that using the ROW and COLUMN functions. If you use the formulas of just “=ROW()” and “=COLUMN()” with nothing in the parenthesis, you’ll get the row and column of the cell you’re currently in.
You can see this below. I added the ROW functions to cells D2, D3, D4, and D5 and it gave me the row numbers. I added the COLUMN functions to C6, D6, and E6 and it gave me the column numbers.
This means we can use these formulas within the ADDRESS function. The final formula would look like this:
=ADDRESS(ROW(),COLUMN(),,,”Contact Info”))
Which just means “give me the address of the cell in the “Contact Info” sheet of the same row and column of where I currently am.” Note that commas separate each part of the function, so because we’re skipping the first two optional parts, you have to put three commas in between the column and the sheet name.
So, to pull it all together, we can use this in our IMPORT RANGE:
=IMPORTRANGE(“1vXc1-xuRRvvDA5jvuOe4qcbcASPF_axvWDVsY9h26p0″,ADDRESS(ROW(),COLUMN(),,,”Contact Info”))
We can now fill this down and it will work perfectly. The row() and column() functions change when we fill it down and it returns the range string the IMPORT RANGE function needs.
One last trick. The third column isn’t going to work like this because the column (C) doesn’t match up to where you want to grab the info from in Test Doc 3 (B). So, a simple edit of the IMPORT RANGE function is all it takes (the change is bolded):
=IMPORTRANGE(“1ONvf5LVFoDbPoFmNn07q8Qy9XteOC41zSqAYADNTOr8”,ADDRESS(ROW(),COLUMN()-1,,,”Location Info”))
We’re referencing a different spreadsheet, so I updated the spreadsheet key and sheet name. But, the tricky part is that “column()” is now “column()-1”. The orginial “column()” would have returned a 3 (meaning C) but we want a 2 (meaning B), so a simple minus one gets us there.
The final doc:
That’s it! Our doc will automatically update and we’ll be able to fill the formulas down if we add new rows in the future.
If I’m missing anything or if you’ve tried this and it doesn’t work, email me and I’ll help you out!
Cheers!
</end of original post>
———————————————————————————————
New Stuff!
As I was porting this over, I realized two things:
1) Linking to the example docs may help some people, so here they are:
2) You can make this example a little faster if you had an especially large spreadsheet with many columns. So, in the “Test Doc 3”, the original setup is in the “Simpler” tab, but I added two “Fancier” tabs. The “Fancier (Reference)” tab is a table for VLOOKUPS and the “Fancier (Final)” tab is the original table using those VLOOKUPs.
The Fancier version just makes it so that you don’t have to write different formulas for each column–as long as you include the lookup data in the “Reference” tab, you can have as many columns as you want and you only need one formula.
Cheers!
Thanks for info – I use importrange a lot, and assumed that it dynamically linked the files (sheets). But, I was just re-importing a table into a source sheet which is imported into some other sheets via lookups, etc. The data in the destination sheets didn’t change to match the new export!? Not at first anyway. Then, perhaps 15 mins later, it did. I wonder if you are familiar with this sort of glacial latency in such linked google sheets?
Hey Hudson. Glad to help. Yes, I would expect a few minutes delay when updating a web of documents like that. 15 seems a bit high, but that would just depend on how slow Google Apps is that day, I suppose?
Hi Lee,
I’ve successfully done the “importrange” using this method for the first spreadsheet. But the 2nd, 3rd and 4th is not coming through. Could you have a quick look at my formulas?
Hey Col. Glad to. Jot them down here and I’ll take a look.
Lee.
This sheet is where I am importing date from 4 others sheets:
The formula below is working fine to import data from cells D1 to H23.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1bl4LgXs06RcOX6g0hwAS19fJNUIaGFWo8pEhFVOM3Ns/edit#gid=0″;ADDRESS(row();column();;;”Sheet1”))
But it is not working for cells d24 to h46 for data importing from the 2nd sheet for this formula =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1JOnJkVKrR3_5gYbw9TT_bbD0WxQoU986SusuYQkc1uI/edit#gid=0″;ADDRESS(row();column();;;”Sheet1”))
Same for 3rd sheet using =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/10OpIhXUnvZFI5_ySktOn-Wr_6aIVP2w2rCkViIOWVAs/edit#gid=0″;ADDRESS(row();column();;;”Sheet1”))
and 4th sheet for
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1ZR8bkZIhxj_wQGyjBCN72W0UsAS771FzhH192l5n-JQ/edit#gid=0″;ADDRESS(row();column();;;”Sheet1”))
Hey Col I took a quick look–everything seemed to be fine on the two docs I had access to. I can’t imagine why that wouldn’t work! Can you give me access to the two other docs?
Hey Col–Is there a 5th document too? Actually, why don’t you email me (lee@leelkennedy.com) and I’ll help you out that way–that will be much easier.
Whoever you are, wherever you live, I will forever owe you “bigly” Lee Kennedy.
You just made my day!
Ha! Glad to help, Omar.
Hey Lee,
This is amazing! Thanks for sharing and keep it alive 🙂 I have an IMPORTRANGE running and importing data from 4 sheets sitting in the same google sheet in a MasterSheet (Data stacked one data set on top of another ). The thing is it leaves blanks in between … so I wonder how to make it stop importing from sheet1 once no data is populated in Sheet1!, so it sticks directly DATA from sheet2! not leaving blanks in between? Is it possible with IMPORT RANGE or I should use a Query please?
Thanks Lee
Kamar
Hey Kamar. Glad it helped. I’m not sure you’d be able to limit the IMPORTRANGE like that unless you somehow dynamically were able to pull in the range of non-blank cells. It’s likely possible, but I’d have to see it. A QUERY function might be a better idea as long as it doesn’t slow everything down too much.
I’ve been using importrange in a google doc for a few weeks and it is working as expected, although with one issue for which I can’t find an answer.
Let’s use your Test Doc 1 as an example, and all the imported Client, Contact and Location info is being imported just fine. Let’s also say that the spreadsheet_key references a doc which you don’t maintain, so you are unable to create a new column there.
Now, in your Test Doc 1, you create a new column labeled “Sent Birthday Gift” and manually enter “yes” or “no” in each respective cell.
Remember, this column is not part of the original spreadsheet_key you are referencing, so it isn’t data you can import and has to be manually created.
Here’s my issue…each time a new row (Client, Contact Name and Location) is imported from the original doc, it pushes the previously imported data down one row. However, the manually added data (yes/no) in the Sent Birthday Gift column is now out of position.
How can the manual data preserve its position in relation to the imported data?
Ah interesting issue there. The first thought that comes to mind is to create a whole new tab that includes just the ‘client name’ and ‘birthday gift’ columns. Then, in your main sheet, just use a VLOOKUP to pull the birthday gift values. This will keep them accurate even if they move up or down. You may have to put the VLOOKUP formula in a bunch of extra rows to account for extra rows being added. Does that make sense? Let me know if that works.
Hi, and thanks for your response.
I don’t think that will do it, as it puts me in the same boat.
Because I’m importing data from a spreadsheet which I cannot modify, I can only add a “birthday” column to the sheet doing the importing. If I make another tab, the VLOOKUP will still find birthday cells that have shifted, as new rows are imported from the source spreadsheet.
I see what you’re saying. There’s two issues here. 1) Find and fill in “birthday” values for people that have already been imported. This you can do. Take the current list of people’s names, create that new sheet with their names and birthdays, and use the VLOOKUP to dynamically pull them in, even if the rows change. Then, you have 2) find and fill in a birthday value for any *new* rows. This is not going to be automatic. But, the best option I can think of is: when you see a new row get added, take that person’s name and add them to the VLOOKUP sheet and fill in the birthday value. Then, the VLOOKUP column in the original sheet will work. You’d have to do it manually for each new row, but I don’t see another way around it. What do you think?
Thanks for offering another option. I’ll give it a try, as every new option is always another learning experience. However, if not automatic, maybe manually re-positioning the “birthday” cells is the quickest option for this particular situation.
Thanks again!
Yeah of course! And if you could, report back if you find a good solution. I think you’re experiencing a common problem (combining dynamic data w/ static data). Cheers.
Hi just letting you know that I had some issues with the formula.
Turns out it was the quotes. I was able to get it to work by replace your quotes with “”.
Oh interesting Leandro. Which specific formula did you copy that had a problem? I’ll check that out, thank you.
I’m trying to use importrange function to bring client information from one sheet(a) to another(b). My question is how do I select from “a” when the column size is fixed and the row size grows frequently.
Basically I want everything in “a” column a through k to appear in “b” in column a through k.
Hey TP. I think the standard setup should work for you, using the ADDRESS function with the COLUMN() function in the column argument and ROW() for row. Did you try the full example in the article?
Saved me hours of work, thank you so much!
Glad to help, Oliver!
Hi Lee, maybe you can help me with this one:
I’m trying to change the range string to the value in a cell corresponding to the name of a sheet on the target workbook. Here’s two images:
Current sheet:
https://static.wixstatic.com/media/93ac46_593aa4ff070247bcba37f9ae86817e8b~mv2.png
Target sheet:
https://static.wixstatic.com/media/93ac46_440ae0107a484066a49d3ad59c4e428f~mv2.png
Im trying to avoid what the directions are in A2 on Current Sheet, where the formula in A11 changes to replace what’s currently ‘OVR’ with whatever is in A1 of the current sheet.
For example if I change A1 to HOP, I want cell A11’s formula to pull from “HOP!A11:ZZ”. Goal is to copy these sheets and only have to change A1.
Hey Bruno. Luckily that’s pretty easy. For that argument, instead of “OVR!A11:ZZ” (that’s including the quotes), you’d do A1&”!A11:ZZ”. That should take the value of A1 and append !A11:ZZ to the end of it.
And now for further complexity…
I am in the process of setting up a number of sign-in/out sheets, which will be their own sheet for each event. They’re built from template, so I know the range I want every time.
Is there a way to, in a new worksheet, record the list of events and their doc keys, and then programmatically load “all of the keys”? I’m not averse to looping and loading “a sheet” to “the last row” in the greater table, instead of a single IMPORTRANGE to load all of the keys at once, but I do need to be able to dynamically figure out what sheets should load.
Hey Mark. Hm so let me make sure I’m understanding: You would have this new worksheet where you’d manually write in the event names and their keys, and you want to pull in the full list of people that “signed in” or out from each event into one big table in that new worksheet?
That sounds about right, yep
Yeah that’s tricky because the number of rows coming in from each source sheet would be different. I could see something working where each source pulls into a separate tab, and then you run a script that copies each tab into the master tab and then sorts it, but that’s out of the scope of this post. Plenty of resources on that though (like https://youtu.be/K3pE7ENwjpQ maybe?).
Hi Lee, thanks for this really useful. I’m trying to manipulate the function just having a little trouble. Basically I have a list that is constantly updating and changing with week on week data entries in the columns b to e. The formula with address function allows me to reference the correct row in that list so if any new rows are added this is accounted for however you can only import one cell at a time. Is there a way I can import all the columns associated to that row?
e.g. ADDRESS(A3,5:50,,,”WoW for KPIs”)
Ah that’s a good question. I think I figured it out. So in your IMPORTRANGE, the argument you’ll end up needing in the second slot is this:
'WoW for KPIs'!E1:BB1. (I’m not sure what row you need so I just put 1.)That should pull what you need just fine. So to do that, you’ll just need to combine two ADDRESS functions with a colon in the middle and put that in the IMPORTRANGE function. Like so:
ADDRESS(1,5,,,"WoW for KPIs")&":"&ADDRESS(1,50)I just tried this out and it seemed to work. Let me know!
Hi Lee. Thanks for helping! Can you nail this one as well?
I have this formula: =importrange(“1oP_P48ghL3aTm5AQJ6c_s7OGh-ORhGSiBjE”;”B31:B999″)
I want the row number “31” to change according to a value in a cell (eg. A1).
So if I enter 45 in A1, the formula will automatically change to:
“=importrange(“1oP_P48ghL3aTm5AQJ6c_s7OGh-ORhGSiBjE”;”B45:B999″)
I have other cells where I use the same importrange formula but linking to another column (eg “C45:C999”).
I want to have all the importrange formulas to reference to the row number in A1 but keep the column reference in the formula. This way I can just change A1 and all formulas will reference to this row number in the import range.
I have tried using “A1&” in the formula but I cant get it placed right. Can you help with this one?
Hey Nicolai. You’d just have to do something like
=IMPORTRANGE(“1oP_P48ghL3aTm5AQJ6c_s7OGh-ORhGSiBjE”;”B"&A1&":B999″), where you replace the45with"&A1&". You have to put the quotations and ampersands on either side of it because if you don’t, the formula will just think you literally mean the text “A1” when you mean it as a reference to a cell. Try that out and let me know if it works.Yes it works. Thanks a lot for your help Lee!
Yes it works. Thanks a lot for your help Lee!