我遵循了本教程，该教程用于使用Gmail填充html模板。因此，在我的谷歌表单文档中，我提供了一个包含数据的列，在html模板中，我使用｛｛Name｝｝，这个脚本替换数据并发送邮件：
// Copyright Martin Hawksey 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
*/
/**
* Change these to match the column names you are using for email
* recipient addresses and email sent column.
*/
const RECIPIENT_COL = "Recipient";
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()) {
subjectLine = "Beste {{Voornaam}}, vul nu jouw persoonlijke progressie & feedback maaltijdplan in!";
// 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();
// get the index of column named 'Email Status' (Assume header names are unique)
// @see http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions
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 = [];
// loop through all the rows of data
obj.forEach(function(row, rowIdx){
// only send emails is email_sent cell is blank and not hidden by 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: 'a.bbc@email.com',
// cc: 'a.cc@email.com',
from: 'Info@example.com',
name: 'Example',
// 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,
inlineImages: emailTemplate.inlineImages
});
// modify 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]]);
}
});
// updating 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_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();
// Handling inline images and attachments so they can be included in the merge
// Based on https://stackoverflow.com/a/65813881/1027723
// Get 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();
// Create 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 to search for all img string positions with cid
const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^>]+>', 'g');
const matches = [...htmlBody.matchAll(imgexp)];
//Initiate 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_line, 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_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');
};
}现在我想添加链接。第一次它工作得很好，但过了一段时间，Gmail从这个http://{链接}}变成了这个http://%7B%7BLink%7D%7D。
Gmail截图1Gmail截图2
我使用Stripo来制作我的模板，这是代码：
<td class="esd-block-button es-p40t es-p40b es-p25r es-p25l" align="center" bgcolor="#ffffff"><span class="es-button-border" style="border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; border-color: #213f92; background-color: #234290; border-width: 1px;"><a href="{{Link}}" class="es-button es-button-1621245929191" target="_blank" style="background-color: #234290; border-color: #234290; color: #ffffff; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; border-width: 10px 30px;">Progressie formulier</a></span></td>因此，我在A href中包含了｛｛Link｝｝。
我的问题：将｛｛｝｝更改为类似于此%%%%或此<lt>gt；gmail没有更改我的合并标签？
任何其他我能解决这个问题的想法都会很棒！
抱歉英语不好btw:(
我的好友帮助我解决
function fillInTemplateFromObject_(template, data) {
// Google mail when opening drafts with URL's in it do not understand {{}}, however we need {{}} for the replace logic.
template.text = template.text.replace("((Link))", "{{Link}}");
template.html = template.html.replace("((Link))", "{{Link}}");
// 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);
}添加Gmail url草稿((((而不是｛｛｝｝，它可以使用上面更改的代码