Google-apps-script – Apply multiple font colors to the text in a single Google Sheets cell

google sheetsgoogle-apps-scripttext formatting

I am trying to format a cell to have multiple font colors using a function in Google Apps Script. I am unable to find any documentation on it. Also, using getFontColor() doesn't return anything useful.

Is there any way to programmatically reproduce this functionality

enter image description here

that is available to users via the Google Sheets web UI?

Best 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:

Related Topic