Sheets Advanced Service

Find and Hide Rows in Google Sheets with Apps Script

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

Version 1 – Starter Sheet

The Video

https://youtu.be/alI2f7w7xjU

Find & Hide Rows in Google Sheets With Apps Script – Basic (https://youtu.be/alI2f7w7xjU)

The Code

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

/**

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

Find All Values in Google Sheets with Apps Script

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

Version 2 – Starter Sheet

The Video

https://youtu.be/CeWUAOK7Ui8

Find & hide rows in Google Sheets using Sheets Advanced Service Batch Update with Apps Script (https://youtu.be/CeWUAOK7Ui8)

Released Monday 12 Feb 2023. Subscribe (Top right) to get a notification for when this video comes out.

The Code

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

/**

* 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 hideRange on the current rowRange variable set.
Lines 65-66 – Reset the rowRange variable 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?

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.

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

Version 3 – 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.

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

/**

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

{
    'updateDimensionProperties': {
      "range": {
        "sheetId": sheetId,
        "dimension": 'ROWS',
        "startIndex": setRange.startRow - 1,
        "endIndex": setRange.endRow,
      },
      "properties": {
        "hiddenByUser": isHide,
      },
      "fields": 'hiddenByUser',
    }
}

{

'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

Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script

While there is not way to directly duplicate Filter Views into other sheet tabs in Google Sheets we can do this with a little bit of Google Apps Script magic.

In this tutorial, we will walk through how to duplicate all filter views from a source Google Sheets tab and duplicate them into selected sheets tab.

We’ll start off with an example sheet to better understand what we are going to achieve and then share the code along with a quick-use guide for those of you who want to to just get in and use the script in your own project.

Next we will provide a video tutorial walking through how I built the script and wrap everything up with some bonus scripts to extract different parts of the code. If you get to this stage you should have a better understanding on how to work with Filter Views programmatically.

Let’s dive in!

The Example Google Sheet

The best way of following how the code works is with an example.

In our example sheet we have 6 different stores in six different Google Sheets tabs. Each store contains the same headers; Date, Company, Name, Notes, Type/Specialty, Quoted, Actual.

We also have a NOTES sheet tab that provides instructions for the sheet.

If you want to play along, you can grab the example sheet from the link below:

Duplicate Filter View – Google Sheet Starter

Aim

We want to create a set of matching Filter Views for each of our stores without having to manually duplicate each one by hand in the sheet tab.

We have five filter views that we want to include in each of our company sheet tabs.

Of course, we don’t want to add filter views to our NOTES sheet tab.

The Problem

While we can create individual filter views inside a tab, we can’t migrate those filter views over to existing sheet tabs. So the only Google Sheets alternative is to manually update each sheet tab with our list of filter views that we want to add.

Another problem then arises when we need to make a modification to one or more of our filter views. All tabs have to be then modified by hand, increasing the chance of mistakes and significantly increase a big old case of boring.

Boring Boss Baby GIFfrom Boring GIFs

Imagine if you had 50 different sheet tabs for 50 different businesses. That would be a nightmare to create and update.

The Solution

We will only create and update filter views in our first business sheet tab that we have called ‘MAIN’ in our example. Then we will use some Google Apps Script to update all the other business sheet tabs with the filter views.

One thing that is important to keep in mind when the script is being built is to ensure that the filter view length changes for each sheet tab to accommodate the length of rows in each business sheet tab as they change.

If we need to make adjustments to our filter views and then update all business sheet tabs, we will first need to remove the existing filter views in all but the origin sheet tab that have the same name as the origin filter views (for us, “MAIN” tab views) before updating them. Otherwise we will generate an increasingly long list of filter views that all have the same view name.

Also, we probably don’t want other users to be able to ‘accidentally’ edit our Apps Script Code for the Google Sheet, so we will store the scrip unbound in a separate Apps Script file.

Here’s the code.

The Duplicate Filter Views Code

To keep things neat and tidy we will keep our duplicate filter view code in a new Google Script (*.gs) file. In our main Code.gs file I’ll add some sample code to add in all of our information needed to run the script.

Keep in mind that you could call the duplicate filter views script on its own like I have below or part of a larger process in your own project.

For your own project, you will need to create the duplicateFilterViews.gs file and copy and paste in the script. Optionally you can add the Code.gs file to run the script like I have or build your own.

Note! Unless your Google Sheets project is exclusively for you or a highly trusted team, I would recommend creating a separate Google Apps Script Project.

Code.gs

/**
 * Main run function to duplicate filter views into selected sheet tabs. 
 * This is a sample function that could be converted into a UI or be included
 * as part of a script process. 
 */
function runsies_duplicateFilterViews() {
  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet ID. Make sure you change this to your own Sheet ID if you are playing along.
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 
  const destinationTabList = {
    areExcludedTabs : true, // Is this a list of sheet tabs to exclude?
    tabNames : [
      "NOTES" // add all sheet tabs by name that you wish to include or exclude separated by a comma
    ]
  };

  duplicateFilterViews(ssID, sourceSheetName, destinationTabList)

};

/**

* Main run function to duplicate filter views into selected sheet tabs.

* This is a sample function that could be converted into a UI or be included

* as part of a script process.

function runsies_duplicateFilterViews() {

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet ID. Make sure you change this to your own Sheet ID if you are playing along.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const destinationTabList = {

areExcludedTabs : true, // Is this a list of sheet tabs to exclude?

tabNames : [

"NOTES" // add all sheet tabs by name that you wish to include or exclude separated by a comma

]

};

duplicateFilterViews(ssID, sourceSheetName, destinationTabList)

};

Quick Use Guide

Add the Google Sheets Advance Service

You will need to access the Advanced Google Sheets Service for Apps Script before continuing. To do this select the Add a service plus button from the Services menu then scroll through the list of services until you find the Google Sheets serivce. Select it and click Add.

Setting up your reference data

The runsies_duplicateFilterView() function for our example contains all of the data we need to run our script.

We first list all of our variables:

ssID (string) – The Spreadsheet ID for the Google Sheet you are working on found in the URL of the document.
click to Expand!
sourceSheetName (string) – The name of the Google Sheet tab that contains the Filter Views that you want to duplicate. For our example this is the “MAIN” sheet tab.
destinationTabList (Object) – this Object contains two properties:
- areExcludedTabs (boolean) – Are you providing a list of all sheet tabs you want to exclude from duplicating the filter views? If so, mark true. This is probably the most common case. Otherwise mark it false if you are providing a list of all sheet tabs that you want to include. If so, mark false.
  We chose true in our example so we only have to add one item (excluding the ‘NOTES’ tab)…cause we lazy.
- tabNames (array) – An array of all the sheet tab included or excluded depending on your choice in areExcludedTabs. Don’t add the source sheet tab to this list.
  - E.g. of included list:["Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5", "Sheet 6"]
  - E.g. of excluded list: ["Notes"]

Finally we run the duplicateFilterViews(ssID, sourceSheetName, destinationTabList). Note that we have included the three constant variables as our arguments for the function.

Paste in the duplicateFilterViews.gs code and run

After you have updated your Code.gs file and added the script to your newly created duplicateFilterView.gs file (script below). Save the file and select run from the menu bar.

Once the script has run, you can check our Google Sheet tabs to see that all filter views have been duplicated successfully.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

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

duplicateFilterViews.gs

Copy and paste the script below into your own project. I recommend adding it to a separate duplicateFilterViews.gs file for easier management.

/**
 * Object containing either a list of all sheet tables in which to copy the 
 * filter views or list or all sheet tables to be excluded from duplicating the list of filter views.
 * @typedef {Object} destTabList
 * @param {Boolean} destTabList.areExcludedTabs - True if tabNames is list of excluded tabs. False if tabNames is
 * list of tabs to add the filter views to.
 * @param {Array} destTabList.tabNames - Tab names of either excluded tabs or included tabs to contain filter views.
 */


/**
 * Duplicates the all of filter views of a selected sheet tab to selected sheet tabs in the same Spreadsheet.
 * 
 * @param {String} ssID - Spreadsheet ID
 * @param {String} sourceSheetName - Selected sheet tab to duplicate.
 * @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.
 */
function duplicateFilterViews(ssID, sourceSheetName, destTabList){

  // ########## VARIABLES ##########
  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  // Get list of sheets by id minus selected sheet and exclusion list.
  const sheetIdList = getSheetIdList(ss, destTabList, sourceSheetName);

  const filterViews = getAllFilterViews(ssID);



  // Get only the filter views for the selected sheet.
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);


  removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

  createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews);
  
};


/**
 * Gets a list of all the sheet tab IDs of all the sheet tabs that will have the filter views 
 * duplicated in. 
 * 
 * @param {SpreadsheetApp.Spreadsheet} ss 
 * @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.
 * @param {string} sourceSheetName - The name of the source sheet tab. 
 * @returns {array} Array of all selected sheet tab ids. 
 */
function getSheetIdList(ss, destTabList, sourceSheetName){
  return ss.getSheets().reduce((acc, sheet) => {

    // Check if we have an inclusion or exclusion list. 
    if(destTabList.areExcludedTabs){

      // Exclusion list
      if (![...destTabList.tabNames, sourceSheetName].includes(sheet.getName())) {
        return acc = acc.concat(sheet.getSheetId());
      };

    }else{
      
      // Inclusion list
      if ([...destTabList.tabNames].includes(sheet.getName())) {
        return acc = acc.concat(sheet.getSheetId());
      };
    };

    return acc
  }, []);
};


/**
 * Get an array of objects containing all filter views for all sheet tabs in a spreadsheet. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @returns {Array<Object>} - Each array is a sheet tab of filter views.  
 */
function getAllFilterViews(ssID){
  // Get full list of filter views for the entire spreadsheet.
  const request = { fields: ["sheets/filterViews"] }
  const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

  // Just the array items labelled 'filterView' in the Object
  return allFilterViews.filter(view => view.filterViews);
};



/**
 * Gets all the filter views for a selected sheet tab. 
 * 
 * @param {Array<Object>} filterViews - an array of objects containing all fiter views for all sheet tabs in a spreadsheet. 
 * @param {Number} SheetID - The ID number of the selected source sheet. 
 * @returns {Array<Object>} - Each array is a sheet tab of filter views.  
 */
function getSelectedSheetFilterViews(filterViews, sheetID){
  return filterViews.filter(view => {

    if (view.filterViews[0].range.sheetId == sheetID) {
      return view
    }
  })[0];

}

/**
 * Removes any existing copies of filter views in destination sheet tab locations that match the titles of the sheet
 * views in the source sheet tab. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab. 
 * @param {Array<Object>} filterViews - an array of objects containing all filter views for all sheet tabs in a spreadsheet.
 * @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs. 
 */
function removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList){

  // Get a list of all the views by name in the source sheet tab.
  const listOfViewsByName = selectedSheetFilterViews.filterViews.map(view => view.title);

  // Iterate through list of views in all sheets to edit.
  let viewIdsToRemove = []


  filterViews.forEach(sheet => {
    if(sheet.hasOwnProperty("filterViews") && sheetIdList.includes(sheet.filterViews[0].range.sheetId)){
      sheet.filterViews.forEach(view => {

        if (listOfViewsByName.includes(view.title)) {

          const deleteView = {
            "deleteFilterView": {
              "filterId": view.filterViewId
            }
          }
          viewIdsToRemove.push(deleteView)
        }
      })
    }

  })


  const delResource = {
    requests: viewIdsToRemove
  };

  if(viewIdsToRemove.length != 0){
    Sheets.Spreadsheets.batchUpdate(delResource, ssID)
  };
};


