Search code examples
google-sheetsgoogle-apps-scriptplaceholdergoogle-slides

Error while importing data from Google sheet to Google Slide using Google Apps Script

I am trying to import values from Google Sheets to specific placeholders in Google Slide. Here is the template of the slide. The issue is that it is not replacing some values in placeholders in this slide. Here is the code snippet:

function createOffer() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var slides = SlidesApp.openByUrl("https://docs.google.com/presentation/d/1bf-3ypg-lGE9_ju_vCUHeQZj0eWiuFRzxIV_9Ap4eaw/edit#slide=id.g2cece5ff8dd_0_76");
  replacePlaceholdersInSlide(slides, sheet);
}

function replacePlaceholdersInSlide(slides, sheet) {
  var slide = slides.getSlides()[0]; // First slide
  var shapes = slide.getShapes();
  var placeholderValuePairs = {
    "{{C10}}": "C10","{{C19}}": "C19","{{C20}}": "C20","{{C23}}": "C23","{{D19}}": "D19",
    "{{C40}}": "C40"
  };
  replacePlaceholdersInShapes(shapes, placeholderValuePairs, sheet);
}


function replacePlaceholdersInShapes(shapes, placeholderValuePairs, sheet) {

  shapes.forEach(function(shape) {
    if (shape.getText) { 
      var text = shape.getText(); 
      for (var placeholder in placeholderValuePairs) {
        var newValue = sheet.getRange(placeholderValuePairs[placeholder]).getDisplayValue();
        text.replaceAllText(placeholder, newValue);
      }
    }
  });
}

However, I am unable to find an issue as to why it does not replace the following values: "{{C19}}","{{C20}}","{{C23}}" in the slide. Any guidance to resolve this issue is much appreciated.

Solution

  • Modification points:

    • About it does not replace the following values: "{{C19}}","{{C20}}","{{C23}}" in the slide, when I saw your provided Google Slides, I noticed that those are included in the group. I think that this is the reason for your current issue.
    • In your script, getDisplayValue() is used in a loop. In this case, the process cost becomes high.

    When these points are reflected in your script, how about the following modification? In this case, I remembered my answer. So, in this modification, this answer is used in your script.

    Modifieid script:

    In this case, in order to retrieve values from multiple cells by one API call, Sheets API is used. So, please enable Sheets API at Advanced Google services.

    function createOffer() {
  var sheetName = "Sheet1";
  var placeholderValuePairs = { "{{C10}}": "C10", "{{C19}}": "C19", "{{C20}}": "C20", "{{C23}}": "C23", "{{D19}}": "D19", "{{C40}}": "C40" };
  var presentationUrl = "https://docs.google.com/presentation/d/1bf-3ypg-lGE9_ju_vCUHeQZj0eWiuFRzxIV_9Ap4eaw/edit#slide=id.g2cece5ff8dd_0_76";

  // Create an array object for replacing the texts.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var { keys, ranges } = Object.entries(placeholderValuePairs).reduce((o, [k, v]) => {
    o.keys.push(k);
    o.ranges.push(`'${sheetName}'!${v}`);
    return o;
  }, { keys: [], ranges: [] });
  var values = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges, valueRenderOption: "FORMATTED_VALUE" }).valueRanges;
  var obj = keys.map((k, i) => [k, values[i].values[0][0]]);

  // Retirve 1st slide.
  var slides = SlidesApp.openByUrl(presentationUrl);
  var slide = slides.getSlides()[0]; // First slide

  // Replace texts in the shapes.
  const shapes = slide.getShapes();
  if (shapes.length > 0) {
    shapes.forEach(shape => {
      const text = shape.getText();
      obj.forEach(e => text.replaceAllText(...e));
    });
  }

  // Replace texts in the groups.
  const processGroups = g => {
    g.getChildren().forEach(c => {
      const type = c.getPageElementType();
      if (type == SlidesApp.PageElementType.SHAPE) {
        const text = c.asShape().getText();
        obj.forEach(e => text.replaceAllText(...e));
      } else if (type == SlidesApp.PageElementType.GROUP) {
        processGroups(c.asGroup());
      }
    });
  }
  slide.getGroups().forEach(processGroups);
}
    • When I tested this script using your provided Google Slide and a sample Spreadsheet, I confirmed that all placeholders could be replaced.

    Reference: