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