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.
Written by Unknown
We are one of the initiators of the development of information technology in understanding the need for a solution that is familiar and close to us.
0 comments:
Post a Comment