Highlighting Cells with Specific Keywords in Google Sheets Using Google Apps Script

Google Apps Script is a powerful tool that enables you to automate and enhance your Google Workspace applications, including Google Sheets. In this blog post, we will walk through an exercise to write a script that finds and highlights all cells in a Google Sheet that contain a specific keyword. Additionally, we will explore the various methods available in Apps Script for manipulating and formatting data in Google Sheets.

Prerequisites

Before we start, ensure you have:

• A Google account
• Basic knowledge of JavaScript
• A Google Sheet ready to use for this exercise

Step 1: Writing the Script

1. Open the Apps Script Editor:
   • Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Create a New Script:
   • Replace any code in the script editor with the following code to find and highlight cells containing a specific keyword:

function highlightKeywordCells() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const keyword = ‘yourKeyword’; // Replace with the keyword you want to search for

  const range = sheet.getDataRange();

  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {

    for (let j = 0; j < values[i].length; j++) {

      if (typeof values[i][j] === ‘string’ && values[i][j].includes(keyword)) {

        sheet.getRange(i + 1, j + 1).setBackground(‘yellow’);

      }

    }

  }

}

Step 2: Running the Script

1. Save and Run the Script:
   • Save your script by clicking the floppy disk icon or pressing Ctrl+S (Windows) or Cmd+S (Mac).
   • Click on the Run button (the play icon) to execute the highlightKeywordCells function.
2. Authorize the Script:
   • The first time you run the script, you will need to authorize it. Click on Review Permissions and follow the steps to grant the necessary permissions.
3. Check Your Google Sheet:
   • After running the script, go back to your Google Sheet. All cells containing the specified keyword should be highlighted in yellow.

Step 3: Customizing the Script

1. Dynamic Keyword Input:
   • Modify the script to accept a dynamic keyword input through a prompt.

function highlightKeywordCells() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const keyword = Browser.inputBox(‘Enter the keyword to search for:’);

  const range = sheet.getDataRange();

  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {

    for (let j = 0; j < values[i].length; j++) {

      if (typeof values[i][j] === ‘string’ && values[i][j].includes(keyword)) {

        sheet.getRange(i + 1, j + 1).setBackground(‘yellow’);

      }

    }

  }

}

2. Customizing Highlight Color:
   • Change the highlight color by modifying the setBackground method.

sheet.getRange(i + 1, j + 1).setBackground(‘lightblue’);

Methods for Manipulating and Formatting Data in Google Sheets Using Apps Script

1. Data Manipulation:
   • getValues(): Retrieves the values in the specified range.
   • setValues(values): Sets the values in the specified range.
   • getRange(row, column, numRows, numColumns): Gets a range of cells.
   • clear(): Clears the content of the specified range.
2. Formatting:
   • setBackground(color): Sets the background color of the specified range.
   • setFontColor(color): Sets the font color of the specified range.
   • setFontWeight(weight): Sets the font weight (e.g., ‘bold’) of the specified range.
   • setNumberFormat(format): Sets the number format (e.g., currency, date) of the specified range.
3. Conditional Formatting:
   • Apps Script does not directly support conditional formatting rules, but you can programmatically apply styles based on conditions as demonstrated in this exercise.
4. Formulas and Functions:
   • setFormula(formula): Sets a formula in the specified cell.
   • getFormula(): Retrieves the formula in the specified cell.
5. Data Validation:
   • setDataValidation(rule): Sets data validation rules for the specified range.

Conclusion

With this exercise, you’ve learned how to use Google Apps Script to find and highlight cells containing a specific keyword in Google Sheets. Additionally, we’ve explored various methods available in Apps Script for manipulating and formatting data. By leveraging these capabilities, you can automate and enhance your Google Sheets workflows, making data management more efficient and effective.