Skip to content

Instantly share code, notes, and snippets.

@mhawksey
Last active December 11, 2015 18:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mhawksey/4641774 to your computer and use it in GitHub Desktop.
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/
// 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();
}
}
@tidymonkey81
Copy link

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?)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment