谷歌表格批量创建Gmail草稿失败求助（仅能单次生成最新条目草稿）

问题分析 #

你的代码存在几个关键问题导致无法批量创建草稿：

• createDraftTemplate函数中，GmailApp.createDraft调用在for循环外部，只会使用循环最后一行的变量值创建单个草稿
• 菜单映射错误："Create draft"选项绑定了错误的函数getDrafts，而非createDraftTemplate
• getTrackingGIF函数中，URL参数拼接存在语法错误，导致追踪像素无法正确生成

修复方案 #

1. 修正菜单映射：将"Create draft (From Responses tab)"的绑定函数改为createDraftTemplate
2. 批量创建草稿：将GmailApp.createDraft移动到for循环内部，遍历每一行数据创建对应草稿
3. 修复追踪像素URL拼接：修正字符串拼接的语法错误，确保参数正确附加到URL
4. 添加空值校验：跳过邮箱为空的行，避免创建无效草稿

修改后的完整代码 #

function onOpen() {
    let menu = SpreadsheetApp.getUi().createMenu("Sheet Functions");
    // 修复菜单函数绑定错误
    menu.addItem("Create draft (From Responses tab)" , "createDraftTemplate");
    menu.addItem("Refresh Draft List (Check the box for desired email on drafts page)", "getDrafts")
    menu.addItem("Send Draft List (Only selected drafts will be sent)", "sendButton")
    menu.addToUi();
}

function createDraftTemplate () {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses"); // 明确指定目标工作表
    var sheetValues = ss.getDataRange().getValues();

    for (let i = 1; i < sheetValues.length; i++) {
        var row = sheetValues[i];
        var fuName = row[1];
        var currentEmail = row[2];
        var dscrptn = row[3];
        var crctn = row[4];

        // 跳过空邮箱行，避免无效草稿
        if (!currentEmail) continue;

        // 循环内创建草稿，实现批量生成
        GmailApp.createDraft(
            currentEmail,
            "Test",
            `Employee:  ${fuName}\n\nWas described performing actions which require corrective action: ${dscrptn}\n\nManagement requirements for correction: ${crctn}`
        );
    }
}

function getDrafts() {
    var spreadSheet = SpreadsheetApp.getActive()
    var draftSheet = spreadSheet.getSheetByName("Drafts")
    draftSheet.getDataRange().clear().clearDataValidations()

    var drafts = GmailApp.getDrafts()
    var draftsOutput = [["Subject","To","ID","Send ?"]]

    for (var i = 0; i<drafts.length; i++) {
        var draft = drafts[i]
        draftsOutput.push([
            draft.getMessage().getSubject(),
            draft.getMessage().getTo(),
            draft.getId(),
            false
        ])
    }

    draftSheet.getRange(1, 1, draftsOutput.length, draftsOutput[0].length).setValues(draftsOutput)

    draftSheet.getRange(2,draftsOutput[0].length,draftsOutput.length-1).setDataValidation(SpreadsheetApp.newDataValidation()
        .setAllowInvalid(true)
        .requireCheckbox()
        .build());
}

function getTrackingGIF(email, subject) {
    var imgURL = "https://script.google.com/macros/s/AKfycbzTqs-Fn444uqxvSRT1DWd7mMbJzStNzmTmkuyl105d95s1S4-qkR1_VxlBLWI0fjjO/exec"
    // 修复URL参数拼接语法错误
    + "?esubject=" + encodeURIComponent(subject.replace(/'/g, ""))
    + "&eto=" + encodeURIComponent(email);
    return `<img src='${imgURL}' width='1' height='1'/>`;
}

function sendMail(draftId) { 
    var draft = GmailApp.getDraft(draftId)
    var message = draft.getMessage()
    var body = message.getBody();
    body += getTrackingGIF(message.getTo(), message.getSubject());
    draft.update(message.getTo(), message.getSubject(), body, {
        htmlBody: body, 
        cc: message.getCc(), 
        bcc: message.getBcc(), 
        attachments: message.getAttachments()
    })
    draft.send()
    getDrafts()
}

function sendButton() {
    var spreadSheet = SpreadsheetApp.getActive()
    var draftSheet = spreadSheet.getSheetByName("Drafts")
    var draftRange = draftSheet.getDataRange().getValues()
    // 从第2行开始遍历，跳过表头
    for (var i=1; i<draftRange.length; i++) { 
        if (draftRange[i][3]==true) sendMail(draftRange[i][2])
    }
    draftSheet.getRange(2,4,draftRange.length-1).clearContent()  
}

function doGet(e) {
    var trackingSpSheet = SpreadsheetApp.openById("1wKetTiUYphmegm7S0YTEU61TohukEbweY1F6iV7M3Sw")
    var outputSheet = trackingSpSheet.getSheetByName("Logs")
    var lastRow = outputSheet.getLastRow()+1
    outputSheet.getRange(lastRow, 1,1,3).setValues([[new Date(),e.parameters.esubject,e.parameters.eto]])
    return ContentService.createTextOutput(JSON.stringify(e)) 
}

额外说明 #

• 代码中明确指定Responses工作表，避免选中错误工作表导致的异常
• 空邮箱校验逻辑可防止生成无收件人的无效草稿
• 使用模板字符串优化邮件内容写法，提升代码可读性

内容的提问来源于stack exchange，提问作者Jacob Mathieu