我刚开始编码，最近我创建了一个谷歌脚本(基于另外两个脚本(，它可以执行以下操作：
- 按主题行搜索Gmail草稿
- 获取Gmail草稿并将其用作模板，以创建具有唯一附件的多个草稿
- 在创建草稿后放入确认短语
这是代码：
//Change these to match the column names you are using for email recepient addresses and merge status column//
var RECIPIENT_COL = "Email";
var MERGE_STATUS_COL = "M";
//Creates the menu item "Mail Merge" for user to run scripts on drop-down//
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Mail Merge')
.addItem('📌 Create Drafts', 'createDrafts').addToUi();
}
function createDrafts() {
// search for the draft Gmail message to merge with by its subject line
var subjectLine = Browser.inputBox("Select draft " + "to merge with:", "Paste the subject line:", 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
var emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
emailTemplate.subject = subjectLine;
// get the data from the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getDataRange();
// fetch values for each row in the Range.
var data = dataRange.getValues();
// assuming row 1 contains our column headings
var header = data.shift();
// get the index of column named 'M' (Assume header names are unique)
var draftCreatedColIdx = header.indexOf(MERGE_STATUS_COL);
var object = data.map(function(row) {
// create a new object for next row using the header as a key
var nextRowObject = header.reduce(function(accumulator, currentValue, currentIndex) {
accumulator[currentValue] = row[currentIndex];
return accumulator;
}, {}) // Use {} here rather than initialAccumulatorValue
return nextRowObject;
});
// loop through all the rows of data
object.forEach(function(row, rowIdx){
// only create drafts if mail merge status cell is blank
if (row[MERGE_STATUS_COL] === ''){
var msgObj = fillInTemplateFromObject_(emailTemplate, row);
var attachment_id = "File Name";
// split the values taken from cell into array
var pdfName = row[attachment_id].split(', ');
// initialize files as empty array
var files = [];
// run through cell values and perform search
for(var j in pdfName){
// perform the search,results is a FileIterator
var results = DriveApp.getFilesByName(pdfName[j]);
// interate through files found and add to attachment results
while(results.hasNext()) {
// add files to array
files.push(results.next());
}
}
// @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient-subject-body-options
GmailApp.createDraft(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {htmlBody: msgObj.html, attachments: files});
// create a confirmation phrase in the first column
sheet.getRange("A" + (rowIdx + 2)).setValue("DRAFT");
}
});
}
/**
* Get a Gmail draft message by matching the subject line.
* @param {string} subject_line to search for draft message
* @return {object} containing the plain and html message body
*/
function getGmailTemplateFromDrafts_(subject_line) {
try {
// get drafts
var drafts = GmailApp.getDrafts();
// filter the drafts that match subject line
var draft = drafts.filter(subjectFilter_(subject_line))[0];
// get the message object
var msg = draft.getMessage();
return {text: msg.getPlainBody(), html:msg.getBody()};
} 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 HTML string with data object.
* @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
* H/T https://developers.google.com/apps-script/articles/mail_merge
*/
function fillInTemplateFromObject_(template, data) {
// convert object to string for simple find and replace
template = JSON.stringify(template);
// Search for all the variables to be replaced, for instance {{Column name}}
var templateVars = template.match(/{{([^}]+)}}/g);
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// strip out {{ }}
var variableData = data[templateVars[i].substring(2, templateVars[i].length - 2)];
template = template.replace(templateVars[i], variableData || "");
}
// convert back to object
return JSON.parse(template);
}
脚本按预期工作，但当我试图处理太多带有太多附件的行时，它超过了谷歌脚本6分钟的最长执行时间
在尝试解决这个问题时，我发现了一个使用continuationToken的简单脚本，这样做永远不会超过限制我的目标是尝试在我自己的脚本中使用相同的原则，并按十行处理行不幸的是，到目前为止我运气不好，需要一些帮助。这是我找到的脚本代码：
代码.gs
function onOpen() {
SpreadsheetApp.getUi().createMenu("List Drive files").addItem('Start', 'start').addToUi();
}
function start() {
var ui = HtmlService.createHtmlOutputFromFile('ui');
return SpreadsheetApp.getUi().showSidebar(ui);
}
function getDriveFiles(continuationToken) {
if(continuationToken) {
var files = DriveApp.continueFileIterator(continuationToken);
}
else {
var files = DriveApp.getFiles();
}
var i = 0;
while (files.hasNext() && i < 10) {
var file = files.next();
SpreadsheetApp.getActiveSheet().appendRow([file.getName(), file.getUrl()]);
i++;
if(i == 10) {
return files.getContinuationToken();
}
}
}
ui.html
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<div style="text-align:center; margin-top:10px">
<div>Files processed:</div>
<div id="nbOfFilesProcessed">0</div>
<br>
<button id="startButton" class="blue" onclick="start()">Start</button>
<div class="secondary">Close the sidebar to stop the script.</div>
</div>
<script>
function start() {
document.getElementById("startButton").disabled = true;
google.script.run.withSuccessHandler(onSuccess).getDriveFiles();
}
function onSuccess(continuationToken){
// If server function returned a continuationToken it means the task is not complete
// so ask the server to process a new batch.
if(continuationToken) {
var nbOfFilesProcessedEl = document.getElementById("nbOfFilesProcessed");
nbOfFilesProcessedEl.innerHTML = parseInt(nbOfFilesProcessedEl.innerHTML) + 10;
google.script.run.withSuccessHandler(onSuccess).getDriveFiles(continuationToken);
}
}
</script>
根据我在您发布的代码中看到的内容，您必须以这种方式编辑createDrafts函数：
- 编辑函数的触发方式：您必须使用HTML ui元素在其中运行javascript
- 编辑
while循环，使其在达到批次限制时具有return语句 - 在HTML ui元素中创建一个Javascript函数，该函数处理
createDrafts函数的成功，并在返回continuationToken的情况下递归调用它
代码段
UI组件
您可以保留自定义菜单，然后单击将此HTML添加到UI对话框中。
- code.gs -
//Creates the menu item "Mail Merge" for user to run scripts on drop-down//
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Mail Merge')
.addItem('📌 Create Drafts', 'openDialog').addToUi();
}
function openDialog() {
// Display a modal dialog box with custom HtmlService content.
var htmlOutput = HtmlService
.createHtmlOutputFromFile('Dialog')
.setWidth(250)
.setHeight(300);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Create Drafts');
}
- Dialog.html -
<!-- The UI will be very similar to the one you found, I will keep only the strictly necessary statements for this example -->
<div>
<button id="startButton" onclick="startBatch()">Start</button>
</div>
<script>
function startBatch() {
google.script.run.withSuccessHandler(onSuccess).createDrafts();
}
function onSuccess(continuationToken){
// If server function returned a continuationToken it means the task is not complete
// so ask the server to process a new batch.
if(continuationToken) {
google.script.run.withSuccessHandler(onSuccess).createDrafts(continuationToken);
}
}
</script>
应用程序脚本组件
function createDrafts(continuationToken) {
var batchLimit = 10;
// ...
// run through cell values and perform search
for(var j in pdfName){
// perform the search,results is a FileIterator
if (continuationToken) {
var results = DriveApp.continueFileIterator(continuationToken);
} else {
var results = DriveApp.getFilesByName(pdfName[j]);
}
// interate through files found and add to attachment results
let i = 0;
while(results.hasNext() && i<batchLimit) {
// add files to array
files.push(results.next());
i++;
if (i === batchLimit) {
return results.getContinuationToken();
}
}
}
最后考虑事项
作为对批处理操作的改进，我将保存所有用户输入，这样您就可以继续执行脚本，而无需再次提示。您可以将这些值传递给javascript对象上的return函数，也可以使用CacheService实用程序将它们保存在缓存中。
此外，尝试在执行时间和批量限制之间找到正确的折衷方案：小批量限制永远不会达到时间限制，但会很快消耗您的配额。
参考文献：
客户端API
缓存服务
应用程序脚本UI