How to get ranges of spreadsheets after applying filter in google sheets API?

Question

I have one google spreadsheet. I use python script and google sheets python api to manipulate my sheet. I first set the basic filter on my google sheet using setBasicFilterRequest like this :

setBasicFilterRequest = {
    'setBasicFilter' : {
        'filter' : _filter
    }
}

body = { 
    'requests' : [setBasicFilterRequest],
}

resp = service.spreadsheets() \
   .batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body).execute()

This sets a filter on my spreadsheet, and when I see it on browser, I see only filtered rows. This is great. Now in this script, I want to get the ranges which are currently visible on the sheet on my browser. For example, when I set my basic filter, the google sheet view is updated to only show filtered rows, and I see rows [2, 11, 27, 31, 55]. Now, I want to fetch this list in my python script. Is this possible to achieve?

Answer 1

I believe your goal as follows.

• You want to retrieve the row numbers of the filtered rows in a sheet.
• You want to achieve this using googleapis for python.

Issue and workaround:

In this case, unfortunately, the row numbers cannot be directly retrieved from Sheets API. So it is required to do the following flow.

1. Retrieve rowMetadata of the sheet using the method of "spreadsheets.get" in Sheets API.
2. Retrieve the filtered rows from the value of rowMetadata using a script.

When this flow is reflected to the script, it becomes as follows.

Sample script:

In this sample script, please use your authorization script for creds of credentials=creds.

spreadsheet_id = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name.

service = build('sheets', 'v4', credentials=creds)
fields = 'sheets(data(rowMetadata(hiddenByFilter)))'
res = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=sheetName, fields=fields).execute()
filteredRows = {"shownRows": [], "hiddenRows": []}
rowMetadata = res["sheets"][0]["data"][0]["rowMetadata"]
for i, r in enumerate(rowMetadata):
    filteredRows["hiddenRows" if "hiddenByFilter" in r and r["hiddenByFilter"] else "shownRows"].append(i + 1)
print(filteredRows)

• In this script, sheets(data(rowMetadata(hiddenByFilter))) is used as fields.

Result:

When above script is run, the following result is obtained.

{
  "shownRows": [#, #, #,,,],
  "hiddenRows": [#, #, #,,,]
}

• shownRows is the shown row numbers by the basic filter.
• hiddenRows is the hidden row numbers by the basic filter.

Note:

• From your previous question, I thought that you have already known about the method using Query Language. So in this answer, I introduced the method using Sheets API.

Reference:

• Method: spreadsheets.get