Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsinsert-updategoogle-slides

How to update instead of aggregating Google Slides when updating records in Google Sheet with Google Apps Script?


Using Google Apps Script, I generate G-Slides based on a template (first slide top left) as shown below...

enter image description here

...from a Google Sheet where each row has a set of attributes corresponding to its respective slide:

enter image description here

Furthermore, a trigger has been set to execute the Google Apps script 'On Open' (i.e. upon refreshing the document) in G-Sheet.

enter image description here

The script currently duplicates the first slide (the template) per rows with complete information, and feeds the variables from G-Sheet as designated in the {{brackets}} onto the template slide (i.e. the template_value matches the template_field).

function fillTemplateV3() {
 
// Id of the slides template
var PRESENTATION_ID = "PRESENTATION ID HERE";
 
// Open the presentation
var presentation = SlidesApp.openById(PRESENTATION_ID);
// Read data from the spreadsheet
var values = SpreadsheetApp.getActive().getDataRange().getValues();
 
// Replace template variables in the presentation with values
let hdr = values.shift()
values.forEach(row =>{
 let templateSlide = presentation.getSlides()[0].duplicate()
 for ( var i = 0 ; i < 4; i++){
      let templateField = hdr[i]
      let templateValue = row[i]
      let logo = row[4]
      console.log(logo)
     templateSlide.replaceAllText(templateField, templateValue)
     templateSlide.getShapes().forEach(s => {
      if (s.getText().asString().trim() == "{{logo}}") s.replaceWithImage(logo);
    });
 }
}
);
 
}

The issue I'm having is that the script is additive, i.e. each time the script is executed it keeps on adding slides on top of those already created. I am not convinced that adding a function to delete the Slides before executing the for loop is efficient to address this issue.

How do I execute the script so that the number of slides in G-Slides correspond to the number of rows in G-Sheets? I.e. if I have 3 rows filled with information in G-Sheet, I should only have 4 slides total (counting the template slide). Right now, every-time the script executes, slides are added to the G-Slide document, so that if I add a fourth row, execute the script, and the script ran once before, I end up with 8 slides total. Instead I want to generate 4 slides, not counting the template slide.

Edited to clarify the code's objective.


Solution

  • I was overthinking this by a lot. I simply had to execute a for loop through my slides first, before executing the for loop to fill my G-Slide template from G-Sheets, in order to delete all slides besides the first one, which serves as my template slide:

    function fillTemplate() {
      // Id of the slides template
      // Remember to replace this with the Id of your presentation
      var PRESENTATION_ID = "YOUR PRESENTATION ID HERE";
      // Open the presentation
      var presentation = SlidesApp.openById(PRESENTATION_ID);
    
      // Read data from the spreadsheet
      var values = SpreadsheetApp.getActive().getDataRange().getValues();
      // Replace template variables in the presentation with values
      let hdr = values.shift()
      var slides = presentation.getSlides();
      //change i to any other index if desired
      for (var i = 1; i < slides.length; i++) {
        slides[i].remove()
      }
      values.forEach(row => {
        let templateSlide = presentation.getSlides()[0].duplicate()
        for (var i = 1; i < 6; i++) {
          let templateField = hdr[i]
          let templateValue = row[i]
          let logo = row[6]
          console.log(logo)
          templateSlide.replaceAllText(templateField, templateValue)
          templateSlide.getShapes().forEach(s => {
            if (s.getText().asString().trim() == "{{logo_url}}") s.replaceWithImage(logo);
          });
        }
      });
    }

    Credit to the answer of this post for helping me find a solution: How to delete slides programmatically after the nth one in google slides?