If you have created lots of filter views in a Google Spreadsheet and find it hard to delete all of them one by one manually then you can use the following Google Apps Script code to delete all filter views in one go!
function deleteAllFilterViews() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ss.getId();
var myFilterViews = Sheets.Spreadsheets.get(id, {
ranges: 'Sheet1',
fields: 'sheets/filterViews/filterViewId',
}).sheets[0].filterViews;
Sheets.Spreadsheets.batchUpdate({
requests: myFilterViews.map(function(e) {
return { deleteFilterView: { filterId: e['filterViewId'] } };
}),
},id);
};
Note:
Paste the above code in the script editor of your spreadsheet and run the function "deleteAllFilterViews"You will need to enable "Google Sheets API", it can be done by navigating to "Resources" > "Advanced Google services..." in the script editor of your spreadsheet.
Also you will need to enable the service in GCP Console:
https://console.cloud.google.com/apis/
Hi
ReplyDeleteThank you for posting this script to remove filter views. Unfortunately it gives me an error Reference Error: "Sheets" is not defined .
May be an easy error to fix, but unfortunately I'm not well versed with google script
Just an update about my prior Reference Error...
ReplyDeleteI went to the script menu Resources > Advanced Google services > and turned on Google Sheets API. When I reran the script it appeared to go through without error. When I looked at my File views they were all still visible but seemed inactive when I tried to bring one up to delete. I closed the google sheet reopened and all the views I wanted gone are now gone.
THANK YOU SO MUCH !
Can you delete by name instead of id. Then i can delete anything that is generic like Filter 7 by catching anything "Filter*"
ReplyDeleteI am searching for this last two day. Finally I got this here.
ReplyDeleteThank you
I don't see what did you get
Deleteofficially, it cannot be done. incredibly ridiculous. Hate google sheets
DeleteReferenceError: "Sheets" is not defined. (line 5, file "Code")
ReplyDeletei have got this error
https://stackoverflow.com/questions/45625971/referenceerror-sheets-is-not-defined
DeleteThank you very much for this; not only did it solve the problem at hand (deleting all filter views) but now I know about the Script Editor and the Google Sheets API, which I did not.
ReplyDeleteInterestingly enough, I need to do just the opposite; create a filterview, via GAS. Any tutorials or examples to share?
ReplyDeleteI have the code all set for creating a Filter, but I want the FilterView, a temporary view and independent to each user.
Is there just some method name changes to make to make it a FilterView, instead of a BasicFilter?
Hi, I'm getting this error: TypeError: Cannot call method "map" of undefined. (line 17, file "Code")
ReplyDeletePossibly related to this bit: Also you will need to enable the service in GCP Console:
https://console.cloud.google.com/apis/
which I'm not sure how to do.
Thanks!
Becouse all filters was deleted already. Update you sheet
DeleteDisregard that error. It was due to testing in a locked tab. Thanks.
ReplyDeleteHi there, I am getting the the error: TypeError: Cannot call method "map" of undefined. (line 10, file "Code") after enabling the google sheets API.
ReplyDeleteAnyone has a solution to this?
ReferenceError: Sheets is not defined (line 8, file "Code")Dismiss
ReplyDeleteY received this Error, :s
Hi i am unable to run the script, it says "Authorization Required"
ReplyDeleteHello, Thank you so much for your help. I recently use this and at first I thought its not working but after I close the Sheet and reopen it, all filter views are gone.
ReplyDeleteHi. Do i need to edit something on the program ? or just copy the program and run it. Thank you.
DeleteCan anyone help on this error?
ReplyDeleteGoogleJsonResponseException: API call to sheets.spreadsheets.get failed with error: Unable to parse range: Sheet1 (line 5, file "Code")
Maybe does not exist Sheet1 in your spreadsheet?
DeleteInput the sheet name in the place of Sheet1 (for example: 'Data'). It will work
DeleteWondering if it can be modified to delete only the Filter View with name "Filter xxx". I am not very sure on which part I have to put the filter at
ReplyDelete+++ i need the same
DeleteIt works
ReplyDeleteBut please help
I need to delete not all Filters, but only standart names like "Filter X". How to delete some Filters?
i solved this problem
Deletecode above
function delete_filters(id_sheet) {
var id_sheet = 1847414961;
var substr = "Фильтр";
var arr_filterViewId = [];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ss.getId();
var json_obj = Sheets.Spreadsheets.get(id, {
fields: 'sheets/filterViews',
});
for(var i = 0; i < json_obj.sheets.length; i++)
{
for(var i2 = 0; i2 < json_obj.sheets[i].filterViews.length; i2++)
{
var current_filterView = json_obj.sheets[i].filterViews[i2];
var sheetId = current_filterView.range.sheetId;
if(sheetId === id_sheet)
{
var str_title = current_filterView.title;
if(str_title.includes(substr))
{
arr_filterViewId.push(JSON.stringify(current_filterView.filterViewId));
}
}
}
}
Logger.log(arr_filterViewId);
for(var x =0 ; x < arr_filterViewId.length; x++)
{
var value_filterId = parseInt(arr_filterViewId[x],10);
Sheets.Spreadsheets.batchUpdate({
"requests": [
{
"deleteFilterView": {
"filterId": value_filterId
}
}
]
},id);
}
//Logger.log(JSON.stringify(json_obj.sheets[3].filterViews[0].range.sheetId));
}
last code
Deletefunction delete_filters() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ss.getId();
var id_sheet = ss.getActiveSheet().getSheetId();
var substr = "Фильтр";
var arr_filterViewId = [];
var json_obj = Sheets.Spreadsheets.get(id, {
fields: 'sheets/filterViews',
});
for(var i = 0; i < json_obj.sheets.length; i++)
{
if(json_obj.sheets[i].filterViews != null)
{
for(var i2 = 0; i2 < json_obj.sheets[i].filterViews.length; i2++)
{
var current_filterView = json_obj.sheets[i].filterViews[i2];
var sheetId = current_filterView.range.sheetId;
if(sheetId === id_sheet)
{
var str_title = current_filterView.title;
if(str_title.includes(substr))
{
arr_filterViewId.push(JSON.stringify(current_filterView.filterViewId));
}
}
} // end of 2 for
}
}
Logger.log(arr_filterViewId);
for(var x =0 ; x < arr_filterViewId.length; x++)
{
var value_filterId = parseInt(arr_filterViewId[x],10);
Sheets.Spreadsheets.batchUpdate({
"requests": [
{
"deleteFilterView": {
"filterId": value_filterId
}
}
]
},id);
}
//Logger.log(JSON.stringify(json_obj.sheets[3].filterViews[0].range.sheetId));
}
Not working. Kindly check and correct the same.
DeleteI am getting this error while exceuting the script. Not sure what could be the reason
ReplyDeleteGoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: The service is currently unavailable.
Worked a treat thank you. I did have to specify my sheets tab name "ranges: 'Sheet1'" Turned into "ranges: 'Custom Sheet Name'".
ReplyDeletePlease help me with this..
ReplyDeleteReferenceError: Sheets is not defined
deleteAllFilterViews @ Code.gs:5