Apply Multiple Font Colors To The Text In A Single Google Sheets Cell

Answer :

As on July 2018, Apps-Script support changing individual text colors and other font related styles. Two methods are added to SpreadsheetApp. newTextStyle() and newRichTextValue(). The following apps-script changes such fontstyles in A1. For best effects, Use a lengthy string(30 characters or more).

function rainbow(){   var rng = SpreadsheetApp.getActiveSheet().getRange("A1");   var val = rng.getValue().toString();   var len = val.length; // length of string in A1   var rich = SpreadsheetApp.newRichTextValue(); //new RichText   rich.setText(val); //Set Text value in A1 to RichText as base    for (var i=0;i<len;i++){ //Loop through each character     var style = SpreadsheetApp.newTextStyle(); // Create a new text style for each character     var red= ("0"+Math.round((1/len)*(i)*255).toString(16)).substr(-2,2); //     var green= ("0"+Math.round((1/len)*Math.min(i*2,len-Math.abs(i*2-len))*255).toString(16)).substr(-2,2); //     var blue= ("0"+Math.round((1/len)*(len-i)*255).toString(16)).substr(-2,2);//     style.setForegroundColor("#"+red+green+blue); // hexcode     style.setFontSize(Math.max(Math.abs(len/2-i),8)); //Use a lengthy string     var buildStyle = style.build();      rich.setTextStyle(i,i+1,buildStyle); // set this text style to the current character and save it to Rich text        }   var format = rich.build()   rng.setRichTextValue(format); //Set the final RichTextValue to A1 } 

---

_{Documentation is not published yet. Methods are subject to change}

References:

• RichTextValue
• TextStyle

The Sheets API is a bit daunting to start using, but allows very fine-grained control over your spreadsheets. You'll have to enable it, as it is an "Advanced Service". I strongly recommend reviewing the Sample Codelab.

With the Sheets API, the TextFormatRun property can be manipulated on a cell-by-cell basis. Note:

Runs of rich text applied to subsections of the cell. Runs are only valid on user entered strings, not formulas, bools, or numbers. Runs start at specific indexes in the text and continue until the next run. Properties of a run will continue unless explicitly changed in a subsequent run (and properties of the first run will continue the properties of the cell unless explicitly changed).

When writing, the new runs will overwrite any prior runs. When writing a new userEnteredValue, previous runs will be erased.

This example uses it to adjust the green value of text, increasing from 0 to 100% over the length of a string in the active cell. Adjust to suit your needs.

function textFormatter() {   // Get the current cell's text.   var wb = SpreadsheetApp.getActive(), sheet = wb.getActiveSheet();   var cell = sheet.getActiveCell(), value = cell.getValue();   var len = value.toString().length;   if(len == 0) return;    // Change the color every 2 characters.   var newCellData = Sheets.newCellData();   newCellData.textFormatRuns = [];   var step = 1 / len;   for(var c = 0; c < len; c += 2) {     var newFmt = Sheets.newTextFormatRun();     newFmt.startIndex = c;     newFmt.format = Sheets.newTextFormat();     newFmt.format.foregroundColor = Sheets.newColor();     newFmt.format.foregroundColor.green = (c + 2) * step;     newCellData.textFormatRuns.push(newFmt);   }    // Create the request object.   var batchUpdateRQ = Sheets.newBatchUpdateSpreadsheetRequest();   batchUpdateRQ.requests = [];   batchUpdateRQ.requests.push(     {        "updateCells": {         "rows": [ { "values": newCellData } ],         "fields": "textFormatRuns",         "start": {           "sheetId": sheet.getSheetId(),           "rowIndex": cell.getRow() - 1,           "columnIndex": cell.getColumn() - 1         }       }     }   );   Sheets.Spreadsheets.batchUpdate(batchUpdateRQ, wb.getId()); } 

---

Edit: Depending on how the value of the cells to be formatted are set, including the value of the cell in the same request may be necessary as well. See this example on the issue tracker