Apps Script: createTextFinder - findAll(): instead changing the format of the whole cell, how the change the format of the found text only

chelder 0
Viewed 39

The following function colours the whole cell when textToFind is found (within the range C5:C23):

function findAndSetBackgroundColor(textToFind, format){


  //Find cells that contain "ddd"
  //let ranges = SpreadsheetApp.getActive()
  let ranges = SpreadsheetApp.getActive().getRange('C5:C23')
    .createTextFinder(textToFind)
    .matchEntireCell(false)
    .matchCase(false)
    .matchFormulaText(false)
    .ignoreDiacritics(true)
    .findAll();

  //Set the background colors of those cells to yellow.
  ranges.forEach(function (range) {
      range.setTextStyle(format);
    });
}

Imagine we have on the range C5:C23:

A header Another header
First thing row
Second thing row

As matchEntireCell is set to false, if textToFind is Second, then the whole cell Second thing will be formatted according with the param format

But I'd like to format the word found only, not the whole cell.

How to do it?

google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • When the text of textToFind is included in the cells "C5:C23", you want to set the text style to only the text of textToFind.

In this case, how about the following modification? I thought that in this case, when getRichTextValues(), searching the texts, and setRichTextValues() are used instead of TextFinder, the script and process might be simple.

Modified script:

function findAndSetBackgroundColor(textToFind, format) {
  const range = SpreadsheetApp.getActive().getRange('C5:C23');
  const v = range.getRichTextValues().map(r =>
    r.map(c => {
      const idx = c.getText().indexOf(textToFind);
      return idx != -1 ? c.copy().setTextStyle(idx, idx + textToFind.length, format).build() : c;
    })
  );
  range.setRichTextValues(v);
}
  • In this modified script, for example, when textToFind = "sample" and format = SpreadsheetApp.newTextStyle().setForegroundColor("red").build() are used, the font color of sample of "C5:C23" is changed to red color.

References:

Tanaike0
Related