Google Sheets邮件合并脚本报错及运行异常排查与修复

在Google Sheets中运行邮件合并脚本时，出现报错：Exception: The starting column of the range is too small。相关代码如下：

/**
 * @OnlyCurrentDoc
*/

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();
}

function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {
  // option to skip browser prompt if you want to use this code in other projects
  if (!subjectLine){
    subjectLine = Browser.inputBox("Mail Merge", 
                                      "Type or copy/paste the subject line of the Gmail " +
                                      "draft message you would like to mail merge with:",
                                      Browser.Buttons.OK_CANCEL);
                                       
    if (subjectLine === "cancel" || subjectLine == ""){ 
    // if no subject line finish up
    return;
    }
  }
  
  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
  
  const dataRange = sheet.getDataRange();

  const data = dataRange.getDisplayValues();

  const heads = data.shift(); 
  
  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
  
  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));


  const out = [];

  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);

        GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
          htmlBody: msgObj.html,
          // bcc: 'a.bbc@email.com',
          // cc: 'a.cc@email.com',
          // from: 'an.alias@email.com',
          // name: 'name of the sender',
          // 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]]);
    }
  });

  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);
}

问题排查过程 #

确定报错出自代码行：

sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);

将其修改为：

sheet.getRange(2, emailSentColIdx+2, out.length).setValues(out);

修改后脚本可执行完成，但无法实现邮件发送功能。执行Logger.log(out)后，输出结果为：

[[null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null], [null]]

此问题与已有的同类提问不同，核心原因在于Sheets端缺失了一个关键值，目前已完成修复。

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