Google Apps Script单元格值转QR码并邮件发送报错求助

问题根源 #

1. ImageItem.getImage()并非Google Apps Script的内置方法，这是代码报错的直接原因。
2. 直接将图片Blob拼接到纯文本邮件正文中，会被解析为乱码——纯文本格式无法处理二进制图像数据。

解决方案1：将QR码作为邮件附件发送 #

这种方式简单直接，图片会以附件形式出现在邮件中，无需修改邮件内容格式：

function sendEmail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();

  var templateText = sheet.getRange(1,7).getValue();
  var tickNum = 1;
  var emailAddressQR = "printerqr@gmail.com";

  for (var i = 2; i <= lastRow; i++) { 
    var tickNum2 = i - tickNum;
    var emailAddress = sheet.getRange(i,2).getValue();
    var name = sheet.getRange(i,3).getValue();
    var subjectLine = "QR Code #" + tickNum2 + " " + name;
    
    // 生成QR码Blob并命名
    var qrImageData = sheet.getRange(i,9).getValue();
    var url = "https://api.qrserver.com/v1/create-qr-code/?size=300x300&data=" + encodeURIComponent(qrImageData);
    var qrBlob = UrlFetchApp.fetch(url).getBlob().setName("QR_Code_" + tickNum2 + ".png");
    
    var emailBody = templateText.replace("{num}", tickNum2);
    var emailSent = sheet.getRange(i,5).getValue();
    var confirmEmail = sheet.getRange(i,6);
    var qrCodeTickNum = sheet.getRange(i,7);
    var confirmEmailValue = confirmEmail.getValue();

    if (confirmEmailValue === "") { 
      if (emailSent === "No") {
        // 发送带附件的邮件
        MailApp.sendEmail({
          to: emailAddress,
          subject: subjectLine,
          body: emailBody,
          attachments: [qrBlob]
        });
        confirmEmail.setValue("Sent to user");
        qrCodeTickNum.setValue(tickNum2);
      } else if (emailSent === "Yes") {
        // 给打印机和用户各发一封带附件的邮件
        MailApp.sendEmail({
          to: emailAddressQR,
          subject: subjectLine,
          body: emailBody,
          attachments: [qrBlob]
        });
        MailApp.sendEmail({
          to: emailAddress,
          subject: subjectLine,
          body: emailBody,
          attachments: [qrBlob]
        });
        confirmEmail.setValue("Sent to printer and user");
        qrCodeTickNum.setValue(tickNum2);
      }
    }
  }
}

解决方案2：在HTML邮件正文中嵌入QR码 #

如果希望QR码直接显示在邮件正文里，需要使用HTML格式的邮件，将图片转为Base64编码嵌入：

function sendEmailWithEmbeddedQR() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();

  var templateText = sheet.getRange(1,7).getValue();
  var tickNum = 1;
  var emailAddressQR = "printerqr@gmail.com";

  for (var i = 2; i <= lastRow; i++) { 
    var tickNum2 = i - tickNum;
    var emailAddress = sheet.getRange(i,2).getValue();
    var name = sheet.getRange(i,3).getValue();
    var subjectLine = "QR Code #" + tickNum2 + " " + name;
    
    // 生成QR码并转为Base64编码
    var qrImageData = sheet.getRange(i,9).getValue();
    var url = "https://api.qrserver.com/v1/create-qr-code/?size=300x300&data=" + encodeURIComponent(qrImageData);
    var qrBlob = UrlFetchApp.fetch(url).getBlob();
    var qrBase64 = Utilities.base64Encode(qrBlob.getBytes());
    var qrImageHtml = '<br><img src="data:image/png;base64,' + qrBase64 + '" alt="QR Code">';
    
    // 拼接HTML邮件内容，同时保留纯文本备用
    var emailBodyHtml = templateText.replace("{num}", tickNum2) + qrImageHtml;
    var emailBodyPlain = templateText.replace("{num}", tickNum2) + "\nQR Code is attached as image.";
    
    var emailSent = sheet.getRange(i,5).getValue();
    var confirmEmail = sheet.getRange(i,6);
    var qrCodeTickNum = sheet.getRange(i,7);
    var confirmEmailValue = confirmEmail.getValue();

    if (confirmEmailValue === "") { 
      if (emailSent === "No") {
        MailApp.sendEmail({
          to: emailAddress,
          subject: subjectLine,
          body: emailBodyPlain,
          htmlBody: emailBodyHtml
        });
        confirmEmail.setValue("Sent to user");
        qrCodeTickNum.setValue(tickNum2);
      } else if (emailSent === "Yes") {
        MailApp.sendEmail({
          to: emailAddressQR,
          subject: subjectLine,
          body: emailBodyPlain,
          htmlBody: emailBodyHtml
        });
        MailApp.sendEmail({
          to: emailAddress,
          subject: subjectLine,
          body: emailBodyPlain,
          htmlBody: emailBodyHtml
        });
        confirmEmail.setValue("Sent to printer and user");
        qrCodeTickNum.setValue(tickNum2);
      }
    }
  }
}

关键修改点说明 #

• 删除了不存在的ImageItem.getImage()调用，直接使用UrlFetchApp获取的Blob对象。
• 改用对象参数方式调用MailApp.sendEmail，支持添加附件或指定HTML正文，避免纯文本邮件无法显示图片的问题。
• 方案1给Blob设置文件名，方便收件人识别附件；方案2将图片转为Base64编码嵌入HTML，确保正文能直接显示QR码。

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