Script to Colour Characters in a spreadsheet

Question

How can I automate a find & replace function to change the font colour of all suit symbols sheet wide?

I write bridge articles (card game), and I need to automate this formatting constantly:

Find all "♣" and color the text green (#00b700). Find all "♦" and color the text orange (#ff8100). Find all "♥" and color the text red (#ff0000). Find all "♠" and color the text blue (#0000ff).

Another person asked the same question for google docs, and produced a script.

I have tried to adapt it to google sheets, but am getting

"TypeError: body.editAsText is not a function" errors

here is the script i am using:

function onOpen() { SpreadsheetApp.getUi() .createMenu('Utilities') .addItem('Auto-Replace', 'replaceSuits') .addToUi(); }; function replaceSuits() { var body = SpreadsheetApp.getActiveSpreadsheet(); var text = body.editAsText();

var found = body.findText("♥");   while (found) {
var elem = found.getElement();
if (found.isPartial()) {
  var start = found.getStartOffset();
  var end = found.getEndOffsetInclusive();
  elem.setForegroundColor(start, end, "#ff0000");
}
else {
  elem.setForegroundColor("#ff0000");
}
found = body.findText("♥", found);   }
found = body.findText("♦");   while (found) {
var elem = found.getElement();
if (found.isPartial()) {
  var start = found.getStartOffset();
  var end = found.getEndOffsetInclusive();
  elem.setForegroundColor(start, end, "#ff8100");
}
else {
  elem.setForegroundColor("##ff8100");
}
found = body.findText("♦", found);   }
found = body.findText("♣");   while (found) {
var elem = found.getElement();
if (found.isPartial()) {
  var start = found.getStartOffset();
  var end = found.getEndOffsetInclusive();
  elem.setForegroundColor(start, end, "#00b700");
}
else {
  elem.setForegroundColor("#00b700");
}
found = body.findText("♣", found);   }
found = body.findText("♠");   while (found) {
var elem = found.getElement();
if (found.isPartial()) {
  var start = found.getStartOffset();
  var end = found.getEndOffsetInclusive();
  elem.setForegroundColor(start, end, "#0000ff");
}
else {
  elem.setForegroundColor("#0000ff");
}
found = body.findText("♠", found);   } };

Answer 1

The code you quote uses classes that are only relevant to the Google Docs text editor. To do the same in Google Sheets, use the RichTextValueBuilder class. The script below will format text automatically as you edit the spreadsheet.

/**
* Simple trigger that runs each time the user opens the
* spreadsheet.
*
* Adds a menu item to highlight a phrase.
*
* @param {Object} e The onOpen() event object.
* @OnlyCurrentDoc
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Suits')
    .addItem('Highlight suits in selected cells', 'formatPhrasesInText')
    .addToUi();
  if (!e) {
    console.log(`New "Suits" menu was added in the spreadsheet's menu bar.`);
  }
}


/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  formatPhrasesInText(e.range);
}


/**
* Applies rich text formats to currently selected cells.
*/
function formatPhrasesInText(range = SpreadsheetApp.getActiveRange()) {
  const specs = [
    { regex: /♥/gi, textColor: 'red', bold: false, },
    { regex: /♦/gi, textColor: 'orange', bold: false, },
    { regex: /♣/gi, textColor: 'green', bold: false, },
    { regex: /♠/gi, textColor: 'blue', bold: false, },
  ];
  formatText(range, specs);
}


/**
* Applies rich text formats to text in a range.
* Formats substrings that match a regex, each substring separately.
* Handles multiple occurrences of each phrase in each cell.
* Overwrites formulas and numbers with the text shown in the cell.
* Mutates specs.
*
* @param {Range} range The range to format.
* @param {Object[]} specs An array of objects { {RegExp} regex, {String} textColor, {Boolean} bold }
* @return {Range} The same range, for chaining.
*/
function formatText(range, specs) {
  // version 1.4, written by --Hyde, 21 October 2022
  //  - see https://webapps.stackexchange.com/a/167300/269219
  specs.forEach(spec => spec.format = SpreadsheetApp.newTextStyle()
    .setForegroundColor(spec.textColor)
    .setBold(spec.bold)
    .build()
  );
  const values = range.getDisplayValues();
  let match;
  const formattedText = values.map(row => row.map(value => {
    const richText = SpreadsheetApp.newRichTextValue().setText(value);
    specs.forEach(spec => {
      while (match = spec.regex.exec(value)) {
        richText.setTextStyle(match.index, match.index + match[0].length, spec.format);
      }
    });
    return richText.build();
  }));
  return range.setRichTextValues(formattedText);
}