Google Sheet App Script 自动高亮单元格关键词

highlight.js

function getAllIndexes(string, texts) {
  const results = [];

  texts.forEach(text => {
    const regex = new RegExp(text, 'gi');
    let match;

    while ((match = regex.exec(string)) !== null) {
      const startIndex = match.index;
      const endIndex = match.index + text.length - 1;
      results.push({ text, startIndex, endIndex });
    }
  });

  return results;
}

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getRange("D2:D3137");
  var values = range.getRichTextValues();


  const color = '#FF0000'; // Red color
  const textStyle = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();

  const new_values = values.map(tmp => {
    var richText = tmp[0]
    var indexes = getAllIndexes(richText.getText(), ["window", "opening"]);
    const value = richText.getText();
    const builder = SpreadsheetApp.newRichTextValue().setText(value);
    indexes.forEach(index => {
      builder.setTextStyle(index["startIndex"], index["endIndex"] + 1, textStyle);
    })
    return [builder.build()]
  })
  range.setRichTextValues(new_values)
}