google-sheetsgoogle-apps-scriptgoogle-slides

To make a copy of Google Slides template and update the values in the slides as per data sheet in Google Sheets

Am trying to create a script that, when the custom menu is clicked, would make a copy of a Google Slides template and update the fields in the copied template based on the values in the Google Sheets.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Auto Data Output')
    .addItem('generate PCRPPT','generatePCRPPT')
    .addToUi();
}

function generatePCRPPT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var update = ss.getSheetByName("update");
  var new_deck_id = make_slide_copy();

  var success = update_new_deck(new_deck_id);

  if (success==1)
  {
    var value = "=HYPERLINK(\"https://docs.google.com/presentation/d/"+new_deck_id+"/edit\";\"Click to open\")";
    update.getRange("B3").setFormula(value);
    }
}

function make_slide_copy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var prototype_deck_id = ss.getSheetByName("update").getRange(2,2).getValue();
  var presentation_prototype= SlidesApp.openById(prototype_deck_id);
  var templateSlides = presentation_prototype.getSlides();
  var currentDate = new Date();
  var newDeck = SlidesApp.create("New PCR PPT"+ currentDate);
  var defaultSlides = newDeck.getSlides();

  defaultSlides.forEach(function(slide) {
    slide.remove();
  });

  var index = 0;
  templateSlides.forEach(function(slide) {
    var newSlide = newDeck.insertSlide(index);
    var elements = slide.getPageElements();
    elements.forEach(function(element) {
      newSlide.insertPageElement(element);
    });
    index++;
  });

  var new_deck_id = newDeck.getId();
  return new_deck_id;
}

function update_new_deck(new_deck_id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var slide_new_deck = SlidesApp.openById(new_deck_id);

    var data_sheet = ss.getSheetByName("update");
    
    var colWidth = data_sheet.getLastColumn();
    var selectedHeigth = data_sheet.getLastRow()
    var selectedFullRange = data_sheet.getRange(1,1,selectedHeigth,colWidth);
    var source_data = selectedFullRange.getValues();

    for(n=1;n<source_data.length;++n){
       slide_new_deck.replaceAllText('{{metric_}}',data_sheet.getRange(n+5,3).getValue());
       slide_new_deck.replaceAllText('{{campaign_name}}',data_sheet.getRange(2,1).getValue());
      }
}
Solution

  • Google Sheets: Set Values for Generated Google Slides

    Kindly update your update_new_deck(new_deck_id) function from:

    function update_new_deck(new_deck_id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var slide_new_deck = SlidesApp.openById(new_deck_id);

  var data_sheet = ss.getSheetByName("update");

  var colWidth = data_sheet.getLastColumn();
  var selectedHeigth = data_sheet.getLastRow()
  var selectedFullRange = data_sheet.getRange(1, 1, selectedHeigth, colWidth);
  var source_data = selectedFullRange.getValues();

  for (n = 1; n < source_data.length; ++n) {
    slide_new_deck.replaceAllText('{{metric_}}', data_sheet.getRange(n + 5, 3).getValue());
    slide_new_deck.replaceAllText('{{campaign_name}}', data_sheet.getRange(2, 1).getValue());
  }
}

    To:

    function update_new_deck(new_deck_id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var slide_new_deck = SlidesApp.openById(new_deck_id);

  var data_sheet = ss.getSheetByName("update");

  var colWidth = data_sheet.getLastColumn();
  var selectedHeigth = data_sheet.getLastRow()
  var selectedFullRange = data_sheet.getRange(1, 1, selectedHeigth, colWidth);
  var source_data = selectedFullRange.getValues();

  for (n = 1; n < source_data.length; ++n) {
    slide_new_deck.replaceAllText('{{campaign_name}}', data_sheet.getRange(1, 2).getValue());
    slide_new_deck.replaceAllText('{{metric_1}}', data_sheet.getRange(n + 5, 3).getValue());
    slide_new_deck.replaceAllText('{{metric_2}}', data_sheet.getRange(n + 6, 3).getValue());
    slide_new_deck.replaceAllText('{{metric_3}}', data_sheet.getRange(n + 7, 3).getValue());
  }
}

    I see that you're already able to change {{campaign_name}}. However, instead of getting #example like in your spreadsheet, it's getting PPT template ID, so I changed the range value from getRange(2, 1) to getRange(1, 2). I also changed {{metric_}} to {{metric_1}} and added {{metric_2}} together with {{metric_3}} with their ranges, respectively.

    Also, update your generatePCRPPT() function from:

    function generatePCRPPT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var update = ss.getSheetByName("update");
  var new_deck_id = make_slide_copy();

  var success = update_new_deck(new_deck_id);

  if (success == 1) {
    var value = "=HYPERLINK(\"https://docs.google.com/presentation/d/" + new_deck_id + "/edit\";\"Click to open\")";
    update.getRange("B3").setFormula(value);
  }
}

    To:

    function generatePCRPPT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var update = ss.getSheetByName("update");
  var new_deck_id = make_slide_copy();
  update_new_deck(new_deck_id);
  update.getRange("B3").setValue("https://docs.google.com/presentation/d/" + new_deck_id + "/edit");
}

    Instead of setFormula(), you can use setValue() to set the generated ID from make_slide_copy() into a link.

    OUTPUT

    OUTPUT

    REFERENCES