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
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/** * Activate all values in a range. * @param {string} text - the text to search for. * @param {string} sheetName - The name of the selected sheet. * @returns {array} Location cell of each selection. */ function findAllItemsInASheet(text, sheetName){ ... return locationList; }; function runsies(){ findAllItemsInASheet("koala", "Sheet1") } |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// Find value over all sheets. function findValueOverAllSheets(){ const text = "koala"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const textFinder = ss.createTextFinder(text) const allOccurrences = textFinder.findAll(); const locationList = allOccurrences.map(item => { return {sheet:item.getSheet().getName(), cell: item.getA1Notation()} }) console.log(locationList) } |
Returns:
|
1 2 3 4 5 6 7 8 |
[ { sheet: 'Sheet1', cell: 'A3' }, { sheet: 'Sheet1', cell: 'A4' }, { sheet: 'Sheet1', cell: 'F14' }, { sheet: 'Sheet1', cell: 'A16' }, { sheet: 'Sheet2', cell: 'B4' }, { sheet: 'Sheet3', cell: 'C6' }, { sheet: 'Sheet3', cell: 'F8' }, { sheet: 'Sheet3', cell: 'E15' } ] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// Find value over one sheet. function findValueOverOneSheet(){ const text = "koala"; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName) const textFinder = sheet.createTextFinder(text) const allOccurrences = textFinder.findAll(); const locationList = allOccurrences.map(item => item.getA1Notation()) console.log(locationList) } |
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.
Example 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
// Find Value over a range function findValueOverARange(){ const text = "koala"; const sheetName = "Sheet1"; const rangeLoc = "A2:A16"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const range = sheet.getRange(rangeLoc); const textFinder = range.createTextFinder(text); const allOccurrences = textFinder.findAll(); const locationList = allOccurrences.map(item => { return item.getRow(); }) console.log(locationList); } |
Returns:
[ 3, 4, 16 ]
Example 2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
function findValueOverARange_v2(){ const text = "cheese"; const sheetName = "Sheet4"; const rangeLoc = "A1:A"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const range = sheet.getRange(rangeLoc); const textFinder = range.createTextFinder(text); const allOccurrences = textFinder.findAll(); const locationList = allOccurrences.map(item => { return item.getRow(); }) console.log(locationList); } |
Returns:
[ 1, 2, 4, 5 ]
The Video
Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.
Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *
*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.
