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!
Table of Contents
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:
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.
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
runsies_duplicateFilterViews()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
* 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.
*/
functionrunsies_duplicateFilterViews(){
constssID="1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y"// Your spreadsheet ID. Make sure you change this to your own Sheet ID if you are playing along.
constsourceSheetName="MAIN"// The sheet tab that you want to get the filter views from.
constdestinationTabList={
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
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.
Looking to learn more about Google Apps Scripts in a more structured format? Udemyhas 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.
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.
duplicateFilterViews.gs
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
92
93
94
95
96
97
98
99
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.
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:
Sheets.Spreadsheets.get
1
2
3
4
5
...
// Get full list of filter views for the entire spreadsheet.
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.
The resource containing the requests from us to update the filter views on the spreadsheet.
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.
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.
deleteFilterView
1
2
3
4
5
6
7
8
...
constdeleteView={
"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:
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.
runsies_showAllFilterViews()
1
2
3
4
5
6
7
8
9
10
11
/**
* Gets a list of all filter views in the spreadsheet.
*/
functionrunsies_showAllFilterViews(){
constssID="1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y"// Your spreadsheet.
constfilterViews=getAllFilterViews(ssID);
// Logging the results here you can use the filterViews variable how you like.
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.
runsies_selectedSheetFilterViews()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
* Gets all of the filter views contained in the selected sheet tab.
*/
functionrunsies_selectedSheetFilterViews(){
constssID="1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y"// Your spreadsheet.
constsourceSheetName="MAIN"// The sheet tab that you want to get the filter views from.
Not only are Google Workspaces, Google Sheets, Docs, Forms and Slide great to work in and look awesome, but the convenience of collaborating and sharing your Google Drive Files and Folders is also super useful. So much so that many course creators share their documents with their students all the time.
The problem that course creators have is that they are generally stuck with two options when sharing their Google Drive files and folders:
Set sharing to Anyone with Link can view. And hope other freeloading students don’t find and share their course material.
Manually share each student as they enrol. Which is time-consuming for the course creator and annoying for the student who needs to wait to be shared before they can get their hands on your awesome course content.
Both options are really terrible.
I reluctantly chose option one for my first Google Sheets Essentials Teachable Course and it really bothered me. I needed to find a way to share my Google Drive course content with only those students who signed up for my course.
In this tutorial, I will guide you through creating a Google Apps Script web app that receives a webhook notification when a student enrols onto one of my Teachable courses. If a student enrolled with a non-Gmail or non-Google Workspace domain email account, they will be sent an email with an attached form to add a Google-friendly email.
The fun thing is that you will experience how the whole process works, because…well…that’s how I am going to share the Google Sheets file with you when you enrol. Neat, hey?
As a part of your purchase you will also get a few other perks:
Set files or folders for ‘view’, ‘comment’ or ‘edit’ access.
Add existing students to your selected course Google Drive Files and Folders.
Get your full course list from your Teachable site right in your Sheet.
A choice to bulk set your files and folders to:
prevent downloads, copying and print.
Prevent sharing by any documents you have provided ‘edit’ permission to.
If you want to understand how it all works and build your own, read on, you can always throw a couple of coins at me and enrol to run the workflow just for fun.
Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?
I know I have.
There have been a number of instances where I wanted to insert new columns or removed unused columns in large Google Sheets projects with Google Apps Script.
In the past, I have dynamically set headers based on something like an IMPORTRANGE or FILTER, or simply from an array ({}) from another sheet tab or Google Sheet. However, when I update the original data the headers change but all the data underneath them does not move along to the updated column with it.
Now all my data is not lined up with the header! As you can imagine, this creates some serious problems.
In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.
Take a look at the visual example below:
As always, read as much or as little as you need to get the job done or learn the skill.