如何使用 Python 访问(读取、写入)Google Sheets 电子表格? [英] 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.

我确实在此处查看了 google docs API https://developers.google.com/google-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 docs 的文档中.

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 API 是上一代 Google API，这就是为什么 @Josh Brown 找到旧的 GData Docs API 文档.虽然并非所有 GData API 都已弃用，但所有较新的Google API不使用Google 数据协议；2) Google 发布了新的 Google Sheets API(不是 GData).为了使用新的 API，您需要获得 Google APIs Client Library for Python(就像 pip install -U google-api-python-client [或 pip3 for Python 3] 一样简单)并使用最新的 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):

• 将 SQL 数据迁移到工作表 加代码深入探讨 post
• 使用Sheets API 加上代码深入探讨 发布
• 从电子表格数据生成幻灯片 加码深入post
• Sheets API 视频库

• 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 之前，并且 安全模型已于 2016 年 8 月 更新.要了解更多信息，请查看 我的 G+ 转发到完整报道来自Google 开发者专家.

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 主要用于以编程方式访问电子表格操作 &功能如上所述，但要执行文件级访问(例如导入/导出、复制、移动、重命名等)，请使用Google Drive API.使用 Drive 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 云端硬盘中列出您的文件并编写代码深入探索post
• Google 云端硬盘:上传 &下载文件 加上穷人的纯文本到 PDF 转换器"代码深入探讨 post (*)
• 将 Google 表格导出为 CSV 博客 post 仅

• 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，这里有一个 开发者视频 结合两个穷人的转换器"帖子.

(*) - 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.

要详细了解如何在 Python 中使用 Google API，请查看我的博客以及各种 Google 开发者视频(系列 1 和 系列 2) 我正在制作.

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 Docs 而言，目前没有可用的 REST API，因此以编程方式访问 Doc 的唯一方法是使用 Google Apps Script(类似于 Node.js 是浏览器之外的 JavaScript，但这些应用程序不是在 Node 服务器上运行，而是在 Google 的云中运行；另请查看我的介绍视频.)使用 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 和 GCP API 更加一致，今年早些时候，所有 G Suite 代码示例都与 GCP 的较新(较低级别的不产品) Python 客户端库.身份验证的完成方式类似，但(目前)需要更多代码来管理令牌存储，这意味着您将使用 pickle<存储它们，而不是我们的库管理 storage.json/code> (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 Sheets 电子表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！