/**
 * Create new filter views for each selected destination sheet tab based on selected
 * source sheet tab. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.
 * @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab. 
 */
function createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews){
  // Loop through every sheet tab that needs to be updated. 
  sheetIdList.forEach(sid => {
    const resource = {
      requests: []
    }

    // Loop through the selected filter view.
    selectedSheetFilterViews.filterViews.forEach(view => {

      // Add each view to the filter property.
      let addFilterView = { 'addFilterView': { filter: view } }

      // Remove the old filterViewId
      delete addFilterView.addFilterView.filter.filterViewId

      // Add the filter view to the new sheet ID.
      addFilterView.addFilterView.filter.range.sheetId = sid

      // Add the new last row to the filter view.
      addFilterView.addFilterView.filter.range.endRowIndex = getSheetById(ssID, sid).getLastRow()

      // Add the view to hte resource.requests array.
      resource.requests.push(addFilterView)

    })
    Sheets.Spreadsheets.batchUpdate(resource, ssID);
  })
};

/** 
  * Searches within a given Google Spreadsheet for a provided Sheet ID and returns
  * the Sheet if the sheet exists; otherwise it will return undefined if not found.
  *
  * @param {Spreadsheet} ss - a Google Spreadsheet object
  * @param {Integer} sheetId - the ID of a Google Sheet 
  * @return {Sheet} the Google Sheet object if found; otherwise undefined
  */
function getSheetById(ssID, sheetId) {
  const ss = SpreadsheetApp.openById(ssID)
  var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);
  return foundSheets.length ? foundSheets[0] : undefined;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

