Re: Inject to Data From Sheets to a Duplicate Slides Presentation

[Karim Ballout]

UPDATE: I edited the script and I am now able to generate the Sheet data to the duplicated Slides Template. However it is also duplicating the first slide (the title slide) times the number of rows.

Say I have 10 rows in Google Sheet, It is successfully creating 10 slides with replaced placeholders (representing each row). But it is also creating 10 title slides. I don't want the title slide to be duplicated.

Here's the updated script:

function generateLandingPagesReport() {

  var templateId = "1bXAYGCKkpZhksXz8gTCgFYbNoI1BIhAZakd68VlXHeo";

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var deck = SlidesApp.openById(templateId);

  var sheet = ss.getSheetByName('Copy of Overall Performance 1');

  var values = sheet.getRange('A2:J15').getValues();

  var getslides = deck.getSlides();

  var templateSlide = getslides[1];

  var presLength = getslides.length;

  // Create a Slides presentation, removing the default

  // title slide.

  var presentationTitle =

    ss.getName() + " Presentation";

  var slides = SlidesApp.create(presentationTitle);

  var defaultSlides = slides.getSlides();

   defaultSlides.forEach(function(slide) {

   slide.remove();

  });

  values.forEach(function(page){

  if(page[0]){

    

   var landingPage = page[0];

   var sessions = page[1];

   var newSessions = page[2];

   var pagesPer = page[5];

   var goalRate = page[7];

   var goalValue = page[9];

    // Access the template presentation

   var template = SlidesApp.openById(templateId);

   var fileName = template.getName();

   var templateSlides = template.getSlides();

 // Insert slides from template

   var index = 0;

   templateSlides.forEach(function(slide) {

     var newSlide = slides.insertSlide(index);

     var elements = slide.getPageElements();

     elements.forEach(function(element) {

       newSlide.insertPageElement(element);

   });

   index++;

   });

   defaultSlides = slides.getSlides (); //update the slides array for indexes and length

   var newSlide = defaultSlides [1];

   var shapes = (newSlide.getShapes());

     shapes.forEach(function(shape){

       shape.getText().replaceAllText('{{landing page}}',landingPage);

       shape.getText().replaceAllText('{{sessions}}',sessions);

       shape.getText().replaceAllText('{{new sessions}}',newSessions);

       shape.getText().replaceAllText('{{pages per session}}',pagesPer);

       shape.getText().replaceAllText('{{goal rate}}',goalRate);

       shape.getText().replaceAllText('{{goal value}}',goalValue);

    }); 

   presLength = defaultSlides.length; 

   newSlide.move(presLength); 

  } // end our conditional statement

  }); //close our loop o

Thank you for any help.

On Saturday, March 5, 2022 at 3:33:16 PM UTC+2 Karim Ballout wrote:

Dear community,

What I have:

• I have a Google Slide Template that has a Title Slide and a second slide with placeholders
• I have a script that enables me to generate the Google Sheets Data to the Google Slides Template, replacing the placeholders and creating slides as much as the number of rows in the sheet.

What I want:

• I want the script to fill and replace the placeholders in a duplicate of the template instead of the template itself.
• I want to replace 2 placeholders with 2 charts per slide. The charts are already created inside Google Sheets and I want to embed them (don't want them as images because I want to be able to update them inside Slides)

What I did so far:

I edited the script to be able to create a new Google Slides Presentation and copy the template Slides. But it just copies the initial placeholders in the New Presentation and updates the placeholders in the Template.

I want the script to keep the template slide intact and update the placeholders data in the new presentation instead.

My 2 questions :

1 - How do I tell the script to keep the template Google Slide intact and instead update the new Presentation created ?

2 - How do I add 2 linked charts in the presentation inside each slide replacing placeholders.

This is the script:

function generateLandingPagesReport() {

  var templateId = "1bXAYGCKkpZhksXz8gTCgFYbNoI1BIhAZakd68VlXHeo";

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var deck = SlidesApp.openById(templateId);

  var sheet = ss.getSheetByName('Copy of Overall Performance 1');

  var values = sheet.getRange('A2:J15').getValues();

  var getslides = deck.getSlides();

  var templateSlide = getslides[1];

  var presLength = getslides.length;

  // Create a Slides presentation, removing the default

  // title slide.

  var presentationTitle =

    ss.getName() + " Presentation";

  var slides = SlidesApp.create(presentationTitle);

  var defaultSlides = slides.getSlides();

   defaultSlides.forEach(function(slide) {

   slide.remove();

  });

   // Access the template presentation

 var template = SlidesApp.openById(templateId);

 var fileName = template.getName();

 var templateSlides = template.getSlides();

 // Insert slides from template

 var index = 0;

 templateSlides.forEach(function(slide) {

   var newSlide = slides.insertSlide(index);

   var elements = slide.getPageElements();

   elements.forEach(function(element) {

     newSlide.insertPageElement(element);

   });

   index++;

 });

  values.forEach(function(page){

  if(page[0]){

    

   var landingPage = page[0];

   var sessions = page[1];

   var newSessions = page[2];

   var pagesPer = page[5];

   var goalRate = page[7];

   var goalValue = page[9];

    

   templateSlide.duplicate(); //duplicate the template page

   getslides = deck.getSlides(); //update the slides array for indexes and length

   newSlide = getslides[2]; // declare the new page to update

    

    

   var shapes = (newSlide.getShapes());

     shapes.forEach(function(shape){

       shape.getText().replaceAllText('{{landing page}}',landingPage);

       shape.getText().replaceAllText('{{sessions}}',sessions);

       shape.getText().replaceAllText('{{new sessions}}',newSessions);

       shape.getText().replaceAllText('{{pages per session}}',pagesPer);

       shape.getText().replaceAllText('{{goal rate}}',goalRate);

       shape.getText().replaceAllText('{{goal value}}',goalValue);

    }); 

   presLength = getslides.length; 

   newSlide.move(presLength); 

   

  } // end our conditional statement

  }); //close our loop of values

//Remove the template slide

templateSlide.remove(); }

Thank you so much for any help. I have ADHD and my job is tedious repetitive task reporting. You would be literally helpong me have better days at work.