Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

commented Mar 11, 2013

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
You can’t perform that action at this time.