Function does not appear nor does it appear to work

I wish to alter the colour of a specific section of text within a cell range. I wish to use it as a function which I have named =CTEXT. I wish to make the function visible and working =CTEXT(range, text, colour)

function CTEXT(range, text, color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange(range);
  var richText = cell.getRichTextValue();
  var newText = richText.getText();
  var startIndex = newText.indexOf(text);
  if (startIndex !== -1) {
    var endIndex = startIndex + text.length;
    var textStyle = SpreadsheetApp.newTextStyle()
      .setForegroundColor(color)
      .build();
    
    var newRichText = richText.copy().setText(newText)
      .setTextStyle(startIndex, endIndex, textStyle)
      .build();
    cell.setRichTextValue(newRichText);

I have tried this, however, I am quite new to apps script and do not know how this works to its full extent.

Solution

Regarding your initial question, from your expected result I wish to make the function visible and working =CTEXT(range, text, colour), if you want to use your function as a custom function, I think that unfortunately, setRichTextValue cannot be used as a custom function because of the current specification at the Google side. I said about this in a comment.

From your following reply,

In this case I would like it to look for key words within the sheet and change the colour of those key words to what I specify as built into the code.

In this case, your goal can be achieved. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set the variables of sheetName, color, and keyWords. color can also use the hex code. keyWords can use only one word.

Please save the script. And, run the function myFunction with the script editor.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const color = "red"; // Please set the font color.
  const keyWords = ["abc", "def", "ghi"]; // Please set the keywords.

  const textStyle = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();
  const srcRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange();
  const values = srcRange.getValues();
  const strValues = srcRange.getDisplayValues();
  const formulas = srcRange.getFormulas();
  const rt = srcRange.getRichTextValues().map((r, i) => r.map((c, j) => {
    if (values[i][j] instanceof Date) {
      return c.copy().setText(strValues[i][j]).build();
    } else if (formulas[i][j] || !isNaN(values[i][j])) {
      return c.copy().setText(formulas[i][j] || values[i][j]).build();
    }
    const t = c.copy();
    keyWords.forEach(e => {
      c.getText().matchAll(new RegExp(e, "g"))
        .forEach(a => t.setTextStyle(a.index, a.index + a[0].length, textStyle));
    });
    return t.build();
  }));
  srcRange.setRichTextValues(rt);
}

Testing:

When this script is run, the following result is obtained. You can see that the font color of the keywords has been changed on the sheet.

Note:

This script cannot be used as a custom function. Please be careful about this.

Function does not appear nor does it appear to work

Sample script:

Testing:

Note:

References: