Find All Values in Google Sheets with Apps Script

Google Sheets Find All with Apps Script

Just like when using the Ctrl + F shortcut in Google Sheets to find values in your spreadsheet, there is a class in Google Apps Script that can do the same thing.

This could be a useful tool as a part of an automation process. For example, finding the location of a value and applying formatting to it or copying the cell’s entire data into a separate location if the value is a part of a larger text in the cell.

This tutorial accompanies the YouTube video tutorial of the same name.

Grab a copy of the starter sheet to play along and get the most out of the video.

Starter Sheet

Find All Apps Script – Starter Sheet

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

The Code

Note that for the video tutorials, I have added the variables (e.g. the Sheet Name, Range) inside each function. It is usually good practice to keep these functions independent and call them from other functions. This makes them more reusable.

Instead, we can add the variables we need as parameters for the functions and return the result. See the example below:

These examples use the TextFinder class as a part of the Google Apps Script Sheets App Class.

In these examples, we use the findAll method of this class. This will return an array containing all the cells containing the selected value searched. From here, you can treat each cell as a range and call range methods like:

  • Get A1 Notation.
  • Get Sheet – Get Name.
  • Get Row.

We use the JavaScript Map method to iterate through each item that we find.

Hire a Google Workspace Developer for your Business Needs

Find All Values in All Sheets

This finds all values in all sheets and returns an array containing an object for each sheet containing the sheet name and the cell location.

Returns:

 

Find All Values in Selected Sheets

Finds all the values in a selected sheet and returns an array identifying the cell that each item is found.

Returns:

[ 'A3', 'A4', 'F14', 'A16' ]

Find All Values in a Selected Range

Finds the values for any item in a selected range and returns the row the item was found on.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Example 1

Returns:

[ 3, 4, 16 ]

Example  2

Returns:

[ 1, 2, 4, 5 ]

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

The Video

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Leave a Reply