Google Sheets脚本问题：批量行标记异常及附加第20列值需求

问题排查 #

原脚本存在两个核心问题：

• 批量新增多行数据时仅最后一行被标记为"sent"：原因是使用for (i in AllValues)遍历数组时，i未声明为局部变量导致索引值异常，且逐个单元格写入标记的方式存在执行不稳定的可能。
• 后续新增行时未标记行重复发送：每次运行脚本时，未标记的行都会被追加到同一邮件内容中，且之前未成功标记的行始终处于未处理状态。

同时需实现两个增强功能：

• 邮件中附加第20列（T列）最后一个单元格的值
• 邮件中附加发送的日期和时间

修复后代码 #

function sendEmail() {
    // 初始化工作表
    const ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    const StartRow = 2;
    const LastRow = ActiveSheet.getLastRow();
    const RowRange = LastRow - StartRow + 1;
    if (RowRange <= 0) return; // 无数据行时直接退出

    const WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 16);
    let AllValues = WholeRange.getValues();
    const sentColumnIndex = 14; // 对应第15列（O列），标记是否已发送
    let message = "";

    // 遍历数据行，使用局部变量i避免全局变量问题
    for (let i = 0; i < AllValues.length; i++) {
        const CurrentRow = AllValues[i];
        const EmailSent = CurrentRow[sentColumnIndex];

        // 跳过已发送的行
        if (EmailSent === "sent") continue;

        // 拼接邮件内容
        message += `
            <p><b>Bank: </b>${CurrentRow[1]}</p>
            <p><b>Branch: </b>${CurrentRow[2]}</p>
            <p><b>Region: </b>${CurrentRow[3]}</p>
            <p><b>Lan Number: </b>${CurrentRow[4]}</p>
            <p><b>Customer Name: </b>${CurrentRow[5]}</p>
            <p><b>Loan Type: </b>${CurrentRow[6]}</p>
            <p><b>Case Worker: </b>${CurrentRow[7]}</p>
            <p><b>Site Visit Status: </b>${CurrentRow[8]}</p>
            <p><b>Site Visit Done By: </b>${CurrentRow[9]}</p>
            <p><b>Document recieved: </b>${CurrentRow[11]}</p>
            <p><b>File Upload: </b>${CurrentRow[12]}</p>
            <p><b>Remarks: </b>${CurrentRow[10]}</p>
            <br><br>
        `;

        // 内存中标记为已发送
        AllValues[i][sentColumnIndex] = "sent";
    }

    // 如果没有待发送内容，直接退出
    if (message === "") return;

    // 获取第20列（T列，索引19）最后一个单元格的值
    const col20LastValue = ActiveSheet.getRange(LastRow, 20).getValue();
    // 获取当前日期时间，格式化为可读形式
    const sendDateTime = new Date().toLocaleString();

    // 附加额外信息到邮件末尾
    message += `
        <hr>
        <p><b>附加信息：</b></p>
        <p><b>第20列最后值：</b>${col20LastValue}</p>
        <p><b>发送时间：</b>${sendDateTime}</p>
    `;

    // 批量更新标记为sent，提高执行效率和稳定性
    const markRange = ActiveSheet.getRange(StartRow, sentColumnIndex + 1, RowRange, 1);
    markRange.setValues(AllValues.map(row => [row[sentColumnIndex]]));

    // 收件人配置
    const SendTo = "#######@gmail.com,#########@gmail.com,#########@gmail.com";
    const Subject = "New case initiated to Shirisha";

    // 发送邮件
    MailApp.sendEmail({
        to: SendTo,
        cc: "#########@gmail.com",
        subject: Subject,
        htmlBody: message,
    });
}

修改说明 #

1. 循环遍历优化：替换for (i in AllValues)为for (let i = 0; i < AllValues.length; i++)，避免全局变量引发的索引异常，确保每行都能被正确处理。
2. 批量标记行：不再逐个单元格写入"sent"，而是先在内存中更新数据，最后通过setValues批量写入，大幅提升执行效率和稳定性，避免单个写入失败的情况。
3. 空邮件拦截：添加判断逻辑，当没有待发送的行时直接退出脚本，避免发送空邮件。
4. 附加信息实现：获取第20列最后一个单元格的值，以及当前日期时间并格式化为可读字符串，附加到邮件末尾。
5. 代码可读性提升：使用const/let声明变量，添加注释，简化收件人拼接逻辑。

内容的提问来源于stack exchange，提问作者Rama Krishna