How can I automatically insert images and charts for each row of Google Sheets document to a Slides template (mail merge) using Apps Script?

Question

I want Apps Script to automatically generate a new set of Slides using data from a Sheets document which has rows of the different information I want inserted into a Slides template replacing the placeholder tags. I want it to do it instantly for each row inside the table with one action, so if there are 10 rows, 10 sets of Slides documents will be generated.

The text replacement works, however I'm not sure how to replace, for example, a shape with "{{image}}" written inside with the image using the URL under the image column. Same goes for charts.

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

};
});
          }

Answer 1

The difference between {{image}} and the other placeholder is that you want to replace the text through an actual image

• Unfortunately you cannot paste an image inside of a text box.

• Instead, you can specify that if an {{image}} placeholder is present you want to paste an image into the slide that contains the text box.

• You can check for existence of the placeholder with

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

• You insert the image if the condition is fulfilled and specify its size and position, e.g. slide.insertImage(image).scaleHeight(0.5).scaleWidth(0.5).setLeft(10);

• Important: To insert the image in Slides you need the webContentLink instead of the Url and the image must be publibly accesible (see here).

• If you do not know the webContentLink of your image, I recommend you to replace the URL in your spreadsheet with the file Id and modify your code to

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

• Mind that the webContentLink cannot be accessed by DriveApp, but only by the Advanced Drive Service which you need to manually enable.

Working sample:

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