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:
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.
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...
A new version is here: https://docs.google.com/spreadsheets/d/1BV9CTI7sq3wJXolWmq2Gji5cEmq12ngg7GCugXUzSYY/edit#gid=0
File > Make a copy.. then use as you like...
Cooler than cool! Thank you!
ReplyDeleteThe insert file dialog could indeed use a bit more work but it works just fine now as well.
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....
ReplyDeleteGreat 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!
ReplyDeleteThanks but I couldn't work out how to make the dialog wider... any ideas?
ReplyDeleteThis 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?
ReplyDeleteThere is a spreadsheet linked at the bottom. Open it and use the menu File> Make a Copy.
ReplyDeleteNow 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!
Tom thanks for sharing works well for me with clear instructions. Alan
ReplyDeleteThank you very much for this script, but when I insert a link of a pdf file from mi Drive, the cell says "#ERROR". Why?
ReplyDeleteI had to change the comma in =hyperlink() to a semicolon.
DeleteReplace 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.
Is there any more than that? You might have to do a bit of debugging...
ReplyDeleteTHX!
ReplyDeleteHi, the fields are all greyed out so I cant copy the script. Do I need access?
ReplyDeleteApologies, I did not do the copy of the sheet.
ReplyDeleteI did get this error when I ran it "Oops
Script function upload_file could not be found"
Or when selecting from a file on your computer, this error message, "Error encountered: Script function not found: InsertGDriveHandler
ReplyDeletemmm?! Sorry don't know what that problem is, I just tried it again and it works for me.
ReplyDeleteFreaking AWESOME! I wonder why this isn't core by Google. They owe you a beer, mate.
ReplyDeleteThanks Andy... I keep trying to convince them that I know best... :-)
ReplyDeleteHi Tom, this is a pretty awesome feature. Two things were missing for me to make it perfect:
ReplyDelete1. 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.
Thanks Phillpp,
ReplyDeletethe code was meant as a "starting point" to be adapted, so well done. And thanks for sharing/improving on it.
Tom
Hey Tom,
ReplyDeleteIs 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.
Hello Leila,
DeleteI 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
Leila,
ReplyDeletethe 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
I just showcased your script in a G SLAM on Air. Great work.. Simple to use
ReplyDeleteHi Ron, I have given you my banking details for the royalty cheques haven't I?
ReplyDeleteThanks :-)
p.s What's a G SLAM on Air?
Hi Tom,
ReplyDeleteThis 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
Istvan,
ReplyDeleteGoogle 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...
This comment has been removed by the author.
ReplyDeleteOne nice improvement would be the option to choose in which folder to upload the document you select....
ReplyDeleteAnyway... "cooler then cool"
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!
ReplyDeleteLiam,
ReplyDeletelook in the linked spreadsheet. Tools > Script Editor
Tom
Hi Tom,
ReplyDeleteUnfortunately, 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
Can you tell me what needs to be changed to get the code working properly with the new Google spreadsheets?
DeleteThank you.
-Phil
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.
ReplyDeleteCould someone please help me... the attach button does not appear in my google docs? Why is this?
ReplyDeleteAny suggestions? ... the attach button does not appear in my google docs? Why is this?
ReplyDeleteIs it a spreadsheet? Have you run onOpen()?
ReplyDeleteHello Tom.
ReplyDeleteI 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
BTW im using new google sheets
DeleteLots of Apps Script features don't work in New Sheets.
ReplyDeleteOK got it to work
Deletecell.setFormula( cell_value ) instead of setValue
Thanks Barry...
ReplyDeleteOH I will pass the thanks on to the scripting guy ;-)
ReplyDeleteTom 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.
ReplyDeleteAny suggestions wuld be warmly welcomed.
Have you made a copy of the spreadsheet for yourself?
ReplyDeleteThen, you may need to Run the code in the Script Editor to get it to authenticate...
Tom
Tom,
ReplyDeleteThanks 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
Not sure what I did different, but I copied and repasted your code into a new google sheet (new version) and now it works.
DeleteOnly 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.
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!
ReplyDeleteGreat script, thank you Tom!
ReplyDeleteThanks for this. Adds an essential feature for me :)
ReplyDeletegreat 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.
ReplyDeleteThe new google spreadsheet requires the use of OAuth 2.0, could you show how to incorporate this into the script??
ReplyDeleteNo. Can you Daniel?
ReplyDeleteFirst 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...
ReplyDeleteHi Tom
ReplyDeleteLooks 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.
Well, don't know why it didn't make a good copy before, but now it works.
ReplyDeleteFor 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)
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!
ReplyDeleteWould not upload pdf or docm file to gdrive. Tried both "," and ";" in htmllink line. Any other ideas as to what might be off?
ReplyDeleteWould not upload pdf or docm file to gdrive. Tried both "," and ";" in htmllink line. Any other ideas as to what might be off?
ReplyDeleteThis is now deprecated by Google. Need to re-do the "new" way.
ReplyDeleteTom, any luck with re-writing this the new way with DriveApp?
ReplyDeleteThanks 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
ReplyDeleteNo longer works. Have these deprecated features been removed?
ReplyDeleteFileUpload 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.
Google Drive "Error encountered: An unexpected error occurred" at the end of every attempt to attach a file from computer.
ReplyDeleteGoogle Drive "Error encountered: "DocsList" is not defined." upon trying to attach from Drive.
DocsList is deprecated...
ReplyDeleteHi all,
ReplyDeleteI'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:)
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!
ReplyDeleteTom or anybody els is maybe able to actualizit once the whole script ?!
I really would appreciate it.
Thanks
Change line 20 to:
ReplyDeleteapp.createDocsListDialog().setOAuthToken(ScriptApp.getOAuthToken()).showDocsPicker().addSelectionHandler(doclisthandler)
also replace the word in lines 41, and 115 from DocsList to DriveApp
worked for me
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!
ReplyDeleteThanks!!
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
ReplyDeleteHm. I get this: Using DocsListDialog requires calling setOAuthToken. For more information, see: http://googleappsdeveloper.blogspot.com/2014/09/change-in-apps-scripts-docslistdialog.htmlDetailsDismiss
ReplyDeleteThanks Kenin this work for me.
ReplyDeleteChange line 20 to:
app.createDocsListDialog().setOAuthToken(ScriptApp.getOAuthToken()).showDocsPicker().addSelectionHandler(doclisthandler)
also replace the word in lines 41, and 115 from DocsList to DriveApp
It worked! Thank you so much!!
DeleteI 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?
ReplyDeleteFor a Doctor's title -- what does DO mean?
ReplyDeletemedical
I have a google drive error when tryng to open the example at link https://docs.google.com/spreadsheet/ccc?key=0Ajnu7JgRtB5CdGtoUmM1YnlHaS1KWVowVkxtMnFzWFE#gid=0
ReplyDeleteCan anyone indicate another link where I could access to it?
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.
ReplyDeleteAny ideas for making it display in a more useful way?
you are providing great and quality services, i appreciate you.
ReplyDeleteTop online MBA programs
Thank you for this! After reading the comments with the fixes for the new sheets I got this working. Awesome
ReplyDeleteI really enjoy reading your blog as the postings are so simple to read and follow. Outstanding. Please keep it up. Thanks.
ReplyDeletelaser sculpting
This is really great news. Thank you for sharing it with us!
ReplyDeleteHoly Quran Khana Kaba
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.
ReplyDeletecellular therapy
I use the script for an accounting table and its working perfect!
ReplyDeleteThe 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
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
ReplyDeleteCado 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”
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.
ReplyDeletemammography view
ReplyDeleteA 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/
Printer Set Up on Windows 10
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteCall 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.
ReplyDeleteWhen 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
ReplyDeleteWith help of customer service number of facebook solve facebook messenger voice call working issues and problems instantly& immediately. http://customercarenumber.angelfire.com
ReplyDeleteWith 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
ReplyDeleteWHAT IS FREQUENTLY LOGIN FAILURE ISSUES OF YAHOO MAIL ACCOUNT? GET SOLUTION.Yahoo Toll Free Help Phone Number
ReplyDeleteMany 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.
ReplyDeleteYou 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.
ReplyDeleteIf 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
ReplyDeleteYou can call our Yahoo toll free helpline number to get in touch with our technicians to know more about the localities for onsite service.
ReplyDeleteIf 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
ReplyDeleteFuneral rites and Shradh must be distinguished from each other.
ReplyDeleteShradh 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/
to solve your printer queries, please follow us here
ReplyDeletehttp://technicalguru.beep.com/
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.
ReplyDeleteHow to transfer files from Yahoo to new email address? You just need follow some steps not, let's visit our website or dial toll free phone number of Yahoo email customer care.
ReplyDeleteIf you are facing any issue related to Windows Defender Antivirus so you may contact our Windows Defender Antivirus Customer support team, it is good service provide 24*7 hours anytime problem with our contact us. Windows Defender Customer Service
ReplyDeleteAs for the new Kaspersky Customer Service provide to the customer care helpline no It is the good service provider with any time installation problem 24 hours support. Kaspersky Customer Service
ReplyDeleteThat's was a great article, I agree with all that you stated and if you need help regarding any issues with Kaspersky Customer Service then you can contact us. Kaspersky Customer Service
ReplyDeleteYou just wanted to get in to your Yahoo email account, but after entering the user name and password; this Temporary Error 1 : UserOpenFailed flashed on your computer screen. What could be more annoying that getting this temporary error when you have correct email address and password, but couldn’t sign in.
ReplyDeleteGreat job!Keep sharing more and more!I just love the work. amazing post.
ReplyDeletehttps://www.contactemailexperts.com/
This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work! if you want to get the information of gift card so visit free imvu gift card generator
ReplyDeleteThis is absolutely exceptional. Despite a variety of articles on this subject, this article contains a number of precious points that have never been read in other articles and also found some interesting topic is Google Play Services the heartbeat of Android smartphone
ReplyDeleteHi, I am not able to chose the drive file, is there an issue with the script that needs to be updated?
ReplyDeleteHello;
ReplyDeleteThis is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles.Yahoo E-Mail Technical Support is providing support on your all Yahoo mail's technical issues. We are the leading Yahoo account recovery services provider in USA, Yahoo phone number support in USA
.
yahoo support phone number
Yahoo Account Pro
how to reset yahoo password
Satta Matka are both some form of gambling and a lottery system. It was initially launched by the originator of Kalyan Matka, Mr. Ratan Khatri, . Check live result on Satta king best. sattaking most Results show like gali results ,desawar Results Satta live results ,faridabad results, ghaziabad Results etc.
ReplyDeleteSattaKing
Satta live results
faridabad results
desawar Results
gali results