Trying to use the mail merge script but modified

[Erin Bunge]

I'm pretty new to Javascript. Basically, I'm looking to use the Mail Merge script, but instead of manually enter the subject line to run the merge, I have the subject line set as a column in the spreadsheet, and the draft emails ready in my mail account. I want the script to check each day and if the "Send Email" column is "yes," (which is a formula that compares the Reminder Date column to Today()) then it sends that specific drafted email out to the addresses listed. I have a few pieces of it working, but I'm stuck with which parts to replace or delete, adding the new parts of the finding the subject line, and triggering it all based on the edit of the Send Email column.  I've watched so many videos to help and none of them have the whole package of what I'm trying to do, so I'm getting lost. Any help would be greatly appreciated! My image below is what my spreadsheet looks like - a list of dates that the groups practice, the date the reminder should go out, the send email column is the trigger to check it against today's date, the class, the email subject that should be sent, the Email Sent confirmation column, I added a Bcc which is where all the email addresses will go, and I kept myself as a recipient.

I'm really tired of spending tons of time typing up and individually scheduling weekly reminders for these classes!  Any help would be greatly appreciated!

[Andrew Roberts]

Share what code you have so far and we can take a look.

Confidentiality Notice:  This email and any attachments are covered by the Electronic Communications Privacy Act, 18 U.S.C.§§2510-2521 and contain privileged and confidential information intended only for the use of the individual or entity named.  If you are not the intended recipient, you are hereby notified that you should not review, use, disclose, distribute, copy, or forward this email.  If you have received this email in error, please notify the sender immediately and delete/destroy any and all copies of the original message.

 

Please note that any statements or opinions presented in this email are solely those of the author and do not necessarily represent those of the Marion Independent School District. E-mail transmissions cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late, incomplete, or contain viruses.  Therefore, the recipient should check this email and any attachments for the presence of viruses. The Marion Independent District accepts no liability for any damage caused by any virus transmitted by this email.

 

Marion Independent School District: 777 S. 15th ST Marion, IA 52302 (319) 377-4691

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/eae8d8ab-33e6-4eae-b342-031aaa7dc4c5n%40googlegroups.com.

[Erin Bunge]

Here's the script so far. This is probably the 10th iteration of me trying different things. It's very dialed back - almost how it is when you get it from the original Mail Merge doc. I added in my const columns at the top (maybe that's wrong, too?), and I'm trying to do the subject line thing right now (SUBJECT), and it doesn't work, so I know I've already mucked it up. Haven't even gotten to the onEdit function for the "Send Email" column.

const RECIPIENT_COL = "Recipient";

const EMAIL_SENT_COL = "Email Sent";

const BCC = "Bcc";

const SUBJECT = "Subject";

const CLASS = "Class";

const SEND_EMAIL = "Send Email";

const REMINDER_DATE = "Reminder Date";

/**

* 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();

}

/**

* Sends emails from sheet data.

* @param {string} subjectLine (optional) for the email draft message

* @param {Sheet} sheet to read data from

*/

function sendEmails(SUBJECT, sheet=SpreadsheetApp.getActiveSheet()) {

// option to skip browser prompt if you want to use this code in other projects

if (!SUBJECT){

SUBJECT = 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 (SUBJECT === "cancel" || SUBJECT == ""){

// If no subject line, finishes up

return;

}

}

const emailTemplate = getGmailTemplateFromDrafts_(SUBJECT);

const dataRange = sheet.getDataRange();

const data = dataRange.getDisplayValues();

const heads = data.shift();

const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);

const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

const out = [];

obj.forEach(function(row, rowIdx){

// Only sends emails if email_sent cell is blank and not hidden by a filter

if (row[EMAIL_SENT_COL] == ''){

try {

const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

// See https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)

// If you need to send emails with unicode/emoji characters change GmailApp for MailApp

// Uncomment advanced parameters as needed (see docs for limitations)

GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

htmlBody: msgObj.html,

bcc: (row[BCC]),

// cc: 'a....@email.com',

from: 'ebu...@marion-isd.org',

name: 'Erin Bunge',

// replyTo: 'a.r...@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,

inlineImages: emailTemplate.inlineImages

});

// Edits cell to record email sent date

out.push([new Date()]);

} catch(e) {

// modify cell to record error

out.push([e.message]);

}

} else {

out.push([row[EMAIL_SENT_COL]]);

}

});

// Updates the sheet with new data

sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);

/**

* 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){

try {

// get drafts

const drafts = GmailApp.getDrafts();

// filter the drafts that match subject line

const draft = drafts.filter(subjectFilter_(SUBJECT))[0];

// get the message object

const msg = draft.getMessage();

// Handles inline images and attachments so they can be included in the merge

// Based on https://stackoverflow.com/a/65813881/1027723

// Gets all attachments and inline image attachments

const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:false});

const attachments = draft.getMessage().getAttachments({includeInlineImages: false});

const htmlBody = msg.getBody();

// Creates an inline image object with the image name as key

// (can't rely on image index as array based on insert order)

const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

//Regexp searches for all img string positions with cid

const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');

const matches = [...htmlBody.matchAll(imgexp)];

//Initiates the allInlineImages object

const inlineImagesObj = {};

// built an inlineImagesObj from inline image matches

matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

return {message: {subject: SUBJECT, text: msg.getPlainBody(), html:htmlBody},

attachments: attachments, inlineImages: inlineImagesObj };

} 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){

return function(element) {

if (element.getMessage().getSubject() === SUBJECT) {

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');

};

}

[Andrew Roberts]

If you want to use a different draft for each email update the script like this (move the template assignment to within the loop)

function sendEmails(SUBJECT, sheet = SpreadsheetApp.getActiveSheet()) {

// // option to skip browser prompt if you want to use this code in other projects

// if (!SUBJECT) {

// SUBJECT = 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 (SUBJECT === "cancel" || SUBJECT == "") {

// // If no subject line, finishes up

// return;

// }

// }

const dataRange = sheet.getDataRange();

const data = dataRange.getDisplayValues();

const heads = data.shift();

const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);

const subjectColIdx = heads.indexOf(SUBJECT);

const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

const out = [];

obj.forEach(function (row, rowIdx) {

SUBJECT = row[subjectColIdx]

const emailTemplate = getGmailTemplateFromDrafts_(SUBJECT);

// Only sends emails if email_sent cell is blank and not hidden by a filter

if (row[EMAIL_SENT_COL] == '') {

Confidentiality Notice:  This email and any attachments are covered by the Electronic Communications Privacy Act, 18 U.S.C.§§2510-2521 and contain privileged and confidential information intended only for the use of the individual or entity named.  If you are not the intended recipient, you are hereby notified that you should not review, use, disclose, distribute, copy, or forward this email.  If you have received this email in error, please notify the sender immediately and delete/destroy any and all copies of the original message.

 

Please note that any statements or opinions presented in this email are solely those of the author and do not necessarily represent those of the Marion Independent School District. E-mail transmissions cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late, incomplete, or contain viruses.  Therefore, the recipient should check this email and any attachments for the presence of viruses. The Marion Independent District accepts no liability for any damage caused by any virus transmitted by this email.

 

Marion Independent School District: 777 S. 15th ST Marion, IA 52302 (319) 377-4691

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9afaa1dc-3ad2-45b7-bdb4-161a13c866f3n%40googlegroups.com.