Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-docs

How to change the text color in google-docs based on an range selected from google-sheets

I have a google sheets with various cells containing diferent colors, i was able to make a script that replaces a google-docs template with the values from the sheets range, but im not being able to also change the color with it's original when i replace the values. I was able to capture the colors from the cells but i can't find a way to apply it.

the setForegroundColor(color); changes the text color to black only...

The code i have till now:

  const template = DriveApp.getFileById('sadfasfadjkg');
  const destination = DriveApp.getFolderById('sadfasfadjkg');
  const range = SpreadsheetApp.getActiveSheet().getRange('C3:E26');
  const rangeValues = range.getDisplayValues();
  const richTextValues = range.getRichTextValues();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const today = `${new Date().getDate()}/${new Date().getMonth()+1}/${new Date().getFullYear()}`;
  const copyDoc = template.makeCopy(`Report ${today}`, destination);
  const doc = DocumentApp.openById(copyDoc.getId());
  const body = doc.getBody();

  for (let x = 0; x < rangeValues.length; x++) {
    for (let y = 0; y < rangeValues[x].length; y++) {
      const cellTxt = richTextValues[x][y];
      const style = cellTxt.getTextStyle();
      const color = style.getForegroundColor();
      
      body.replaceText(`{{${x}-${y}}}`, rangeValues[x][y]).setForegroundColor(color);
    }
  }
  doc.saveAndClose();
  const url = doc.getUrl();
  sheet.getRange('J1').setValue(url);
}
Solution

  • I guessed your expected result is as follows.

    • You want to replace the placeholders in Google Documents with the cell values of Spreadsheet, and also, you want to copy the font color of the cell value.

    Although, unfortunately, I'm not sure whether I could correctly understand but im not being able to also change the color with it's original when i replace the values., in your script, how about the following modification?

    From:

    const richTextValues = range.getRichTextValues();

    To:

    const fontColor = range.getFontColorObjects();

    And, please modify as follows.

    From:

    const cellTxt = richTextValues[x][y];
const style = cellTxt.getTextStyle();
const color = style.getForegroundColor();

body.replaceText(`{{${x}-${y}}}`, rangeValues[x][y]).setForegroundColor(color);

    To:

    const forgroundColor = fontColor[x][y].asRgbColor().asHexString().slice(0, 7);
const search = `{{${x}-${y}}}`;
let s = body.findText(search);
while (s) {
  const e = s.getElement();
  const start = s.getStartOffset();
  const text = e.asText().replaceText(search, rangeValues[x][y]);
  text.asText().setForegroundColor(start, start + rangeValues[x][y].length, forgroundColor);
  s = e.findText(search, s);
}
    • When this modified script is run, each placeholder is replaced with the cell value, and the font color of the cell value is also reflected.

    References: