I’m working on a Google Apps Script project where I need to change the font color of specific parts of text inside a single cell. The regular UI in Google Sheets lets you select portions of text and give them different colors, but I can’t figure out how to do this programmatically.
I’ve tried looking through the documentation but haven’t found anything helpful. When I use getFontColor() it doesn’t give me useful information about mixed colors in the same cell.
Does anyone know if there’s a way to recreate this multi-color text formatting using Google Apps Script? I want to be able to make some words red and others blue in the same cell, just like you can do manually in the web interface.
Any code examples or guidance would be really appreciated. Thanks!
richTextValue indices are a pain - i always screw them up initially. pro tip: console.log your text length b4 applying styles to avoid range errors. and don’t forget spaces count as characters (catches everyone at least once).
This isn’t supported through standard Range methods in Google Apps Script - that’s why you can’t find it in the docs. The problem is getFontColor() and similar methods only work at the cell level, not for individual text segments inside a cell.
I’ve run into this before. You need to use RichTextValue objects instead. The RichTextValueBuilder class lets you build text with different formatting for different segments. Use setText() with setTextStyle() to apply different colors to specific character ranges.
You’ll build the rich text piece by piece, setting start and end positions for each formatted segment. It’s more complex than simple font methods, but it’s the only way I’ve found to get mixed formatting within a single cell programmatically. Check the RichTextValue documentation for the specific syntax.
Yes, using RichTextValueBuilder can be challenging but it’s the right approach. The key is to create a builder, define your text segments, and then specify their styles before applying them to the cell. Here’s an example:
var builder = SpreadsheetApp.newRichTextValue();
var redStyle = SpreadsheetApp.newTextStyle().setForegroundColor('#FF0000').build();
var blueStyle = SpreadsheetApp.newTextStyle().setForegroundColor('#0000FF').build();
builder.setText('Red text and blue text');
builder.setTextStyle(0, 8, redStyle); // 'Red text'
builder.setTextStyle(13, 22, blueStyle); // 'blue text'
sheet.getRange('A1').setRichTextValue(builder.build());
The important part is using the character indices correctly when defining styles. I’ve personally found it useful for visually enhancing specific terms in my documents.