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()
