Script to colour specific characters in a sheet-CodePudding

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);   } };

CodePudding user response：

Set Font color for symbols in an entire spreadsheet

function myfunk(color="#000000") {
  const arr = ["♦","♥","♣","♠"];
  const ss = SpreadsheetApp.getActive();
  arr.forEach(s => ss.createTextFinder(s).matchEntireCell(false).findAll().forEach(rg => {
    let t = rg.getDisplayValue();
    let idx = t.indexOf(s)
    let c = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();
    let r = SpreadsheetApp.newRichTextValue().setText(t).setTextStyle(idx,idx   1,c).build();
    rg.setRichTextValue(r);
  })
  );
}