In this tutorial, we will explore how to find the first and last values in a Google Sheets Range using Google Apps Script. Then we will modify our code to find a value at a specific location. We will then find the position of the first set of values in our Google Sheets range. Lastly, we will create a reusable function to find the first or last set of values in a range.
This tutorial accompanies the YouTube tutorial of the same name:
The starter sheet for the video tutorial:
The code for each section of the tutorial can be found below under each section header along with any explanation, where needed.
In our previous tutorial, we covered how to find all values in a Google Sheet, a sheet table or a range. Check it out here:
Table of Contents
The Runsies Function
This is an example function that you can use to run your find functions below. All arguments for the functions below are placed in here along with the execution of the desired function.
In the example below we are running the first function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
function findFirst_FindAll(sheetName, text){ ... } function runsies(){ const sheetName = "Data"; const text = "19-Feb-2023"; findFirst_FindAll(sheetName, text) } |
Find the first value or text in a Google Sheets Range using Apps Scripts FindAll method
In this approach, we make use of our previous tutorial’s script and use the findAll method of the createTextFinder Class.
This method returns an array for each found item that can access the Google Apps Script range class methods.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Find the first position of the match. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * */ function findFirst_FindAll(sheetName, text){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const foundValue = sheet.createTextFinder(text) .findAll(); const firstVal = foundValue[0]; console.log(firstVal.getA1Notation()) firstVal.activate(); }; |
Line 15 – Once all items in the range are found, we can select the zeroeth item. Incidentally, we could get the last item in the range with this approach by modifying this line to:
const lastVal = foundValue[foundValue.length - 1];
Of course, you would need to update the variable names too.
Line 17 – Here we log the cell position of the first found item using the SpreadsheetApp Range Class getA1Notation method.
Line 19 – Finally to make it easier to test. We set our selected cell value to activate. This makes the cell active and makes it easy for us to crosscheck in our testing. It also has the benefit of navigating the user on the Google Sheet to the desired cell. Check out the image below for an example:
Note! This approach can be much, much slower than the next option, particularly if your text finder discovers a lot of cells.
If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.
Find the first value or text in a Google Sheets Range using the iterator method
A much more common approach to finding the first position of the matching value is using the findNext() iterator method of the Text Finder Class.
This is considerably faster on large datasets where you expect to find many occurrences of the found item. Why? Because, the find-all approach will collect all cells containing the value, whereas findNext() will only collect the next value to be found.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Find the first position of the match. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * */ function findFirstValue(sheetName, text){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const firstVal = sheet.createTextFinder(text) .findNext(); console.log(firstVal.getA1Notation()) firstVal.activate(); }; |
Line 13 – Here we replace findAll() with findNext().
Find the last value or text in Google Sheets Range
In this example, we swap the findNext() method with the findPrevious() method.
Interestingly, if we call the find previous method straight after creating the text finder, It will look for the first item starting in reverse order. This means it will start its search from the bottom of the range.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Find the last position of the match. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * */ function findLastValue(sheetName, text){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const lastVal = sheet.createTextFinder(text) .findPrevious(); console.log(lastVal.getA1Notation()) lastVal.activate(); }; |
Find the position of the nth value in a Google Sheets Range with Apps Script
Here we want to find the nth value in a range. Perhaps the 3rd value or the 10th.
Lines 14-15- In this function, we return the findAll() method and then identify the position of the value in the selectedVal array.
Line 12 – Note that we will need to subtract one from our position value here before we can use it to find the value in the array. Remember, the array will start at zero.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Find the cell of the nth match. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * @param {number} n - position. * */ function findNthValue(sheetName, text, n){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const position = n - 1; const selectedVal = sheet.createTextFinder(text) .findAll()[position]; console.log(selectedVal.getA1Notation()) selectedVal.activate(); }; |
Find the first n values in a Google Sheets Range
In this scenario, we want to find a starting set of values from our range. Maybe we want to grab the top 3 found cells or the first 5.
Line 15 – Here we use the findAll() method again to get a list of all values in the range.
Line 19-29 – Next, we will use a JavaScript ‘for’ loop to iterate over the found cells. We will need to store all the found A1 notation locations in a locations variable on line 19.
Line 25 – On each iteration, we will append the locations with our new cell value.
Line 28 – However, if the index (i) matches the same number as the position variable then we want to break our loop.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/** * Finds the first set of matching cells of n in length or less. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * @param {number} n - the max number of items to report. * */ function findFirstN(sheetName, text, n){ const position = n - 1; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const found = sheet.createTextFinder(text) .findAll(); let locations = []; for(let i = 0; i < found.length; i++){ const cell = found[i]; locations.push(cell.getA1Notation()) if( i === position) break; } console.log(locations) }; |
Find the first or last values of a selected number in a Google Sheets Range
Finally, we have two functions here. Both functions allow you to find either the first set or last set or cells where the search term is found. Their usage really depends upon the type of data that you are working with.
Both functions take 4 arguments:
sheetName– The name of the sheet tab.text– The text to search.n– The number of found cells to collect.reverse(optional) – If true, conduct the search in reverse.
Again, both functions will return an array containing all found cell locations in A1-notation.
Checkout the post below for a detailed benchmark analysis of each function:
Performance of Google Apps Script Text Finder Class on 2 Approaches to Searching Large Datasets
Find All Approach
This approach is more performant when you don’t expect to find a lot of matching cells in a large range. It is also suitable if you want to extract a larger list of n items starting from the beginning or end of the range.
Why? Because if there are a large number of found variables then the findAll method will take more time to collect the full list of ALL cell locations before we can continue and select the ones we want. Whereas if we use the next option using findNext or findPrevious then each item will be retrieved one at a time, making the second option more performant.
Alternatively, if we want to retrieve say the first 100 items, then the first item would be a better approach. This is because we only make one call to the Google Sheet instead of 100 which is a much slower approach.
Line 16 – Here, we use the findAll() method again.
Line 22 – Note the if-statement here that checks if the reverse parameter is set to true.
Line 24 – If we want to start the search from the bottom of the range, we first set a counter to zero.
Line 25 – Then we run a reverse for loop, starting from the bottom of the found array.
Line 29 – If we find a cell we add it to the locations list like in our previous example.
Lines 31-33 – Finally, we update the counter and then check the counter count against n and break the look if we have a match.
Lines 36 -46 – If there is not reverse argument or the argument is set to false then we want to start our search from the top of the range. Here, we use the same for-loop as the previous chapter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
/** * Finds the first set of matching cells of n in length or less. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * @param {number} n - the max number of items to report. * @param {boolean} [reverse] - list from bottom to top. * @return {array} - Array of cell positions of the found match. */ function findFirstN_v2(sheetName, text, n, reverse = false){ const position = n - 1; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const found = sheet.createTextFinder(text) .findAll(); let locations = []; if(reverse){ let counter = 0; for(let i = found.length - 1; i >=0; i--){ const cell = found[i]; locations.push(cell.getA1Notation()); ++counter; if(counter === n) break; } }else{ for(let i = 0; i < found.length; i++){ const cell = found[i]; locations.push(cell.getA1Notation()) if( i === position) break; } } return locations }; |
Find Next or Previous Approach
This approach performs better in a large range when there the range contains many found cells, but you may only need to collect only a few of the first or last proceeding items.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
/** * Finds the first set of matching cells of n in length or less. * @param {string} sheetName - the name of the sheet tab. * @param {string|number} text - text to search. * @param {number} n - the max number of items to report. * @param {boolean} [reverse] - list from bottom to top. * @return {array} - Array of cell positions of the found match. */ function findFirstN_v3(sheetName, text, n, reverse = false){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const finder = sheet.createTextFinder(text) let locations = []; let counter = n; while(counter > 0){ let found = ""; if(reverse){ found = finder .findPrevious() .getA1Notation(); }else{ found = finder .findNext() .getA1Notation(); }; locations.push(found); --counter; }; return locations }; |
Line 15 – To start our search, we only initialise the text finder class.
Line 17 – Set our empty location array.
Line 18 – Creates a counter base on the number (n) of found cell locations that we want to retrieve.
Line 20 – Next, we generate JavaScript while loop to finish once the counter reaches 1. The loop will either find the next or find the previous cell range.
Line 22 – We set the found variable to receive the cell location.
Lines 24-28 – If we have set the item to be reversed we use the findPrevious method and collect each cell location.
Lines 29-34 – Alternatively, we will use the findNext method if reverse is set to false or not included.
Line 36 – Next, we push the cell reference to the locations array.
Line 28 – The last task in the loop is to decrease our counter by one.
Line 43 – Outside our loop, we return the locations array back to the initiating function.
If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I 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.