How can I modify text color in Google Docs based on a range selected in Google Sheets?

Other Questions Google docs
1

I have a Google Sheets document containing various cells with different colors. I’ve created a script that successfully replaces a Google Docs template with the values from a specified range in Sheets. However, I am struggling to update the text color according to the original colors from the cells during this replacement. While I can extract the colors from the cells, I can’t find a method to apply them appropriately. For instance, the function setTextColor(color); only seems to apply black text color. Here’s the code I’ve developed so far:

const templateFile = DriveApp.getFileById('your_template_id');
const outputFolder = DriveApp.getFolderById('your_folder_id');
const selectedRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('C3:E26');
const valuesFromRange = selectedRange.getDisplayValues();
const styledText = selectedRange.getRichTextValues();
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
const currentDate = `${new Date().getDate()}/${new Date().getMonth()+1}/${new Date().getFullYear()}`;
const copiedDoc = templateFile.makeCopy(`Report ${currentDate}`, outputFolder);
const document = DocumentApp.openById(copiedDoc.getId());
const documentBody = document.getBody();

for (let row = 0; row < valuesFromRange.length; row++) {
    for (let col = 0; col < valuesFromRange[row].length; col++) {
        const textInCell = styledText[row][col];
        const textStyle = textInCell.getTextStyle();
        const cellColor = textStyle.getForegroundColor();
        
        documentBody.replaceText(`{{${row}-${col}}}`, valuesFromRange[row][col]).setTextColor(cellColor);
    }
}
document.saveAndClose();
const docLink = document.getUrl();
currentSheet.getRange('J1').setValue(docLink);

I would appreciate any guidance on how to achieve the desired outcome!

2

The issue you’re facing probably arises because the setTextColor method has been applied incorrectly in your loop. Instead of chaining it directly after replaceText, which may not maintain the formatting context, try breaking it apart. First, identify the replaced text within the document, then separately apply the text color. Here’s a refined approach:

After replacing the placeholder in your document, locate the range of text you just modified using findText on documentBody. Once identified, you can then apply the text color using setTextColor on this specific text range. This workflow ensures that you select the exact portion of text to style. I hope this clarifies and helps implement the color styling effectively.