0

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.

HP-Nunes
  • 111
  • 1
  • 11
  • 1
    I think that it is required to know when your script is run. For example, when a user edits column "A" to an empty row, when a script is run, columns "B" to "E" are still empty. And, about `2) for the code to execute even if there are empty records for any given row (ex: a missing url for the 'logo' column)`, I cannot understand this. And about `3) for G-Sheets edits in rows which have already generated a Slide to affect those existing slides instead of creating new ones.`, how will you confirm this? I apologize for this. Can I ask you about the detail of your expected specification? – Tanaike Dec 02 '22 at 07:55
  • 1
    If I understand correctly, what you are trying to do in your script is to recognize each edit from the spreadsheet and then update each slide with the updated information from the spreadsheet, correct? Or do you have any other specific way of triggering the script as mentioned by @Tanaike? – Fernando Lara Dec 02 '22 at 16:21
  • Edited the original post to change the trigger to 'On Open', i.e. simply refreshing the G-Sheet will trigger the script to have it reflected onto the Slides. – HP-Nunes Dec 02 '22 at 17:56
  • @Tanaike: the trigger isn't what's bothering me. I thought that having the Slide change as I made changes to the Sheet was the best option, but the 'On Open' trigger makes things simpler by triggering the script only once the G-Sheet document is refreshed. I addressed your comment about Objective #2 in my edit of the post (italic). As for Objective 3: well, I can see right after the script executes whether changes have been made to the Slides. So if more slides are added-in, instead of editing the ones already existing, that's indicative that my script is not working as intended. – HP-Nunes Dec 02 '22 at 18:03
  • @FernandoLara: that's right. Although if I change the trigger instance to 'On Open', then those changes will materialize only when I refresh the G-Sheet. What I really want to figure out is to not add EXISTING slides each time I run the script, but instead have those edits reflected in them. – HP-Nunes Dec 02 '22 at 18:05
  • @HP-Nunes Are you willing to use something like a checkbox on each row to trigger changes? – Fernando Lara Dec 02 '22 at 18:16
  • @FernandoLara That's fine with me, so long as I'm not creating new slides on top of the ones I've already created. If I have only 4 rows with data, then I should only have 4 slides (excluding the Template slide). – HP-Nunes Dec 02 '22 at 18:19
  • Thank you for replying. From your reply, I think that your `1)` and `2)` can be achieved. But, about `3) for G-Sheets edits in rows which have already generated a Slide to affect those existing slides instead of creating new ones.`, I cannot still understand it. In this case, how will you confirm it? For example, when a slide is created by a row from Spreadsheet and when the row is modified, the existing slide has no placeholder. In this situation, how will you confirm the specific slide and update the slide using a modified row of Spreadsheet? I apologize for my poor English skill. – Tanaike Dec 03 '22 at 00:01
  • 1
    Is there a correspondence between rows and slides? Last row would be first slide, previous row is second slide and so on? You could have a timestamp per row to know last edit and a second timestamp per row of last update in Slides. So you can check those that were actually modified and update the changes in the slides that were already created (you save the time of updating every row, and optimize it for the most recents only). And if you have a row that doesn't have the Slides Timestamp the script should create one new slide. What do you think of something like this? – Martín Dec 03 '22 at 03:25
  • @Tanaike: The very first time I run the script, I only have the template Slide in the G-Slide document. I fill two rows with values in the G-Sheet document. The trigger executes once I refresh the G-Sheet doc. This generates two copies of the template with the template values in {{brackets}}, which are replaced with the values of the G-Sheet for their respective slides. Now, I add one more rows with values. Now I have 3 rows with values in my G-Sheet. If I trigger the script, my G-Slide will end up with 5 slides (excluding the template). What I want is to only have 3 slides total. – HP-Nunes Dec 03 '22 at 22:48
  • @Martín: Each row (with their set of values) = a corresponding slide. Each slide is a copy of the template with the {{template fields}} with are matched to the values called from the G-Sheet document. I only want a number of slides in G-Slides that correspond to the number of rows with values that exist in G-Sheets. Currently the script is adding slides on top of the slides that have already been created. A timestamp could be useful to track when slides are changed. – HP-Nunes Dec 03 '22 at 22:54
  • Thank you for replying. About `3) for G-Sheets edits in rows which have already generated a Slide to affect those existing slides instead of creating new ones.`, in this case, you don't want to update the existing slides using the updated Spreadsheet. Is my understanding correct? – Tanaike Dec 04 '22 at 00:42
  • @Tanaike: I do want to update existing slides if I'm making changes in G-Sheets. I just don't want to duplicate slides. – HP-Nunes Dec 05 '22 at 04:48
  • Thank you for replying. About `I do want to update existing slides if I'm making changes in G-Sheets. I just don't want to duplicate slides.`, In this case, how will you confirm it? For example, when a slide is created by a row from Spreadsheet and when the row is modified, I cannot understand it. the existing slide has no placeholder. In this situation, how will you confirm the specific slide and update the slide using a modified row of the Spreadsheet? – Tanaike Dec 05 '22 at 05:07
  • @Tanaike I can take Martin's suggestion and add a timestamp to the slide, indicating when it was last edited. I'm also not working with thousands of slides here, so I should be able to clearly see if I change the name of the tile of a slide, under the column {{title}} in G-Sheets, that I'll see it reflected on the corresponding slide right away. – HP-Nunes Dec 05 '22 at 06:21
  • Thank you for replying. I'm glad your issue was resolved. I apologize that t my comments were not useful for your situation. – Tanaike Dec 05 '22 at 06:22

1 Answers1

1

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?

HP-Nunes
  • 111
  • 1
  • 11