-1

I have a script in Google Sheets that automatically finds and formats specific text substrings within a cell that I edit.

A side effect of using the script is that it will reapply the existing cell format, to everything other than the substrings it styles.

For example, if I apply my own custom format to any substring in a cell, the format will be overwritten by the script with the format for the cell itself.

Is there a way that I can use the script to automatically format specified substrings when entered and clear that formatting when removed, but not lose any additional formatting I may have applied within the same cell?

Link to my Google spreadsheet

Current Script

Source:  Script to colour characters in a spreadsheet, by @doubleunary

/** @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('Padang Team')
    .addItem('Highlight crew involved in selected cells', 'formatPhrasesInText')
    .addToUi();
  if (!e) console.log(`New "Padang Team" 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: /\bDO\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bKB\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bDC\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bAL\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bMG\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bYY\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bDS\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bNS\b/gi, textColor: 'red', bold: true, underline: true },
    { regex: /\bHL\b/gi, textColor: 'red', bold: true, underline: 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.
*
* @param {Range} range The range to format.
* @param {Object[]} specs An array of objects { {RegExp} regex, {String} textColor, {Boolean} bold, {Boolean} underline }
* @return {Range} The same range, for chaining.
*/
function formatText(range, specs) {
  const values = range.getDisplayValues();
  const formulas = range.getFormulas();
  let match;
  const formattedText = values.map((row, rowIndex) => row.map((value, columnIndex) => {
    if (formulas[rowIndex][columnIndex]) return value;
    const richText = SpreadsheetApp.newRichTextValue().setText(value);
    specs.forEach(spec => {
      while (match = spec.regex.exec(value)) {
        spec.format = spec.format || SpreadsheetApp.newTextStyle()
          .setForegroundColor(spec.textColor)
          .setBold(spec.bold)
          .setUnderline(spec.underline)
          .build();
        richText.setTextStyle(match.index, match.index + match[0].length, spec.format);
      }
    });
    return richText.build();
  }));
  range.setRichTextValues(formattedText);
}
6
  • Welcome to Web Applications SE. It's possible. How are you exactly applying the manual formatting to the specific keywords not styled by the script? Please include enough details so others can reproduce the behavior you are seeing. Include how you prevent the onEdit function from running when the manual formatting is applied. P.S. If you are looking for help with JavaScript, consider asking questions about that on Stack Overflow, as this site focuses on using Web Applications, not programming. I'm not saying that this question is off-topic. Feb 8 at 15:49
  • To be crystal clear, questions on Web Applications SE should focus on using web apps like the Google Sheets and Google Apps Script "web browser" editors. Frequently, questions related to the use of these apps include stuff that is on-topic on other sites, like Stack Overflow, Mathematics, Cross Validated, etc. If you need help understanding programming and programming artifacts like triggers and JavaScript functions, the best place to ask questions about them is Stack Overflow. Feb 8 at 15:57
  • 2
    The code quoted in the question is from Script to Colour Characters in a spreadsheet with author info removed and no attribution. Please edit the question and give appropriate credit as required by the license. Feb 8 at 22:12
  • 1
    Is it possible? Probably Yes. If you want one or the other -per cell, but not both simultaneously, you can simply exit the loop with values for [getRichTextValues()] like you're doing with formulas. But, if you want both simultaneously, it's going to be complicated, but would still be possible..
    – TheMaster
    Feb 9 at 12:52
  • 1
    I edited your question for clarity. Please review my edit to ensure I properly understood what you meant.
    – Blindspots
    Feb 9 at 19:33

3 Answers 3

1

Here is a sample script that will change only the relevant values that match the strings with word boudaries.

There is a lot of functionality in the original code from @doubleunary that is missing in this that you will probably want to include.

Minimal Example

Supports editing a single cell.

function onEdit(e) {
  const range = e.range;
  const value = e.value
 
  if (!range.getFormula()){
    const strings = ["DO", "KB", "DC", "AL", "MG", "YY", "DS", "NS", "HL"];
    const style = SpreadsheetApp.newTextStyle()
      .setBold(true) 
      .setUnderline(true)
      .setForegroundColor('red')
      .build();
    const regex = new RegExp("\\b(" + strings.join("|") + ")\\b", "gi");
    const matches = [...value.matchAll(regex)].map(a => a.index);
    
    if (matches) {
      var newRich = range.getRichTextValue().copy().build();
      matches.forEach(s => newRich = newRich.copy().setTextStyle(s, s+2, style).build());
      range.clear().setRichTextValue(newRich);
    }
  }
}
0

You can try getRichTextValues from the range

const values = range.getRichTextValues();

and use it as base for a builder for further modifications:

const richText = value.copy()
1
  • See tag info page for official documentation, free resources and more details.
    – TheMaster
    Feb 11 at 3:12
0

Use formatText_ version 1.6 with these parameters:

  const specs = [
    { regex: /\b(DO|KB|DC|AL|MG|YY|DS|NS|HL)\b/gi, format: { setForegroundColor: 'green', setBold: true, setUnderline: true, } },
  ];

The new version will retain existing rich text formats and apply new formats to matching phrases incrementally.

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.