Find and Select or Format rows in Google Sheets with Apps Script

Find & Select or Format Rows in Google Sheets with Apps Script

In this tutorial, we create 3 Google Apps Script functions that are used to:

  1. Find and select the row containing the first or last value in a Google Sheets sheet tab.
  2. Find and select all rows containing the search value.
  3. Format all rows containing the search text.

You can grab a copy of the starter Google Sheet to play along here:

Find & Select or Format Rows – Tutorial

This tutorial accompanies the YouTube video of the same name:

https://youtu.be/Y7rnQT6qYiA

All functions are built to be run from another function, making it easy for you to integrate them into your own projects.

For our example, we will use the runies function for this purpose.

All three of the  examples below take two arguments as parameters:

  • text – the search text.
  • sheetName – the Name of the selected sheet tab.

These functions rely on the Apps Script Spreadsheet App Text finder class to search for the target text within the range.

All examples search a selected sheet tab. However, you can easily modify the script to search a specific range or the entire Google Sheets workbook. You can learn more about how to do this here:

Find All Values in Google Sheets with Apps Script

Find and activate the first or last row in a Google Sheet with Apps Script

Lines 10-12 – In this scenario, we activate the first (findNext()) or last (findPrevious())found row based on a search item that can be found anywhere in the row. We then store this cell range in the cell variable.

Line 17 –  From the cell variable, we can select the Spreadsheet App Range getRow() method to get the row number of the cell.

Line 19-21 – Next, we can activate the range of the selected row using the activate method.

We set the entire range by using A1-notation here within a template literal (that is a string within backticks ()). In Google Sheets, we can set the full width of a sheet by leaving the last column value blank in the notation, for example:

A1:1 or even 1:1.

Incidentally, you can also select a portion of the width by including an end letter in the range, for example:

A1:K1 or in the script .getRange(A${row}:K${row}`)

You can learn more about selecting the first or last found values in this tutorial:

Find first, last or nth value in a Google Sheets range with Apps Script

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Find and activate first row based on a search value in Google Sheets
Example finds the first instance of ‘Koala’ in the range and selects the row.

Find and activate all rows in a Google Sheet with Apps Script

In this example, we select all of the rows containing the target search value.

Line 13 – First, we modify the Text Finder variable to the findAll method and rename the variable. This will return an array of all the cells where the values are found as an array.

Lines 15-16 – Now, we can map over each item in the array creating a new array of ranges that contain A1-notation of the selected row.

Line 18 – Finally, we can use the Spreadsheet App Sheet Class getRangeList() method to collect all ranges in our array and then activate them.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px
Find and activate all rows based on a search value in Google Sheets
Finds and activates all rows in the range that contain ‘Koala’.
Hire me for our next Google Workspace project.

Find and format all rows in a Google Sheet with Apps Script

This last function is virtually the same as the previous one. However, we are doing something much more useful, we are programmatically updating the format of the selected rows.

Lines 18-21 – Using the getRangeList method, we can modify the formatting of any range in the list. In our example, we set the background of the row to a dark blue, changed the text colour to white and bolded the font.

Find and format all rows based on a search value in Google Sheets
Finds and formats all rows containing the search item ‘Koala’.

More formatting methods you can use.

Here is a list of common formatting methods that you can apply to a range list:

  1. Clear FormatclearFormat() – Clears the formatting of the selected range.
  2. Set background ColoursetBackgroundColor(colour) – Use CSS notation like hexadecimal colours, colour names or RGB colours
  3. Set font ColoursetFontColor(colour)– Use CSS notation like hexadecimal colours, colour names or RGB colours.
  4. Set font WeightsetFontWeight(type) – Either “bold” or “normal”.
  5. Set font Size= setFontSize(size) – The font size as a number.
  6. Set font StylesetFontStyle(style) – The style, e.g. “Helvetica”, “Poppins”.
  7. Set vertical alignmentsetVerticalAlignment(alignment) – “top”, “middle” or “bottom”.
  8. Set horizontal alignmentsetHorizontalAlignment(alignment) – “left”, “center”, “right”.
  9. Set text rotationsetTextRotation() – Sets the rotation of the text in each cell in degrees from the original horizontal plain.

In the next tutorial, we will cover how to hide and unhide rows based on found values in a range. The process is a little different, so well worth checking out.

Subscribe to get an email when the next tutorial in this series comes out or get regular updates on my latest tutorials.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~Yagi

Leave a Reply

%d bloggers like this: