Find and 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 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.

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.

More formatting methods you can use.

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

1. Clear Format – clearFormat() – Clears the formatting of the selected range.
2. Set background Colour – setBackgroundColor(colour) – Use CSS notation like hexadecimal colours, colour names or RGB colours
3. Set font Colour – setFontColor(colour)– Use CSS notation like hexadecimal colours, colour names or RGB colours.
4. Set font Weight– setFontWeight(type) – Either “bold” or “normal”.
5. Set font Size= setFontSize(size) – The font size as a number.
6. Set font Style – setFontStyle(style) – The style, e.g. “Helvetica”, “Poppins”.
7. Set vertical alignment–setVerticalAlignment(alignment) – “top”, “middle” or “bottom”.
8. Set horizontal alignment – setHorizontalAlignment(alignment) – “left”, “center”, “right”.
9. Set text rotation – setTextRotation() – 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