Is there a function to preserve the formatting when using apps script to input data from Google Sheets to Google Slides?

Question

I am using Google Apps script to input data from Google Sheets to Google Slides. I am want the value and the formatting shown in Google Sheets to transfer over to Google Slides. For example: Placeholder {{A4}} shows -20 in red. The value -20 is transferring over but I can't get the script to preserve the red color. {{A7}} shows +40 in green but it will not show in green in google slides. I have tried multiple scripts (loaded in the Variable google sheet file). One example is the script below. Is there any way to make this happen? The two files I have been using are also attached.

• Variable Google Sheet file
• Google Slides file

function updateTemplate() {
  const presentationID = "1tDFPYHd-U1mp5h5tC0VRkXciSS4wfCKA6FS9TmlseDg";
  const presentation = SlidesApp.openById("1tDFPYHd-U1mp5h5tC0VRkXciSS4wfCKA6FS9TmlseDg");
  const values = SpreadsheetApp.getActive().getDataRange().getValues();
  const slides = presentation.getSlides();

  let placeholderMap = {};

  slides.forEach((slide, slideIndex) => {
    const shapes = slide.getShapes();
    shapes.forEach((shape, shapeIndex) => {
      if (shape.getShapeType() === SlidesApp.ShapeType.TEXT_BOX && shape.getText) {
        const text = shape.getText().asString();
        values.forEach(([placeholder, value]) => {
          if (text.includes(placeholder)) {
            if (!placeholderMap[placeholder]) {
              placeholderMap[placeholder] = [];
            }
            placeholderMap[placeholder].push({ slideIndex, shapeIndex, originalText: text });
          }
        });
      }
    });
  });

  // Replace the placeholders
  values.forEach(([placeholder, value]) => {
    presentation.replaceAllText(placeholder, value.toString());
  });

  // Store the placeholder map as JSON in Script Properties
  PropertiesService.getScriptProperties().setProperty("placeholderMap", JSON.stringify(placeholderMap));
  Logger.log("Template updated and placeholder map saved.");
}

Answer 1

In your situation, I thought that this script might be able to be used. When this is used by modifying it to your situation, it becomes as follows.

Sample script:

Please copy and paste the following script into the script editor of the Spreadsheet and save the script.

Also, please enable Sheets API at Advanced Google services.

When I saw your sample spreadsheet, I noticed that the theme color is used for the font color. In this case, in order to retrieve the color code, I used the Sheets API. So, please enable it.

Then, please set your presentation ID to presentationId in the function main.

/**
 * ### Description
 * Replace all texts in all slides in a Google Slide by giving the text styles.
 *
 * @param {SlidesApp.Presentation} presentation Class Presentation object
 * @param {Object} replaceObj Object for replacing texts.
 * @return {void}
 */
function findReplaceAllText_(presentation, replaceObj) {
  const replaceWithStyle_ = text => {
    replaceObj.forEach(({ replaceText, containsText, style }) => {
      const f = text.find(replaceText);
      if (f.length > 0) {
        f.forEach(ff => {
          const text = ff.setText(containsText);
          if (style) {
            Object.entries(style).forEach(([k, v]) =>
              text.getTextStyle()[`set${k.replace(/^./, ([a]) => a.toUpperCase())}`](...v)
            );
          }
        });
      }
    });
  }
  const forTable = table => {
    for (let r = 0; r < table.getNumRows(); r++) {
      for (let c = 0; c < table.getNumColumns(); c++) {
        replaceWithStyle_(table.getCell(r, c).getText());
      }
    }
  }
  const forGroup = g => {
    g.getChildren().forEach(c => {
      const type = c.getPageElementType();
      if (type == SlidesApp.PageElementType.SHAPE) {
        replaceWithLink_(c.asShape().getText());
      } else if (type == SlidesApp.PageElementType.TABLE) {
        forTable(p.asTable());
      } else if (type == SlidesApp.PageElementType.GROUP) {
        forGroup(c.asGroup());
      }
    });
  }
  presentation.getSlides().forEach(slide => {
    slide.getPageElements().forEach(p => {
      const type = p.getPageElementType();
      if (type == SlidesApp.PageElementType.SHAPE) {
        replaceWithStyle_(p.asShape().getText());
      } else if (type == SlidesApp.PageElementType.TABLE) {
        forTable(p.asTable());
      } else if (type == SlidesApp.PageElementType.GROUP) {
        slide.getGroups().forEach(forGroup);
      }
    });
  });
}

// Please run this function.
function main() {
  const presentationId = "###"; // Please set your presentation ID.

  const sheetName = "Variables";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const styles = Sheets.Spreadsheets.get(ss.getId(), { ranges: [`'${sheetName}'!B2:B`], includeGridData: true }).sheets[0].data[0].rowData.map(({ values }) => {
    if (values && values[0].userEnteredFormat && values[0].userEnteredFormat.textFormat && values[0].userEnteredFormat.textFormat.foregroundColor) {
      const temp = {};
      const o = values[0].userEnteredFormat.textFormat;
      const { red, green, blue } = o.foregroundColor;
      if (red || green || blue) {
        temp.foregroundColor = [red ? red * 255 : 0, green ? green * 255 : 0, blue ? blue * 255 : 0];
      }
      temp.bold = [o.bold];
      return temp;
    } else if (values && values[0].effectiveFormat && values[0].effectiveFormat.textFormat && values[0].effectiveFormat.textFormat.foregroundColorStyle) {
      const temp = {};
      const o = values[0].effectiveFormat.textFormat;
      const { red, green, blue } = o.foregroundColorStyle.rgbColor;
      if (red || green || blue) {
        temp.foregroundColor = [red ? red * 255 : 0, green ? green * 255 : 0, blue ? blue * 255 : 0];
      }
      temp.bold = [o.bold];
      return temp;
    }
    return null;
  });
  const sheet = ss.getSheetByName(sheetName);
  const v = sheet.getRange("A2:B" + sheet.getLastRow()).getDisplayValues()
  const replaceObj = v.map(([a, b], i) => {
    const temp = { replaceText: a.trim().replace(/([(){}])/g, "\\$1"), containsText: b.trim() };
    if (styles[i]) {
      temp.style = styles[i];
    }
    return temp;
  });
  const presentation = SlidesApp.openById(presentationId);
  findReplaceAllText_(presentation, replaceObj);
}

Testing:

When this script is used on your sample Spreadsheet and Slide, the following result is obtained.

Note:

• This script was tested using your provided Spreadsheet and Slide. When you change it, this script might not be able to be used. Please be careful about this.

• Updated: I noticed that your spreadsheet has the bold type with the font color. So, I updated the above script to include both bold type and font color. If you don't want to set the text to the bold type, please remove temp.bold = [o.bold] from the above script.