Google Apps Script：Gmail JPG附件插入Google Sheets异常问题

使用Google Apps Script从指定发件人Gmail提取JPG附件，存入数组后插入Google Sheets单元格，实际运行时单元格显示“Blob”而非图片，尝试多种方法未解决，原代码如下：

function importEmailsWithJPGs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var emailAddress = "a@gmail.com";

  var threads = GmailApp.search("from:" + emailAddress + " filename:jpg");
  var attachments;
  var date;
  
  var rowData = {};
  
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      attachments = messages[j].getAttachments();
      date = messages[j].getDate();
      var dateString = Utilities.formatDate(date, "GMT", "yyyy-MM-dd");
      
      if (!rowData[dateString]) {
        rowData[dateString] = [date];
      }
      
      for (var k = 0; k < attachments.length; k++) {
        if (attachments[k].getContentType().indexOf("image/jpeg") !== -1) {
         
         //var image = attachments[k].getAs('image/jpeg');
         // var imageName = attachments[k].getName();
         // var blob = new Blob([image], { type: 'image/jpeg' });
         // rowData[dateString].push(blob);
          //var image = attachments[k].getAs('image/jpeg');
        
         // var imageName = attachments[k].getName();
         // var byteArray = image.getBytes();
         // var base64EncodedImage = Utilities.base64Encode(byteArray);
         // var blob = Utilities.newBlob(base64EncodedImage, 'image/jpeg', imageName);
         // rowData[dateString].push(blob);

          var image = attachments[k].copyBlob();
          var imageName = attachments[k].getName();
          var blob = Utilities.newBlob(image.getBytes(), 'image/jpeg', imageName);
          rowData[dateString].push(blob);



        }
      }
    }
  }
  
  for (var key in rowData) {
    sheet.appendRow(rowData[key]);
  }
  
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 1; j < data[i].length; j++) {
      var cell = sheet.getRange(i + 1, j + 1);
      var value = data[i][j];
       if (value && value.getBytes) {
        //var image = value;
        var image = value.getAs('image/jpeg');

        //var imageData = new Uint8Array(image.getBytes());
        var cellWidth = cell.getWidth();
        var cellHeight = cell.getHeight();
        var imageWidth = image.getWidth();
        var imageHeight = image.getHeight();
        var ratio = Math.min(cellWidth / imageWidth, cellHeight / imageHeight);
        cell.setValue("");
       // cell.setImageData(imageData);
       cell.setImageData(image);
        }      
    }
  }
}

问题原因 #

• appendRow()方法无法直接存储Blob对象，会自动将其转换为字符串"Blob"写入单元格。
• 后续通过getDataRange().getValues()读取时，拿到的是字符串而非原始Blob对象，导致setImageData()无法识别并插入图片。

修改后的代码 #

function importEmailsWithJPGs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var emailAddress = "a@gmail.com";
  var threads = GmailApp.search("from:" + emailAddress + " filename:jpg");
  var rowData = {};

  // 遍历邮件，按日期分组存储图片Blob
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      var date = messages[j].getDate();
      var dateString = Utilities.formatDate(date, "GMT", "yyyy-MM-dd");

      // 初始化日期对应的对象，存储日期和图片列表
      if (!rowData[dateString]) {
        rowData[dateString] = { date: date, images: [] };
      }

      // 筛选JPG附件并添加到对应日期的图片列表
      for (var k = 0; k < attachments.length; k++) {
        var attachment = attachments[k];
        if (attachment.getContentType() === "image/jpeg") {
          rowData[dateString].images.push(attachment.copyBlob());
        }
      }
    }
  }

  // 从工作表最后一行的下一行开始插入数据
  var startRow = sheet.getLastRow() + 1;
  // 遍历分组数据，逐行插入日期和图片
  for (var key in rowData) {
    var currentData = rowData[key];
    // 写入日期到当前行第一列
    sheet.getRange(startRow, 1).setValue(currentData.date);
    
    // 逐个插入图片到后续单元格
    for (var imgIndex = 0; imgIndex < currentData.images.length; imgIndex++) {
      var cell = sheet.getRange(startRow, imgIndex + 2); // 从第二列开始放置图片
      var imageBlob = currentData.images[imgIndex];
      
      // 计算缩放比例，使图片适配单元格
      var cellWidth = cell.getWidth();
      var cellHeight = cell.getHeight();
      var imageWidth = imageBlob.getWidth();
      var imageHeight = imageBlob.getHeight();
      var ratio = Math.min(cellWidth / imageWidth, cellHeight / imageHeight);
      
      // 清空单元格内容并插入图片
      cell.clearContent();
      var insertedImage = cell.setImage(imageBlob);
      // 按比例调整图片大小（不需要缩放可删除此步骤）
      insertedImage.setWidth(imageWidth * ratio).setHeight(imageHeight * ratio);
    }
    
    startRow++; // 切换到下一行
  }
}

代码说明 #

1. 数据分组优化：不再将Blob存入数组后用appendRow()写入，而是直接按日期分组存储日期和图片Blob列表，避免Blob被转成字符串。
2. 直接插入图片：遍历分组数据时，先写入日期，再逐个单元格插入图片，使用setImage()方法直接将Blob插入单元格，无需中间转换。
3. 图片适配：自动计算单元格与图片的比例，缩放图片使其适配单元格大小，避免图片超出范围。

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