How to get filtered sheet cells values with pyghseets

Question

How can I get only filtered values from spreadsheet? If user have filtered some values, I don't need them.

Answer 1

• You want to retrieve the values from the filtered sheet.
• The filter is the basic filter.
• For example, when the values are put in the cells "A1:J25" and the values of "A4:J4" and "A11:J11" are shown by the filter, you want to retrieve the values of cells of "A4:J4" and "A11:J11".
• You want to achieve this using pygsheets.
• You have already been able to get and put values for Google Spreadsheet using Sheets API with pygsheet.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, I used Query Language for this situation. At first, the access token and sheet ID are retrieved using pygsheets, and then, the values from the filtered sheet are retrieved by the Query Language. For this, I used "requests".

Sample script:

Before you use this script, please set the variables.

import csv
from io import StringIO
import pygsheets
import requests

service_account_file = '###'  # Please set the JSON filename including the credentials of service account.
spreadsheet_id = '###'  # Please set the Spreadsheet ID.
sheet_name = 'Sheet1'  # Please set the sheet name.

gc = pygsheets.authorize(service_file=service_account_file)
sh = gc.open_by_key(spreadsheet_id)
wks = sh.worksheet_by_title(sheet_name)
sheet_id = wks.jsonSheet['properties']['sheetId']
url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet_id + '/gviz/tq?tqx=out:csv&gid=' + str(sheet_id)
res = requests.get(url, headers={'Authorization': 'Bearer ' + gc.oauth.token})
strValues = res.text
f = StringIO(strValues)
reader = csv.reader(f, delimiter=',')
ar = [row for row in reader]
ar.pop(0)
print(ar)

• In this sample script, I used the service account. If you are using OAuth2, please modify the script of authorization.
• When this script is used for your sample Spreadsheet, the values of cells of "A4:J4" and "A11:J11" can be retrieved.

Reference:

• Query Language