add_action('wp_head', 'add_google_rel_author'); function add_google_rel_author() { echo ''; }

In moving from Excel to Google Spreadsheets, some may find the need to work with data or ranges from one spreadsheet in another spreadsheet by using formulas and not manually copying the data into multiple sheets, sometimes this is called linking Google spreadsheets.

Google does provide a solution to this via the ImportRange function, however there is still more to learn and know about this function, and it is best to start with the help from Google.
(https://support.google.com/drive/table/25273?page=table.cs&rd=3)

Now that you have probably read the Google support notes on the ImportRange function, here are some tips and notes via examples on how it works:

(please note the spreadsheet links are not valid, and only for illustration purposes)
Spreadsheet1 – https://docs.google.com/spreadsheet/ccc?key=0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E#gid=0
There are two sheets named Sheet1 and Sheet2

Spreadsheet2 – https://docs.google.com/spreadsheet/ccc?key=0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc#gid=0
There are two sheets named SALES and EXPENSES
There are named ranges called SALES_SUMMARY, MONTHLY_SALES

TIP: The name of the sheet and named/protected range within the file is NOT case-sensitive

Using a new spreadsheet of your own creation, I will try to give you some good examples of how to pull data from another Google Spreadsheet.
Examples of getting data from file Spreadsheet1, sheet named Sheet1
=ImportRange(“0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E”,”A1″)
=ImportRange(“0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E”,”A1:D1″)
=ImportRange(“0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E”,”sheet1!A1:B1″)

Examples of getting data from file Spreadsheet1, sheet named Sheet2
NOTE: If you are not accessing the FIRST sheet created, you must specify the sheet name!
=ImportRange(“0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E”,”sheet2!A1″)
=ImportRange(“0Ak__Spreadsheet1__ZoUzRhNFBIaGo2WXk3eFZPU1E”,”sheet2!A1:D1″)

Examples of getting data from file Spreadsheet2, sheet named SALES
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”A5:F5″)
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”sales!A5:F5″)
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”SALES!A6″)

Examples of getting data from file Spreadsheet2, sheet named EXPENSES
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”expenses!A5:F5″)
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”Expenses!A5:F5″)
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”EXPENSES!A6″)

Examples of getting data from file Spreadsheet2, named range SALES_SUMMARY
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”SALES_SUMMARY”)
=ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”Sales_Summary”)

Examples of getting data from file Spreadsheet2, named range MONTHLY_SALES
NOTE: You can use the ImportRange Function inside of some functions, but not VLOOKUP
To use VLOOKUP, you must basically pull that data into the same file (optionally in a new sheet), then do a VLOOKUP against the current sheet.

=SUM(ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”MONTHLY_SUMMARY”))
=3*MIN(ImportRange(“0Ak__Spreadsheet2__XaBC1LaDdSd1EzblQ1N3BBcWc”,”MONTHLY_SUMMARY”))

NOTE: The “auto” update of data between spreadsheets when using the ImportRange function seems to be between a few seconds and about two minutes.

I hope this helps you get more use out of your Google Spreadsheets!

