Google Scripts新手求助：如何实现逐行发送邮件

一、原代码的问题（距离目标的差距） #

• 字符串模板语法错误：应该用反引号``包裹动态字符串，不需要加msg关键字；B2、C2这类单元格引用不能直接写在模板里，得先读取对应单元格的值再代入
• 遗漏关键数据：邮件正文需要的到期日期（D2）没有在代码中读取
• 仅支持单行发送：当前代码只能处理第2行的数据，没有遍历所有行的逻辑

二、修正后的单行发送代码 #

先把单行逻辑跑通，确保单条邮件能正常发送：

function sendSingleInvoiceReminder() {
  let sheet = SpreadsheetApp.getActiveSheet();
  // 读取第2行各列数据
  let address = sheet.getRange("A2").getValue();
  let invoiceTitle = sheet.getRange("B2").getValue();
  let invoiceNumber = sheet.getRange("C2").getValue();
  let dueDate = sheet.getRange("D2").getValue();

  // 正确使用模板字符串拼接内容
  let subject = `${invoiceTitle}-Upcoming Due Date for invoice ${invoiceNumber}`;
  let body = `Dear Sir/Madam. I hope you are well. This email serves as a reminder that your invoice ${invoiceNumber} will be due on ${dueDate}. Please advise as to any issues that may cause delay in payment or if you require any additional information from us. In the case that we do not have the correct details for queries relating to invoicing and payments we ask that you make us aware and we will update our system.`;

  // 执行邮件发送
  MailApp.sendEmail(address, subject, body);
}

三、扩展为批量发送所有行的代码 #

修改后可以自动遍历表格中所有有数据的行，逐行发送提醒邮件：

function sendAllInvoiceReminders() {
  let sheet = SpreadsheetApp.getActiveSheet();
  // 获取表格中所有有数据的单元格，转为二维数组（每行是一个子数组）
  let allData = sheet.getDataRange().getValues();
  
  // 跳过第1行表头，从第2行开始遍历
  for (let i = 1; i < allData.length; i++) {
    let row = allData[i];
    // 解构当前行的各列数据：A列邮箱、B列标题、C列发票号、D列到期日
    let [address, invoiceTitle, invoiceNumber, dueDate] = row;

    // 跳过邮箱为空的行，避免无效发送
    if (!address) continue;

    // 格式化日期（可选，让日期显示更友好）
    let formattedDueDate = dueDate.toLocaleDateString();

    // 构造邮件内容
    let subject = `${invoiceTitle}-Upcoming Due Date for invoice ${invoiceNumber}`;
    let body = `Dear Sir/Madam. I hope you are well. This email serves as a reminder that your invoice ${invoiceNumber} will be due on ${formattedDueDate}. Please advise as to any issues that may cause delay in payment or if you require any additional information from us. In the case that we do not have the correct details for queries relating to invoicing and payments we ask that you make us aware and we will update our system.`;

    // 发送当前行对应的邮件
    MailApp.sendEmail(address, subject, body);
  }
}

新手操作提示 #

1. 打开目标Google表格，点击「扩展程序」→「Apps脚本」，把代码粘贴到编辑器中
2. 先测试单行发送函数：点击运行按钮，第一次运行需要授权，按页面提示完成权限申请即可
3. 测试批量发送前，建议先备份表格数据，或者用少量测试行验证，避免误发
4. 如果日期显示格式不符合需求，可以调整toLocaleDateString()的参数，比如toLocaleDateString('en-US', {year: 'numeric', month: 'long', day: 'numeric'})

内容的提问来源于stack exchange，提问作者Ken G