I have created a google form that has 5 demographics questions, then 60 scale questions where the user will rate how much it applies to them (choose 1-5). I have incorporated the copyDown add-on to allow for combining multiple questions into a specific category score (ie. questions 11, 31, 51 summed together give a total score for a category) displayed to the right (starting in column BR) of the auto-generated data from the form. I would like a way to report each form submission through email, and found a tutorial at http://www.labnol.org/internet/google-docs-email-form/20884/ to send an email with the data submitted in the form.
/* Send Google Form by Email v2.1 */
/* For customization, contact the developer at [email protected] */
/* Tutorial: http://www.labnol.org/?p=20884 */
function Initialize() {
var triggers = ScriptApp.getProjectTriggers();
for(var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger("SendGoogleForm")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function SendGoogleForm(e)
{
try
{
// You may replace this with another email address
var email = Session.getActiveUser().getEmail();
// Optional but change the following variable
// to have a custom subject for Google Form email notifications
var subject = "Google Docs Form Submitted";
var s = SpreadsheetApp.getActiveSheet();
var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
// Only include form fields that are not blank
for ( var keys in columns ) {
var key = columns[keys];
if ( e.namedValues[key] && (e.namedValues[key] != "") ) {
message += key + ' :: '+ e.namedValues[key] + "\n\n";
}
}
// This is the MailApp service of Google Apps Script
// that sends the email. You can also use GmailApp for HTML Mail.
MailApp.sendEmail(email, subject, message);
} catch (e) {
Logger.log(e.toString());
}
}
This script works great to send the email with the responses from the form, however, fails to include any of the additional columns created in the google spreadsheet with the formulas to sum the categories.
Is there any way to send an email after each form submission to include any data submitted with the form, and any additional data manipulation found in the columns to the right of the form data that uses formulas to summarize the categories?
Thanks in advance for any advice!
