Skip to main content

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

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.

Update...

After Google moved the goal posts I've finally got around to updating and improving this...


File > Make a copy.. then use as you like... 


Comments

  1. Cooler than cool! Thank you!

    The insert file dialog could indeed use a bit more work but it works just fine now as well.

    ReplyDelete
  2. Thanks Bálint... often I post this stuff hoping someone will a way to do it a bit better... the dialog box is very ugly, but it works....

    ReplyDelete
  3. Great work! To make it practical, the dialog needs to be resizeable for long document titles, or at least make the full title visible on hover. Also, it would be very useful to be able to navigate by folders, not just by document type. Thanks!

    ReplyDelete
  4. Thanks but I couldn't work out how to make the dialog wider... any ideas?

    ReplyDelete
  5. This is exactly what I need but I do not understand how to follow your instructions. Could you provide more detailed instructions? Copy and paste code..where do you copy it from? how do you paste it and where? What does it mean to run the open function?

    ReplyDelete
  6. There is a spreadsheet linked at the bottom. Open it and use the menu File> Make a Copy.

    Now go to Tools > Script Editor ( you'll see the code in there ) and the "play" button for the onOpen function.

    From there you either will get it or need to maybe start doing the tutorials on the Apps Script documentation site to become more familiar with what's what.

    Good luck!

    ReplyDelete
  7. Tom thanks for sharing works well for me with clear instructions. Alan

    ReplyDelete
  8. Thank you very much for this script, but when I insert a link of a pdf file from mi Drive, the cell says "#ERROR". Why?

    ReplyDelete
    Replies
    1. I had to change the comma in =hyperlink() to a semicolon.

      Replace the comma in
      var cell_value = '=hyperlink("' + file_url + '","' + file_name + '")'
      with a semicolon
      var cell_value = '=hyperlink("' + file_url + '";"' + file_name + '")'

      Do the same on all lines that have "=hyperlink()" and the pattern + '","' + in them.

      Delete
  9. Is there any more than that? You might have to do a bit of debugging...

    ReplyDelete
  10. Hi, the fields are all greyed out so I cant copy the script. Do I need access?

    ReplyDelete
  11. Apologies, I did not do the copy of the sheet.

    I did get this error when I ran it "Oops
    Script function upload_file could not be found"

    ReplyDelete
  12. Or when selecting from a file on your computer, this error message, "Error encountered: Script function not found: InsertGDriveHandler

    ReplyDelete
  13. mmm?! Sorry don't know what that problem is, I just tried it again and it works for me.

    ReplyDelete
  14. Freaking AWESOME! I wonder why this isn't core by Google. They owe you a beer, mate.

    ReplyDelete
  15. Thanks Andy... I keep trying to convince them that I know best... :-)

    ReplyDelete
  16. Hi Tom, this is a pretty awesome feature. Two things were missing for me to make it perfect:
    1. store files into a given drive folder, this can be done by adding getFolderById before the createFile, e.g. like this: DocsList.getFolderById('0B0uw1JCogWHuc29FWFJMWmc3Z1k').createFile(...)
    2. the link was always showing up in the first sheet, although I added the file in the 2nd or 3rd sheet. This was driving me crazy and in the end I rewrote the whole thing, details here: http://howto.pui.ch/post/73949358752/how-to-attach-a-file-to-google-spreadsheet
    although in retrospective, maybe all what is missing in your script is a "getSheetByName", didn't test that though.

    ReplyDelete
  17. Thanks Phillpp,

    the code was meant as a "starting point" to be adapted, so well done. And thanks for sharing/improving on it.

    Tom

    ReplyDelete
  18. Hey Tom,

    Is the 'Attach' tab a new feature of Google Sheets or a feature that you have to enable? It doesn't appear along the top of documents that I have recently created.

    I have also tried the hyperlink option and it keeps coming up with a parse error.

    I was also wondering if you could help. I am trying to put together a simple database of members of a charity that I run and want to attach documents and if possible emails in cells for each member. Ideally I would like it to be a bit like a GP database with basic info on the spreadsheet and access to further information. Do you think that this is possible using Google Docs?

    Any help would be much appreciated.

    ReplyDelete
    Replies
    1. Hello Leila,
      I had the same error.
      For some reason the syntax of the hyperlink function is different in my spreadsheet (use ';' as parameters separator) compared with the Tom's spreadsheet (use a ',').
      So if you are facing the same problem you should change all the hyperlink instace in the code from :
      var cell_value = '=hyperlink("' + url + '","' + text + '")'
      to
      var cell_value = '=hyperlink("' + url + '";"' + text + '")'

      Don't know why this... is not a user level configuration since in my drive I have the Tom doc copy working perfectly in one way while mine is working in the other.

      Hope it helps..
      Regards

      Delete
  19. Leila,

    the Attach feature is one I made using Apps Script in the spreadsheet.

    Learn Apps Script here: https://developers.google.com/apps-script/overview
    ... or if you're completely new to Javascript, maybe begin here: http://www.codecademy.com/tracks/javascript

    Good luck!

    Tom

    ReplyDelete
  20. I just showcased your script in a G SLAM on Air. Great work.. Simple to use

    ReplyDelete
  21. Hi Ron, I have given you my banking details for the royalty cheques haven't I?

    Thanks :-)

    p.s What's a G SLAM on Air?

    ReplyDelete
  22. Hi Tom,

    This is a perfect tool for Google Spreadsheets. I use it frequently.
    I am wondering if something similar could be created for any other type of Google Docs? Especially documents.

    Many thanks.

    Istvan

    ReplyDelete
  23. Istvan,

    Google Docs has similar tools, but they require coding slightly differently. For some reason Google Docs UI code is different to Google Spreadsheets UI code. Don't know why they did that..

    You could try it yourself, but there isn't much to gain over simple copy n paste really...

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. One nice improvement would be the option to choose in which folder to upload the document you select....

    Anyway... "cooler then cool"

    ReplyDelete
  26. Hi Tom. Wondering what happened to the code? I am a professor and have over 100 students sharing editing rights to a Google Drive Spreadsheet. We'd LOVE to have your feature!

    ReplyDelete
  27. Liam,

    look in the linked spreadsheet. Tools > Script Editor

    Tom

    ReplyDelete
  28. Hi Tom,

    Unfortunately, the code is not working properly with the new google spreadsheet.
    There is a leading single apostrophe ' prevents it from working properly.

    Best regards,
    Istvan

    ReplyDelete
    Replies
    1. Can you tell me what needs to be changed to get the code working properly with the new Google spreadsheets?

      Thank you.
      -Phil

      Delete
  29. Thanks Istan. I've not played with new spreadsheets much simply because I can see from a distance that loads of Apps Scripts are going to break. Woeful upgrade by Google. They are losing the plot really.

    ReplyDelete
  30. Could someone please help me... the attach button does not appear in my google docs? Why is this?

    ReplyDelete
  31. Any suggestions? ... the attach button does not appear in my google docs? Why is this?

    ReplyDelete
  32. Is it a spreadsheet? Have you run onOpen()?

    ReplyDelete
  33. Hello Tom.

    I am trying to use your script in a google sheet spread sheet. I am trying to link a file in a cell on my g drive. I copied and pasted the script in to the script manager, saved it and ran onOpen from the Run tab in Script manager. Tha Attach tab appears next to Help and I have the three attachment options. When I chose a file from my drive it only shows the whole path In the cell and edit window at the top. I do not get the "go to LInk" option. Nothing happens after that. I can not open the link

    ReplyDelete
  34. Lots of Apps Script features don't work in New Sheets.

    ReplyDelete
    Replies
    1. OK got it to work

      cell.setFormula( cell_value ) instead of setValue

      Delete
  35. OH I will pass the thanks on to the scripting guy ;-)

    ReplyDelete
  36. Tom I was hoping for your help please. "You do not have permission to perform this action" - i've read everything on permissions and the script is permissioned in my account settings. I can't seem to go any further.

    Any suggestions wuld be warmly welcomed.

    ReplyDelete
  37. Have you made a copy of the spreadsheet for yourself?

    Then, you may need to Run the code in the Script Editor to get it to authenticate...

    Tom

    ReplyDelete
  38. Tom,

    Thanks for the great script. Have you had a chance to test it out in the new sheets? I was able to get the "Attach" menu item to load, but when I try running to attach from Drive, I get the following error
    TypeError: Cannot find function showADocsPicker in object DocsListDialog

    Disclaimer....I'M VERY NEW TO GOOGLE SCRIPTS!!! I'm very versed in Excel VBA, but I'm looking at trying to port a few collaborative sheets to google.

    Thanks
    Guy

    ReplyDelete
    Replies
    1. Not sure what I did different, but I copied and repasted your code into a new google sheet (new version) and now it works.

      Only issue is when attaching a file from my drive, it puts the link on page one of the sheet like someone above described. I'll find the solution above and make the correction.

      Delete
  39. Any ideas on how I could open the dialogue/window directly on a specific folder using the folder id? I spent hours and I don't seem to find a way to do this. Thanks!

    ReplyDelete
  40. Great script, thank you Tom!

    ReplyDelete
  41. Thanks for this. Adds an essential feature for me :)

    ReplyDelete
  42. great script, just noticed that if you upload an image file from your computer through the attach menu, if you try to view the link you get the 'no preview available' error.

    ReplyDelete
  43. The new google spreadsheet requires the use of OAuth 2.0, could you show how to incorporate this into the script??

    ReplyDelete
  44. First off I want to thank you for making my life heaps easier with your script... God bless you for this brother! Has anyone in this forum figured out how to incorporate the OAuth error into the script... I have tried but am still failing... probably because I am a noob at scripting...

    ReplyDelete
  45. Hi Tom

    Looks like a very good script. However, the link provides a spreadsheet that is locked from editing (which I understand), but no menu item, nor when I copy is there a script to play with.

    Please advise.

    ReplyDelete
  46. Well, don't know why it didn't make a good copy before, but now it works.

    For those who want to get the insert gdrive to work, go to the script editor and look for this part (2nd line is the extra code you need). Then it works nicely.

    app.createDocsListDialog()
    .setOAuthToken(ScriptApp.getOAuthToken())
    .showDocsPicker().addSelectionHandler(doclisthandler)

    ReplyDelete
  47. Awesome! I know nothing about coding and didn't know any of this was possible in google docs. It took several tries reading through all the comments, but it's working now and I am stoked. Thank you all!

    ReplyDelete
  48. Would not upload pdf or docm file to gdrive. Tried both "," and ";" in htmllink line. Any other ideas as to what might be off?

    ReplyDelete
  49. Would not upload pdf or docm file to gdrive. Tried both "," and ";" in htmllink line. Any other ideas as to what might be off?

    ReplyDelete
  50. This is now deprecated by Google. Need to re-do the "new" way.

    ReplyDelete
  51. Tom, any luck with re-writing this the new way with DriveApp?

    ReplyDelete
  52. Thanks Tom , awesome script, is working very very well I'm realy happy with this feature, the only thing that I'm trying to discover is how can I attach a file already shared like a public file, because the others users of the sheet doesn't have acess to the files, every time google ask permissions

    ReplyDelete
  53. No longer works. Have these deprecated features been removed?

    FileUpload API is deprecated.Collapse
    File: Choose GDrive File Line: 151
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    Grid API is deprecated.Collapse
    File: Choose GDrive File Line: 146
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    SubmitButton API is deprecated.Collapse
    File: Choose GDrive File Line: 156
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    TextBox API is deprecated.Collapse
    File: Choose GDrive File Line: 153
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    (class) API is deprecated.Collapse
    File: Choose GDrive File Line: 139
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    UiApp API is deprecated.Collapse
    File: Choose GDrive File Line: 139
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    FormPanel API is deprecated.Collapse
    File: Choose GDrive File Line: 142
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.
    Label API is deprecated.Collapse
    File: Choose GDrive File Line: 150
    The API has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.

    ReplyDelete
  54. Google Drive "Error encountered: An unexpected error occurred" at the end of every attempt to attach a file from computer.

    Google Drive "Error encountered: "DocsList" is not defined." upon trying to attach from Drive.

    ReplyDelete
  55. DocsList is deprecated...

    ReplyDelete
  56. Hi all,

    I've used this code succesfully for a while, but it seems to be broken now.
    I've tried rewriting/fixing the code, but I can't seem to fix it myself.

    Is there anyone who did succeed making it work in New Google Spreadsheets?

    Would be much appreciated!

    Thanks:)

    ReplyDelete
  57. I tried the Apps, like it very much, even if it not working! I now it depends on the Error encountered: "DocsList" is not defined!
    Tom or anybody els is maybe able to actualizit once the whole script ?!
    I really would appreciate it.

    Thanks

    ReplyDelete
  58. Change line 20 to:
    app.createDocsListDialog().setOAuthToken(ScriptApp.getOAuthToken()).showDocsPicker().addSelectionHandler(doclisthandler)

    also replace the word in lines 41, and 115 from DocsList to DriveApp

    worked for me

    ReplyDelete
  59. Brilliant! I am completely new to Google App Script and followed Tom's instructions above and the amendments by Kevin and it's worked perfectly!

    Thanks!!

    ReplyDelete
  60. Would it be possible to amend this script so it would automatically list files in a specified folder, this would be great as it would update when files added/removed

    ReplyDelete
  61. Hm. I get this: Using DocsListDialog requires calling setOAuthToken. For more information, see: http://googleappsdeveloper.blogspot.com/2014/09/change-in-apps-scripts-docslistdialog.htmlDetailsDismiss

    ReplyDelete
  62. Thanks Kenin this work for me.

    Change line 20 to:
    app.createDocsListDialog().setOAuthToken(ScriptApp.getOAuthToken()).showDocsPicker().addSelectionHandler(doclisthandler)

    also replace the word in lines 41, and 115 from DocsList to DriveApp

    ReplyDelete
  63. I inserted that into line 20, and it at least allows me to view items in Google Drive, but says "Doclist not defined" - anyone figure this out, yet?

    ReplyDelete
  64. For a Doctor's title -- what does DO mean?
    medical

    ReplyDelete
  65. I have a google drive error when tryng to open the example at link https://docs.google.com/spreadsheet/ccc?key=0Ajnu7JgRtB5CdGtoUmM1YnlHaS1KWVowVkxtMnFzWFE#gid=0

    Can anyone indicate another link where I could access to it?

    ReplyDelete
  66. So I have this running, the problem is the view - I can only see one file at a time and scrolling is very hard. The Menu is up top instead of on the left like the image in the original post shows.

    Any ideas for making it display in a more useful way?

    ReplyDelete
  67. you are providing great and quality services, i appreciate you.
    Top online MBA programs

    ReplyDelete
  68. Thank you for this! After reading the comments with the fixes for the new sheets I got this working. Awesome

    ReplyDelete
  69. I really enjoy reading your blog as the postings are so simple to read and follow. Outstanding. Please keep it up. Thanks.
    laser sculpting

    ReplyDelete
  70. This is really great news. Thank you for sharing it with us!
    Holy Quran Khana Kaba

    ReplyDelete
  71. You got a really useful blog I have been here reading for about half an hour. I am a newbie and your post is valuable for me.
    cellular therapy

    ReplyDelete
  72. I use the script for an accounting table and its working perfect!
    The only thing what was strange...when I changed the number format to European the links produced only errors and didn`t work!
    I changed back to english number formats and it worked again fine.
    Any ideas ?
    Greetings from Rome
    Joerg

    ReplyDelete
  73. With every hand using smart phone and its extensive features, the need for mobile presence is so well known. The market is competitive and to create a competitive edge above the rest, you need experts. With every hand using smart phone and its extensive features. Learn more about  visit given links below

    Cado Magenge
    "http://appdevelopmentcompany.com.au/android-application-development.html"
    "http://www.appdevelopmentcompany.com.au/email-marketing.html"
    "http://appdevelopmentcompany.com.au/iphone-application-development.html"
    ”http://appdevelopmentcompany.com.au/ipad-application-development.html”

    ReplyDelete
  74. I was really want to get some more detail and information about the health, body care and clinic but your blog helped me so much thank you for sharing it.
    mammography view

    ReplyDelete

  75. A great many people really tended to reflexively tap on an Adsense advertisement with the outcome, ka-ching Adsense master just got paid...and paid a ton! https://800support.net/sign-up/gmail-sign-up-gmail-register/

    ReplyDelete
  76. This comment has been removed by the author.

    ReplyDelete
  77. Call at the road runner email customer care corporate toll free phone number and look forward for carrying out emailing processes in easy way. We assure you that third party Roadrunner customer support executives at will help in eradicating undue all type of problems like password recovery and hacking issues instantly.

    ReplyDelete
  78. When you upgrade latest version of window 10 than you face various issues when you operate Skype mail. With the help of Skype guidelines you simply find solution. If you face any other issues than visit to our website.http://customercarenumber.blogdigy.com/fix-skype-audio-not-working-on-windows-10-2822747

    ReplyDelete
  79. With help of customer service number of facebook solve facebook messenger voice call working issues and problems instantly& immediately. http://customercarenumber.angelfire.com

    ReplyDelete
  80. With help of these steps & tips you can instantly sign in temporary locked yahoo mail account. If you cannot sign in into your yahoo mail than visit to our website@:- https://www.youtube.com/watch?v=vCFuXl7L5S0&feature=youtu.be

    ReplyDelete
  81. Your blog is very useful for me,I really like you post.Thanks for sharing.
    gclub casino
    goldenslot
    Gclub จีคลับ

    ReplyDelete
  82. WHAT IS FREQUENTLY LOGIN FAILURE ISSUES OF YAHOO MAIL ACCOUNT? GET SOLUTION.Yahoo Toll Free Help Phone Number

    ReplyDelete
  83. Many times it happens that Gmail users forget their account password and this manner, they confront issues in accessing Gmail account. In addition because of the technical mishaps, sometimes they come across an issue in login the account. The particular trouble will delay the work of a user. The Gmail customer service professional will provide you round the clock tech support assistance for all the issues in your account.

    ReplyDelete
  84. You need to also change the password quickly as possible. These solutions will surely help you in fixing the hacked account troubles. For getting the further technical assistance, you can dial a Yahoo mail helpdesk customer service phone number. The Yahoo support number will provide you the complete solution to the same issue.

    ReplyDelete
  85. You can consult a certified customer service team of Gmail account. With its aid, you will indeed obtain the exceptional assistance within a short duration of time period. Without any hesitation simply get in touch with them and get the best help for Gmail account problem.

    ReplyDelete
  86. If you get an error msg and code when you take a print from HP printer than simply solve it with the help of these supportive guidelines of HP printer.https://www.kiwibox.com/techhelpsupport/blog/entry/140728557/how-to-clear-an-hp-check-printer-cartridge-error-code/?pPage=0

    ReplyDelete
  87. You can call our Yahoo toll free helpline number to get in touch with our technicians to know more about the localities for onsite service.

    ReplyDelete
  88. You try to affix an important document to your email in Yahoo mail, a loading style progress bar, assist you in tracking the procedure of attaching a file. At times, Yahoo mail account creates an issue in attaching a file and there might be many reasons behind the specific trouble and verified via Yahoo helpline toll free phone number. In most of the cases, attachment issues occur when the limit of an attachment gets exceeded.

    ReplyDelete
  89. Find the Yahoo mail technical support issues can be easily fixed via the help of Yahoo help page.

    ReplyDelete
  90. If you enable to set up Epson wireless printer on your PC and laptop than with the help of these tips and tricks simply set up Epson printer without any problem. Let’s follow our website for quality support and service of printer related issues.Printer Help Phone Number

    ReplyDelete
  91. Funeral rites and Shradh must be distinguished from each other.
    Shradh is performed every year on the anniversary of the person as per the Hindu calendar. Mahalaya Shradh is performed during a fortnight called as the “Pitra Palesh”. In the month of Ashwin of the Hindu Vikram Samvat 2012. Sunday Bhadra Pud and 15th October 2012, Monday Amavasya. Shradh can be performed on every New Moon day or “Amavasya”.

    http://www.sanskaarkidskingdom.com/

    ReplyDelete
  92. to solve your printer queries, please follow us here
    http://technicalguru.beep.com/

    ReplyDelete
  93. Hello Readers, If anyone is looking for email support services so you can easily read Gmail Customer Service from where you will get solution for your query.

    ReplyDelete

Post a Comment

Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.



The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.


The Web Application The web application, which only checks items in or out and should be used on a phone in conjunction with a QR cod…

One-To-Many Relationship in a Google Spreadsheet

It's often the case that you want and need to be creating a database to store your data, but Google Spreadsheets are just so handy aren't they? But Google Spreadsheets are very good at relational data.

Here's an example where, you want to have one column for the name of your recipe and another for the ingredients ( comma separated ).

How you use this script is you click on the cell you want to be relational and choose the Admin > Show Relationship Editor. This opens up a dialog window showing you all the options included so far. You then alter the ingredients and it saves a comma separated list into the spreadsheet.







Here's the spreadsheet. Use File > Make a copy to see it work and rummage around in the code.

If anyone can help make the UI prettier I'd be grateful, thanks.