Creating “MailTo:” Hyperlinks in Google Docs

March 14, 2011

The whole point of cloud computing and Google Docs is to make your life easier. Recently I moved our teacher email list that was on our website to a Google Spreadsheet and used the Publish feature to make it available on our website. The issue with the spreadsheet was you were unable to click on the email address to automatically open your email program and set the To: portion in your program. Until now.

By utilizing 2 formula’s in Google Docs you can take your list of addresses and make them clickable.

To get started Have your columns set up in this fashion:


Spreadsheet Layout

Column D:

Now we are going to get into our first formula, the Concatenate function. This function allows you to tie 2 cells together and merge them together. You can do this by typing in “=CONCATENATE” we now have to add “mailto:” to the users email address.


To do this we type =CONCATENATE(“mailto:”,C1) (C being the column that has the users email address)




Highlight the cell and drag it to then bottom of your list.


Column E:

Now we have to create the link that the end user will click on. To do this, we use the Hyperlink function. This is done by typing in “=HYPERLINK”. So to create our hyperlink we type “=HYPERLINK(D1;C1)” This will result in your list becoming hyperlinked in the fashion that we want. Again drag this all the way to the bottom of your list. Your spreadsheet should look like this:


all columns


The final step is to hide Columns C and D. You do this by clicking in the top of the column and selecting “Hide”


hide column


You can now publish single sheets in the workbook, or all of the sheets. To publish click “Share” on the top right hand corner and click “Publish as web page” you can now embed the sheet into your website for your different buildings. You can see this in practice my checking out our page at: http://www.firelandsschools.org/drupal/?q=node/485


If you have any questions feel free to let me know!