How to flush out all existing data when re-running a script which injects Google Sheet data to Google slides?

Question

I have a flow that sends data from a google sheet into google slides. For each row in google sheets, it will generate one slide. When I do the initial run, it works fine. However, when I then add a new or delete an existing row in the google sheets, my slides currently does not update properly.

Issue 1: I have 2 initial rows (see below), and I then add a new row, the script then generates the initial 2 slides PLUS 3 additional slides (initial two slides + new slide) which leads to duplicates.

Issue 2: When I delete a row in the google sheets and then re-run the script, the slide (for the deleted row) still exists and is not removed from the deck.

I think the easiest way is the remove all filled slides and only use the raw slide (with variables) and then start re-filling (see raw slide below).

Here is the google sheet example (assuming we start with 2 rows):

Here is the script I run:

function generateSlides() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/7Nsah"; 
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deck = SlidesApp.getActivePresentation();
  var sheet = ss.getSheetByName('tab_1');
  var values = sheet.getRange('A2:B1000').getValues();
  var slides = deck.getSlides();
  var templateSlide = slides[1];
  var presLength = slides.length;
  
  values.forEach(function(page){
  if(page[0]){
    
   var Email = page[1];
   var Name = page[0];
   

   templateSlide.duplicate(); //duplicate the template page
   slides = deck.getSlides(); //update the slides array for indexes and length
   newSlide = slides[2]; // declare the new page to update
    
    
   var shapes = (newSlide.getShapes());
     shapes.forEach(function(shape){
       shape.getText().replaceAllText('{{Email}}',Email);
       shape.getText().replaceAllText('{{Name}}',Name);
       
    }); 
   presLength = slides.length; 
   newSlide.move(presLength); 
  } // end our conditional statement
  }); //close our loop of values

//Remove the template slide
templateSlide.remove();
  
}

Here is the raw slide which is filled with each run (and therefore overwritten with the first run which is part of the problem).

So, the issue is that after the initial run the raw slide form does not exist anymore as it was filled with Name/Email. However, with every subsequent run, I want to use only the raw slide (again) and remove any existing slides which gives it the flushing effect. How would I do this? Any help is appreciated! Thanks

I have to apologize for my poor English skill. Unfortunately, from your question, I cannot understand about your input and output you expect. By this, I cannot understand about your current issue. So in order to correctly understand about your question, can I ask you about the detail of input and output you expect? — Tanaike
For your issue #2 maybe this could help: After you have the sheet, the first time you create the presentation from the data, try mapping a unique value like the slide index to the row on the sheet (write it to the row). Then check all your rows' slideIndexes are +1 the previous index, means the slide at that skipped index(es) should be removed from the desk. What do you think? — Aerials
Your request could be implemented with the installable triger onChange. Are you still interested in a solution? — ziganotschka

Rubén Rubén · Accepted Answer · 2020-08-20T00:20:52

Unfortunately the Google Slides API and hence Google Apps Script doesn't include an easy way to do what you are looking for.

The easier solution is to make a copy of your slide with the place holders and replace them on the copy instead of using the "original".

Another option is that you set your script somehow the shape id (unique object id) and the text position then use that data to update that content.

Resources

Merging Data into Slides . Extending Google Slides

How to flush out all existing data when re-running a script which injects Google Sheet data to Google slides?

1 Answers