Google Sheets – How to Find and Replace Rich Text Within a Cell

google sheetsgoogle-apps-script

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.

Best Answer

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

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

Related Topic