/**

* Object containing either a list of all sheet tables in which to copy the

* filter views or list or all sheet tables to be excluded from duplicating the list of filter views.

* @typedef {Object} destTabList

* @param {Boolean} destTabList.areExcludedTabs - True if tabNames is list of excluded tabs. False if tabNames is

* list of tabs to add the filter views to.

* @param {Array} destTabList.tabNames - Tab names of either excluded tabs or included tabs to contain filter views.

/**

* Duplicates the all of filter views of a selected sheet tab to selected sheet tabs in the same Spreadsheet.

* @param {String} ssID - Spreadsheet ID

* @param {String} sourceSheetName - Selected sheet tab to duplicate.

* @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.

function duplicateFilterViews(ssID, sourceSheetName, destTabList){

// ########## VARIABLES ##########

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

// Get list of sheets by id minus selected sheet and exclusion list.

const sheetIdList = getSheetIdList(ss, destTabList, sourceSheetName);

const filterViews = getAllFilterViews(ssID);

// Get only the filter views for the selected sheet.

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews);

};

/**

* Gets a list of all the sheet tab IDs of all the sheet tabs that will have the filter views

* duplicated in.

* @param {SpreadsheetApp.Spreadsheet} ss

* @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.

* @param {string} sourceSheetName - The name of the source sheet tab.

* @returns {array} Array of all selected sheet tab ids.

function getSheetIdList(ss, destTabList, sourceSheetName){

return ss.getSheets().reduce((acc, sheet) => {

// Check if we have an inclusion or exclusion list.

if(destTabList.areExcludedTabs){

// Exclusion list

if (![...destTabList.tabNames, sourceSheetName].includes(sheet.getName())) {

return acc = acc.concat(sheet.getSheetId());

};

}else{

// Inclusion list

if ([...destTabList.tabNames].includes(sheet.getName())) {

return acc = acc.concat(sheet.getSheetId());

};

return acc

}, []);

};

/**

* Get an array of objects containing all filter views for all sheet tabs in a spreadsheet.

* @param {string} ssID - the id of the selected spreadsheet.

* @returns {Array<Object>} - Each array is a sheet tab of filter views.

function getAllFilterViews(ssID){

// Get full list of filter views for the entire spreadsheet.

const request = { fields: ["sheets/filterViews"] }

const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

// Just the array items labelled 'filterView' in the Object

return allFilterViews.filter(view => view.filterViews);

};

/**

* Gets all the filter views for a selected sheet tab.

* @param {Array<Object>} filterViews - an array of objects containing all fiter views for all sheet tabs in a spreadsheet.

* @param {Number} SheetID - The ID number of the selected source sheet.

* @returns {Array<Object>} - Each array is a sheet tab of filter views.

function getSelectedSheetFilterViews(filterViews, sheetID){

return filterViews.filter(view => {

if (view.filterViews[0].range.sheetId == sheetID) {

return view

}

})[0];

}

/**

* Removes any existing copies of filter views in destination sheet tab locations that match the titles of the sheet

* views in the source sheet tab.

* @param {string} ssID - the id of the selected spreadsheet.

* @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab.

* @param {Array<Object>} filterViews - an array of objects containing all filter views for all sheet tabs in a spreadsheet.

* @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.

function removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList){

// Get a list of all the views by name in the source sheet tab.

const listOfViewsByName = selectedSheetFilterViews.filterViews.map(view => view.title);

// Iterate through list of views in all sheets to edit.

let viewIdsToRemove = []

filterViews.forEach(sheet => {

if(sheet.hasOwnProperty("filterViews") && sheetIdList.includes(sheet.filterViews[0].range.sheetId)){

sheet.filterViews.forEach(view => {

if (listOfViewsByName.includes(view.title)) {

const deleteView = {

"deleteFilterView": {

"filterId": view.filterViewId

}

viewIdsToRemove.push(deleteView)

}

})

}

})

const delResource = {

requests: viewIdsToRemove

};

if(viewIdsToRemove.length != 0){

Sheets.Spreadsheets.batchUpdate(delResource, ssID)

};

/**

* Create new filter views for each selected destination sheet tab based on selected

* source sheet tab.

* @param {string} ssID - the id of the selected spreadsheet.

* @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.

* @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab.

function createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews){

// Loop through every sheet tab that needs to be updated.

sheetIdList.forEach(sid => {

const resource = {

requests: []

}

// Loop through the selected filter view.

selectedSheetFilterViews.filterViews.forEach(view => {

// Add each view to the filter property.

let addFilterView = { 'addFilterView': { filter: view } }

// Remove the old filterViewId

delete addFilterView.addFilterView.filter.filterViewId

// Add the filter view to the new sheet ID.

addFilterView.addFilterView.filter.range.sheetId = sid

// Add the new last row to the filter view.

addFilterView.addFilterView.filter.range.endRowIndex = getSheetById(ssID, sid).getLastRow()

// Add the view to hte resource.requests array.

resource.requests.push(addFilterView)

})

Sheets.Spreadsheets.batchUpdate(resource, ssID);

})

};

/**

* Searches within a given Google Spreadsheet for a provided Sheet ID and returns

* the Sheet if the sheet exists; otherwise it will return undefined if not found.

* @param {Spreadsheet} ss - a Google Spreadsheet object

* @param {Integer} sheetId - the ID of a Google Sheet

* @return {Sheet} the Google Sheet object if found; otherwise undefined

function getSheetById(ssID, sheetId) {

const ss = SpreadsheetApp.openById(ssID)

var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);

return foundSheets.length ? foundSheets[0] : undefined;

}

Using the Google Sheets Advanced Service for Filter Views

We need to approach the Google Sheets Advance Service API quite differently to how we use the SpreadsheetApp class. The advance service requires us to retrieve and update an object or array-object to carry out our processes.

This means that we need to test the object directory path so we can see where locations are for us to find or update what we need. I’ve left this testing phase out of this tutorial, but this is something you will need to do.

For our project we need to use the Sheet.Spreadsheet resource. From here we will either get our Filter View data for the spreadsheet or send a batch update to make bulk changes to the sheet.

Get our Filter View Data

We retrieve our list of all filter views only one time in our getAllFilterViews() function.

Now we can simply get a list of all the date in the entire spreadsheet by using the get method and apply the selected spreadsheet ID like this:

Sheets.Spreadsheets.get(ssID)

Get just filter view field data

However, this is pretty wasteful and generates a bulky data set. Instead we can narrow in on the spreadsheets list of filter views by adding some some optional arguments to our get request.

Here, we use the “fields” property to tell the API what fields that we only want to retrieve. For us, this is our filter views. Our filter views are applied to each of the sheets in our spreadsheet so we must create the path “sheets/filterViews”. Our code then looks like this:

...
  // Get full list of filter views for the entire spreadsheet.
  const request = { fields: ["sheets/filterViews"] }
  const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;
...

...

// Get full list of filter views for the entire spreadsheet.

const request = { fields: ["sheets/filterViews"] }

const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

...

This will return our data containing all the filter views.

Once retrieved we can follow the property tree or iterate through the sheets, and filter view arrays. In the code above we immediately reduce our data down to just the array of all of our sheets.

Batch update our Filter View Data

We update our filter view data on two occasions in our script. First when we delete all the duplicates in our destination sheet tabs and then to add our new duplicate filter data to our destination sheet tabs.

To do this we use the batchUpdate method. This method takes two parameters:

The resource containing the requests from us to update the filter views on the spreadsheet.
1. requests – this is our list of requests to update our data. It will contain an array of objects where each object contains the data that we wish to update.
  1. Instruction – Each update object starts off with an instruction that is known as a ‘request’. You can find a list of all available requests here.
The spreadsheet ID.

Delete a filter view

To delete a filter view we use the deleteFilterView request. This requests is pretty simple all it requires is for us to provide the filter id of the item we want to delete. You can see it in use in lines 133-136 of the DuplicateFilterView.gs file code above.

...

          const deleteView = {
            "deleteFilterView": {
              "filterId": view.filterViewId
            }
          }
...

...

const deleteView = {

"deleteFilterView": {

"filterId": view.filterViewId

}

...

Add a Filter View

Adding a filter view is much more complicated. Well… it would be if we were not just using an existing filter view and making a few modifications to it.

To add a filter view we use the the addFilterView request. This requests sets the filter and then an object containing all the data we need to build the object.

{ 'addFilterView': { filter: Object of filter view data } }

You can see how we added this on line 175 of our duplicateFilterViews.gs file.

For our tutorial we didn’t need to build the view in its entirety, we just needed to:

remove the existing id of the view (You shouldn’t have one, because it will be added for you)
update the range > sheet ID to the new destination id
update the end row to match the current depth of the data in the destination sheet

Lines 177-187 of DuplicateFilterView.gs

The Video Tutorial

Here’s the link to the Starter Sheet for you to follow along:

Duplicate Filter View – Google Sheet Starter

https://youtu.be/I5cWzmjXBM4

Duplicating Google Sheets filter views in other Sheet Tabs with Google Apps Script (https://youtu.be/I5cWzmjXBM4)

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

Bonus Functions

Maybe you only want to get a list of all of your filter views in a spreadsheet or all the filter views for a selected sheet tab. Perhaps you just want to delete all the non-source tab filter views you created.

Check out the following three functions to give you some ideas on how you might use the duplicateFilterViews.gs file to achieve this.

I have appended each function name with runsies_ but you can rename and rebuild them how you want.

If you have been playing along, you can add them to the Code.gs file to run them.

runsies_showAllFilterViews()

This function takes your spreadsheet ID and runs the showAllFilterViews() function. The function returns a full list of all the filter views in all of your sheet tabs for your selected Google Sheet spreadsheet.

/**
 * Gets a list of all filter views in the spreadsheet. 
 */
