google-apps-script - 如何使用Apps Script自动将Google Sheets文档中每行的图片和图表插入到Slides模板中（邮件合并）？

我想让Apps Script使用Sheets文档中的数据自动生成一组新的幻灯片，其中有几行不同的信息，我想插入到幻灯片模板中，替换占位符标签。我想让它对表里面的每一行都用一个动作即时完成，所以如果有10行，就会生成10套Slides文档。

文字替换可以用，但是我不知道如何替换，比如说，里面写有"{{图片}}"的形状，用图片替换，使用URL下的 形象 列。同样的情况也发生在 图表.

function generateNewSlides() {  

var wsID = "worksheet ID here";
var ws = SpreadsheetApp.openById(wsID).getSheetByName("Data");
var data = ws.getRange(2, 1, ws.getLastRow()-1, 6).getValues();

// the above should get the relevant table from the sheet     

data.forEach(function(info){
if(info[0]){

var firstname = info[0];
var surname = info[1];
var email = info[2];
var phone = info[3];
var image = info[4];
var presName = info[5];

// the above are columns where the different pieces of data would be taken from for the placeholders in the Slides template

var slidesTemplateID = "slides template ID here";   
var template = DriveApp.getFileById(slidesTemplateID);
var folderID = "folder where itll be saved ID here";      

var copiedTemplate = template.makeCopy(presName, DriveApp.getFolderById(folderID));
var Presentation = SlidesApp.openById(copiedTemplate.getId());

// the above should create a copy and then open it

Presentation.getSlides().forEach(function(slide) {
  slide.getShapes().forEach(function(shape) {
    shape.getText().replaceAllText("{{firstname}}",firstname);
    shape.getText().replaceAllText("{{surname}}",surname);
    shape.getText().replaceAllText("{{email}}",email);
    shape.getText().replaceAllText("{{phone}}",phone);
    shape.getText().replaceAllText("{{presname}}",presName);

  })
      // replaces the placeholder tags with the desired text    
      // I am not sure how to insert something similar for images and charts in the code here
      // I've tried variations of the below, none of which have worked
      // slide.getShapes().forEach(function(picture) {

        // picture.find("{{image}}").replace(image); 
          //  picture.findText("{{image}}").replace(image);
              //  picture.getText("{{image}}").replaceWithImage(image);        
                 // picture.getText().findText("{{image}}").replace(image);
}); 

};
});
          }

之间的区别 {{image}} 另一个占位符是你想通过一个实际的图像来替换文本

• 不幸的是，你不能在文本框内粘贴图像。

• 相反，你可以指定如果一个 {{image}} 占位符是存在的，你想把图像粘贴到包含文本框的幻灯片中。

• 您可以使用以下方法检查占位符是否存在

  var imageText = shape.getText().replaceAllText("{{image}}",""); if(imageText == true){...}

• 如果条件满足，你就可以插入图片，并指定其大小和位置，例如。slide.insertImage(image).scaleHeight(0.5).scaleWidth(0.5).setLeft(10);

• 重要：要在Slides中插入图片，你需要使用webContentLink而不是Url，而且图片必须是可公开访问的（请看 此处).

• 如果你不知道你的图片的webContentLink，我建议你替换为 URL 在您的电子表格中使用 file Id 并修改你的代码为

  var id = info[4]; var image = Drive.Files.get(id).webContentLink

• 请注意，webContentLink不能被DriveApp访问，而只能通过 高级驱动器服务 您需要手动 启用.

工作样本。

function generateNewSlides() {  
  
  var wsID = "worksheet ID here";
  var ws = SpreadsheetApp.openById("xxx").getSheetByName("Data");
  var data = ws.getRange(2, 1, ws.getLastRow()-1, 6).getValues();
  
  // the above should get the relevant table from the sheet     
  
  data.forEach(function(info){
    if(info[0]){
      
      var firstname = info[0];
      var surname = info[1];
      var email = info[2];
      var phone = info[3];
      var id = info[4];
      var image = Drive.Files.get(id).webContentLink
      var presName = info[5];
      
      // the above are columns where the different pieces of data would be taken from for the placeholders in the Slides template
      
      var slidesTemplateID = "xxx";   
      var template = DriveApp.getFileById(slidesTemplateID);
      var folderID = "folder where itll be saved ID here";      
      
      var copiedTemplate = template.makeCopy(presName, DriveApp.getFolderById(folderID));
      var Presentation = SlidesApp.openById(copiedTemplate.getId());
      
      // the above should create a copy and then open it
      Presentation.getSlides().forEach(function(slide) {
        slide.getShapes().forEach(function(shape) {
          shape.getText().replaceAllText("{{firstname}}",firstname);
          shape.getText().replaceAllText("{{surname}}",surname);
          shape.getText().replaceAllText("{{email}}",email);
          shape.getText().replaceAllText("{{phone}}",phone);
          shape.getText().replaceAllText("{{presname}}",presName);
          var imageText = shape.getText().replaceAllText("{{image}}","");          
          if(imageText == true){
            slide.insertImage(image).scaleHeight(0.5).scaleWidth(0.5).setLeft(10);
          }
        })
      }); 
      
    };
  });
}