I have been trying to create an app script that will send a formatted email summary of each response to someone. I keep getting an error message returned for function onFormSubmit(e) { var responses = e.namedValues; The script is connected to the spreadsheet and not the form. I tried making the spreadsheet and then creating the form from the spreadsheet. I have deleted and reinstalled the triggers. From the other questions, I have been looking at if the e.namedValues is not working its because the script is connected to the form and not the sheet. I know that is not my problem here. This code was adapted from a project I used in the past that did work. So I am not sure what else to try. Any ideas or suggestions would be greatly appreciated.
var EMAIL_TEMPLATE_DOC_URL = 'google doc url';
var EMAIL_SUBJECT = 'Report Completed';
/**
* Installs a trigger on the Spreadsheet for when a Form response is submitted.
*/
function installTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActive())
.onFormSubmit()
.create();
}
/**
* Sends a customized email for every response on a form.
*
* @param {Object} event - Form submit event
*/
function onFormSubmit(e) {
var responses = e.namedValues;
// If the question title is a label, it can be accessed as an object field.
// If it has spaces or other characters, it can be accessed as a dictionary.
var email = '[email protected]'();
var timestamp = responses.Timestamp[0];
var name = responses['Name of Staff Person completing form'][0].trim();
var researcht = responses['How much time did you spend researching?'][0].trim();
var mt = responses['How much time did you spend in meetings or doing tasks related to meetings?'][0].trim();
var wt = responses['How long did you spend practicing workshops?'][0].trim();
var rtt = responses['How long did you spend preparing for your Real Talk?'][0].trim();
var wpt = responses['How much time did you spend presenting workshops?'][0].trim();
var rtpt = responses['How much time did you spend hosting Real Talks?'][0].trim();
var misct = responses['How much time did you spend on miscellanous tasks?'][0].trim();
var topic1 = responses['First Topic Researched'][0].trim();
var topic2 = responses['Second Topic Researched'][0].trim();
var topic3 = responses['Third Topic Researched'][0].trim();
var meet1 = responses['First Meeting and Tasks'][0].trim();
var meet2 = responses['Second Meeting and Tasks'][0].trim();
var meet3 = responses['Third Meeting and Tasks'][0].trim();
var practicedw = responses['What workshop(s) did you practice?'][0].trim();
var practicedrt = responses['What Real Talk did you prepare for?'][0].trim();
var workshopname = responses['Name of Workshop Presented'][0].trim();
var workshopatt = responses['How many people attended your workshop?'][0].trim();
var workshopimp = responses['How could you improve your workshop presentation?'][0].trim();
var weval = responses['Did you email your workshop evaluations?'][0].trim();
var rtname = responses['Name of Real Talk'][0].trim();
var rtatt = responses['How many people attended your Real Talk?'][0].trim();
var rtimp = responses['How could you improve your Real Talk facilitation?'][0].trim();
var rteval = responses['Did you email your Real Talk evaluations?'][0].trim();
var misc1 = responses['Task 1'][0].trim();
var misc2 = responses['Task 2'][0].trim();
var misc3 = responses['Task 3'][0].trim();
var misc4 = responses['Task 4'][0].trim();
var concerns = responses['Third Topic Researched'][0].trim();
//get info for email
Utilities.sleep(1000);
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
var isCellBlank = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").getRange(1,1).isBlank();
// If there is at least one topic selected, send an email to the recipient.
var status = '';
if (!isCellBlank) {
MailApp.sendEmail({
to: email,
subject: EMAIL_SUBJECT,
htmlBody: createEmailBody(timestamp, name, researcht, mt, wt, rtt, wpt, rtpt, misct, topic1, topic2, topic3, meet1, meet2, meet3, practicedw, practicedrt, workshopname, workshopatt, workshopim, weval, rtname, rtatt, rtimp, rteval, misc1, misc2, misc3, misc4, concerns) ,
});
status = 'Sent';
}
else {
status = 'Email Not Sent';
}
// Append the status on the spreadsheet to the responses' row.
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRow();
var column = e.values.length + 1;
sheet.getRange(row, column).setValue(status);
Logger.log("status=" + status + "; responses=" + JSON.stringify(responses));
}
/**
* Creates email body and includes the links based on topic.
*
* @param {string} recipient - The recipient's email address.
* @param {string[]} topics - List of topics to include in the email body.
* @return {string} - The email body as an HTML string.
*/
function createEmailBody(timestamp, name, researcht, mt, wt, rtt, wpt, rtpt, misct, topic1, topic2, topic3, meet1, meet2, meet3, practicedw, practicedrt, workshopname, workshopatt, workshopim, weval, rtname, rtatt, rtimp, rteval, misc1, misc2, misc3, misc4, concerns) {
// Make sure to update the emailTemplateDocId at the top.
var docId = DocumentApp.openByUrl(EMAIL_TEMPLATE_DOC_URL).getId();
var emailBody = docToHtml(docId);
emailBody = emailBody.replace(/{{NAME}}/g, name);
emailBody = emailBody.replace(/{{timestamp}}/g, timestamp);
emailBody = emailBody.replace(/{{researcht}}/g, researcht);
emailBody = emailBody.replace(/{{mt}}/g, mt);
emailBody = emailBody.replace(/{{wt}}/g, wt);
emailBody = emailBody.replace(/{{rtt}}/g, rtt);
emailBody = emailBody.replace(/{{wpt}}/g, wpt);
emailBody = emailBody.replace(/{{rtpt}}/g, rtpt);
emailBody = emailBody.replace(/{{misct}}/g, misct);
emailBody = emailBody.replace(/{{topic1}}/g, topic1);
emailBody = emailBody.replace(/{{topic2}}/g, topic2);
emailBody = emailBody.replace(/{{topic3}}/g, topic3);
emailBody = emailBody.replace(/{{meet1}}/g, meet1);
emailBody = emailBody.replace(/{{meet2}}/g, meet2);
emailBody = emailBody.replace(/{{meet3}}/g, meet3);
emailBody = emailBody.replace(/{{practicedw}}/g, practicedw);
emailBody = emailBody.replace(/{{practicedrt}}/g, practicedrt);
emailBody = emailBody.replace(/{{workshopname}}/g, workshopname);
emailBody = emailBody.replace(/{{workshopatt}}/g, workshopatt);
emailBody = emailBody.replace(/{{workshopim}}/g, workshopim);
emailBody = emailBody.replace(/{{weval}}/g, weval);
emailBody = emailBody.replace(/{{ rtname}}/g, rtname);
emailBody = emailBody.replace(/{{rtatt}}/g, rtatt);
emailBody = emailBody.replace(/{{rtimp}}/g, rtimp);
emailBody = emailBody.replace(/{{rteval}}/g, rteval);
emailBody = emailBody.replace(/{{misc1}}/g, misc1);
emailBody = emailBody.replace(/{{misc2}}/g, misc2);
emailBody = emailBody.replace(/{{misc3}}/g, misc3);
emailBody = emailBody.replace(/{{misc4}}/g, misc4);
emailBody = emailBody.replace(/{{concerns}}/g, concerns);
return emailBody;
}
/**
* Downloads a Google Doc as an HTML string.
*
* @param {string} docId - The ID of a Google Doc to fetch content from.
* @return {string} The Google Doc rendered as an HTML string.
*/
function docToHtml(docId) {
// Downloads a Google Doc as an HTML string.
var url = "https://docs.google.com/feeds/download/documents/export/Export?id=" +
docId + "&exportFormat=html";
var param = {
method: "get",
headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
muteHttpExceptions: true,
};
return UrlFetchApp.fetch(url, param).getContentText();
}
Following Cooper's suggestion, I deleted the install trigger function. There were a few more problems with the script after that, so I made some edits. I will post the completed code, in case anyone is experiencing a similar problem.
var EMAIL_TEMPLATE_DOC_URL = 'document URL';
var EMAIL_SUBJECT = ' daily report summary for ';
var EMAIL_1 = '[email protected]';
var EMAIL_2 = [email protected]';
//variable IDs
var timestampId = 1;
var nameId = 2;
var researchtId = 4;
var mtId = 8;
var wtId = 12;
var rttId = 14;
var wptId = 16;
var rtptId = 22;
var misctId = 29;
var topic1Id = 5;
var topic2Id = 6;
var topic3Id = 7;
var meet1Id = 9;
var meet2Id = 10;
var meet3Id = 11;
var practicedwId = 13;
var practicedrtId = 15;
var workshopnameId = 17;
var workshopattId = 18;
var workshopimpId = 19;
var wevalId = 20;
var rtnameId = 23;
var rtattId = 24;
var rtimpId = 25;
var rtevalId = 26;
var misc1Id = 30;
var misc2Id = 31;
var misc3Id = 32;
var misc4Id = 33;
var concernsId = 34;
var workstsId = 35;
function PrepEmail(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
var responses = e.namedValues;
// If the question title is a label, it can be accessed as an object field.
// If it has spaces or other characters, it can be accessed as a dictionary.
var timestamp = sheet.getRange(lr,timestampId,1,1).getValue();
var name = sheet.getRange(lr,nameId,1,1).getValue();
var today = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
var researcht = sheet.getRange(lr,researchtId,1,1).getDisplayValue();
var mt = sheet.getRange(lr,mtId,1,1).getDisplayValue();
var wt = sheet.getRange(lr,wtId,1,1).getDisplayValue();
var rtt = sheet.getRange(lr,rttId,1,1).getDisplayValue();
var wpt = sheet.getRange(lr,wptId,1,1).getDisplayValue();
var rtpt = sheet.getRange(lr,rtptId,1,1).getDisplayValue();
var misct = sheet.getRange(lr,misctId,1,1).getDisplayValue();
var topic1 = sheet.getRange(lr,topic1Id,1,1).getValue();
var topic2 = sheet.getRange(lr,topic2Id,1,1).getValue();
var topic3 = sheet.getRange(lr,topic3Id,1,1).getValue();
var meet1 = sheet.getRange(lr,meet1Id,1,1).getValue();
var meet2 = sheet.getRange(lr,meet2Id,1,1).getValue();
var meet3 = sheet.getRange(lr,meet3Id,1,1).getValue();
var practicedw = sheet.getRange(lr,practicedwId,1,1).getValue();
var practicedrt = sheet.getRange(lr,practicedrtId,1,1).getValue();
var workshopname = sheet.getRange(lr,workshopnameId,1,1).getValue();
var workshopatt = sheet.getRange(lr,workshopattId,1,1).getValue();
var workshopimp = sheet.getRange(lr,workshopimpId,1,1).getValue();
var weval = sheet.getRange(lr,wevalId,1,1).getValue();
var rtname = sheet.getRange(lr,rtnameId,1,1).getValue();
var rtatt = sheet.getRange(lr,rtattId,1,1).getValue();
var rtimp = sheet.getRange(lr,rtimpId,1,1).getValue();;
var rteval = sheet.getRange(lr,rtevalId,1,1).getValue();
var misc1 = sheet.getRange(lr,misc1Id,1,1).getValue();
var misc2 = sheet.getRange(lr,misc2Id,1,1).getValue();
var misc3 = sheet.getRange(lr,misc3Id,1,1).getValue();
var misc4 = sheet.getRange(lr,misc4Id,1,1).getValue();
var concerns = sheet.getRange(lr,concernsId,1,1).getValue();
var pname = name + '\'s';
var worksts = sheet.getRange(lr,workstsId,1,1).getDisplayValue();
// If there is at least one topic selected, send an email to the recipient.
var isCellBlank = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").getRange(1,1).isBlank();
var status = '';
if (!isCellBlank) {
MailApp.sendEmail({
to: EMAIL_2 +','+ EMAIL_1,
subject: pname + EMAIL_SUBJECT + today,
htmlBody: createEmailBody(timestamp, name, today, worksts, researcht, mt, wt, rtt, wpt, rtpt, misct, topic1, topic2, topic3, meet1, meet2, meet3, practicedw, practicedrt, workshopname, workshopatt, workshopimp, weval, rtname, rtatt, rtimp, rteval, misc1, misc2, misc3, misc4, concerns) ,
});
status = 'Sent';
}
else {
status = 'Email Not Sent';
}
// Append the status on the spreadsheet to the responses' row.
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRow();
var column = e.values.length + 1;
sheet.getRange(row, column).setValue(status);
Logger.log("status=" + status + "; responses=" + JSON.stringify(responses));
}
/**
* Creates email body and includes the links based on topic.
*
* @param {string} recipient - The recipient's email address.
* @param {string[]} topics - List of topics to include in the email body.
* @return {string} - The email body as an HTML string.
*/
function createEmailBody(timestamp, name, today, worksts, researcht, mt, wt, rtt, wpt, rtpt, misct, topic1, topic2, topic3, meet1, meet2, meet3, practicedw, practicedrt, workshopname, workshopatt, workshopimp, weval, rtname, rtatt, rtimp, rteval, misc1, misc2, misc3, misc4, concerns) {
// Make sure to update the emailTemplateDocId at the top.
var docId = DocumentApp.openByUrl(EMAIL_TEMPLATE_DOC_URL).getId();
var emailBody = docToHtml(docId);
emailBody = emailBody.replace(/{{name}}/g, name);
emailBody = emailBody.replace(/{{today}}/g, today);
emailBody = emailBody.replace(/{{worksts}}/g, worksts);
emailBody = emailBody.replace(/{{timestamp}}/g, timestamp);
emailBody = emailBody.replace(/{{researcht}}/g, researcht);
emailBody = emailBody.replace(/{{mt}}/g, mt);
emailBody = emailBody.replace(/{{wt}}/g, wt);
emailBody = emailBody.replace(/{{rtt}}/g, rtt);
emailBody = emailBody.replace(/{{wpt}}/g, wpt);
emailBody = emailBody.replace(/{{rtpt}}/g, rtpt);
emailBody = emailBody.replace(/{{misct}}/g, misct);
emailBody = emailBody.replace(/{{topic1}}/g, topic1);
emailBody = emailBody.replace(/{{topic2}}/g, topic2);
emailBody = emailBody.replace(/{{topic3}}/g, topic3);
emailBody = emailBody.replace(/{{meet1}}/g, meet1);
emailBody = emailBody.replace(/{{meet2}}/g, meet2);
emailBody = emailBody.replace(/{{meet3}}/g, meet3);
emailBody = emailBody.replace(/{{practicedw}}/g, practicedw);
emailBody = emailBody.replace(/{{practicedrt}}/g, practicedrt);
emailBody = emailBody.replace(/{{workshopname}}/g, workshopname);
emailBody = emailBody.replace(/{{workshopatt}}/g, workshopatt);
emailBody = emailBody.replace(/{{workshopimp}}/g, workshopimp);
emailBody = emailBody.replace(/{{weval}}/g, weval);
emailBody = emailBody.replace(/{{rtname}}/g, rtname);
emailBody = emailBody.replace(/{{rtatt}}/g, rtatt);
emailBody = emailBody.replace(/{{rtimp}}/g, rtimp);
emailBody = emailBody.replace(/{{rteval}}/g, rteval);
emailBody = emailBody.replace(/{{misc1}}/g, misc1);
emailBody = emailBody.replace(/{{misc2}}/g, misc2);
emailBody = emailBody.replace(/{{misc3}}/g, misc3);
emailBody = emailBody.replace(/{{misc4}}/g, misc4);
emailBody = emailBody.replace(/{{concerns}}/g, concerns);
return emailBody;
}
/**
* Downloads a Google Doc as an HTML string.
*
* @param {string} docId - The ID of a Google Doc to fetch content from.
* @return {string} The Google Doc rendered as an HTML string.
*/
function docToHtml(docId) {
// Downloads a Google Doc as an HTML string.
var url = "https://docs.google.com/feeds/download/documents/export/Export?id=" +
docId + "&exportFormat=html";
var param = {
method: "get",
headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
muteHttpExceptions: true,
};
return UrlFetchApp.fetch(url, param).getContentText();
}