Last active
December 11, 2015 18:28
-
-
Save mhawksey/4641774 to your computer and use it in GitHub Desktop.
Google Apps Script to split form submissions by value in first field (e.g. split student submissions by student name). Automatically create new spreadsheet for each user completing a form. Used in http://mashe.hawksey.info/2011/07/apps-script-intro-form-split/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// If not installed from the Apps Script Library to start form processing select Run > onInstall twice (once to authnticate), the second time to add the trigger | |
function doOnFormSumbit(e) { | |
var doc = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheetName = e.values[1]; // assuming field to split data by is first form element | |
// see if new sheet | |
var sheetToWrite = doc.getSheetByName(sheetName) || null; | |
// if new sheet insert column headers | |
if (sheetToWrite == null){ | |
var sheets = doc.getSheets(); | |
var sheetToWrite = doc.insertSheet(sheetName); | |
var sheet = sheets[0]; // assuming first sheet has the column headers we need | |
var colHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); | |
sheetToWrite.getRange(1, 1, 1, sheet.getLastColumn()).setValues(colHeader); | |
} | |
// copy form data submitted | |
var insertRow = sheetToWrite.getLastRow()+1; | |
sheetToWrite.getRange(insertRow, 1, 1, e.values.length).setValues([e.values]); | |
} | |
function onInstall(){ | |
if (ScriptApp.getScriptTriggers().length === 0) { | |
ScriptApp.newTrigger("doOnFormSumbit") | |
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()) | |
.onFormSubmit() | |
.create(); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I couldn't see how this creates a new spreadsheet file ... does it create a new SHEET within an already present spreadsheet?
If you care to go further...
How can you go about renaming the new spreadsheet file with the original form response file name and the form respondent username
e.g. originalformresponsefilename(username).
can destination directory be specified (or will it default to the location of the original form response file?)