Find All Values in Google Sheets 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:

/**
 * 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")
}

/**

* 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.

// 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)
}

// 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:

[ { 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' } ]

[ { 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.

// 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)
}

// 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.

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

Example 1

// 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);
}

// 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

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);
}

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

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.

Find All Values in Google Sheets with Apps Script

Starter Sheet

The Code

Find All Values in All Sheets

Find All Values in Selected Sheets

Find All Values in a Selected Range

Example 1

Example 2

The Video

Like this:

Related

Leave a ReplyCancel reply

Starter Sheet

The Code

Find All Values in All Sheets

Find All Values in Selected Sheets

Find All Values in a Selected Range

Example 1

Example 2

The Video

Share this:

Like this:

Related

Leave a ReplyCancel reply