ISSUE:
With the wonderful assistance of @Cooper, I was able to optimize my mail merger script using an html template ("emailEinladung.html") and a list of recipients ("terminBestaetige"). Each recipient gets a personalized email based on the html template, as long as his/her status in the 11th column (so, "K") is not set to "EMAIL SENT". Now I want to add one or more attachments to every email being sent.
ATTEMPTED SOLUTION:
function terminEinladungVersendenWithAttachments() {
// variables to reference the sheet and its content
const anrede = 2;
const nachname = 3;
const emailAdresse = 5;
const terminTag = 6;
const terminUhrzeit = 8;
const terminURL = 9;
let emailTemp = HtmlService.createTemplateFromFile('emailEinladung');
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("terminBestaetigen");
// variables for "Betreff" (in English: "subject") coming from the google Sheet named "Vorlagen"
var wsVorlagen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Vorlagen");
var betreff = wsVorlagen.getRange("B1").getValue();
const sr = 3;//start row of data
const data = sh.getRange(sr, 1, sh.getLastRow() - sr 1, 11).getValues();
data.forEach((row, i) => {
if (row[10] == "EMAIL NOT SENT YET") {
emailTemp.anrede1 = (row[anrede]);
emailTemp.nachname1 = (row[nachname]);
emailTemp.emailAdresse1 = (row[emailAdresse]);
emailTemp.terminTag1 = (row[terminTag]);
emailTemp.terminUhrzeit1 = (row[terminUhrzeit]);
emailTemp.terminURL1 = (row[terminURL]);
// including attachements to each email being sent.
var attachment1 = DriveApp.getFileById("[FILE_ID1]")
var attachment2 = DriveApp.getFileById("[FILE_ID2]")
var blob1 = attachment1.getBlob()
var blob2 = attachment2.getBlob()
var htmlMessage = emailTemp.evaluate().getContent();
GmailApp.sendEmail(row[emailAdresse],
betreff, "Dies ist eine Nachricht im HTML-Format. Sie müßen Ihre Email-Software entsprechend einrichten.",
{ htmlBody: htmlMessage, replyTo: "[email protected]", attachments: [blob1, blob2]});
sh.getRange(i sr, 11).setValue("EMAIL SENT");//stops emails from being sent again
}
});
}
QUESTION:
Is there anything I should improve in my script to make it more efficient? If yes, what would that improvement be and how would YOUR script look like?
Thank you so much in advance for your help.
CodePudding user response：
I believe your goal is as follows.
- You want to reduce the process cost of your script.
Modification points:
- When I saw your script, I thought that
blob1andblob2can be put out of the loop. I think that by this, the process cost might be a bit low. - But, when 500 emails are sent by
GmailApp.sendEmailin a loop, this process cost will be high. I thought that this cost is larger than that of the above point. - In order to reduce the process cost for sending 500 emails, in this answer, as a workaround, I would like to propose using Gmail API and the batch request. But I'm not sure whether this is the direct solution to your issue. So please test the following script.
The modified script is as follows.
Modified script:
Before you use this script, please install a Google Apps Script library of BatchRequest. You can see how to install this library at here.
And, please enable Gmail API at Advanced Google services.
And please set the file IDs of [FILE_ID1] and [FILE_ID2].
const convert_ = ({ to, subject, textBody, htmlBody, attachmentfile1, attachmentfile2 }) => {
const boundary1 = "boundaryboundary001";
const boundary2 = "boundaryboundary002";
const mailData = [
`MIME-Version: 1.0`,
`To: ${to}`,
`Subject: =?UTF-8?B?${Utilities.base64Encode(subject, Utilities.Charset.UTF_8)}?=`,
`Content-Type: multipart/mixed; boundary=${boundary1}`,
``,
`--${boundary1}`,
`Content-Type: multipart/alternative; boundary=${boundary2}`,
``,
`--${boundary2}`,
`Content-Type: text/plain; charset=UTF-8`,
``,
textBody,
`--${boundary2}`,
`Content-Type: text/html; charset=UTF-8`,
`Content-Transfer-Encoding: base64`,
``,
Utilities.base64Encode(htmlBody, Utilities.Charset.UTF_8),
`--${boundary2}--`,
`--${boundary1}`,
`Content-Type: image/png; charset=UTF-8; name="sample1.png"`,
`Content-Transfer-Encoding: base64`,
``,
attachmentfile1,
`--${boundary1}`,
`Content-Type: image/png; charset=UTF-8; name="sample2.png"`,
`Content-Transfer-Encoding: base64`,
``,
attachmentfile2,
`--${boundary1}--`,
].join("\r\n");
return Utilities.base64EncodeWebSafe(mailData);
};
// Please run this function.
function terminEinladungVersendenWithAttachments() {
const anrede = 2;
const nachname = 3;
const emailAdresse = 5;
const terminTag = 6;
const terminUhrzeit = 8;
const terminURL = 9;
let emailTemp = HtmlService.createTemplateFromFile('emailEinladung');
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("terminBestaetigen");
var wsVorlagen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Vorlagen");
var betreff = wsVorlagen.getRange("B1").getValue();
const sr = 3;
const data = sh.getRange(sr, 1, sh.getLastRow() - sr 1, 11).getValues();
var attachmentfile1 = Utilities.base64Encode(DriveApp.getFileById("[FILE_ID1]").getBlob().getBytes());
var attachmentfile2 = Utilities.base64Encode(DriveApp.getFileById("[FILE_ID2]").getBlob().getBytes());
var {requests, sent} = data.reduce((o, row, i) => {
if (row[10] == "EMAIL NOT SENT YET") {
emailTemp.anrede1 = (row[anrede]);
emailTemp.nachname1 = (row[nachname]);
emailTemp.emailAdresse1 = (row[emailAdresse]);
emailTemp.terminTag1 = (row[terminTag]);
emailTemp.terminUhrzeit1 = (row[terminUhrzeit]);
emailTemp.terminURL1 = (row[terminURL]);
var htmlMessage = emailTemp.evaluate().getContent();
const obj = {
to: row[emailAdresse],
subject: betreff,
textBody: "Dies ist eine Nachricht im HTML-Format. Sie müßen Ihre Email-Software entsprechend einrichten.",
htmlBody: htmlMessage,
attachmentfile1, attachmentfile2
};
o.requests.push({method: "POST", endpoint: "https://gmail.googleapis.com/gmail/v1/users/me/messages/send", requestBody: { raw: convert_(obj) }});
o.sent.push("K" (i 3));
}
return o;
}, {requests: [], sent: []});
if (sent.length > 0) sh.getRangeList(sent).setValue("EMAIL SENT");
const res = BatchRequest.EDo({ batchPath: "batch/gmail/v1", accessToken: ScriptApp.getOAuthToken(), requests: requests });
console.log(res)
}
Note:
- This method is my try. So please test the above script.