1

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

1 Answer 1

2

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.

/** @OnlyCurrentDoc */
'use strict';

/**
* 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.
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Highlight')
    .addItem('Highlight text in selected cells', 'formatPhrasesInText')
    .addToUi();
  if (!e) console.log(`New "Highlight" 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('The onEdit(e) function is not designed to run in the script editor window.');
  formatPhrasesInText(e.range);
}


/**
* Applies rich text formats to currently selected cells.
* Text that matches regex is formatted using the methods listed.
* For a regex overview, see:
* https://www.regular-expressions.info/quickstart.html
* For available formatting methods and their parameters, see:
* https://developers.google.com/apps-script/reference/spreadsheet/text-style-builder
*/
function formatPhrasesInText(range = SpreadsheetApp.getActiveRange()) {
  const specs = [
    { regex: /♥/gi, format: { setForegroundColor: 'red', } },
    { regex: /♦/gi, format: { setForegroundColor: 'orange', } },
    { regex: /♣/gi, format: { setForegroundColor: 'green', } },
    { regex: /♠/gi, format: { setForegroundColor: 'blue', } },
    { regex: /interest/gi, format: { setBold: true, } },
    { regex: /de facto/gi, format: { setItalic: true, } },
    { regex: /^.*(stands? out).*$/gim, format: { setFontSize: 16, setForegroundColor: 'blue', setBold: true, setItalic: true, } },
  ];
  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.
* Skips formula cells. Mutates specs. Retains existing formats.
*
* @param {SpreadsheetApp.Range} range The range to format.
* @param {Object[]} specs An array of objects { {RegExp} regex, [{SpreadsheetApp.TextStyleBuilder['methodName']: parameter}] }
* @return {SpreadsheetApp.Range} The same range, for chaining.
*/
function formatText_(range, specs) {
  // version 1.7, written by --Hyde, 24 June 2024
  //  - see https://webapps.stackexchange.com/a/167300/269219
  const richTextValues = range.getRichTextValues();
  const values = range.getValues();
  const formulas = range.getFormulas();
  if (formulas.flat().join().match(/(arrayformula|filter|index|sort|sortn|query|unique|transpose|split)(\s*\()/i)) return;
  let numModified = 0;
  const result = richTextValues.map((row, r) => row.map((value, c) => {
    if (!value || typeof values[r][c] !== 'string' || formulas[r][c]) return;
    const text = value.getText();
    const richText = value.copy();
    let match;
    specs.forEach(spec => {
      while (match = spec.regex.exec(text)) {
        if (!spec.textStyle) {
          const textStyle = SpreadsheetApp.newTextStyle();
          for (const [method, parameter] of Object.entries(spec.format)) textStyle[method](parameter);
          spec.textStyle = textStyle.build();
          numModified += 1;
        }
        richText.setTextStyle(match.index, match.index + match[0].length, spec.textStyle);
      }
    });
    return richText.build();
  }));
  if (numModified) range.setRichTextValues(result);
  return range;
}

The first four entries in specs format the suite characters ♥♦♣♠ in various colors, the fifth one shows how to format the word "interest" in bold , and the sixth one shows how to format the phrase "de facto" in italic.

The last example shows how to format entire lines that contain the phrase "stand out" or "stands out" in bold italic in large blue text, working over multiple of lines of newline-separated text in each cell.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.