I would like to know if it is possible in the current sheets api, or the google script for sheets, to find and replace rich text.

In my specific example, I have red text that I would like to remove in another cell.

The purpose of this is to create many results with different outputs. My example would be scaled to possibly hundreds of cells. and it would be nice to set different styles in a source cell to output different results in the ~100 cells.

removing red text

I have looked in the text element api references, and it seems to only apply to documents, and not the sheets cells. and my attempts to script using those methods have failed.

  • Welcome. Please checkout developers.google.com/apps-script/reference/spreadsheet/… Commented Nov 2, 2019 at 16:08
  • Thank you, but part of the problem is, I can't get this to work because I don't understand how to use these functions. I'm getting errors when trying to get the rich text types from the cell value, does it need to be a different object type?. I want to make a function that can be called like a normal function from a cell, like "=removeRed(A1)".
    – Mike
    Commented Nov 5, 2019 at 3:05
  • I suggest you to learn the Google Sheets lingo: a function that can be called like a normal function from a cell, like "=removeRed(A1)" is a "custom function". To learn about custom functions please read developers.google.com/apps-script/guides/sheets/functions. Commented Nov 5, 2019 at 3:24
  • So this is something I know already. What I don't understand is why [code]function myFunction(A1) { var x = A1.getRuns(); return x } doesn't work. it is saying that getRuns isnt a function I can use. why is that? is it because the value of A1 isn't a string? I can't seem to get the rich text functions to work on cell values.
    – Mike
    Commented Nov 7, 2019 at 1:52
  • Welcome. The answer to your question is yes, it can be replaced but the notion of search/replace, as we understand it, doesn't apply. "What I don't understand is why..." - this is a simple issue about syntax. getRuns() is pretty new (almost 12 months) and there is precious little documentation and examples available. This external post Script to change the color of individual text in a cell, particularly the code included in the "UPDATE" at the bottom of the page, is handy.
    – Tedinoz
    Commented Nov 14, 2019 at 12:53

2 Answers 2


The following isn't an answer to your question per se, but it is an exploration of the syntax surrounding TextStyle and RichTextValue. It may assist in understanding why var x = A1.getRuns(); doesn't work, and how you might build code to analyse cells. Note the programmatic creation of RichText is another thing altogether.

Spreadsheet Screenshot


function wa13593205() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);

  var range = sheet.getRange("G3");
  var ar = sheet.setActiveRange(range);
  //var ar = ss.getActiveRange();

  var gr = ar.getRichTextValue().getRuns();
  //Logger.log(gr); //DEBUG

  var richText = ss.getCurrentCell().getRichTextValue(); 

  var thistext =  richText.getText(); 
  Logger.log("DEBUG: Cell content = "+thistext);
  var thisstyle = richText.getTextStyle();
  //Logger.log("DEBUG: this style = "+thisstyle)

  var runs = richText.getRuns(); 
  //Logger.log("runs length="+runs.length)
  for (var i=0; i<runs.length; i++) { 

    // get the style of this section of text, between startIndex and endIndex 
    var run = runs[i]; 
    var runtext = run.getText();
    var color = run.getTextStyle().getForegroundColor();
    var font = run.getTextStyle().getFontFamily();
    var fontsize = run.getTextStyle().getFontSize();
    var startIndex = run.getStartIndex(); 
    var endIndex = run.getEndIndex(); 
    var styleBold = run.getTextStyle().isBold(); 
    var styleItalic = run.getTextStyle().isItalic();
    var stylesThru = run.getTextStyle().isStrikethrough();
    var styleUline = run.getTextStyle().isUnderline();
    Logger.log("i="+i+", Start Idx:"+startIndex+", End Idx:"+endIndex+", Text:"+runtext+", Bold?: "+styleBold+", Color:"+color+", Font:"+font+", Size:"+fontsize+", Italic?:: "+styleItalic+", Strikethru?:"+stylesThru+", Underline?:"+styleUline);


Logger - Results

DEBUG: Cell content = black red blue black
i=0, Start Idx:0, End Idx:5, Text:black, Bold?: false, Color:#000000, Font:Calibri, Size:14, Italic?:: true, Strikethru?:false, Underline?:false
i=1, Start Idx:5, End Idx:6, Text: , Bold?: true, Color:#000000, Font:Open Sans, Size:10, Italic?:: false, Strikethru?:false, Underline?:false
i=2, Start Idx:6, End Idx:9, Text:red, Bold?: true, Color:#ff0000, Font:Comic Sans MS, Size:18, Italic?:: false, Strikethru?:false, Underline?:false
i=3, Start Idx:9, End Idx:10, Text: , Bold?: true, Color:#000000, Font:Open Sans, Size:10, Italic?:: false, Strikethru?:false, Underline?:false
i=4, Start Idx:10, End Idx:14, Text:blue, Bold?: false, Color:#4a86e8, Font:Roboto, Size:14, Italic?:: false, Strikethru?:true, Underline?:false
i=5, Start Idx:14, End Idx:15, Text: , Bold?: true, Color:#000000, Font:Open Sans, Size:10, Italic?:: false, Strikethru?:false, Underline?:false
i=6, Start Idx:15, End Idx:20, Text:black, Bold?: false, Color:#b6d7a8, Font:Trebuchet MS, Size:18, Italic?:: true, Strikethru?:false, Underline?:false


FWIW, I've just solved an analogous problem. I wanted to find certain words in a cell & bold & color them for emphasis. You may consider my "constructive" approach, which is the corollary of your "destructive" approach & that should let you recycle a lot of this. Otherwise, I hope it helps you make sense of rich text formatting.

To test this as is, you'll need a sheet "Copy of master" & cell A2 populated w/ a string like "word term1 word2 term2 term3". "term1" & "term2" will be given different kinds of emphasis & stand apart from "word", "word2", & "term3."

This code generalizes & can handle repetition & case sensitivity too.

function getIndicesOf(searchStr, str, caseSensitive) {
    if (searchStr.length == 0) {
        return [];
    var startIndex = 0, index, indices = [];
    if (!caseSensitive) {
        str = str.toLowerCase();
        searchStr = searchStr.toLowerCase();
    while ((index = str.indexOf(searchStr, startIndex)) > -1) {
        startIndex = index + searchStr.length;
    return indices;

function emphasizeTerms(term1, term2, range) {

  var bold = SpreadsheetApp.newTextStyle()

  var magenta = SpreadsheetApp.newTextStyle()

  var red = SpreadsheetApp.newTextStyle()

  var value = range.getCell(1, 1).getValue();
  var richText = SpreadsheetApp.newRichTextValue();

  var tags = [term1, term2]
  var styles = [red, magenta]
  for (i = 0; i < tags.length; i++) {
    tag = tags[i]
    style = styles[i]
    var indices = getIndicesOf(tag, value, false)
    for (j = 0; j < indices.length; j++) {
      tag_l_bound = indices[j];
      tag_r_bound = tag_l_bound + tag.length;
      richText.setTextStyle(tag_l_bound, tag_r_bound, style);
      richText.setTextStyle(tag_l_bound, tag_r_bound, bold);

function main(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Copy of master');
  var range = sheet.setActiveSelection('A2');
  emphasizeTerms('term1', 'term2', range)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.