I am trying to conceptualize my structure and values needed to organize form responses in multiple sheets within a Google Sheet based on the value selected for one of my form question responses ("Location of Change"). I have a checklist that is completed so far as below. I also listed the additional steps and what I think I need to create to achieve what I'm looking for. Can someone guide me in pointing out where I might have issues or a better approach?
- Open Form - Completed
- Open Spreadsheet - Completed
- Form saves responses in proper format to Spreadsheet - Completed
- Set form responses to a variable - Completed
- Set form questions to a variable - Completed
- Find "Location of Change" Questions in a for loop - Completed
- Get Responses and group response within new tabs in Google Sheets based on value for "Location of Change"
To work on step 7, my original thought is to create a for loop within my for loop based on the form response and then within that for loop create an if/else statement to create a new tab if it doesn't exist and save values to that sheet, otherwise just add values to appropriate tab name.
Does this sound right? Am I approaching this the wrong way?
UPDATE:
function onSubmit() {
//Open Marketing - Discoveries and Changes - v1
var form = FormApp.openById('id');
//Open Marketing - Discoveries and Changes - v1
var sheet = SpreadsheetApp.openById('id');
//Capture Form Responses
var formResponses = form.getResponses();
//Capture Questions
var items = form.getItems();
for (var i in items) {
var title = items[7].getTitle();
Logger.log(title);
}
}
Your general thought process is fine, but you are making inappropriate choices for the specific objects and methods that you're using to address the problem.
You've started by naming your function onSubmit(), which implies that it is some type of Form Submission trigger function. As long as you're intending to process form responses as they arrive, this is the right approach. There are two flavours of these trigger functions, and it's not clear which you intend this to be, so you need to make a decision there.
Use properties of the Event object
Trigger functions respond to events, and when they are invoked trigger functions are given an Event object that includes the most relevant information about the event.
Your function isn't currently using the Event object, but it should, with an immediate benefit: In your initial script you are hard-coding the IDs for the Form and Spreadsheet, but you don't need to.
Depending on the type of Form Submission trigger function this is, you can modify the preamble to take advantage of the linkage between forms and spreadsheets, using the event data that is passed to the trigger function when it is invoked. (This assumes that you have linked a spreadsheet with your form - for your application this may not be required.)
If your script is a Google Forms Form Submit Submit Event with an Event parameter e:
var form = e.source;
var ssId = form.getDestinationId();
var ss = SpreadsheetApp.openById(sheetId);
If your script is a Google Sheets Form Submit Event with an Event parameter e:
var sheet = e.range.getSheet();
var ss = sheet.getParent();
var form = FormApp.openByUrl(ss.getFormUrl());
Using either of those approaches will produce more portable code, since they adapt to the context of the event being handled.
But that's not all... the responses that you're opening the form for are provided as properties on the Event object as well! Because of that, you may find that you don't need to go opening the form at all.
Storing responses in specific sheets according to input data
To work on step 7, my original thought is to create a for loop within my for loop based on the form response and then within that for loop create an if/else statement to create a new tab if it doesn't exist and save values to that sheet, otherwise just add values to appropriate tab name.
The language here is a bit confusing, but here's what I understand it to mean: Select a target sheet within the destination spreadsheet and append the new responses, depending on what the response to "Location of Change" is. Don't worry about the details of looping etc. just yet.
From what we've already seen about Event objects, all the info you need to operate is available to you. For example, in a Google Sheets Form Submission trigger function you could do this:
// Choose destination sheet according to Location of Change
var destSheet = getSheet(ss, e.namedValues['Location of Change']);
// Store the current response in destination sheet
destSheet.appendRow(e.values);
That is simplified by use of a utility function, getSheet().
Get or create a sheet
This utility function encapsulates the logic to either get a reference to an existing sheet with a given name, or create one if necessary.
/**
* Get the sheet with given name. Create sheet if it doesn't exist.
* New sheet will be created with optional headings, if provided.
*
* From:
*
* @param {Spreadsheet} spreadsheet Google Spreadsheet object.
* @param {String} sheetName Sheet name to get.
* @param {String[]} headings Optional array of headings (for new sheet).
*
* @returns {Sheet} Sheet object.
*/
function getSheet( spreadsheet, sheetName, headings ) {
spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(sheetName);
if (sheet == null) {
// Not found, so add new sheet
sheet = spreadsheet.insertSheet(sheetName);
if (headings && headings.constructor === Array) {
sheet.getRange(1,1,1,headings.length).setValues([headings]);
}
}
return sheet;
}
Sheets Alternative
You could accomplish almost all this without a script, just using features of Google Sheets.
- Start with your current form, with a Spreadsheet response destination.
- In the spreadsheet, form response will typically go to a sheet named "Form Responses", perhaps with a number after it.
- Insert additional sheets for the categories you're interested in. In each of these, use a spreadsheet QUERY function to select the sub-set of responses that are relevant. This is simplified if you use a common cell, A1 say, to store the category, then put the QUERY function in a subsequent row, referring to the value in A1. That way, additional sheets can be copied from this one, and adjusted by modifying just the content of A1.
