Using Google Docs To Make Google Spreadsheets Easier to Read

A lot of our staff are using Google Forms to gather lots of data into spreadsheets, from Grant Application forms to self assessment questionnaires and more.

Spreadsheets are of course great places to store lots of data, but when that data is mainly textual, it is ridiculously hard to read and edit in a spreadsheet.

Our solution has been to generate a Google Doc of the data in a row of data. Sometimes this happens automatically and is emailed to the relevant people and sometimes we add a little interface to be able to say "Make a Google Doc with this row" to the spreadsheet.

The challenge is making it easy to set up.

Our Solution

We've used this a few times. First we create our Google Form and add some data. The spreadsheet now has a list of column headers across the top.

Now, we make a template Google Doc. In Tools > Script Editor we add some code that allows us to insert the spreadsheet header names as funny tags, like this, {Username}. You could of course do this by hand, but when your forms get very complex, or your headers are very long, it's easy to make mistakes.

This code adds a "Show Tags" menu to document, which, in a sidebar shows a list of the spreadsheets header names which can be inserted into the document.


function onOpen() {
  var menu = DocumentApp.getUi().createMenu('Tags')
  menu.addItem("Show tags..." ,"show_tags" )
  menu.addToUi();
}

function get_ss_headers(){
  var ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID')
  var sheet = ss.getSheets()[0] // Get the first one
  var range = sheet.getRange(1, 1, 1, sheet.getLastColumn() )
  var values = range.getValues()[0]
  return values
}

function show_tags(){
  var headers = get_ss_headers( )
  var app = UiApp.createApplication().setTitle("Insert Tags")
  var panel = app.createVerticalPanel();
  var list_box  = app.createListBox(true).setId('list_box').setName('list_box').setWidth(240)
  list_box.setVisibleItemCount(10 )

  for ( h in headers){
    var header = headers[h]
    list_box.addItem(header).setValue(Number(h), header)
   
  }
  panel.add( list_box)

  var handler = app.createServerHandler('insert_tag').addCallbackElement(list_box)
  var button = app.createButton('Insert!' ).setId('button').addClickHandler(handler)
  panel.add( button)
  app.add(panel)
  DocumentApp.getUi().showSidebar(app)

}

function insert_tag(e){
   var app = UiApp.getActiveApplication()
   var list_box = e.parameter.list_box
   var tag = "{" + list_box + "}"
   var doc = DocumentApp.getActiveDocument();
   var cursor  = doc.getCursor()
   cursor.insertText(tag)


}

Copy and paste this code into your document, changing the spreadsheet ID, then run onOpen(). It will ask for authorisation, then the menu will appear, like this.

Once you've added all your fields, you need to first, create a Google Folder and note the ID of it ( you can see it in the URL ) and then add some code to the spreadsheet to render a spreadsheet row into a Google Doc. ( Caveat: This does assume that your header names are unique - with one particularly complex form with multiple pages and stages, we titled questions as a.name, a.institution and b.name, b.institution and so on. )

Go to your spreadsheet and add this code via Tools > Script Editor...


function create_google_doc() {

  var ss = SpreadsheetApp.openById('YOUR_SPREADHEET_ID')
  // Logger.log( ss.getName())
  var sheet = ss.getSheetByName("Form Responses")
  var row = SpreadsheetApp.getActiveRange().getRow()

  //get headers
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0]
  var range = sheet.getRange(row, 1, 1, sheet.getLastColumn())
  var values = range.getValues()[0]

  //Build a dict
  var tags = {}
  for (h in headers){
    var header = headers[h]
    tags[header] = values[h]
  }
   
  try{
    //Get some hard-wired values ( CHANGE THIS FOR YOUR NEEDS )We need some data to name the file
    var student_name = values[2] + " " + values[1]
    var student_email = values[4]
   
    //Make a Google Doc
    var new_doc_title =  student_name + " - Registration Form" // CHANGE THIS TOO.
    var template_id = 'YOUR_GOOGLE_DOC_TEMPLATE_ID' // The ID of your template file
    var template_doc = DocsList.getFileById(template_id)
    var new_doc_id = template_doc.makeCopy(new_doc_title).getId()
   
   // Move new document
    var destination_folder = DocsList.getFolderById('YOUR_FOLDER_ID')
    var doc = DocsList.getFileById(new_doc_id)  //Move to destination folder
    doc.addToFolder(destination_folder)
   
   
    var new_doc = DocumentApp.openById( new_doc_id )
    //Render the values into the doc
    var s = ''
    for ( var t in tags) {
     
      var tag = "{" + t + "}"
      var value = tags[t]
      s+=  tag + " " + value + "\r" // Just for debugging
      new_doc.replaceText(tag, value )
    }
    Logger.log(s)
    //Replace any unreplaced tags for tidiness
    new_doc.replaceText("\{.*?\}", "" )
   
    //Share it to the student, optional
    //new_doc.addViewer(student_email)
 
    //Add URL to the Spreadsheet
    var url = new_doc.getUrl()
    var range = sheet.getRange(row, sheet.getLastColumn()+1 ).setValue(url)
    Browser.msgBox("Document created for '" + student_name + "'  in folder 'Wherever'")
    return new_doc
   
  }catch(e){
    Logger.log( e)
  }

}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var menuEntries = [ {name: "Create Google Doc", functionName: "create_google_doc"}                                      ]
  ss.addMenu("Admin", menuEntries)
}

function url_escape(s){
  var s = encodeURIComponent(s)
  return s
}


Lastly make sure that anyone generating a Google Doc has access to the Template Document, otherwise the code won't work ( they only need View access ).

And there you have it, we use these sorts of scripts for all sorts of occasions where reading form submissions in the spreadsheet isn't appropriate. We've even added code that adds to bottom of the document, a prepopulated URL that examiners can click to complete a form for marking that document, with the student's name and other details already filled in.