CODEWITHSUNDEEP
pythonfiltergoogle-sheets-api
jason
jason

Reputation: 4429

google sheets api filter feature not working?

I'm trying to get the filter to work for Google sheets API4 using the example here: https://developers.google.com/sheets/api/guides/filters.

The link to their example sheet doesn't work so I created my own here: https://docs.google.com/spreadsheets/d/1kT4UU-jvFj273psdJ_fQz5xUP1Xv3Mg9zrs9U2ydM0g/edit#gid=0. It is shared.

When I run the code that they give on the site, the sheet does absolutely nothing. Here is my code. Only the top 3 lines were added by me.

import custom
spreadsheet_id = '1kT4UU-jvFj273psdJ_fQz5xUP1Xv3Mg9zrs9U2ydM0g'
g = custom.APIv4()  # builds service

my_range = {
    'sheetId': 0,
    'startRowIndex': 0,
    'startColumnIndex': 0,
}
addFilterViewRequest = {
    'addFilterView': {
        'filter': {
            'title': 'Sample Filter',
            'range': my_range,
            'sortSpecs': [{
                'dimensionIndex': 3,
                'sortOrder': 'DESCENDING'
            }],
            'criteria': {
                0: {
                    'hiddenValues': ['Panel']
                },
                6: {
                    'condition': {
                        'type': 'DATE_BEFORE',
                        'values': {
                            'userEnteredValue': '4/30/2016'
                        }
                    }
                }
            }
        }
    }
}

body = {'requests': [addFilterViewRequest]}
addFilterViewResponse = g.service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

duplicateFilterViewRequest = {
    'duplicateFilterView': {
        'filterId':
            addFilterViewResponse['replies'][0]['addFilterView']['filter']
            ['filterViewId']
    }
}

body = {'requests': [duplicateFilterViewRequest]}
duplicateFilterViewResponse = g.service.spreadsheets() \
    .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

updateFilterViewRequest = {
    'updateFilterView': {
        'filter': {
            'filterViewId': duplicateFilterViewResponse['replies'][0]
            ['duplicateFilterView']['filter']['filterViewId'],
            'title': 'Updated Filter',
            'criteria': {
                0: {},
                3: {
                    'condition': {
                        'type': 'NUMBER_GREATER',
                        'values': {
                            'userEnteredValue': '5'
                        }
                    }
                }
            }
        },
        'fields': {
            'paths': ['criteria', 'title']
        }
    }
}

body = {'requests': [updateFilterViewRequest]}
updateFilterViewResponse = g.service.spreadsheets() \
    .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

Upvotes: 1

Views: 662

Answers (1)

yuri
yuri

Reputation: 3400

On that example there are 3 request:

First, addFilterViewRequest that has addFilterViewResponse as a response.

The second one is a duplicated of the first: duplicateFilterViewRequest with response duplicateFilterViewResponse

The third one is updateFilterViewRequest with response updateFilterViewResponse

The example certainly does not return nothing, but it does create the filter, you'll find them if you open your spreadsheet and at the menu: Data --> Filter Views you'll find them listed by the name you've given to them.

But you could also add the following to the code you are using:

    return addFilterViewResponse

so this will print you the complete response for the first filter

    return addFilterViewResponse['replies'][0]['addFilterView']['filter']['filterViewId']

This will give you the filterViewId for the filter you've created first, if you want to see it in action you can do the following:

https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=YOUR_GID&fvid=FILTERVIEWID

Where YOUR_SPREADSHEET_ID YOUR_GID can be 0 or be the GID of your spreadsheet which you'll find it on the link. FILTERVIEWID is the number you just got.

I hope this clarifies your doubts.

Upvotes: 1

Related Questions