Google Apps Script: Replace all text in slide table with values and formatting from sheets

Question

Here is the description of my problem.

I have a range of placeholder text and its associated values in a spreadsheet. The placeholder text also exists in a slide presentation which gets replaced using the replacealltext function. However, the colors in the spreadsheet for the values do not change. Please see the examples below.

Google Apps Script:

  // Google Sheets
  var masterSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = masterSheet.getSheetByName('Monthly Report'); //gets the data from the active pubfile
  var range = sheet.getRange('S1:T110');
  var data = range.getValues();
  var titleName = sheet.getRange('AA14:AA14').getValues().toString();
  data.shift(); //removes the headers in the sheets

  // Creating the new slide
  var spreadsheetID = SpreadsheetApp.getActiveSpreadsheet().getId(); //Ensure Code is applied to active Pubfile
  var spreadsheetFolder = DriveApp.getFileById(spreadsheetID);
  var parentFolder = spreadsheetFolder.getParents();
  var folderID = parentFolder.next().getId();
  var reportTemplate = DriveApp.getFileById('SLIDE ID'); // Gets the report Template
  var copiedTemplate = reportTemplate.makeCopy(titleName, DriveApp.getFolderById(folderID)); //Makes a copy of the orginal and saves it in the specified folder
  var newPresoID = copiedTemplate.getId();
  var skeleton = SlidesApp.openById(newPresoID); //opens the new template presentation 
  var slides = skeleton.getSlides(); //calls the new slides 
  return newSlide1();

 function newSlide1() {
    var slide1new = slides[0]; // defining the location of the new slide 1
    data.forEach(function (row) {
      var templateVariable = row[0]; // First column contains variable names
      var templateValue = row[1]; // Second column contains values
      slide1new.replaceAllText(templateVariable, templateValue); //replaces all the text that matches the placeholders
      slide1new.
    });

As you can see, this code just replaces the value but does not bring in the source formatting.

Here are snapshots of the original spreadsheet data and the slides:

Source Data

Destination Slide with Placeholders

Current Result

Desired Result

Please suggest a way to fix this issue. Thank you!

Answer 1

Here is an example of applying the colors of a spreadsheet cell to the table cells of a slide.

My spreadsheet looks like this.

My slide looks like this.

Code.gs

function myFunction() {
  try {
    let spread = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxxxxxxxxx");
    let sheet = spread.getSheetByName("Sheet1");
    let range = sheet.getRange(2,1,sheet.getLastRow()-1,2);
    let values = range.getDisplayValues();
    let colors = range.getFontColors();

    let present = SlidesApp.getActivePresentation();
    let slide = present.getSlides()[0];
    let table = slide.getTables()[0];
    for( let i=0; i<table.getNumRows(); i++ ) {
      let row = table.getRow(i);
      for( let j=1; j<row.getNumCells(); j++ ) {
        let text = row.getCell(j).getText();
        values.some( (value,k) => {
            let l = text.replaceAllText(value[0],value[1]);
            if( l > 0 ) {
              text.getTextStyle().setForegroundColor(colors[k][1]);
              return true;
            }
            return false;
          }
        );
      }
    }
  }
  catch(err) {
    console.log(err);
  }
}

And the final results look like this.

Reference

• TableCell
• Array.some()