[Google Sheets] Word Highlighter

Did you know that there are glow-in-the-dark mushrooms in the world? For example, in Japan, there is a mushroom called “Shii-No-Tomosibi-Dake” that provides a fantastic glow. However, there are still many unanswered questions about why these mushrooms glow at night. The natural world is so profound! 😳

By the way, we sometimes want to highlight text, for obvious reasons. In this time, I have created a handy highlighting tool using Google Sheets and Google Apps Script.
Before I introduce it, I’d like to say why I came up with it.

Reddit kills two birds with one stone

It was about six months ago that I started answering people who post questions on Reddit. My initial goal in doing so was to improve my English, which, as you know, is poor.😅 But as I continued to do so to some extent, I realized that there were other things that would benefit me. Those are:

1. Improving my skills in technical matters,
2. Finding out what people are interested in,
3. Increasing my own visibility, and
4. Being useful to someone else.

So I strongly recommend you answer questions in any community so that you can improve not just one but several skills.

The theme of this article also came from a question on Reddit. It was about checking spam words in an email message so that you can avoid using any words that get caught in spam filters. Until I saw that question, I had no idea that some people needed the ability to highlight certain words. I was curious about the technicalities and requirements of the problem, so I decided to create a simple tool for it.

Specification

The specification of this tool is very simple.

1. The user pastes a message into the certain cell (A2).
2. The tool checks if the message contains some words which listed in another dedicated sheet(Spam Word List).
3. The tool hilights(bold and color red) the found words in the message.

Yes, there are many spam word checkers in the world. For instance, Email Spam Trigger Words Checker Tool. However, most of them can’t change the spam word list because they are specialized in spam checkers. On the other hand, my tool can change the word list to anything you want so this tool can use for not only spam word checkers but any type of word checker.

To achieve to make the feature of this tool, I technically use the RichTextValueBuilder. Many people think that one cell can only use one style to decorate text but actually, it can use multiple styles.

Source Code

const SHEET_EMAIL = 'Email';
const SHEET_SPAM_WORD_LIST = 'Spam Word List';
const CELL_EMAIL = 'A2';

/**
 * Executes checkSpamWords() when the content of A2 changes.
 */
function onEdit(e) {
  const range = e.range;
  if (range.getA1Notation().toUpperCase() === CELL_EMAIL.toUpperCase() ){
    checkSpamWords();
  }
}
/**
 * Checks and highlights the contents of the email for spam words.
 */
function checkSpamWords() {
  let ui = SpreadsheetApp.getUi();

  // set sheet objects
  let emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_EMAIL);
  if ( emailSheet == null ){
    ui.alert('Sheet not found: ' + SHEET_EMAIL);
    return;
  }
  let spamListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_SPAM_WORD_LIST);
  if ( spamListSheet == null ){
    ui.alert('Sheet not found: ' + SHEET_SPAM_WORD_LIST);
    return;
  }

  // spamlist
  let spamList = spamListSheet.getDataRange().getValues();

  let emailRange = emailSheet.getRange(CELL_EMAIL);
  if ( emailRange == null ){
    ui.alert('Range not found: ' + CELL_EMAIL);
    return;
  }

  // build a style
  var style = SpreadsheetApp.newTextStyle()
    .setBold(true)
    .setForegroundColor('red')
    .build();
  
  // do the check
  doCheck(emailRange, spamList, style);
    
}
/**
 * Does the check for all spam words and highlights them.
 */
function doCheck(emailRange, spamlist, style) {
  let emailText = emailRange.getValue().toString();
  let richTextValue = SpreadsheetApp.newRichTextValue().setText(emailText);

  // loop the spam list
  for( spamWord of spamlist ){
    console.log('checking: ' + spamWord);
    checkOneWord(emailText, spamWord.toString(), richTextValue, style);  
  }

  // set rich text
  emailRange.setRichTextValue(richTextValue.build());
}
/**
 * Does the check for one spam word.
 */
function checkOneWord(emailText, search, richTextValue, style) {
  let start = emailText.indexOf(search);
  while( start >= 0 ){    
    console.log('found:' + start + '-' + (start + search.length));
    richTextValue.setTextStyle(start, start + search.length, style);
    start = emailText.indexOf(search, start + search.length);
  }
}

How to get this tool

You can get this tool by clicking this link. (This is the answer to the question on Reddit)

Conclusion

If you combine Google Sheets and Google Apps Script, there are infinite possibilities.

I would like to continue to improve my skills by checking out communities such as Reddit and Stack Overflow. 👍