function runsies_showAllFilterViews(){
  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet. 

  const filterViews = getAllFilterViews(ssID);

  // Logging the results here you can use the filterViews variable how you like. 
  console.log(JSON.stringify(filterViews, null, " "));
};

/**

* Gets a list of all filter views in the spreadsheet.

function runsies_showAllFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet.

const filterViews = getAllFilterViews(ssID);

// Logging the results here you can use the filterViews variable how you like.

console.log(JSON.stringify(filterViews, null, " "));

};

We then log the results, but you may wish to use them in other ways.

Keep in mind that the results will most likely be larger than what the console will contain, but logging the result will give you a good idea on how the Array of filter view objects for each tab is stored.

runsies_selectedSheetFilterViews()

This function gets all the filter views from all sheets and then returns just the data for the selected source sheet. In our case, this in ‘MAIN’, but you can change this to what ever sheet tab you are looking for.

/**
 * Gets all of the filter views contained in the selected sheet tab.
 */
function runsies_selectedSheetFilterViews(){

  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet. 
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 

  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  const filterViews = getAllFilterViews(ssID);
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

  // Logging the results here you can use the filterViews variable how you like. 
  console.log(JSON.stringify(selectedSheetFilterViews, null, " "));
};

/**

* Gets all of the filter views contained in the selected sheet tab.

function runsies_selectedSheetFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

const filterViews = getAllFilterViews(ssID);

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

// Logging the results here you can use the filterViews variable how you like.

console.log(JSON.stringify(selectedSheetFilterViews, null, " "));

};

The results are then logged. The filterViews property will give you an array of all of the views in the selected tab.

runsies_deleteAllCopiedFilterViews()

If you no longer wish to have the duplicates of the source filter views in your destination sheet tabs, you can use this function to remove them.

You should put in all the same arguments in this function that you had for your runsies_duplicateFilterViews() function.

/**
 * Deletes all the filtered views in the destination locations based on the source sheet tabs list of 
 * filter views. Does not remove the source sheets list of filter views. 
 */
