In the Google Sheets API, how to delete all existing FilterViews?

Question

I'm using the Google sheets API and have a sheet that I'd like to refresh monthly which would include deleting all the filter views and then recreating them. I know how to create them, but googles example code on deleting them requires you to have a specific ID you want to delete.

I found this other stack overflow question (Mass Delete Filter Views in Google Sheets) but it's not Python so I'm having a hard time understanding how to convert it to Python:

function delFilterViews() {
  var ssId = SpreadsheetApp.getActive().getId();
  Sheets.Spreadsheets.batchUpdate(
    {
      requests: Sheets.Spreadsheets.get(ssId, {
        ranges: 'Sheet1', //Sheet in which filterviews are present
        fields: 'sheets/filterViews/filterViewId',
      }).sheets[0].filterViews.map(function(e) {
        return { deleteFilterView: { filterId: e['filterViewId'] } }; //create a new delete filter view request for each filter view present in sheet1
      }),
    },
    ssId
  );
}

Any suggestions would be appreciated!

score 0 · Answer 1 · answered Jun 04 '21 at 23:19

I believe your goal as follows.

You want to convert the following Google Apps Script to python.

  function delFilterViews() {
    var ssId = SpreadsheetApp.getActive().getId();
    Sheets.Spreadsheets.batchUpdate(
      {
        requests: Sheets.Spreadsheets.get(ssId, {
          ranges: 'Sheet1', //Sheet in which filterviews are present
          fields: 'sheets/filterViews/filterViewId',
        }).sheets[0].filterViews.map(function(e) {
          return { deleteFilterView: { filterId: e['filterViewId'] } }; //create a new delete filter view request for each filter view present in sheet1
        }),
      },
      ssId
    );
  }

You are using googleapis for python and you have already done the authorization process for using Sheets API.

In this case, how about the following sample script?

Sample script:

spreadsheetId = '###' # Please set the Spreadsheet ID.

service = build('sheets', 'v4', credentials=creds) # Please your "creds" for using Sheets API here.
res1 = service.spreadsheets().get(spreadsheetId=spreadsheetId, fields='sheets(filterViews)').execute()
deleteFilterViews = sum([[{'deleteFilterView': {'filterId': f['filterViewId']}} for f in e['filterViews']] for e in res1['sheets'] if 'filterViews' in e], [])
if deleteFilterViews != []:
    service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': deleteFilterViews}).execute()

Note:

When above script is run, all filter views are deleted. So please be careful this. I would like to recommend to use the sample Spreadsheet and sample fiter views for testing the script.

In the Google Sheets API, how to delete all existing FilterViews?

1 Answers1

Sample script:

Note:

References: