如何使用Python访问(读取和写入)Google表格电子表格? [英] How do I access (read, write) Google Sheets spreadsheets with Python?
问题描述
我想知道您是否可以为我提供一个使用python读取/写入google文档/电子表格的示例.
I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python.
我确实在此处 https://developers.google.com/google上查看了Google文档API -apps/spreadsheets/，但不确定我是否点击了正确的链接.另外一个例子也会有很大帮助.
I did look at google docs API here https://developers.google.com/google-apps/spreadsheets/ but not sure if I hit the right link. Also an example will be of great help.
我想做的是根据不同的列查询电子表格，更像是SQL查询，然后对数据进行一些下游解析，然后将其放入Google文档中的另一个电子表格或文档中.
What I am trying to do is query spreadsheets based on the different columns more like a SQL query and then do some downstream parsing with the data and put it into another spreadsheet or doc at google docs.
最好， -阿比
推荐答案
(2016年6月12日)现在，这里的大多数答案已过期:1)乔什·布朗查找旧的GData Docs API文档.尽管并非所有GData API都已被弃用，但所有更新 Google API 不会不使用 Google数据协议；和2)Google 发布了新的Google Sheets API (不是GData).为了使用新的API，您需要获取 Python的Google API客户端库(对于Python 3，它就像pip install -U google-api-python-client [或pip3一样简单))，并使用最新的 Sheets API v4 + ，功能更强大&比旧版API更加灵活.
(Jun-Dec 2016) Most answers here are now out-of-date as: 1) GData APIs are the previous generation of Google APIs, and that's why it was hard for @Josh Brown to find that old GData Docs API documentation. While not all GData APIs have been deprecated, all newer Google APIs do not use the Google Data protocol; and 2) Google released a new Google Sheets API (not GData). In order to use the new API, you need to get the Google APIs Client Library for Python (it's as easy as pip install -U google-api-python-client [or pip3 for Python 3]) and use the latest Sheets API v4+, which is much more powerful & flexible than older API releases.
以下是官方文档中的一个代码示例，可帮助您入门.但是，这里有更长的，更真实的使用API的示例(您可以从中学习视频和博客文章):
Here's one code sample from the official docs to help get you kickstarted. However, here are slightly longer, more "real-world" examples of using the API you can learn from (videos plus blog posts):
- Migrating SQL data to a Sheet plus code deep dive post
- Formatting text using the Sheets API plus code deep dive post
- Generating slides from spreadsheet data plus code deep dive post
- Those and others in the Sheets API video library
最新的Sheets API提供了较旧版本中不可用的功能，即为开发人员提供了以编程方式访问Sheet的功能，就好像您正在使用用户界面一样(创建冻结的行，执行单元格格式设置，调整行/列的大小，添加数据透视表，创建图表等)，但并不是好像您可以对数据库进行搜索并从中获取选定的行一样.基本上，您必须在执行此操作的API之上构建一个查询层.一种替代方法是使用 Google Charts Visualization API查询语言，该语言支持类似SQL的查询.您也可以在表格内查询本身.请注意，此功能在v4 API之前存在，并且 Google Developer Expert .
The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (create frozen rows, perform cell formatting, resizing rows/columns, adding pivot tables, creating charts, etc.), but NOT as if it was some database that you could perform searches on and get selected rows from. You'd basically have to build a querying layer on top of the API that does this. One alternative is to use the Google Charts Visualization API query language, which does support SQL-like querying. You can also query from within the Sheet itself. Be aware that this functionality existed before the v4 API, and that the security model was updated in Aug 2016. To learn more, check my G+ reshare to a full write-up from a Google Developer Expert.
还要注意，Sheets API主要用于以编程方式访问电子表格操作&功能如上所述，但要执行文件级的访问，例如导入/导出，复制，移动，重命名等，请使用
Also note that the Sheets API is primarily for programmatically accessing spreadsheet operations & functionality as described above, but to perform file-level access such as imports/exports, copy, move, rename, etc., use the Google Drive API instead. Examples of using the Drive API:
- 在Google云端硬盘中列出您的文件并进行深度潜水编码="http://wescpy.blogspot.com/2014/11/authorized-google-api-access-from-python.html" rel ="nofollow noreferrer">发布
- Google云端硬盘:正在上传&正在下载文件以及穷人的纯文本格式转换为PDF转换器"代码帖子(*)
- 将Google表格导出为CSV博客发布仅
- Listing your files in Google Drive and code deep dive post
- Google Drive: Uploading & Downloading Files plus "Poor man's plain text to PDF converter" code deep dive post (*)
- Exporting a Google Sheet as CSV blog post only
(*)-TL; DR:将纯文本文件上传到云端硬盘，导入/转换为Google文档格式，然后将该文档导出为PDF.以上发布内容使用Drive API v2； 此后续帖子描述了如何将其迁移到Drive API v3，这是一个开发者视频合并两个穷人的converter依者"帖子.
(*) - TL;DR: upload plain text file to Drive, import/convert to Google Docs format, then export that Doc as PDF. Post above uses Drive API v2; this follow-up post describes migrating it to Drive API v3, and here's a developer video combining both "poor man's converter" posts.
要了解有关一般如何将Google API与Python结合使用的更多信息，请查看我的博客以及各种Google开发者视频(系列1 和
To learn more about how to use Google APIs with Python in general, check out my blog as well as a variety of Google developer videos (series 1 and series 2) I'm producing.
ps.就 Google文档而言，目前尚没有REST API，因此以编程方式访问文档的唯一方法是使用简介视频.)使用Apps脚本，您可以构建一个文档应用或
ps. As far as Google Docs goes, there isn't a REST API available at this time, so the only way to programmatically access a Doc is by using Google Apps Script (which like Node.js is JavaScript outside of the browser, but instead of running on a Node server, these apps run in Google's cloud; also check out my intro video.) With Apps Script, you can build a Docs app or an add-on for Docs (and other things like Sheets & Forms).
更新2018年7月:以上"ps".不再是真的. G Suite开发人员团队在Google Cloud NEXT '18上宣布了新的Google Docs REST API.有兴趣加入新API的抢先体验计划的开发人员应在 https://developers.google.com/上进行注册.文档.
UPDATE Jul 2018: The above "ps." is no longer true. The G Suite developer team pre-announced a new Google Docs REST API at Google Cloud NEXT '18. Developers interested in getting into the early access program for the new API should register at https://developers.google.com/docs.
更新2019年2月:去年7月启动预览的Docs API现已向所有人开放...阅读
UPDATE Feb 2019: The Docs API launched to preview last July is now available generally to all... read the launch post for more details.
2019年11月更新:为了使G Suite API和GCP API彼此更加内联，今年早些时候，所有G Suite代码示例均已部分集成了GCP的较新版本(较低级别的未集成)产品)Python客户端库.身份验证的方式类似，但是(当前)需要更多的代码来管理令牌存储，这意味着不是由我们的库来管理storage.json，而是使用pickle(token.pickle或您喜欢的任何名称)进行存储)，或者选择您自己的永久性存储形式.在这里为您的读者看一下更新的Python快速入门示例
UPDATE Nov 2019: In an effort to bring G Suite and GCP APIs more inline with each other, earlier this year, all G Suite code samples were partially integrated with GCP's newer (lower-level not product) Python client libraries. The way auth is done is similar but (currently) requires a tiny bit more code to manage token storage, meaning rather than our libraries manage storage.json, you'll store them using pickle (token.pickle or whatever name you prefer) instead, or choose your own form of persistent storage. For you readers here, take a look at the updated Python quickstart example.
这篇关于如何使用Python访问(读取和写入)Google表格电子表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
