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

Question

I want that 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 placeholder tag with "{{image}}"

The Image is a generated Qr code in column (N) with an sheet addon (QR Code for Classroom Attendance)and for each row separate. For example 10 rows with different qr codes. This addon writes the generate QR code in the column N. As I said for each I have a different Qr code.

function mailMergeSlidesFromSheets() {
  // Load data from the spreadsheet
  var dataRange = SpreadsheetApp.getActive().getDataRange();
  var sheetContents = dataRange.getValues();

  // Save the header in a variable called header
  var header = sheetContents.shift();

  // Create an array to save the data to be written back to the sheet.
  // We'll use this array to save links to Google Slides.
  var updatedContents = [];

  // Add the header to the array that will be written back
  // to the sheet.
  updatedContents.push(header);

  // For each row, see if the 4th column is empty.
  // If it is empty, it means that a slide deck hasn't been
  // created yet.
  sheetContents.forEach(function(row) {
    if(row[14] === "") {
      // Create a Google Slides presentation using
      // information from the row.
      var slides = createSlidesFromRow(row);
      var slidesId = slides.getId();
   
      // Create the Google Slides' URL using its Id.
      var slidesUrl = `https://docs.google.com/presentation/d/${slidesId}/edit`;

      // Add this URL to the 4th column of the row and add this row
      // to the updatedContents array to be written back to the sheet.
      row[14] = slidesUrl;
      updatedContents.push(row);
    }
  });

  // Write the updated data back to the Google Sheets spreadsheet.
  dataRange.setValues(updatedContents);

}

function createSlidesFromRow(row) {
 // Create a copy of the Slides template
 var deck = createCopyOfSlidesTemplate();

 // Rename the deck using the firstname and lastname of the student
 deck.setName(row[4] + " " + row[9] + row[3]);

 // Replace template variables using the student's information.
    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";

 // Create a copy of the file using DriveApp
 var copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy();

 // Load the copy using the SlidesApp.
 var slides = SlidesApp.openById(copy.getId());

 return slides;
}

function onOpen() {
 // Create a custom menu to make it easy to run the Mail Merge
 // script from the sheet.
 SpreadsheetApp.getUi().createMenu("⚙️ Create BWN by Pavlos")
   .addItem("Create Slides","mailMergeSlidesFromSheets")
   .addToUi();
}

 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
    

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

Answer 1

I believe your goal is as follows.

• You want to replace {{Image}} on 1st page of Google Slides with the image.
• The image can be retrieved by the URL like "https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl="&M2.

In this case, how about directly using the values of column "M" as follows? When your script is modified, please modify it as follows.

From:

deck.insertImage("{{image}}", row[13]);

To:

deck.getSlides()[0].getShapes().find(s => s.getText().asString().trim().toUpperCase() == "{{IMAGE}}").replaceWithImage(`https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl=${row[12]}`);

Note:

• In this modification, it supposes that row[14] === "" is true and the column "M" has the value of https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl=${value}. Please be careful about this.

• It seems that there are a lot of rows you want to process. So, if the maximum execution time (6 minutes) is over, please separate the rows for processing. In your this question, you want to replace {{Image}} with the image. So, this answer is for it. Please be careful about this.

Reference:

• replaceWithImage(imageUrl)