How to copy Values from sheets to slides using Google app scripts

Question

I am working on a slide that would take dynamic data from Google sheet.

1. I want to keep the slide template
2. Update the slide values in real-time

I want to use Google app scripts to connect the data from the sheet to the slide. I am new to Java Scripts and following a tutorial online to achieve this but with no luck.

My 2 files are set up as follows

and the slides I am working on can be found here

A copy of the data can also be found here

The tutorial I am following is found in this link

Below is the code I have implemented but the problem is it does not work and tries to create multiple pages and repeat same value on each page. I only want one page with the values updated from the sheet Your help would be grately appareciated. Thank you

function generateLandingPagesReport() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1UccsFtqKKsXhlus4-02dXfJP0ECcCsBUmLNZajJnS_4/edit"; //make sure this includes the '/edit at the end
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deck = SlidesApp.getActivePresentation();
  var sheet = ss.getSheetByName('metrics'); // sheet name
  var values = sheet.getRange('C4:L12').getValues(); // range for values
  var slides = deck.getSlides();
  var templateSlide = slides[0];
  var presLength = slides.length;
 
  values.forEach(function(page){
  if(page[0]){
   
   var Current = page[2];
   var Target = page[3];
   var Emea = page[5];
   var Depac = page[7];
   var Emma = page[9];
   var Comment = page[11];
   
 
   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('{{Current}}',Current);
       shape.getText().replaceAllText('{{Target}}',Target);
       shape.getText().replaceAllText('{{Emea}}',Emea);
       shape.getText().replaceAllText('{{Depac}}',Depac)
       shape.getText().replaceAllText('{{Emma}}',Emma);
       shape.getText().replaceAllText('{{Comment}}',Comment);
       
    });
   presLength = slides.length;
   newSlide.move(presLength);
  } // end our conditional statement
  }); //close our loop of values

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

Answer 1

I believe your goal as follows.

• You want to convert the table from Google Spreadsheet to Google Slides.

  • As the sample situation, you want to achieve as follows. This is from your question.

    

• When I saw your sample situation, I can confirm that there are 2 values of Samuel Tuffuor in Google Slides. From this situation, you want to put the values to the Google Slides using the row titles of METRIC.

Modification points:

• In your current script,
  • duplicate() is used in the loop. By this, new slide is inserted every loop. I think that this is one of several reasons of your issue.
  • At 1st loop of values.forEach(), all values are replaced. Because in the template of your Google Slides, {{current}}, {{Target}}, {{Emea}}, {{Depac}}, {{Emma}}and{{Comment}}of each row is replaced by eachreplaceAllText`.
  • In this case, it is required to distinguish {{current}}, {{Target}}, {{Emea}}, {{Depac}}, {{Emma}}and{{Comment}}` of each row.

In order to distinguish the values of each row of template of Google Slides, I would like to propose to group each row.

Usage:

1. Grouping each row of template of Google Slides.

Please group each row of template of Google Slides as follows. The red dotted line is the groups. In your sample Google Slides, 9 groups are created. Using these groups, the values retrieved from Google Spreadsheet are replaced with the placeholders of each group.

2. Sample script.

In order to replace the values retrieved from Google Spreadsheet with the place holders of each row of Google Slides, I modified your script as follows.

function generateLandingPagesReport() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1UccsFtqKKsXhlus4-02dXfJP0ECcCsBUmLNZajJnS_4/edit";
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deck = SlidesApp.getActivePresentation();
  
  // 1. Retrieve values from Google Spreadsheet.
  var sheet = ss.getSheetByName('metrics');
  var values = sheet.getRange('A4:L12').getDisplayValues(); // or .getValues();
  
  // 2. Create an object for using replacing the values.
  var obj = values.reduce((o, [a,,c,d,,f,,h,,j,,l]) => Object.assign(o, {[a.trim()]: {"{{current}}": c, "{{Target}}": d, "{{Emea}}": f, "{{Depac}}": h, "{{Emma}}": j, "{{Comment}}": l}}), {});

  // 3. Replace values for each group.
  var slides = deck.getSlides();
  var templateSlide = slides[0];
  var groups = templateSlide.getGroups();
  groups.forEach(g => {
    var c = g.getChildren();
    var key = "";
    var r = new RegExp(/{{\w.+}}/);
    for (var i = 0; i < c.length; i++) {
      var t = c[i].asShape().getText().asString().trim();
      if (!r.test(t)) {
        key = t;
        break;
      }
    }

    // I modified below script as the additional modification.
    if (obj[key]) {
      c.forEach(h => {
        var t = h.asShape().getText().asString().trim();
        if (r.test(t)) h.asShape().getText().setText(obj[key][t]);
      });
    }
  });
}

The flow of this modified script is as follows.

1. Retrieve values from Google Spreadsheet.
2. Create an object for using replacing the values.
3. Replace values for each group.

3. Result.

When the modified script is used for your sample Google Spreadsheet and the sample Google Slides with grouping each row, the following result is obtained. In this case, the 1st slide in the Google Slides is used. So templateSlide.remove(); is not required to be used.

Note:

• In your script, the values are retrieved from Google Spreadsheet using getValues(). In this case, 10 % is 0.1. If you want to use 10 %, please use getDisplayValues() instead of getValues(). In above modified script, getDisplayValues() is used.

References:

• replaceAllText
• setText(newText)
• getDisplayValues()

Added:

The reason of your current issue is due to the different row titles between Google Slides and Google Spreadsheet. There are 2 different titles in your sample Google Slides and Google Spreadsheet.

• Mashal Mashal and Mashal Mashal for Google Slides and Google Spreadsheet, respectively.
  • Mashal Mashal is 1 space between Mashal and Mashal.
  • Mashal Mashal is 2 spaces between Mashal and Mashal.
• Chelsea Great and Chelea Great for Google Slides and Google Spreadsheet, respectively.

Please use the same row titles for Google Slides and Google Spreadsheet. When the row title is different, I modified above script for not replacing the values. So could you please confirm it? When above modified script is used for your Google Slides and Google Spreadsheet, the rows of Mashal Mashal and Chelsea Great are not replaced.