Skip to main content

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.











Comments

  1. I like the sounds of this, very handy - will give it a try with one of our forms if I ever get a bit of spare time. Nice job.

    ReplyDelete
  2. Exciting to study this writing piece! You put up an amazing type research and cooperate with your readers. Thanks much personal statement mistakes

    ReplyDelete
  3. The need and demand to write effective letters has existed for long as a means of communication between two individuals. It holds a key importance in all aspects of life including social, personal, and professional. law school application help

    ReplyDelete
  4. Very nice post. Thanks for share this. You talk about Using Google Docs To Make Google Spreadsheets Easier to Read. To know about mental health counseling personal statement read more

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

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

    ReplyDelete
  7. A medical specialist could be a medical man that makes a speciality of matters of the functioning of the guts and its relationship to the remainder of the body. There ar regarding half dozen billion human inhabitants within the world, that is, half dozen billion hearts to stay cardiologists terribly busy. fellowship personal statement help

    ReplyDelete
  8. The job outlook for Physicians within the us is promising and therefore the employment of Doctors is alleged to grow twenty 2 p.c from the year 2008 - 2018, this represents a way quicker average growth for all occupations. The Dr. jobs and outlook can grow attributable to the continuing and active growth of the healthcare-industry. internal medicine letter of recommendation

    ReplyDelete
  9. A biographical analysis of events in author Tom Clancy's age thirty six, 'Year of Revolution' shows an ideal correlation with 'Life Cycles' theory. He went from obscurity to breakthrough with 'The rummage around for Red October' throughout this era during a fateful flip of events, that saw him accepted by associate unlikely publisher. here

    ReplyDelete

Post a Comment

Popular posts from this blog

Inserting A Google Doc link into a Google Spreadsheet

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 necessaryThe 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 si…

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…

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...


Create an AppScript in Google that pulled a .CSV file from a web serverWrite a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API. The Google AppScript Way As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.



function encode_utf8( s ){
//This is the code that "I think" turns the UTF16 LE into standard stuff....
return unescape( encodeURIComponent( s ));
}

function get_csv(){
var url ='http://www-users.york.ac.uk/~admn812/alarms.csv.Active BA Alarms.csv';// Change this to the URL of your file
var response = UrlFetchApp.fetch(url);
// If there's an error in the response code, maybe tell someone
//MailApp.sendEmail("s.brown@york.ac.uk&qu…