Inserting A Google Doc link into a Google Spreadsheet

This article looks at using Apps Script to add new features to a Google Spreadsheet.

At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form.

Spreadsheets can be better than Forms for data collection because:

• The spreadsheet data saves as you are editing.
• If you want to fill in half the data and come back later, your data will still be there.
• The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary
• The commenting features are brilliant - especially the "Resolve" button in comments.

One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often all look too similar and you don't know what you are getting until you have clicked it.

For example, one part of your spreadsheet might ask "Do you have any supporting files?" and it would be handy to be able to insert a link to any number of Google Docs which might include a project plan Google Document, a PDF letter of support and a budget spreadsheet.

Like this.

When you select "A file from Google Drive" a dialog is presented showing you your Google Drive files, like this.

In this example, I am selecting the "What goes where: Action Plan" file. A =HYPERLINK() formula with the name and link of this Google Document is then inserted into spreadsheet.

Adding More Abilities

Once I'd created the ability to attach a Drive document, I knew that people would ask for other obvious features.

1. Uploading a file directly from your computer

You may have noticed that the menu also allows you to "Upload a file from your computer". This simply uploads the document into your Google Drive and then links to that uploaded file.

2. Attaching a regular link

There is also the ability to add a simple "regular hyperlink" - we often have documentation in our wiki or on web servers that need linking to as well as GDrive documents. This tool provides a simple interface

Conclusions: Keeping It Simple

This tool doesn't try to make sure that the permissions on the attached Drive file match those of the spreadsheet, but that is a good thing and anyone without access to a particular file can request access the usual way.

I experimented with showing an icon of the file type, or a link icon if linking to an external site, but that made the code need to look in adjacent cells to see if the data was empty.

This code doesn't save the uploaded files into a particular folder because it would make the code less "copy and pastable" but you could easily add that feature.

I also thought it might be nice to add a cell comment to say who uploaded the file. Again, that wouldn't be hard to add yourself.

There is a little interface clunkiness with the UiApp which means that the Google Drive chooser dialog window is a bit cramped, but all the functionality works well enough to add the extra dimension of collections of related files to your spreadsheets. Hopefully by publishing this article I may get some help with this.

You never know, maybe Google will roll some of these features into the main applications, but until then you can use this addition to easily collect together references to various Google Docs in a spreadsheet.

Also, I wonder when Google will add a Script Editor, or the ability to add menus to Google Documents? ( Update: Since I wrote this, Google have now added this. Cool. )

How To Add This Menu To Your Spreadsheet

The code to add an "Attach..." menu to your spreadsheet is in this spreadsheet called "Inserting GDrive docs into a spreadsheet" here:

https://docs.google.com/spreadsheet/ccc?key=0Ajnu7JgRtB5CdGtoUmM1YnlHaS1KWVowVkxtMnFzWFE#gid=0



1. Make a copy of the spreadsheet.
2. Go to menu Tools >> Script Editor
3. Copy and paste the code into the Script Editor of another spreadsheet of your choice.
You will have to run the onOpen() function to get the menu to appear.

Browse More Articles

Tweet

Written by Unknown

We are one of the initiators of the development of information technology in understanding the need for a solution that is familiar and close to us.