Sad to Happy

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!

To get this setup follow these quick steps to start rocking your Google Forms world!

  1. In your Google Spreadsheet click Tools > Script editor…
  2. 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);
         }
    }
  3. Paste the copied script into the Google Script editor
  4. Save the project and enter Save As Doc as the name of the project
  5. Close the project window and in your Google Spreadsheet click Tools > Script manager...
  6. Click the Save As Doc script and click Run
  7. 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
  8. Finally, click Run again and enter a name for the Google Document that will be saved in your Google Documents List.

Enjoy!

12 Comments

  1. Thank you so much for sharing this. Seriously. Changing my workflow. Very appreciative. I’m going to share this with so many people!

  2. […] to students.[2] (Alternatively, you can use a script within Google Drive to do the same; see http://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 […]

  3. ayt says:

    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?

  4. Matt Green says:

    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

    • James Bowles James Bowles says:

      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!

  5. James Bowles James Bowles says:

    Izhar,
    I plan on publishing this myself soon. Any ideas for improvement would be appreciated!

  6. Izhar Aazmi says:

    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!

  7. Kathryn Odell says:

    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!

  8. Kathryn odell says:

    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?

  9. James Bowles James Bowles says:

    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.

  10. Marcel says:

    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

  11. Jana says:

    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

Leave a Comment