Setting different text colors within one cell using Google Apps Script

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need help with text formatting.

I want to make different parts of text in the same cell show up in different colors using Google Apps Script code. When I try to use methods like getFontColor(), it doesn’t give me the results I’m looking for.

In the regular Google Sheets interface, you can select part of the text in a cell and change just that portion to a different color. I’m wondering if there’s a way to do this same thing through code instead of manually.

Here’s a simple example of what I’m trying to achieve:

function colorizeText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetCell = sheet.getRange('A1');
  
  // Want to make 'Hello' red and 'World' blue in same cell
  targetCell.setValue('Hello World');
  // Need code here to set different colors for different parts
}

Has anyone figured out how to programmatically set multiple font colors within a single cell? I can’t seem to find clear documentation on this specific feature.

2

Performance tanks when you’re hitting hundreds of cells with RichTextValue. API calls pile up fast and you’ll hit timeouts. I found batching helps tons - grab all your RichTextValue objects first, then push them to ranges in chunks instead of going cell by cell. Also, getRichTextValue() keeps existing formatting when you just need to tweak part of already-formatted text. Pull the current RichTextValue, grab its runs, change what you need, and rebuild. Saves you from accidentally nuking formatting on other parts of the cell. Way cleaner for incremental updates than rebuilding everything each time.

3

RichTextValue works, but I got sick of writing all that boilerplate code every time I needed colored text.

I ended up automating the whole thing. Built a workflow that takes text with simple markup tags and automatically applies colors to Google Sheets cells. Something like “Hello World” gets processed and pushed to cells with proper formatting.

Now I can batch process hundreds of cells at once, pull data from other sources, and set up triggers to update colors when data changes. Way more powerful than manually coding each RichTextValue.

The automation handles all the index calculations and TextStyle building. I focus on content instead of wrestling with start/end positions for every text segment.

If you’re doing this regularly or formatting lots of cells, automation beats writing individual scripts every time.

4

The RichTextValue approach works, but I learned the hard way that hardcoding indices is a nightmare with dynamic content. Text lengths change and everything breaks. What saved me was calculating indices programmatically instead of guessing. If you’re coloring specific words, use indexOf() to find their positions: javascript var text = 'Hello World'; var helloStart = text.indexOf('Hello'); var helloEnd = helloStart + 'Hello'.length; var worldStart = text.indexOf('World'); var worldEnd = worldStart + 'World'.length; This is crucial when you’re pulling data from other cells or databases where content changes. Also, setTextStyle doesn’t overwrite previous styles on the same RichTextValue object - it layers them. Super useful for applying multiple formatting rules to overlapping ranges.

5

Use the RichTextValue class for this. Instead of setting plain text, you build rich text. Here’s how I do it:

function colorizeText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetCell = sheet.getRange('A1');
  
  var richText = SpreadsheetApp.newRichTextValue()
    .setText('Hello World')
    .setTextStyle(0, 5, SpreadsheetApp.newTextStyle().setForegroundColor('#FF0000').build())
    .setTextStyle(6, 11, SpreadsheetApp.newTextStyle().setForegroundColor('#0000FF').build())
    .build();
  
  targetCell.setRichTextValue(richText);
}

setTextStyle takes start index, end index, and a TextStyle object. Indices are zero-based and end index is exclusive. I’ve used this method in tons of projects where I needed mixed formatting in cells - works every time.

6

yeah, richtext is def the way to go, but unicode characters can really mess with your indices. learned this the hard way with emojis and special chrs - they throw off start/end positions. test with weird text first or you’ll end up with colors all over the place.