Development
Google Apps Script
Gmail Add-On Demo
Gmail can be extended with custom add-ons which respond to specific triggers such as opening a message or starting a draft. A division or research group could create custom add-ons to keep their teams up to date on highlights and key dates.
Open for code
function loadAddOn(event) {
var accessToken = event.gmail.accessToken;
var messageId = event.gmail.messageId;
GmailApp.setCurrentMessageAccessToken(accessToken);
var mailMessage = GmailApp.getMessageById(messageId);
var from = mailMessage.getFrom();
var subject = mailMessage.getSubject();
var openDocButton = CardService.newTextButton()
.setText("Reference Doc")
.setOpenLink(
CardService.newOpenLink().setUrl("https://developers.google.com/gmail/add-ons/"));
var fixedFooter = CardService.newFixedFooter()
.setPrimaryButton(
CardService.newTextButton()
.setText("help")
.setOpenLink(CardService.newOpenLink()
.setUrl("https://www.google.com")))
.setSecondaryButton(
CardService.newTextButton()
.setText("submit")
.setOnClickAction(
CardService.newAction()
.setFunctionName(
"submitCallback")));
var alien = CardService.newImage()
.setAltText("LBL Logo")
.setImageUrl("https://i.ibb.co/f0Z4fJD/lbl-logo.png");
var card = CardService.newCardBuilder()
.setHeader(CardService.newCardHeader()
.setTitle("Demo Add-on"))
.addSection(CardService.newCardSection()
.addWidget(CardService.newImage().setAltText("LBL Logo")
.setImageUrl("https://i.ibb.co/f0Z4fJD/lbl-logo.png"))
.addWidget(CardService.newKeyValue()
.setTopLabel('Subject')
.setContent(subject))
.addWidget(CardService.newTextParagraph().setText("The email is from: " + from))
.addWidget(openDocButton))
.setFixedFooter(fixedFooter)
.build();
return [card];
}
Reference: Extending Gmail with Google Workspace add-ons
Google Sheets Mail Merge Example
There are many Gmail compatible add-ons available on the market including those focused on sending mass emails and mail merge. Free add-ons typically impose limits on customization and total number of messages per day. It's convenient to use a custom mail merge solution which works seamlessly between Google Sheets and Gmail.
Open for code
// Based on script from Martin Hawksey - https://developers.google.com/gsuite/solutions/mail-merge
//
// Updated with notes by Arica for use at LBL 10/23/2020
//
// Licensed under the Apache License, Version 2.0 (the "License"); you may not
// use this file except in compliance with the License. You may obtain a copy
// of the License at
//
// https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
// WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
// License for the specific language governing permissions and limitations under
// the License.
/**
* @OnlyCurrentDoc
*/
const MENTEE_COL = "Mentee";
const MENTEE_EMAIL_COL = "Mentee Email";
const MENTEE_DIV_COL = "Mentee Division";
const MENTOR_COL = "Mentor";
const MENTOR_EMAIL_COL = "Mentor Email";
const MENTOR_DIV_COL = "Mentor Division";
const SEND1_COL = "Send Mentee?";
const SEND2_COL = "Send Mentor?";
const EMAIL_SENT_COL = "Email Sent";
/**
*
* Creates the menu item "Mail Merge" for user to run scripts on drop-down.
*
*
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Mail Merge')
.addItem('Send Emails', 'sendEmails')
.addToUi();
}
/**
*
* Send emails from sheet data.
* @param {string} subjectLine (optional) for the email draft message
* @param {Sheet} sheet to read data from
*
*/
function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {
// option to skip browser prompt if you want to use this code in other projects
if (!subjectLine){
subjectLine = Browser.inputBox("Mail Merge",
"Type or copy/paste the subject line of the Gmail " +
"draft message you would like to mail merge with:",
Browser.Buttons.OK_CANCEL);
if (subjectLine === "cancel" || subjectLine == ""){
// if no subject line finish up
return;
}
}
// get the draft Gmail message to use as a template
const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
// get the data from the passed sheet
const dataRange = sheet.getDataRange();
// Fetch displayed values for each row in the Range HT Andrew Roberts
// https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187490
// @see https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues
const data = dataRange.getDisplayValues();
// assuming row 1 contains our column headings
const heads = data.shift();
const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
// convert 2d array into object array
// @see https://stackoverflow.com/a/22917499/1027723
// for pretty version see https://mashe.hawksey.info/?p=17869/#comment-184945
const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
// used to record sent emails
const out = [];
obj.forEach(function(row, rowIdx){
if (row[SEND2_COL] == 'TRUE'){
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
GmailApp.sendEmail(row[MENTOR_EMAIL_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
// bcc: 'a.bbc@email.com',
// cc: 'a.cc@email.com',
// from: 'an.alias@email.com',
name: 'PSA Mentorship Committee',
// replyTo: 'a.reply@email.com',
// noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
attachments: emailTemplate.attachments
});
// modify cell to record email sent date
out.push([new Date()]);
} catch(e) {
// modify cell to record error
out.push([e.message]);
}
}
if (row[SEND1_COL] == 'TRUE'){
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
GmailApp.sendEmail(row[MENTEE_EMAIL_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
// bcc: 'a.bbc@email.com',
// cc: 'a.cc@email.com',
// from: 'an.alias@email.com',
name: 'PSA Mentorship Committee',
// replyTo: 'a.reply@email.com',
// noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
attachments: emailTemplate.attachments
});
// modify cell to record email sent date
out.push([new Date()]);
} catch(e) {
// modify cell to record error
out.push([e.message]);
}
} else if(row[SEND1_COL] == 'TRUE'){
out.push([row[EMAIL_SENT_COL]]);
}
else if(row[SEND2_COL] == 'TRUE'){
out.push([row[EMAIL_SENT_COL]]);
}
});
// updating the sheet with new data
sheet.getRange(2, emailSentColIdx+1, out[0].length).setValues([out[0]]);
/**
* Get a Gmail draft message by matching the subject line.
* @param {string} subject_line to search for draft message
* @return {object} containing the subject, plain and html message body and attachments
*
*/
function getGmailTemplateFromDrafts_(subject_line){
try {
// get drafts
const drafts = GmailApp.getDrafts();
// filter the drafts that match subject line
const draft = drafts.filter(subjectFilter_(subject_line))[0];
// get the message object
const msg = draft.getMessage();
// getting attachments so they can be included in the merge
const attachments = msg.getAttachments();
return {message: {subject: subject_line, text: msg.getPlainBody(), html:msg.getBody()},
attachments: attachments};
} catch(e) {
throw new Error("Oops - can't find Gmail draft");
}
/**
* Filter draft objects with the matching subject linemessage by matching the subject line.
* @param {string} subject_line to search for draft message
* @return {object} GmailDraft object
*/
function subjectFilter_(subject_line){
return function(element) {
if (element.getMessage().getSubject() === subject_line) {
return element;
}
}
}
}
/**
* Fill template string with data object
* @see https://stackoverflow.com/a/378000/1027723
* @param {string} template string containing {{}} markers which are replaced with data
* @param {object} data object used to replace {{}} markers
* @return {object} message replaced with data
*/
function fillInTemplateFromObject_(template, data) {
// we have two templates one for plain text and the html body
// stringifing the object means we can do a global replace
let template_string = JSON.stringify(template);
// token replacement
template_string = template_string.replace(/{{[^{}]+}}/g, key => {
return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
});
return JSON.parse(template_string);
}
/**
* Escape cell data to make JSON safe
* @see https://stackoverflow.com/a/9204218/1027723
* @param {string} str to escape JSON special characters from
* @return {string} escaped string
*/
function escapeData_(str) {
return str
.replace(/[\\]/g, '\\\\')
.replace(/[\"]/g, '\\\"')
.replace(/[\/]/g, '\\/')
.replace(/[\b]/g, '\\b')
.replace(/[\f]/g, '\\f')
.replace(/[\n]/g, '\\n')
.replace(/[\r]/g, '\\r')
.replace(/[\t]/g, '\\t');
};
}
Reference: Create a mail merge using Gmail and Google Sheets
- To test the mail merge, make a copy of the sheet
- Enter your own email address in the columns
- Create a draft email in your Gmail account using column names in curly braces {{Column Name}}
- Emails will only be sent if the checkbox is marked/cell validated
- Select Mail Merge > Send Emails
- Copy/paste the subject line of your draft email and click Okay
- The send date and time will be recorded
Google Slides Tips
References
- Table of Contents - Automatically create a linked table of contents
- Custom Dialogs - Add a pop-up message
- Custom Sidebars - Add a custom sidebar. Click Reference
- Translate Add-on - Quickly translate text on slides
