如何用Apps Script实现Sheets行数据批量生成带图Slides邮件合并

问题背景 #

• 已实现通过Google Apps Script从Google Sheets每行数据批量生成对应Slides文档，文本占位符替换功能正常
• 无法完成{{image}}占位符的替换，该占位符对应Sheets中N列（代码中为row[13]）的二维码图片（由"QR Code for Classroom Attendance"插件生成）

现有代码 #

function mailMergeSlidesFromSheets() {
  // 加载表格数据
  var dataRange = SpreadsheetApp.getActive().getDataRange();
  var sheetContents = dataRange.getValues();

  // 保存表头
  var header = sheetContents.shift();

  // 创建数组用于存储要写回表格的数据（包含生成的Slides链接）
  var updatedContents = [];

  // 将表头加入待写回数组
  updatedContents.push(header);

  // 遍历每行数据，若第15列（索引14）为空则生成对应Slides
  sheetContents.forEach(function(row) {
    if(row[14] === "") {
      // 根据当前行数据生成Slides
      var slides = createSlidesFromRow(row);
      var slidesId = slides.getId();
   
      // 构造Slides链接
      var slidesUrl = `https://docs.google.com/presentation/d/${slidesId}/edit`;

      // 将链接存入当前行第15列并加入待写回数组
      row[14] = slidesUrl;
      updatedContents.push(row);
    }
  });

  // 将更新后的数据写回表格
  dataRange.setValues(updatedContents);

}

function createSlidesFromRow(row) {
 // 复制Slides模板
 var deck = createCopyOfSlidesTemplate();

 // 用数据重命名文档
 deck.setName(row[4] + " " + row[9] + row[3]);

 // 替换文本占位符
 deck.replaceAllText("{{id}}", row[0]);
 deck.replaceAllText("{{tag}}", row[3]);
 deck.replaceAllText("{{besetzung}}", row[4]);
 deck.replaceAllText("{{beginn}}", row[5]);
 deck.replaceAllText("{{ende}}", row[6]);
 deck.replaceAllText("{{halle}}", row[7]);
 deck.replaceAllText("{{stand}}", row[8]);
 deck.replaceAllText("{{firma}}", row[2]);
 deck.replaceAllText("{{veranstaltung}}", row[9]);
 // 原无效的图片插入写法
 deck.insertImage("{{image}}", row[13]);

 return deck;
}

function createCopyOfSlidesTemplate() {
 var TEMPLATE_ID = "19PKvWoDtbeVHcqm4DnWUxRx1OBO817uG3cL5Ox-dQoo";

 // 复制模板文件
 var copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy();

 // 打开复制后的Slides
 var slides = SlidesApp.openById(copy.getId());

 return slides;
}

function onOpen() {
 // 创建自定义菜单
 SpreadsheetApp.getUi().createMenu("⚙️ Create BWN by Pavlos")
   .addItem("Create Slides","mailMergeSlidesFromSheets")
   .addToUi();
}

尝试过的无效写法 #

// 尝试过的各类无效图片替换写法
// picture.find("{{image}}").replace(image); 
// picture.findText("{{image}}").replace(image);
// picture.getText("{{image}}").replaceWithImage(image);        
// picture.getText().findText("{{image}}").replace(image);

解决方案 #

问题核心在于原代码使用deck.insertImage()直接插入图片，并未定位到{{image}}占位符的位置进行替换。正确的做法是先找到占位符所在的文本框，获取其位置和尺寸，删除占位符文本后在原位置插入图片。

修改createSlidesFromRow函数中的图片处理部分：

function createSlidesFromRow(row) {
 // 复制Slides模板
 var deck = createCopyOfSlidesTemplate();

 // 用数据重命名文档
 deck.setName(row[4] + " " + row[9] + row[3]);

 // 替换文本占位符
 deck.replaceAllText("{{id}}", row[0]);
 deck.replaceAllText("{{tag}}", row[3]);
 deck.replaceAllText("{{besetzung}}", row[4]);
 deck.replaceAllText("{{beginn}}", row[5]);
 deck.replaceAllText("{{ende}}", row[6]);
 deck.replaceAllText("{{halle}}", row[7]);
 deck.replaceAllText("{{stand}}", row[8]);
 deck.replaceAllText("{{firma}}", row[2]);
 deck.replaceAllText("{{veranstaltung}}", row[9]);

 // --- 新增图片占位符替换逻辑 ---
 var imagePlaceholder = "{{image}}";
 var allSlides = deck.getSlides();
 
 allSlides.forEach(function(slide) {
   // 查找当前幻灯片中所有包含占位符的文本范围
   var textMatches = slide.findText(imagePlaceholder);
   
   while (textMatches.hasNext()) {
     var matchRange = textMatches.next();
     var textElement = matchRange.getElement();
     var textBox = textElement.getParent();
     
     // 获取占位符所在文本框的位置和尺寸
     var left = textBox.getLeft();
     var top = textBox.getTop();
     var width = textBox.getWidth();
     var height = textBox.getHeight();
     
     // 清空占位符文本
     textElement.asText().setText("");
     
     // 处理二维码图片：若row[13]是图片URL，先获取Blob
     var imageBlob = UrlFetchApp.fetch(row[13]).getBlob();
     
     // 在原位置插入图片并设置相同尺寸
     slide.insertImage(imageBlob, left, top, width, height);
   }
 });

 return deck;
}

特殊情况说明 #

如果row[13]不是图片URL，而是Sheets单元格中直接插入的图片，需要改用以下方式获取图片Blob：

// 获取当前表格中所有图片，匹配对应行的二维码
var sheet = SpreadsheetApp.getActiveSheet();
var allImages = sheet.getImages();
var qrBlob;

// 注意：sheetContents已移除表头，原表格行号为当前循环索引+2（根据实际情况调整）
allImages.forEach(function(img) {
  if (img.getCellRow() === sheetContents.indexOf(row) + 2) {
    qrBlob = img.getBlob();
  }
});

// 后续插入逻辑不变
slide.insertImage(qrBlob, left, top, width, height);

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