Using the Spreadsheet App Class’ Text Finder Class to find and hide rows in Google Sheets containing target text in a cell can be a fast way to hide values. In many situations, this may be a faster way to hide rows based on cell value in Google Sheets with Google Apps Script than iterating through a range and extracting rows.
In this tutorial, we will cover 3 approaches to using the Text Finder class to hide rows. Each may be useful in its own circumstances.
This tutorial accompanies the YouTube video series of the same name. You can find the links to each of the related videos in each of the sections along with the starter Google Sheets so that you can play along.
V1 – Basic Find and Hide Rows based on cell values in Google Sheets
Starter Sheet
The Video
The Code
|
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 |
/** * Hide or unhide all rows that contains the selected value. * Single sheet * @param {string} text - the text to find. * @param {string} sheetName - the target sheet. * @param {boolean} [isHide] - True to hide false to show. */ function hideAllRowsWithVal(text, sheetName, isHide = true) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const textFinder = sheet.createTextFinder(text); const allOccurrences = textFinder.findAll(); allOccurrences.forEach(cell => { const row = cell.getRow(); if (isHide) { sheet.hideRow(row); } else { sheet.showRows(row); } }) }; |
Unlike when formatting rows and cell activation, – as we did in the previous tutorial – our fasted approach here is to hide and unhide sheets while looping through all the found cells.
In this basic approach, our function contains 3 parameters:
text– The text to search.sheetName– The sheet name to search.isHide– An optional argument set to true by default to hide values or false manually to unhide them.
Lines 10-11 – We first collected our current Google Sheet workbook and then select the sheet tab we will be working in.
Lines 13-14 – Then we use the Text Finder class in the Spreadsheets App to search for our target text and then use the findAll() method to get an array constructor of all the found value cell ranges. You can learn more about this in the first tutorial in this series here:
Line 16 – Next we iterate through each found row with a JavaScript forEach() loop.
Line 17 – On each iteration, we collect the row number. We do the same thing here in our previous tutorial when activating and formatting entire rows.
Lines 19-23 – Lastly we need to check if the user has set isHide to false to show the rows or true (or not used) to hide the row. We then call the sheet and apply either the Spreadsheets Apps Hide Rows (hideRows()) method or (showRows()) Show Rows method. These methods can take a single row as an argument. We provide this with the row variable.
This is a quick and easy solution to understand. However, it does not perform well with a large dataset. It will also try and hide rows multiple times when a found cell is on the same row as a previously found cell.
The function, just like the other two examples, can be called from another function with:
hideAllRowsWithVal(text, sheetName, boolean)
e.g.:
hideAllRowsWithVal("koala", "Sheet1", true)
If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.
V2 – Find and Hide Rows based on cell values in Google Sheets with Range Grouping to improve performance
Starter Sheet
The Video
Released Monday 12 Feb 2023. Subscribe (Top right) to get a notification for when this video comes out.
The Code
|
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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
/** * Hide or unhide all rows that contains the selected value. * Single sheet * Collects adjacent rows into one range before hiding or unhiding. * @param {string} text - the text to find. * @param {string} sheetName - the target sheet. * @param {boolean} [isHide] - True to hide false to show. */ function hideAllRowsWithVal_v2(text, sheetName, isHide = true) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); // Find all matching values. const textFinder = sheet.createTextFinder(text); const allOccurrences = textFinder.findAll(); /** * Hides or shows the desired range. * @param {Object} setRange - {startRow, endRow} */ function hideRange(setRange) { if (isHide) { sheet.hideRows(setRange.startRow, setRange.numRows); } else { sheet.showRows(setRange.startRow, setRange.numRows); }; }; // Iterates throuhg each row and combines them into ranges. let rowRange = { startRow: null, numRows: null } allOccurrences.forEach((cell, idx) => { const row = cell.getRow(); const lastRowRangeRow = rowRange.startRow + rowRange.numRows; if (idx === 0) { // If on the first iteration. OR rowRange.startRow = row; rowRange.numRows = 1; } else if (lastRowRangeRow - 1 === row){ // If the the current row is the same as the previous. Skip this row. return; } else if (lastRowRangeRow === row) { // If the next row number is equal to the sum of the start row and number of rows to count. ++rowRange.numRows; // If this is the last row in the range if (idx === allOccurrences.length - 1) { hideRange(rowRange); }; } else { // If the new row is not part of the previous range. hideRange(rowRange); rowRange.startRow = row; rowRange.numRows = 1; // If this is the last row in the range if (idx === allOccurrences.length - 1) { hideRange(rowRange); }; } }) SpreadsheetApp.flush(); }; |
In larger, ranges we might have a lot of situations where our found text is on multiple adjacent rows.
In the image above, we can see that rows 30 and 31, 38-43, and 46-49 can all be batched together into a single range.
Furthermore, rows with the found text (Koala) on the same row can be ignored, the consecutive times that they are found.
Let’s go ahead and fix our code to make it more efficient.
Note that lines 9-16 of the code are the same as the previous function. Refer to this for an explanation.
The Hide Range METHOD
Line 22 – After we have collected our ranges of found rows we will create a hideRange() method. This will either hide or show the range based on the isHide argument.
This function takes a single object parameter that contains a start row and a number of rows.
Lines 24 – 26 – Unlike the previous version, we use the Hide Rows and Show Rows extra parameter to include the number of rows deep to hide.
Row Range variable
Lines 32 – 35 – This mutable variable stores the start row and row depth as we collected each range of rows to hide in our sheet.
Iterate through all cells
Line 37 – Here, we start the forEach loop that iterates through each found cell range. We include the index (idx) in our arguments in our arrow function as well here.
Line 39 – Next, we grab the row number with the Get Row (getRow()) method.
Line 40 – Then we get the next possible row in the current range collection by adding the start row with the row depth. This will be used in a moment ot compare against the currently iterated row.
On the first iteration
Lines 42-46 – On our first loop through our found cells, all we need to do is add our first found row and add one to our depth.
There is another cell on the same row
Lines 47-51 – If there is another cell on the same row, then we don’t need to do anything and we simply return the function for that iteration.
There is a cell directly below the previous one
Lines 52-55 – If the next found row is only one cell down (adjacent), then we just want to add one to the number of rows of the existing rowRange variable.
Lines 57-60 – If the row is the last found row, then we can run the hideRange function on our update range set.
A new range begins
If the next found row is not directly below the previous one. We need to:
- Line 63 – Run the
hideRangeon the currentrowRangevariable set. - Lines 65-66 – Reset the
rowRangevariable adding in the current row to the start range and one to the depth.
Lines 69-71 – Again, if we are on the last found row, we can just run hideRow to end the loop.
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.
V3 – Find and Hide Rows based on cell values in Google Sheets using the Google Sheets Advanced Service API and Range Grouping
The Video
Released Thursday 13 Feb 2023. Subscribe (Top right) to get a notification when this video comes out.
Starter Sheet
The Code
I found version two to be remarkably efficient. However, if you want to do a lot of editing to the sheet tab then you might want to consider using the Google Apps Script Advanced Sheet Service API.
|
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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
/** * Hide or unhide all rows that contains the selected value. * Single sheet * Uses Google Sheets Advanced Service Batch Update. * @param {string} text - the text to find. * @param {string} sheetName - the target sheet. * @param {boolean} [isHide] - True to hide false to show. */ function hideAllRowsWithVal_v3(text, sheetName, isHide = true){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const sheetId = sheet.getSheetId() const ssId = ss.getId(); // Find all matching values. const textFinder = sheet.createTextFinder(text); const allOccurrences = textFinder.findAll(); // Build Requests for Batch Update let requests = [] /** * Appends each request to the requests array. * @param {Object} setRange - {startRow, endRow} */ function appendRequest(setRange){ requests.push({ 'updateDimensionProperties': { "range": { "sheetId": sheetId, "dimension": 'ROWS', "startIndex": setRange.startRow - 1, "endIndex": setRange.endRow, }, "properties": { "hiddenByUser": isHide, }, "fields": 'hiddenByUser', }}) }; // Iterates throuhg each row and combines them into ranges. let rowRange = { startRow: null, endRow: null } allOccurrences.forEach((cell, idx) => { const row = cell.getRow(); if (idx === 0) { // If on the first iteration. OR rowRange.startRow = row; rowRange.endRow = row; }else if (rowRange.endRow === row){ // If the the current row is the same as the previous. Skip this row. return; } else if (rowRange.endRow === row - 1) { // If the next row number is equal to the sum of the start row and number of rows to count. ++rowRange.endRow; // If this is the last row in the range if (idx === allOccurrences.length - 1) { appendRequest(rowRange); }; } else { // If the new row is not part of the previous range. appendRequest(rowRange); rowRange.startRow = row; rowRange.endRow = row; // If this is the last row in the range if (idx === allOccurrences.length - 1) { appendRequest(rowRange); }; } }) Sheets.Spreadsheets.batchUpdate({requests: requests}, ssId) } |
Main Variables
We need to add a few more variables to our main variables here.
Line 12 – We will need to get the spreadsheet ID to use in our batch update.
Line 13 – The sheet name does not work the same as an identifier in the advanced service, so we will need to extract the sheet ID too.
A request list
Line 21 – The Sheets batch request property requires a list of requests as its value. Here we will store an array of all the requests that we want to batch together to hide each row range of the Google Sheet.
Update Dimension Properties
Line 27 – The appendRequest(setRange) function pushes a new request to the requests property.
When hiding and displaying rows or columns in a Google Sheet with the Advanced Service we are updating a dimension property. These properties can be a number of different field types, but for us, we want to hide rows.
Let’s take another look at the layout of this JSON object:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
{ 'updateDimensionProperties': { "range": { "sheetId": sheetId, "dimension": 'ROWS', "startIndex": setRange.startRow - 1, "endIndex": setRange.endRow, }, "properties": { "hiddenByUser": isHide, }, "fields": 'hiddenByUser', } } |
Lines 3-8 – The first sub-property is the range object. This object requires a sheet ID. The dimension is either the columns or rows.
Then we need to set the start index. Note that the cell ranges will start from zero instead of 1. We must then subtract 1 from our start row to get the correct start index of our row range.
Finally, we set the end row. This will be one row after our desired end row. Think, ‘up to, but not including this row’.
Lines 9-11 – Next, we identify the property that we want to change. For hiding and showing rows this is the "hiddenByUser" property, where the value is a boolean. This conveniently fits with our ishide parameter.
Line 12 – Weirdly, we then need to declare that we are using the "hiddenByUser" property by adding it to the fields list.
Store the current row range
Lines 46-49 – Just like the previous version, we need to store each range before sending it to appendRequest(). Unlike the previous version, our last property is the end row rather than the number of rows.
Starting the loop
Lines 52-54 –Now we can commence our loop through the found cells.
Here, the first task is to collect all the row numbers.
On the first iteration
Lines 56-60 – On the first loop of our cell array, we just want to add the current row number to the start and end row of rowRange.
If the next row is on the same row
Lines 61 – 65 – If we have more than one result on the same row we want to ignore it and return the current loop.
If its the next row below
Line 69 – We add one to our end row value in rowRange.
Lines 72-74 – If we are on our last found row, send it to appendRequest().
Create a new row range
Line 77 – If the current row is not the next row down then we send appendRequest() with our current rowRange.
Lines 79-80 – Next, we create a new rowRange.
Line 83-85 – If the current row is the last row in the array, we sent it to appendRequest().
The Batch request
Line 90. Here we use the batch request of the spreadsheet resource. The batch request takes an object with a requests property. This property, in turn, requires an array of JSON object requests.
For its second argument, we reference the Spreadsheet ID.
Some notes
If you know that there may not be any matches in your text finder then you might wish to add a return agent after the allOccurrences variable:
if(allOccurences.length === 0) return;
Another good measure might be to use a try/catch statement in the third version when running the batch call, just in case there is an error with the API server.
If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.
~ Yagi