如何使用Google API仅将经过过滤的行从Google表格获取到python脚本? [英] How to get only filtered rows from google sheets to python script using google API?
问题描述
我创建了一个python脚本，用于在我的Google表格上设置过滤器.此筛选器按预期方式工作.我现在对设置此过滤器后抓取所有行感兴趣.但是，当我得到api调用时，我得到了所有行.
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.
当我拨打电话时，我认为在提供范围方面犯了一个错误.我以 Sheet1！A:J 的格式提供范围.此范围指示从A到J列中的所有数据.但是，我不知道如何仅提供与过滤后的数据相对应的范围.我已经在这篇文章中介绍了Google应用程序脚本中的一种解决方案(
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?
我目前是这样做的
# 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)
推荐答案
我相信您的目标如下.
-
在以下您的回复中，
From the following your replying,
我的意思是行.因此，例如，我仅选择了TEXT_EQ值为"Open"的行，我想获取所有值为"Open"的行.在某些列中.现在清楚了吗，还是我需要详细说明?
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.
您正在将googleapis用于python，并且已经能够使用Sheets API获取和放置Google Spreadsheet的值.
You are using googleapis for python, and you have already been able to get and put values for Google Spreadsheet using Sheets API.
从我不想对获取的数据运行循环.，您想要实现这一点而不通过使用脚本过滤从所有数据中检索出来.您只想通过一个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.
在使用此脚本之前，请设置电子表格ID和工作表ID.并且，此示例脚本使用 service = build('sheets'，'v4'，凭据= creds)的 creds 来检索访问令牌.因此，请包括您的授权脚本.
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)
- 在此示例中，还使用了
import csv，import io和import request.
这篇关于如何使用Google API仅将经过过滤的行从Google表格获取到python脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
