I worked with an editorial team for this project. And editorial teams love working in Google Docs.
But managing them can be a bear because sometimes you have to have multiple Google Docs open in different tabs to provide context for the research you’re doing. So I’ve been exposing the team to crazy techniques like dynamically pulling a data set from one spreadsheet in Google Sheets into another using the IMPORTRANGE function in Google Sheets.
If you’d prefer to watch the process I created a video walkthrough. However, there are some adult keywords in the spreadsheet, so if you’re easily offended, you might want to pass.
Steps
For those who don’t want to watch a video, I will provide a rough sketch of the steps I took below. If they don’t make sense watch the video. (Duh.)
Step 1
Grab the key from the URL of the Google Doc you want to import.
Step 2
Take note of the columns or range you want to import. You can import entire columns by entering something like Tab Title!A:F. Or, for a specific range, something like Tab Title!A6:F100.
Step 3
In the cell you want the data to import into, enter the following formula:
=IMPORTRANGE("1P3DhQtE46xxBTopuklWEoBdr1NzH0efXjWFTATAH1Z1c","CTR!B6:AC100")
Of course, you’ll switch out my key and range for your own. Alternatively, you can drop each of these values into a cell and just reference the cells. If you do that, you won’t need the quotation marks. It would look something like:
=IMPORTRANGE(B1,B2)
Step 4
Format your data. I demonstrate how I formatted mine in the video. (For those of you who know I’m not a big fan of the color pink, those are YourTango’s branded colors. We have an updated look coming though, which I’m all too relieved about!)
Image Credit: Bernd ? Dittrich on Unsplash
Long enough work for a company that is professionally engaged in advertising, by the way, here is our site gladvert.net So, read your post, enjoyed it, our company adheres to the same principles!
Do you have any knowledge on the limitations on the number of times you can use =IMPORTRANGE in i) all tabs of a google sheet, ii) each tab within a google sheet?
I don’t. I haven’t hit that limit yet.
Hi Annie,
Thanks for your post!
I already asked you on your YouTube and I am asking here to ensure you see my question.
What do you think of Import Sheet [1] to automatise the process of importing and exporting data between multiple Google Sheets? Do you find it superior to using IMPORTRANGE?
[1] http://importsheet.com/install
Hi, Robert! I haven’t ever used it. Looks interesting though.
Hi Annie,
A lot of things have changed.
Import Sheet is now Sheetgo and has a lot more to offer.
Import, Export, Consolidate, Filter, Append, just to name a few.
Are you still using Sheetgo / Import Sheet? 🙂
https://www.sheetgo.com/connect-spreadsheets/
I’ve never used it.
Hi, is it possible to import a data from a specific cell instead of a range?
I haven’t tested it, but you should be able to.
Thank you sooooooo much! I was about to throw my PC through the window!!!
So glad it helped! I know that feeling. 🙂
Thanks for the tip. Awesome stuff here. Cute language in the vid by the way… I’m a guy, I get it.
As any data access, database, or business software programmer will tell you, there are several questions you have to answer when you build any kind of datagram (a document with ordered\sorted data).
1. Where to get it all from originally
2. Where to store it for later
3. Who can adjust it and how
4. Who needs only to see it, and how do you get the data there
5. Is it used to calculate or chart, and by whom
6. Backing it up, how, where
7. Maintenance scheduling to keep it all sorted and fast
With small businesses, I’ve almost never had to go outside of EXCEL, except in mailmerging documents for sending to clients\customers. I used CSV for a long time, but found that it required more data validation after the initial entry, than did a sheet for storing values only. USing webservices and some VBScript for intranet work, and Jscript for internet linkage, most of the data worked fine going to an entry sheet, validating on entry, then formatting when sent to a value only sheet. Any calculated values or adjusted values could be adjusted quickly and efficiently, then any other workbooks could access them without fail.
With google docs, sheets, and Forms, I’ve revamped some work. Much of it remains the same kind of functions, but it’s easier to validate data with regular expressions in FORMS, and then pass it to sheets, then format the data for use in a second sheet, and pass that to other workbooks. Since all the data from most sheets goes to one workbook with a ton of sheets, and the data are formatted and calculated for use in a second sheet there, automatically where I use a non-ending set (A#:A) , any entered values are there. I can even validate each formmating sheet to be limited to a date range using the timestamp that gets attached to the form entry, or allow other workbooks to do it on their own. This function is a necessity. I’ve had to do it using script and it was a lot more work. This will cut down the time.
Thank you again for all your help.
Thanks for the thorough and very helpful feedback! This is great!
By the way, the limit is 50 imports per sheet for the free version.
IF you use a whole column\row for each import, you can maximize your data input.
For instance, If you want to import data from more than 50 either way, just separate the sheets in the workbook logically. I’ve been moving many small businesses to the free versions of google docs, as it’s easy to share across email accounts, and make data linkage a snap. Then you only have to unshare any shared docs, and remove a person from them. By keeping as many functions in one workbook as possible, you can unshare the entire workbook from the sharing panel, and leave the other users untouched. Keeping formatted docs in a central location (or at least a copy of them), allows you to continue to use the same documents with other users, just by granting\denying access again. Excel only does this by SHEET or WORKBOOK. Google allows you to do this by RANGE, SHEET, WORKBOOK. It’s easy to see which would be more useful to small businesses, as allowing access to different format and data portions keeps people from slipping and destroying an entire document (whatever the reason behind it). Locking the data down even further, by using import statements and scripts to fill the cells that are otherwise locked allows you to further prevent disaster. They should only ever have to spend 10minutes opening a set of documents and printing them, that way the meeting is a go at the drop of a hat.
I would venture to say if someone needs more than 50 imports, s/he should probably be using a database, not a Google Sheet. That said, this is solid info. Thanks!
I was able to get the import to work, but am trying to figure out how to keep the data from one sheet in sync with the other. I have tried a few things but nothing seems to work. Any ideas?
Hi Lisa,
I haven’t run into that myself, but I’ve seen others have issues with it since Google updated Sheets. This post may help you: https://productforums.google.com/forum/#!topic/docs/RUHKFQlEdec.
Disclaimer: I HATE that he uses the term slave/master to refer to sheets. I mean, c’mon … seriously? What year is this? (Not that it was ever acceptable.) But the info seems legit.
Hi,
I am usually pretty good with the formulas. However, I cannot get my data to dynamically update. I did everything as you describe, but I actually have to delete the A1 cell, paste the “importrange” formula in after the update and then the spreadsheet updates. Any suggestions??
I would need you to share the workbook. I can’t tell what’s wrong without seeing your formula.
Hi – I have tried this with just 1 cell. It says ‘loading…’ and then said ‘Error’ – ‘allow access’ [to the other spreadsheet’ I clicked on allow access and the cell just goes blank! The formula appears to be correct! Any ideas, please help?
Hi Gemma, this thread may address the issue you’re running into: https://productforums.google.com/forum/#!msg/docs/AEyMPxcAY90/Xgi0WBdT5s0J. There have been quite a few complaints with importing into the newest version of Google Sheets.
This was SO helpful! Thank you!
I am an investor in westport, ct. I have some excel files of agents info have to upload on google dive so I am learning about it. I found your article so helpful. Thanks for sharing.
Glad it helped! ?
Great! I’m glad I could help!
Thank you for sharing. What I am trying to do is grab information from several separate sheets into one Master. I would like to share the individual sheets with employees then have everything incorporated into a Master. I used importrange to grab the info from one sheet, however I’m not sure how to pull from the others. Surely this is possible, however I have not been able to make it happen. Can you assist?
You can just follow the same process to pull in another sheet. If you have trouble you can share the document with annie(at)annielytics(dot)com, and I can help you via comments.
Can anyone tell me how can I import from other’s google driver data sheet,
Import range is working only sheet of mine only
You have to have access to the sheet to import it. You can always try requesting it.
Hi.
Is it possible to import range of data including formulas, from one workbook into another? I have worked out how to import the data but all the formulas are lost.
thanks
I don’t think so. You carry over the processed data.
How to import including the formats?
Unfortunately, you can’t. You have to reformat everything.
Thanks for this. Saved me a big headache. For formatting what I’ve done is copy the source sheet and paste special into the target sheet using format only. It got me most of the way there.
Brilliant workaround!