Make Google Forms Results Readable!
Google Forms is an excellent quick way to produce a form for polling, surveys, or gathering any data from any amount of people. It produces a nice spreadsheet of all the submissions to the form and even provides some basic analytics for the data. You may be thinking, “This is great! I can get all my friends to send me a lengthy explanation of the meaning of life through my Google Form and have it displayed nicely in a Google Spreadsheet!” Unfortunately my friend, you will be a little disappointed as the spreadsheet becomes virtually unreadable when long paragraph field entries are submitted. Fortunately, I created a neat little script that will convert the contents of any Google Spreadsheet into a Google Document!
UPDATE
I recently published this script as a Google Sheets add-on. You can install it by clicking Add-ons > Get add-ons… in the Sheets menu. Then find Save as Doc and install it!
To get this setup follow these quick steps to start rocking your Google Forms world!
- In your Google Spreadsheet click Tools > Script editor…
- Copy the following script
function saveAsDOC() { // Prompts the user for the file name var fileName = Browser.inputBox("Save DOC file as (e.g. myDOCFile):"); // Check that the file name entered wasn't empty if (fileName.length !== 0) { // Create a file in the Docs List with the given name and the DOC data var doc = DocumentApp.create(fileName); } else { Browser.msgBox("Error: Please enter a DOC file name."); } // Get the range in the spreadsheet var ws = SpreadsheetApp.getActiveSpreadsheet().getDataRange(); try { var data = ws.getValues(); // Loop through the data in the range and create paragraphs with the appropriate heading if (data.length > 1) { for (var row = 1; row < data.length; row++) { for (var col = 0; col < data[row].length; col++) { if(data[row][col]!=""){ var section = doc.appendParagraph(data[0][col]); section.setHeading(DocumentApp.ParagraphHeading.HEADING4); doc.appendParagraph(data[row][col]); } } // Add a page break to end of each row, except for the last one if (row < data.length-1) { doc.appendPageBreak(); } } } } catch(err) { Logger.log(err); Browser.msgBox(err); } } - Paste the copied script into the Google Script editor
- Save the project and enter Save As Doc as the name of the project
- Close the project window and in your Google Spreadsheet click Tools > Script manager…
- Click the Save As Doc script and click Run
- After clicking Run for the first time Google will ask you to authorize the script to run. Click Authorize and then Close in the new window that appears
- Finally, click Run again and enter a name for the Google Document that will be saved in your Google Documents List.
Enjoy!
James! You are da MAN! OMG…seriously….you just made my YEAR! i was so frustrated in my grad school work trying to get this dang google spreadsheet to output in a readable way, with each header rown repeating for EVERY response. I am SO very happy I happened upon your post. THANKS very, VERY much for taking the time to share. And now, I’ve got the script saved for future reference.
Thank You Again!
Best ~ Jana Tate
Austin, Texas
Hi James,
great script, but I get this error message:
Missing } after function text. (line 1, file ‘Code’)
Do you know what I could do about this?
thanks,
Marcel
Hi Marcel,
This most likely means one of two things, an extra opening curly brace ‘{‘ was left when pasting the script into the script editor, or a closing curly brace was missed when copying the script. My suggestion is to delete everything from the script editor and copy and paste the code again being sure to copy everything in step 2.
One thing of note is this only works on the spreadsheet view of form responses, not the form itself.
This is exactly what I have been looking for! Unfortunately, I am having the same experience as a previous poster:
Missing } after message body (line 1 file” Code”)
I pasted everything (from “function” to the two “} }” into the first line of the script editor. Any suggestions?
Your script works great once you know one extra piece of information! When you copy the script, you will need to make sure each line starts exactly like your script, above. That means you need to take a few extra minutes to hit return in the appropriate spots. Thank you for your work!
Hey mate, thanks a lot and lot more than that. And may I please improve it more and publish it as an App for everyone to use?
Please reply soon!
Izhar,
I plan on publishing this myself soon. Any ideas for improvement would be appreciated!
James! Thank you for posting this! I’m having trouble and hoping you might have a suggestion for me.
It works great but each time I try to use it on a new document I have to re-create it. Any ideas on what I might be doing wrong?
Thanks!!!
Matt
Hi Matt,
Since this is just a Google Spreadsheets script, it is only associated with the single form results spreadsheet it was added to. So, unfortunately, you will need to add it to each form results spreadsheet you wish to make readable. Glad you like it!
Thanks so much for this script James!
I run a program that uses Google Forms to collect applications with long essay responses. This script makes it much easier to evaluate the applications.
I wonder if it would be possible to alter the script so that the resulting Google Document would also have a table of contents of the submissions? I figure that this would involve:
– switching the headers to be normal text (bolded?) so that they don’t get caught in Google Doc’s Table of Contents feature
– adding script language that would look for the item under a specific column (ex. “Name” or Column A) and make that text a header so that it shows up in the Table of Contents
– Add a Table of Contents to the beginning of the Google Doc
I don’t know how to code, but I’m slowly learning with CodeAcademy. All I’ve managed to do so far is switch the headers to normal text. I’m stumped on the rest. Thoughts?
[…] to students.[2] (Alternatively, you can use a script within Google Drive to do the same; see https://ctl.mesacc.edu/blog/make-google-forms-results-readable/. A big thanks to Miriam Posner for posting this to Twitter!) You can also combine all submissions […]
Thank you so much for sharing this. Seriously. Changing my workflow. Very appreciative. I’m going to share this with so many people!
[…] reformat them from an unreadable Google Spreadsheet into a clean-looking Google Doc (using this handy script), remove the names, and evaluate them that […]
[…] them from a relatively hard-to-read Google Spreadsheet into a clean-looking Google Doc (using this handy script), remove the names, and evaluate them that way” he says on the […]
THANK YOU! I just spent 25 minutes looking for this post again. Anyway, I have saved the instructions in my drive for future reference. 🙂
Hi Jen,
I created a Google Sheets add-on that accomplishes this task. I recommend using the add-on versus this script I wrote.
Hey, I do have the add-on, but it is not responding and now I have an issue because I use the Google Form as a Lesson Plan recorder. I was taking the information from the sheet and converting it to a DOC and dispersing it to my teachers for use in their classes. Now, it has stopped working, is it possible there is an update that has caused it to stop working since Wednesday March 4th? That is the last day that I used it successfully. I tried yesterday and today without success.
Thank you for your help and response,
R
I tried to find your add-on view the Sheets menu, but you never give a name here to look for it amongst the other options. I tried another service, but if you help me find your I might try it, too.
The Add-on is called Save As Doc.
Thank you so much for sharing this. Seriously.
This worked so great and was exactly what I was looking for. Thanks for sharing!
I am an intern and was looking at making a nice impression by automating the weekly status accomplishment process.
As a bright light idea, I thought of making a Google form and sending it across the team.
Unfortunately, my manager would have to collect the responses and format them after I leave, later on, at the end of summer.
This Add-On has made life much much easier and earned me an Intern Idea of the Summer award! (Just kidding) 🙂
Add 1 more to the zillion and counting Thank you list!
I love you, man! At last I can read all that the parents are telling me about their child who is a struggling reader. With these kids, everything the parent can tell me is valuable, and I was missing pieces and not getting the full picture until I found YOU!
You ought to put a donation thing on the page, because I’d give you a small amount for sure! Small because I’m a teacher who spends all her money on the kids! As do so many.
Anyway, wow…what an incredible help this script (now an Add-on) is. You amaze me!
Thank you.
Thanks a ton. Amazing script , makes the work so easy.
Thank you so much for this! You are my hero!
Thank you so much!! I was the picture on the left until I found this post. Now Im the picture on the right!
Thank you so very much!
Any way us non-programmers can do this? I developed a form no problem – the data gets output to a spreadsheet no problem… But now I have no clue how to implement any of these “fixes” because I am a just a non-programming laymen and don’t know what a script is or what to do with one…
Marti,
This is now a Google Sheets Add-on. You can install it by clicking Add-ons > Get add-ons… in the Sheets menu. Then find Save as Doc and install it!
This is fantastic… And thanks for making it easy by creating the add-on!
Is there any way to set it up to run automatically whenever a new form submission comes in? That would be over-the-top great!
You are a lifesaver! Thank you so much, James! I am telling sharing this with all my friends!
Needed to qualitatively analyze all posts from a blog for my sociology thesis and was only able to get a csv from the scraper (which was much more cumbersome to work with). Your script perfectly fitted into the last step of my process. Thanks a lot 🙂
Thank you!!!
OMG, thank you so much! This was beyond a great boon! ♥♥♥
Great job!
I am wondering if you can help me with a project that seems to be headed in a similar direction as your script.
I am having folks enter notes throughout the day on places. Each place has it’s own ID number. Multiple Note entries are made on each ID number and they show up in the Form Responses sheet.
I want to have a script like yours, but triggered for any new entry, to ADD the new note to a GOOGLE DOC in a folder for that ID.
This means for each ID there is a Google Shared Folder, and in that folder is a Google Doc that has a chronological list of the Notes made for that place.
This is a little different than what you have done. More involved too, but I hope you can help me or at least hold my hand while I get through it.
Thanx for sharing your work here!
Jim
Hi Jim,
It sounds like you need an onSubmission hook from Google Forms and then you can call my script to run. You will need to adapt it to use Google Forms instead of Sheets. I haven’t looked into that API much, but I imagine it can be done.
Thanks for this resource! I have used your instructions many times with success. However, today when I tried to run the script I keep getting an error message: Cannot call Browser.inputBox() from this context. (line 3, file “Code”)
I’m not sure what this means – do you know what I might be doing wrong?
Hi Tiona,
I suggest using the Save As Doc Add-on now available in the Add-ons Menu in Google Sheets. Simply install it and it should run without issue. Glad you’re enjoying it!
I’m so glad I found this. I tried it and it works great! The only issue is that when an additional form is submitted, the document isn’t automatically updated. I have it embedded in a website and it isn’t updated their either. I did select ‘automatically republish when changes are made’ in the Google Doc publish settings.
Is it designed to update automatically and if not is there something I can do to work this out?
Thanks so much!
Hi,
So I am following the instruction with your Add-on and I guess I am missing something. Save as is where? Google sheets saves as we type and does not offer the “Save as” feature. Help!