stumbled upon yet another cool problem statement on twitter 😬
based on that 👆🏽 i nicknamed this activity as
Project BoldX — basic, i know 😅
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,
make a copy of the original sheet + script using the link here.
let's knock-off the ui parts of the solution first as they're (fairly) minimal.
- a simple form
- takes 2 input: one is for range and the other a list of comma separated keywords
- submitting them triggers a
to display the sidebar on the spreadsheet -
we'll talk about the code that handles the incoming data from the html form in the backend portion of the solution.
let's split this into 3 segments -
mainfunction that receives incoming data from the sidebar and also directs the flow of the automation
checkWordfunction that returns a
falsedepending on whether or not the cells from the range contains the keywords we're looking for
noteworthy points -
- i'm using the
somemethod 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
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.
you can access the entire script on my github repository here.