Script to change individual text color within a cell

Question

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this:

First Last
First Last
First Last

First Last
First Last

Many of the names are color coded red while the others remain black. I'm using a script to remove the extra newlines to collapse the names to this (many of them are removed using find/replace for things such as days off, resulting in the blank newline):

First Last
First Last
First Last
First Last
First Last

The problem is, is that when this script is ran the red names have their font changed to black. I can't figure out a way to write a for loop to turn these individual lines within the cell back to red. I either end up with all the text in the cell being red, or HTML code indicating it should be red.

Any ideas on how I could make this work?

Answer 1

At this time this it's there are only two kinds of range properties to handle its content: values and formulas. Unfortunately changing the cell content using them removes the format applied to parts of the content.

In case like this, users could take a look to the Google Apps Script Issue Tracker to search for a feature request and in case that there isn't one submit one. If a feature request already exists for the feature that you are looking for, star it as the number of people that starred a feature request is considered by the Google Apps Script team to prioritize them.

Please star the following feature request

Issue 6000: Handle parts of cell content programmatically

Answer 2

UPDATE: The methods mentioned on this post are now included on the January 22, 2019 release notes

Someone found undocumented methods and shared examples relevant for this question.

From comment 21 to Handle parts of cell content programmatically mentioned on my previous answer.

You can also get the current format of a cell where different parts of the cell's texts are in different styles:

  var richText = ss.getCurrentCell().getRichTextValue();
  var runs = richText.getRuns();
  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 startIndex = run.getStartIndex();
    var endIndex = run.getEndIndex();
    var styleBold = run.getTextStyle().isBold();
  }

Probably the new methods will be announced this week first on Google Cloud Next 18 San Francisco then on the Google Developers Blog.

Related

• Styling Text Within a Cell in Google Spreadsheets