如何使用Python访问（读取，写入）Google表格电子表格？ [英] How do I access (read, write) to Google Sheets spreadsheets with Python?

问题描述

我想知道是否可以指向我使用python读取/写入/来自Google文档/电子表格的示例。

我在这里查看了Google文档API https://developers.google.com/google-apps/spreadsheets/ ，但不知道我是否点击了正确的链接。另外一个例子会有很大的帮助。

我试图做的是基于不同列的查询电子表格，比如SQL查询，然后做一些下游解析这些数据并将其放入另一个电子表格或doc文档中。

Best，
-Abhi

（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，您需要获取 Python API的Google API客户端库（对于Python 3来说，就像 pip install -U google-api-python-client [或 pip3 一样简单]），并使用最新的 Sheets API v4 + ，它功能更强大，比旧版API更灵活。

以下是一个代码示例来自官方文档，以帮助您启动。但是，使用可以学习的API（视频和博客文章）的API可能会稍微更长一些，更真实一些：

• 将SQL数据迁移到工作表加上代码深入文章


• 使用Sheets API格式化文字以及深度潜水代码张贴


• 从电子表格数据生成幻灯片以及深层代码 post

最新的Sheets API提供的功能不可用在较早的版本中，即让开发人员可以像使用用户界面一样编程访问工作表（创建冻结行，执行单元格格式化，调整行/列大小，添加数据透视表，创建图表等），但不是是一些您可以执行搜索并从中获取选定行的数据库。你基本上必须在这个API的基础上构建一个查询层。另一种方法是使用 Google Charts Visualization API查询语言，它支持类似于SQL的查询。您也可以从工作表内部进行查询。请注意，此功能在v4 API之前已存在，并且安全模型已在 2016年8月 中更新。要了解更多信息，请查看 G +转贴至完整写作 href =http://developers.google.com/experts =noreferrer> Google开发者专家。

另外请注意，Sheets API主要用于以编程方式访问电子表格操作&功能，但要执行文件 -level 访问，例如导入/导出，复制，移动，重命名等，请使用 Google Drive API 。使用Drive API的示例：

• 在Google云端硬盘中列出文件并深入代码发布


• Google云端硬盘：上传&下载文件加上穷人的纯文本到PDF转换器代码深潜发布（*）


• 将Google表单导出为CSV博客
  

- TL; DR：将纯文本文件上传到云端硬盘，导入/转换为Google文档格式，然后将该文档导出为PDF。上面的帖子使用Drive API v2; 此后续文章描述了将其迁移到Drive API v3，以及开发人员视频

a>合并穷人转换器帖子。

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

ps。至于 Google文档，目前没有可用的REST API，因此以编程方式访问文档的唯一方法是使用 Google Apps Script （其中Node.js就是JavaScript以外的浏览器，但不是运行在Node服务器上，而是运行在Google的云中;也是请查看我的介绍视频）。使用Apps脚本，您可以构建 Google文档应用或 Google文档附加组件（以及其他诸如表格和表格）。

I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python.

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.

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.

Best, -Abhi

解决方案

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

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

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.

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:

• 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: 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.

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

这篇关于如何使用Python访问（读取，写入）Google表格电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！