SmexyyWeby

Scripting for Fun and Passion

Cross-Workbook Reference Functions in Google Docs


I have been using Google Docs for quite some time now and really love the ease with which it you can do simple tasks like putting a contact form on your website in no time. I personally use Google forms quite a lot not only for my personal purpose but also for all AIESEC(not for profit organizations) purposes. I also suggest many others to use it because it is something which is really user friendly. The best feature is the embedding a form in your website. No problem of it clashing with your website theme. All you need is the plain theme of Google docs and paste the embed code in the web page source.

Well that said and done Google docs is indeed a very simple application made for basic usage. You can do that many automation tasks in Google docs. Also one problem which I find with Google forms is that for every for you need to make a new spreadsheet. This is something I don’t like that much. Let take my case only. I use Google Forms to make application forms for membership and internship for AIESEC. However I want all the details related to all incoming applications in one spreadsheet only. This is something which is not allowed by default in Google. You need to use some function calls present in Google Sheets to make this work.

Fortunately, this feature is easily implementable if you have an idea about using the function called

=ImportRange(“SpreadsheetKey”;”SheetName!CellRange”)

This is a really neat function which allows you to set up an import of Live data from any other Workbook. Its really simple to use. I will explain it with a simple example of a simple email subscription form. The form takes an Email Id as input and stores in a spreadsheet.

image

This Workbook has a sheet names Sheet1 and The Columns which will contain data will be A and B. So lets say we need to import the data of this form into another Workbook which has emails from different Websites in a separate Sheets. Now we go to our new Workbook and make a new sheet for our Email Id on from this form. Once the sheet is made we need to ensure that it keeps its data in sync with this form. Now we need to make an assumption here about the maximum number of entries we expect in this form. I assume 200 and for that I add 200 more row in the original form Spreadsheet. Now we go back to our Aggregator sheet (The one where we collect all email ids from different websites) and in the new Sheet for this form select the A1 cell (the selection of cell is personal preference. For forms it makes more sense because I want the exact copy of the spreadsheet as I have in the form).

Now we need to enter the formula in Cell A1. But before applying this formula we need to collect a few values. There are basically 3 Parameters (although it looks 2 only but the 2nd parameter itself contains 2 parameters)

  • SpreadSheetKey: This is the unique identifier of the spreadsheet you want to import from. In this case it is tdtdimATHn-WvPZ4aWGsMMQ
  • SheetName: This is the name of the sheet from which you want to import. This case its Sheet1
  • CellRange: This is the range of cells whose data you want to sync with the new sheet. I am selecting the data for first 200 entries. So it makes CellRange as A1:B201 (Note: I am going one extra with 201 because of the heading row as well.)

Now once you have collected these values just put them in the proper format for the ImportRange Function call. The final function call becomes

=ImportRange(“tdtdimATHn-WvPZ4aWGsMMQ“;”Sheet1!A1:B201“)

PS: Do not forget to put the exclamation mark ( ! )after the Sheetname as it is very important to refer to the cells of the right sheet.

Now once you have made this above statement. Enter it into the first cell of your new Sheet in the Aggregator Workbook. And then press enter. It will take a few seconds (depending on the data you have in the form spreadsheet) and then it will show you your new sheet filled with the data from the form Sheet.

image Also this data import is synced meaning that whenever you enter a new value in the form spreadsheet it will automatically be imported in this aggregator spreadsheet. So now no more hassles of searching multiple spreadsheets from Google docs. Sync it once and you have all your data in once place.

  • Note1: It will only import the date for the cell range specified in the import command. If at any point of time you want to increase the cell range go to the aggregator sheet and click on the first cell and you will see your formula again. Now make the necessary changes to your formula and you are done. It will re-sync the sheet with new data.
  • Note2: At times you might get #REF error in your spreadsheet. This happens if you are trying to import data from the sheet you do not have access to. You need to have atleasr view rights to import data from a workbook.
  • Note3: I have deleted the sheet which I made for the example of this post so this import function will not work with the SpreadSheet key given here. Please use the key of the Sheet you want to import from.

So next time if you think of making a new form and are concerned about the data aggregation try this method and make your life easier.

Please feel free to ask any queries and give any suggestions to improve the process  in the comments section given below.

Advertisements

3 responses to “Cross-Workbook Reference Functions in Google Docs

  1. Jade Cahoon May 9, 2013 at 5:42 pm

    thanks so much for this! so helpful, i used to make a change and then copy-paste it to the other…

  2. Frans van Konijnenburg January 4, 2013 at 2:08 pm

    Hi I am looking for a way to sync cell values between sheets two way.
    So when changing a cell value in sheet 1 it should automatically change also in the connected cell in sheet 2 (or workbook 2) but also the other way around (meaning that they are equal) do you now if this is possible?

  3. racha October 30, 2012 at 1:03 pm

    thanks for the post! this is exactly what i was looking for! 😀

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: