Check out Kite (free AI Coding Assistant) → Link



Buy Me a Coffee? Your support is much appreciated!

PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Source Code:    

Adding a filter

request_body = {
    'requests': [
        {
            'setBasicFilter': {
                'filter': {
                    'range': {
                        'sheetId': 0,
                        'startRowIndex': 0,
                        'startColumnIndex': 0 
                    },
                    'sortSpecs': [
                        {
                            'dimensionIndex': 1,
                            'sortOrder': 'ASCENDING'
                        }
                    ],
                    'filterSpecs': [
                        {
                            'filterCriteria': {
                                'hiddenValues': [
                                    'Canada', 'Mexico'
                                ]
                            },
                            'columnIndex': 1
                        },
                        {
                            'filterCriteria': {
                                'condition': {
                                    'type': 'NUMBER_GREATER_THAN_EQ',
                                    'values': [
                                        {
                                            'userEnteredValue': '1901'
                                        }
                                    ]
                                },                                
                            },
                            'columnIndex': 4
                        }
                    ]
                }
            }
        }
    ]
}

response = service.spreadsheets().batchUpdate(
    spreadsheetId=GOOGLE_SHEETS_ID,
    body=request_body
).execute()

 Remove autofilter

request_body = {
    'requests': [
        {
            'clearBasicFilter': {
                'sheetId': 0
            }
        }
    ]
}

response = service.spreadsheets().batchUpdate(
    spreadsheetId=GOOGLE_SHEETS_ID,
    body=request_body
).execute()




Create a filter view

request_body = {
    'requests': [
        {
            'addFilterView': {
                'filter': {
                    'title': 'Sample View1',
                    'range': {
                        'sheetId': 0,
                        'startRowIndex': 0,
                        'startColumnIndex': 0 
                    },
                    'sortSpecs': [
                        {
                            'dimensionIndex': 1,
                            'sortOrder': 'ASCENDING'
                        }
                    ],
                    'filterSpecs': [
                        {
                            'filterCriteria': {
                                'hiddenValues': [
                                    'Canada', 'Mexico'
                                ]
                            },
                            'columnIndex': 1
                        },
                        {
                            'filterCriteria': {
                                'condition': {
                                    'type': 'NUMBER_GREATER_THAN_EQ',
                                    'values': [
                                        {
                                            'userEnteredValue': '1901'
                                        }
                                    ]
                                },                                
                            },
                            'columnIndex': 4
                        }
                    ]
                },
                # 'fields': '*'
            }
        }
    ]
}

response = service.spreadsheets().batchUpdate(
    spreadsheetId=GOOGLE_SHEETS_ID,
    body=request_body
).execute()

filter_view_id = response['replies'][0]['addFilterView']['filter']['filterViewId']

Duplicate a filter view

request_body = {
    'requests': [
        {
            'duplicateFilterView': {
                'filterId': filter_view_id
            }
        }
    ]
}

response = service.spreadsheets().batchUpdate(
    spreadsheetId=GOOGLE_SHEETS_ID,
    body=request_body
).execute()




Update a filter view

filter_view_id2 = '1087930812'

request_body = {
    'requests': [
        {
            'updateFilterView': {
                'filter': {
                    'filterViewId': filter_view_id2,
                    'title': 'Report View 2',
                },
                'fields': 'filterViewId,title'
            }
        }
    ]
}

response = service.spreadsheets().batchUpdate(
    spreadsheetId=GOOGLE_SHEETS_ID,
    body=request_body
).execute()

Delete a filter view

response = service.spreadsheets().get(spreadsheetId=GOOGLE_SHEETS_ID).execute()
for view in response['sheets'][0]['filterViews']:
    service.spreadsheets().batchUpdate(
        spreadsheetId=GOOGLE_SHEETS_ID,
        body={
            'requests': [
                {
                    'deleteFilterView': {
                        'filterId': view['filterViewId']
                    }
                }
            ]
        }
    ).execute()