How can I change specific text color in Google Spreadsheets?

Question

First of all, Sorry for my English. English is not my first language. I simply want to change specific text color in Google Spreadsheets.

Example

"▶Mike: Hey, How is going on?"

In this sentence, I want to change only "▶Mike:" this part. I have no idea what's going on in Java and programming language. But I really need this. please help. Thank you

Answer 1

If you want to change the color of a cell in Google Sheets, you can use one of the two options:

1. Use Sheets API

For updating the color of the text, you will have to use the spreadsheets.batchUpdate method.

Request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate

Body

{
   "requests": [{
      "repeatCell": {
         "cell": {
            "userEnteredFormat": {
               "textFormat": {
                  "bold": true,
                  "italic": true,
                  "foregroundColor": {
                     "blue": 1.0,
                     "green": 0.0,
                     "red": 0.0
                  }
               }
            }
         },
         "range": {
            "sheetId": 0,
            "startRowIndex": 0,
            "endRowIndex": 1
         },
         "fields": "userEnteredFormat(textFormat)"
      }
   }]
}

If you want to use Java, you might benefit from taking a look at this snippet from here and adapt it according to your task.

However, this option does not offer you the possibility of changing only a part of the text from the cell. For this, you should try using Apps Script.

2. Use Apps Script

Since you want to change a particular number of characters from the cell, you can use the below script

function changeColor() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var range = sheet.getRange("A1:A2");
   var redColor = SpreadsheetApp.newTextStyle()
      .setForegroundColor("#ff0000")
      .build();
   var purpleColor = SpreadsheetApp.newTextStyle()
      .setForegroundColor("#871f78")
      .build();
   var richTextA1 = SpreadsheetApp.newRichTextValue()
      .setText("▶Mike: Hey, How is going on?")
      .setTextStyle(0, 6, redColor)
      .setTextStyle(7, 28, purpleColor)
      .build();
   var richTextA2 = SpreadsheetApp.newRichTextValue()
      .setText("red words, purple words")
      .setTextStyle(0, 10, redColor)
      .setTextStyle(11, 23, purpleColor)
      .build();
   range.setRichTextValues([
      [richTextA1],
      [richTextA2]
   ]);
}

The above script creates two text styles and later applies them depending on the startOffset and the endOffset parameters which essentially represent which characters will have one of the two text styles.

After executing the above script, this is how the cells will look like:

Reference

• Sheets API V4 Method: spreadsheets.batchUpdate;

• Apps Script Range Class - setRichTextValues(values).