Creating “MailTo:” Hyperlinks in Google Docs

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)

 

Concat

 

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!

 

Thanks

TJ

TJ Houston

Hey everyone, My name is TJ and I am here to help you! I am a lifelong learner that strives to help others. Work: By day I work for Panasonic as the Sales Support Rep for the Midwest Region. Play: I love all things photography, videography, and tech! Home: I have a loving wife (Ashley) and an adorable German shepherd (Jynx) You can find me on twitter @tjhouston on instagram @tjshotsphotography or if you want to see some of my professional photography work check out tjshots.com. All of the views on this site are my own and do not reflect my employers.