Sheets

Format specific word inside a Google Sheet cell using Apps Script

Format (Bold) a single word or a group of words automatically in Sheets, using Google Apps Script.

Sourabh Choraria

Jan 13, 2021 • 3 min read

Format specific word inside a Google Sheet cell using Apps Script

Stumbled upon yet another cool problem statement on twitter 😬

Anyone know if it's possible to format text in G Sheets?

I.e: Any mention of "X" get's bolded?
— Dan Brooks 🤓🔍 (@seodanbrooks) January 11, 2021

Based on that 👆🏽 I nicknamed this activity as Project BoldX — basic, i know 😅

Solution

The task at hand relies heavily on the rich text value and text style classes available in Google Apps Script to handle the backend (i.e. the Code.gs) activities and to make it more user-friendly, we'll also be working with a sidebar, google.script.run — the asynchronous client-side javascript api in our sidebar's HTML to communicate with the Apps Script server.

Demo

Make a copy of the original sheet + script using the link here.

format (bold) specific word(s) inside a cell using google apps script

Frontend

Let's knock-off the UI parts of the solution first as they're (fairly) minimal.

<!DOCTYPE html>
<html>

<head>
	<base target="_top">
	<script>
	function runBoldX() {
			var range = document.getElementById("range").value;
			var words = document.getElementById("words").value;
		google.script.run.main(range, words);
	}
	</script>
</head>

<body>
	<form id="BoldX" onsubmit="runBoldX()">
		<label for="range">Range: </label>
		<input type="text" id="range" name="range">
		<br />
		<br />
		<label for="words">Words: </label>
		<input type="text" id="words" name="words">
		<br />
		<br />
		<input type="submit" value="Bold 'em"> </form>
</body>

</html>

Index.html

a simple form
takes 2 input: one is for range and the other a list of comma separated keywords
submitting them triggers a google.script.run function

To display the sidebar on the spreadsheet -

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('BoldX Menu')
    .addItem('Show sidebar', 'showSidebar')
    .addToUi();
}

function showSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Index').setTitle('BoldX Sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

Code.gs

We'll talk about the code that handles the incoming data from the HTML form in the backend portion of the solution.

Backend

Let's split this into 3 segments -

the main function that receives incoming data from the sidebar and also directs the flow of the automation

function main(range, csvWords) {
  range = ss.getRange(range);
  const rIndex = range.getRow();
  const cIndex = range.getColumn();
  const values = range.getValues();
  const rawWords = csvWords.split(",");
  const words = [];
  rawWords.forEach(word => {
    words.push(word.trim());
  });


  for (let rowIndex = 0; rowIndex < values.length; rowIndex++) {
    const row = values[rowIndex];
    for (let colIndex = 0; colIndex < row.length; colIndex++) {
      const value = row[colIndex];
      if (value != '' && value != null && value != undefined) {
        if (checkWords(value, words)) {
          setBoldFormat(value, words, (rowIndex + rIndex), (colIndex + cIndex));
        }
      }
    }
  }
}

Code.gs

the checkWord function that returns a true or false depending on whether or not the cells from the range contains the keywords we're looking for

function checkWords(value, words) {
  const wordArray = value.match(/\b(\S+)\b/g);
  const hasWord = words.some((value) => wordArray.indexOf(value) !== -1);
  return hasWord;
}

Code.gs

Noteworthy points -

I'm using the some method to look for at least one of the words to show-up in the cell from the list of words that were entered
a pretty cool regex (matching) pattern that creates a word boundary around each word in a sentence (regardless of commas, spaces, apostrophes etc.)

and finally, the more complex function to handle the format change in each cell — in this case, i've named it the setBoldFormat function

function setBoldFormat(value, words, rowIndex, colIndex) {
  const range = ss.getRange(rowIndex, colIndex);
  const boldX = SpreadsheetApp.newTextStyle().setBold(true).build();
  for (let wordIndex in words) {
    let word = words[wordIndex];
    const richTextValue = range.getRichTextValue().copy();
    const startIndex = value.indexOf(word);
    if (startIndex > 0) {
      const endIndex = startIndex + word.length;
      const formattedOutput = richTextValue.setTextStyle(startIndex, endIndex, boldX).build();
      range.setRichTextValue(formattedOutput);
      SpreadsheetApp.flush();
    }
  }
}

Code.gs

Instead of building a new rich text value component, I'm copying the existing one because it may so happen that there could be more than one word in a cell that we may want to highlight 🤷🏽‍♀️

I'm also flushing the spreadsheet as that allows us to "see" each transformation happen instead of getting to witness the final output directly — in this scenario, it's a vanity function call and so feel free to either comment or delete it, should it be impacting your execution run time.

Codebase

You can access the entire script on my GitHub repository here.