function runsies_deleteAllCopiedFilterViews(){

  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet id. 
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 
  const destinationTabList = {
    areExcludedTabs : true,
    tabNames : [
      "NOTES"
    ]
  };

  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  const filterViews = getAllFilterViews(ssID);
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

  const sheetIdList = getSheetIdList(ss, destinationTabList, sourceSheetName);
  removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);  
};

/**

* Deletes all the filtered views in the destination locations based on the source sheet tabs list of

* filter views. Does not remove the source sheets list of filter views.

function runsies_deleteAllCopiedFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet id.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const destinationTabList = {

areExcludedTabs : true,

tabNames : [

"NOTES"

]

};

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

const filterViews = getAllFilterViews(ssID);

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

const sheetIdList = getSheetIdList(ss, destinationTabList, sourceSheetName);

removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

};

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

Happy Coding!

~Yagi

V1 – Basic Find and Hide Rows based on cell values in Google Sheets

Starter Sheet

The Video

The Code

V2 – Find and Hide Rows based on cell values in Google Sheets with Range Grouping to improve performance

Starter Sheet

The Video

The Code

The Hide Range METHOD

Row Range variable

Iterate through all cells

On the first iteration

There is another cell on the same row

There is a cell directly below the previous one

A new range begins

V3 – Find and Hide Rows based on cell values in Google Sheets using the Google Sheets Advanced Service API and Range Grouping

The Video

Starter Sheet

The Code

Main Variables

A request list

Update Dimension Properties

Store the current row range

Starting the loop

On the first iteration

If the next row is on the same row

If its the next row below

Create a new row range

The Batch request

Some notes

Share this:

Like this:

The Example Google Sheet

Aim

The Problem

The Solution

The Duplicate Filter Views Code

Code.gs

Quick Use Guide

Add the Google Sheets Advance Service

Setting up your reference data

Paste in the duplicateFilterViews.gs code and run

duplicateFilterViews.gs

Using the Google Sheets Advanced Service for Filter Views

Get our Filter View Data

Get just filter view field data

Batch update our Filter View Data

Delete a filter view

Add a Filter View

The Video Tutorial

Bonus Functions

runsies_showAllFilterViews()

runsies_selectedSheetFilterViews()

runsies_deleteAllCopiedFilterViews()

Share this:

Like this: