/**
* ## Sending Email
* The `sendEmail()` function is the main and only runner function for this script.
* To have this function respond to new form responses, please add `sendMail()`
* as an installable trigger in your project. Please see {@link https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers|the installable triggers documentation} for more information.
* <br /><br />
* Your project information must be added to this config object before the
* `sendEmail()` function will work correctly. Please see the documentation
* for {@link module:emailConfig~config|config} for a description of
* required parameters.
* <br /><br />
* For debugging this script, please see {@link module:sendEmail~sendMail|the sendMail function} below or the {@link module:debug|debug.js file} for more information.
*
* @author unforswearing
* @module sendEmail
* @see {@link module:emailConfig~config|config}
*/
import config from "./config";
/**
* Get the spreadsheet object for the active spreadsheet.
* Uses the Google Apps Script **{@link https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app|SpreadsheetApp class}**.
* @constant {Object}
* */
const activeSpreadsheet = SpreadsheetApp.getActiveSheet();
/**
* Use the `activeSpreadsheet` object to retrieve the name
* of the current sheet via the **{@link https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getName()|getName}** method.
* @constant {string}
* */
const sheetName = activeSpreadsheet.getName();
/**
* A method to retrieve the letter of the last used column
* in the spreadsheet. Use getLastColumnLetter as the value for
* the {@link emailConfig~config|the config object} parameter
* `data.sheetInfo.lastColumnLetter`. This is set by default in
* the `sendMail` function.
* @function getLastColumnLetter
* @example
* // Example: modify the script to retrieve an arbitrary range
* // from your Google Spreadsheet
* const getLastRange = () => {
* let lastCol = getLastColumnLetter()
* return activeSpreadsheet.getRange(`A30:${lastCol}30`)
* }
* @return {string} The last column letter as a string.
*/
const getLastColumnLetter = function getLastColumnLetter() {
var alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".split("");
var alphaLen = alphabet.length;
var col = spreadsheet.getLastColumn();
if (col <= alphaLen) return alphabet.splice(col - 1, 1).toString();
var firstLetter = alphabet[Math.floor(col / alphaLen) - 1];
return `${firstLetter}${alphabet.splice(col % alphaLen, 1).toString()}`;
};
/**
* `procParams` is created using values from {@link module:emailConfig~config|the config object}.
* The parameters in this `procParams` object will default to the imported
* config object. New information will be calculated from the active sheet
* where necessary. **Do not modify the `procParams` object directly!**
* @name procParams
* @borrows emailConfig#config as procParams
* */
let procParams = {
admin: config.admin,
formName: config.formName | sheetName.replace(" (Responses"),
recipient: config.recipient,
emailFooter: config.emailFooter | "",
sheetId: config.sheetId | activeSpreadsheet.getSheetId(),
sheetNameFilter: config.sheetNameFilter | ` (Responses)`,
subjectFilter: config.subjectFilter | " Form Submission",
sheetInfo:
config.sheetInfo |
{
firstCol: "A",
lastCol: getLastColumnLetter(),
lastRow: activeSpreadsheet.getLastRow(),
}
};
/**
* 'sendMail()` is the main runner function. Be sure to add this functiom
* as an **installable trigger** in your project. See {@link https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers|the installable triggers documentation} for more information.
* ### Debugging
* sendEmail(true) to send all notifications to 'admin' for testing.
* The `debugRunner` function is equivalent to running `sendMail(true)` -
* all actions between debugRunner and sendMail(true) are equivalent.
* Both functions will send all runtime notifications to the email
* address listed in `procParams.data.admin` instead of the recipient
* listed in `procParams.data.recipient`. This is most useful for testing.
* @function sendMail
* @argument {boolean} debug specify whether the script should send errors to `admin`
* @returns {void}
* */
function sendMail(debug) {
/* create the helper object **/
var parameters = procParams;
/**
* extract helper code and info from procParams (var parameters)
* assumption: parameters.helper contains an object of helper vars / funcs
* var helper = parameters.helper;
* */
var id = parameters.sheetId;
/** open the sheet for parsing */
var sheet = SpreadsheetApp.openById(id);
/** sheetInfo = { firstCol, lastCol } */
var sheetInfo = parameters.sheetInfo;
/**
* Get the latest response range as text
* eg. "A10:G10"
* */
sheetInfo.rangeString = `${sheetInfo.firstCol}${sheetInfo.lastRow}:${sheetInfo.lastCol}${sheetInfo.lastRow}`;
/**
* use the response range to get the questions from row 1
* if your questions are in a different row or column
* change 'A1' to the location of the cell containing the first question
* replace the '1' in 'sheetInfo.lastCol with the number of the row
* containing the last question.
* */
sheetInfo.questionString = `A1:${sheetInfo.lastCol + 1}`;
/**
* get the values for question and latest response ranges
* */
sheetInfo.questions = sheet.getRange(sheetInfo.questionString).getValues()[0];
sheetInfo.submissionData = sheet
.getRange(sheetInfo.rangeString)
.getValues()[0];
/** only shorten timestamps if the timestamps array has a value */
if (parameters.timestampsArray.join("")) {
for (var n in parameters.timestampsArray) {
/** tsi === time stamp index */
var tsi = parameters.timestampsArray[n];
/** change the date formatting if needed */
sheetInfo.submissionData[tsi] = Utilities.formatDate(
sheetInfo.submissionData[tsi],
Session.getScriptTimeZone(),
"M/d/yyyy h:mm a"
).toString();
}
}
/**
* create the submission info table and styles for html emails
* table rows will be pushed to the dataTable column in the loop below
* */
var dataTable = [];
var tdstyle = 'style="padding:7px;"';
/** loop through the submissionData to create a table of questions and answers */
for (var i in sheetInfo.submissionData) {
var answer = sheetInfo.submissionData[i];
var question = sheetInfo.questions[i];
/** add the generated row to the dataTable array */
dataTable.push(
`<tr><td align="right" ${tdstyle}><b>${question}</b></td><td align="left" ${tdstyle}>${answer}</td></tr>`
);
}
/** add styles for full table and nest the dataTable rows in table tags */
var tablestyle = 'style="width:80%;padding:7px;"';
dataTable = `<table ${tablestyle}>${dataTable.join("")}</tables>`;
/** Use admin email address if debug is true, otherwise use the script default */
var recipient = debug === true ? parameters.admin : parameters.recipient();
/** create email subject */
var sheetName = sheet.getName().replace(parameters.sheetNameFilter, "");
var subject = sheetName + parameters.subjectFilter;
/** add a company logo if desired. otherwise, comment out the two lines below */
var logo = undefined;
logo = `<img src="${logo}" width="120px" height="80px">`;
/** create the email body */
var emailFooter = parameters.emailFooter;
var body = `${logo}<br><br>
'Hello,<br><br>'${sheetName} form was submitted on ${sheetInfo.submissionData[0]}.
Please find the submitted information below.<hr><br>${dataTable}<br><br><br><br>${emailFooter}`;
/** send email to 'recipient', admin if debugging */
MailApp.sendEmail(recipient, subject, body, {
htmlBody: body,
noReply: true,
});
}
export default sendMail;