26 Responses to Tips for working with data in multiple Google Spreadsheets or linking Google Spreadsheets

    • Steph,

      Unfortunately Google does not currently support the “concept” of a named range that is actually multiple ranges, so it does not work…Myself and others hope it will.

      Reply
  1. Hans van der Meer says:

    Admin, this is very helpful,but how to proceed if you want to import data from colums that are not adjacent in the source spreadsheet?

    Reply
    • Justin Gale says:

      Use the import range multiple times…It can be used up to 50? if I remember correctly…so that would give you 50 non-adjacent rows, columns, cells to play wtih.

      Reply
  2. Hans van der Meer says:

    I found an answer to my Sept. 25 question: by using ImportRange multiple times in the target spreadsheet. So you can import the columns A,B and the columns E,F,G etc. and place them neatly adjacent.

    Reply
    • Justin Gale says:

      Yes, I do that to “hide” columns that I do not want shared with others. I create my new spreadsheet to “share” and select only the columns I want them to see.

      -Justin

      Reply
    • Hi, you can use the function =QUERY() to select some columns.
      ie :
      =QUERY(importRange(“key”;”range”); “Select A,B,E,F,G”)

      You may add a where condition to import some specific datas
      ie:
      =QUERY(importRange(“key”;”range”); “Select A,B,E,F,G where B = ‘X’”)

      Regards

      Reply
  3. It seems like the importrange function is based off the key string in the other worksheet’s URL. I wanted to set up a spreadsheet with the import range formulas built in for google spreadsheets I have not yet made yet that will all be named using a date nomenclature. Is there any way to dictate the Key String of a new Google spreadsheet so I can build out these formulas or are the Key Strings completely randomly generated and not able to be generated?

    Thanks

    Reply
    • Justin Gale says:

      Brian,

      Yes, the easiest way is to either do a named range or put the “key” in a cell and reference that cell. There were lots of little tricks I found, the more I played around with what you “could do.”

      -Justin

      Reply
  4. Hello! this was very helpful and got me going. however I’m still running into a #REF! error.

    Here’s my scenario:

    I have a spreadsheet that is a full editorial calendar with multiple tabs for each media channel.

    I have to create individual spreadsheets for each channel for uploading purposes.

    I want whatever info I put on the tabs of the full calendar to populate on the individual spreadsheets.

    I can’t seem to pull the information from each tab of the full calendar. I can get the function to work when there is only one tab in the spreadsheet, but not when there are multiple tabs in the same spreadsheet.

    here’s what I’m trying:

    =IMPORTRANGE(“0AsD_Spreadsheet1_pe294HoWdGZXYnFpc3Z2UjFTc3ZUMnVzVXNaQ1E”,”Twitter!A3:E3″)

    Can you see what I’m doing wrong? Thanks so much!

    Reply
    • Justin Gale says:

      Malorie,

      It should be pretty easy to fix. I will probably need the IMPORTRANGE commands (plural) you are doing for multiple tabs. If I were to take a guess, though, I would guess the Names of the tabs that you are trying to import is what is creating the problem. I try to name my tabs with underscore (_) instead of spaces, and no special accent characters or symbols, so I can most easily use the tab names in forumulas (remember they are CASE SENSITIVE).

      Let me know if this helps, or if you can share them with me and help get you going in the right direction.

      Cheers!
      -Justin

      Reply
  5. Asif Azeem says:

    I have created a Google Spreadsheet. It contains multiple sheets (i.e. Mr. A, Mr. B, Mr. C, Mr. D and Mr. E) where people are listing down their tasks along with the status of task (i.e. Done or On Hold or In Progress). I want to pull only those tasks which are Done at one sheet from all the sheets.

    How is this possible?

    Please help

    Asif

    Reply
  6. hot water heater Reviews says:

    Howdy! I just wish to offer you a huge thumbs up for the great info you’ve got right here on this post.
    I’ll be coming back to your blog for more soon.

    Reply
  7. Thank you very much for all informative help, I am doing =importrange properly but i want some addition, for example I want use this range data with my new data entry, I just want some connection in my working sheet between importrange data and my new colums data, can you peoples help me in this regards,

    really will be very helpful & appreciated,
    kaleem

    Reply
    • Justin Gale says:

      Kaleem,

      I am sure I can help with the technical examples, if you can share a little more of the 2 spreadsheets or data that you are trying to use:
      example:
      Spreadsheet1 (SS1) has email addresses and full coordinates/addresses of people who expressed interest in Product1 (from a Google Form or other method to capture this information)
      Spreadsheet2 (SS2) is manually entering email addresses of people interested in Product2 from business cards at a tradeshow.
      In SS2, we can do an importrange (to a new tab from SS1), then in SS2 do a VLOOKUP on that to see who was interested in BOTH product1 and product2 and pull over their addresses/coordinates from the VLOOKUP over the importrange. Hope that helps a bit.

      -Justin

      Reply
  8. I was curious if you ever considered changing the page layout of your
    site? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect
    with it better. Youve got an awful lot of text for only having
    one or two pictures. Maybe you could space it out better? https://vimeo.com/89180931

    Reply
    • As long as you are the owner of the unshared SS. The first time it goes to pull the data, it will ask you for access.

      Reply
  9. Is there a way to use the importrange to Vlookup two data sets from two separate Google Spreadsheets as a simultaneous lookup? I have two different sheets that have the same columns(Somewhat of a split database) but depending on the lookup value, it may appear in one or the other. Is there a way to accomplish this? In excel I don’t need to use the importrange function as excel doesn’t have a limit to the cells but I would like to be able to put this “database” on Google Docs. Let me know if you have any ideas please.

    This was my initial attempt but kept receiving an error saying there are too many arguments.

    =VLOOKUP(B3,(IMPORTRANGE(“1pUvmmNBpMdY7b8yfjo24Burt0-RnGaGnJV0cG5PouEQ”,”sheet1!A1:F10000″),IMPORTRANGE(“1fiOhf5IN5ZdYyRyE65LFHXXouOBeTAF5lsn3XRl7OpM”,”sheet1!A1:F10000″)),6,FALSE)

    Reply
  10. Anonymous says:

    Also for the attention of all,
    one major thing is that you must use the new google drive file
    also copying inside drive is not enough, you need to export, import
    and one more thing:
    all the excalmation and other marks have to be perfect(from the new drive char set!!!)
    for me the same text did not work , only after changing these marks, with new handwritten ones.

    Reply
  11. Hey Justin,

    Thanks so much for creating this site! I’m trying to edit the data now that I’ve imported it but it just keeps converting back to the original data set. Is there a way to add extra data to the table when it’s not in the original file or do you just have to edit and maintain the original file?

    Cheers!

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>