How to get only filtered rows from google sheets to python script using google API?

1
votes

I have created a python script that sets a filter on my google sheet. This filtering works as expected. I'm now interested in grabbing all the rows after setting this filter. However, when I do get api call, I'm getting all the rows.

I think I'm making a mistake in providing the range when I make get call. I provide range in this format Sheet1!A:J. This range indicates all data in columns from A to J. However, I don't know how to provide only range corresponding to filtered data. I have seen one solution in google app script described in this post (https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters#TOC-Get-filtered-rows). However, this is quite an inefficient solution. I don't want to run for loop on fetched data. I want to prevent fetching all data, and would rather prefer to fetch only the filtered data. Is that possible to do from python script?

I currently do it like this

    # print(json.dumps(body, indent=4))
    resp = service.spreadsheets() \
   .batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body).execute()

    sheet = service.spreadsheets()
    data = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A:J").execute()
    print(data)
1
pythongoogle-sheetsgoogle-sheets-apigoogle-api-python-client
In your title, from How to get only filtered columns from google sheets to python script using google API?, it seems that you want to retrieve only filtered columns. But in your question, from I'm now interested in grabbing all the rows after setting this filter., it seems that you want to retrieve rows. So I cannot understand about your goal. I apologize for this. Can I ask you about the detail of your goal? – Tanaike
I'm really sorry about that mistake. I meant rows. So for example, I have selected only rows with TEXT_EQ value "Open", I want to fetch all the rows with value "Open" in certain column. Is this clear now or I need to elaborate more? – Ruchit Patel
Thank you for replying. From your replying, I proposed an answer. Could you please confirm it? If that was not the result you expect, I apologize. – Tanaike

1 Answers

2
votes

I believe your goal as follows.

  • From the following your replying,

    I meant rows. So for example, I have selected only rows with TEXT_EQ value "Open", I want to fetch all the rows with value "Open" in certain column. Is this clear now or I need to elaborate more?

  • You want to retrieve the showing rows from the filtered sheet.

  • You are using googleapis for python, and you have already been able to get and put values for Google Spreadsheet using Sheets API.

  • From I don't want to run for loop on fetched data., you want to achieve this without retrieving from all data by filtering with a script. You want to achieve this by only one API call.

In this case, I would like to propose to achieve your goal using the Query Language. When Query Language is used, the showing rows can be directly retrieved by one call. The sample script is as follows.

Sample script:

Before you use this script, please set the Spreadsheet ID and sheet ID. And, this sample script uses creds of service = build('sheets', 'v4', credentials=creds) for retrieving the access token. So please include your authorization script.

# In this sample script, the access token is retrieved from "creds".
# service = build('sheets', 'v4', credentials=creds)

spreadsheet_id = "###" # Please set the Spreadsheet ID.
sheet_id = "0"  # Please set the sheet name.

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 ' + creds.token})
ar = [row for row in csv.reader(io.StringIO(res.text), delimiter=',')]
# ar.pop(0) # When you want to remove the header row, you can also use this.
print(ar)
  • In this sample, import csv, import io and import requests are also used.

Reference: