Format(Color) Specific Text in a Sheet using G-App Script: A Step-by-Step Guide

Are you tired of manually formatting specific text in your Google Sheets? Do you wish there was a way to automate this process using Google Apps Script? Well, you’re in luck! In this article, we’ll take you through a step-by-step guide on how to format specific text in a sheet using G-App Script.

What You’ll Need

• A Google Sheet with data you want to format
• Basic knowledge of Google Apps Script
• A willingness to learn and automate!

Understanding the Problem

Let’s say you have a sheet with a list of names, and you want to highlight specific names that contain a certain keyword, such as “John” or ” Doe”. Manually formatting each cell can be tedious and time-consuming, especially if you have a large dataset.

That’s where Google Apps Script comes in. With a few lines of code, you can automate the formatting process and make your life easier.

The Solution

The script will use regular expressions to search for specific text patterns in your sheet and apply the desired formatting. We’ll use the `onEdit` trigger to run the script whenever the sheet is updated.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  
  var regex = /John|Doe/gi; // modify this to match your keyword(s)
  var color = "green"; // modify this to your desired color
  
  formatCells(range, regex, color);
}

function formatCells(range, regex, color) {
  var cells = range.getValues();
  var numRows = cells.length;
  var numCols = cells[0].length;
  
  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      var cellValue = cells[i][j];
      
      if (regex.test(cellValue)) {
        range.getCell(i+1, j+1).setFontColor(color);
      }
    }
  }
}

How the Script Works

The script uses two functions: `onEdit` and `formatCells`. The `onEdit` function is triggered whenever the sheet is updated, and it calls the `formatCells` function with the updated range and the desired formatting.

The `formatCells` function loops through each cell in the range and checks if the cell value matches the regular expression using the `test` method. If it does, it sets the font color of the cell to the specified color.

Customizing the Script

To customize the script, you can modify the regular expression and the font color to match your specific needs.

Parameter	Description	Example
regex	The regular expression pattern to match	/John|Doe/gi
color	The desired font color	"green"

Regular Expressions 101

If you're new to regular expressions, here's a quick primer:

• `/pattern/` - defines the regular expression pattern
• `|` - matches either the pattern before or after the `|` character
• `gi` - flags to make the search case-insensitive (`i`) and global (`g`)

For example, the regular expression `/John|Doe/gi` matches any cell value that contains the words "John" or "Doe", regardless of case.

Tips and Variations

Here are some tips and variations to take your script to the next level:

1. Use multiple regular expressions: You can modify the script to use multiple regular expressions by adding more parameters to the `formatCells` function.

2. Format entire rows or columns: Instead of formatting individual cells, you can modify the script to format entire rows or columns by changing the range and loop logic.

3. Use conditional formatting: Instead of using a script, you can use Google Sheets' built-in conditional formatting feature to achieve similar results.

4. Create a custom menu: You can create a custom menu in your Google Sheet to trigger the script on demand instead of relying on the `onEdit` trigger.

Conclusion

And there you have it! With this script, you can automate the formatting of specific text in your Google Sheet using Google Apps Script. Remember to customize the script to match your specific needs and explore the variations to take your automation to the next level.

Happy scripting!

Further Reading

• Google Apps Script Documentation
• Regexr: A Regular Expression Tester
• Google Sheets Conditional Formatting

Frequently Asked Question

Get ready to unleash the power of Google Apps Script and format specific text in your sheet like a pro!