【译】如何应用最新版的谷歌表格 API
Python
16 minutes read (About 2466 words)

【译】如何应用最新版的谷歌表格 API

引言

本文将演示如何使用最新的 Google 表格 API. Google 在 2016 I/O 大会上发布了第四版的表格 API（博客，视频），与之前版本相比，新版增加了大量功能。现在，你可以通过 API v4 完成 Google 表格移动版和桌面版的大部分操作。

文章下面会通过 Python 脚本，一步步将一个玩具公司关系型数据库里的客户订单数据逐条读出，并写到一个 Google 表格中。其他会涉及到的 API 还有：新建 Google 表格、从表格中读取数据。

在之前的几篇文章中，我们已经介绍了 Google API 的结构和大致的使用说明，所以近期的文章会关注特定 API 在实际问题中的使用方法。如果你已经阅读过之前那篇，便可以从下面的授权范围开始，了解具体如何使用。

Google 表格 API 授权认证及权限范围

之前版本的 Google Sheets API（早期名为 Google Spreadsheets API）作为 GData API 组 的一部分，与其他 API 一起构建实现了较不安全的 Google Data (GData) 协议，以一种 REST 驱动的技术方式读写网络中的信息。而新版表格 API 已成为 Google APIs 中的一员，使用 OAuth2 方式认证，且利用 Google APIs 客户端库 降低了使用难度。

目前 API 提供两种授权范围：只读和读写。一般建议开发者根据用途尽量选择较多限制的授权范围。这样可以向用户请求较少的权限，用户更乐意一些，而且这样会令你的应用更加安全，防止可能的数据破坏，并可以预防流量及其他配额不经意地超出。在这个例子中我们需要创建表格并写入数据，所以_必须_选择『读写』授权。

使用 Google 表格 API

开始代码部分的讲解：从 SQLite 数据库读取记录，根据这些数据新建 Google 表格。

之前的文章和视频中已经包含了授权的完整例子，所以这里直接从创建表格的调用点开始。调用 apiclient.discovey.build() 函数，并传入 API 名字符串 'sheets' 和版本号字符串 'v4'。

1
SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))

有了表格服务的调用点，首先要做的就是新建一个空白的 Google 表格。在此之前，你需要知道一点：大多数的 API 调用都需要传入一个包含操作名和数据的 JSON 请求主体，随着使用的深入，你会越来越熟悉这一点。对于新建表格来说，JSON 主体就比较简单，不需要加入任何值，传进一个空的 dict 就行，但最好还是提供一个表格的名字，参照下面这个 data：

1
data = {'properties': {'title': 'Toy orders [%s]' % time.ctime()}}

注意表格的标题 “title” 是它属性 “properties” 的一部分，另外这里还给名字加上了当前的时间戳。完成主体的构建后，将其传入 spreadsheets().create() 并执行，完成空白表格的创建。

1
res = SHEETS.spreadsheets().create(body=data).execute()

另外，你还可以通过 Google Drive API (v2 或 v3) 来新建表格，但还需要传入 Google 表格（文件）的 MIME 类型:

1
2
3
4
5
data = {
    'name': 'Toy orders [%s]' % time.ctime(),
    'mimeType': 'application/vnd.google-apps.spreadsheet',
}
res = DRIVE.files().create(body=data).execute() # insert() for v2

一般来说如果你只需要进行表格的操作，那仅表格的 API 就已足够。但如果你还需要创建其他文件，或是操作其他 Drive 文件和文件夹，你才需要 Drive API。当然如果你的应用复杂，你也可以都用，或是结合其他 Google API 使用。但这里就只用到表格 API。

新建完表格后，获取并显示一些信息。

1
2
SHEET_ID = res['spreadsheetId']
print('Created "%s"' % res['properties']['title'])

你也许会问：为什么要先新建表格然后再另外调用 API 添加数据？为什么不能在新建表格的时候同时添加数据？这个问题的答案虽然是可以，但是这样做意味着你需要在创建表格的时候，构建一个包含整张表格所有单元格数据及其格式的 JSON，而且单元格的格式数据相当繁复，结构并不像一个数组这么简单（当然你可以尽管尝试）。所以才有了 spreadsheets().values() 的一系列相关函数，来简化仅针对表格数据的上传和下载。

现在再看看 SQLite 数据库文件（db.sqlite） 读写部分，你可以从 Google 表格 Node.js 代码实验 处获取该文件。下面的代码通过 sqlite3 标准库来连接数据库，读取所有记录，添加表头，并去除最后两列时间戳：

1
2
3
4
5
6
7
8
FIELDS = ('ID', 'Customer Name', 'Product Code', 'Units Ordered',
        'Unit Price', 'Status', 'Created at', 'Updated at')
cxn = sqlite3.connect('db.sqlite')
cur = cxn.cursor()
rows = cur.execute('SELECT * FROM orders').fetchall()
cxn.close()
rows.insert(0, FIELDS)
data = {'values': [row[:6] for row in rows]}

拿到表格主体（由记录组成的数组）后，调用 spreadsheets().values().update()，比如：

1
2
SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
    range='A1', body=data, valueInputOption='RAW').execute()

除了表格 ID 和数据主体之外，这个 API 调用还需要另外两个参数字段：一个是写入表格中的单元格位置范围（这里是左上角，记为 A1）。另一个是值的输入选项，用来定义数据该如何处理：是作为原始值（”RAW”），或用户输入值（”USER_ENTERED”），还是转换成字符串、数字。

从表格中读取行数据就比较简单，spreadsheets().values().get() 只需要传入表格 ID 和读取单元格的范围。

1
2
3
4
5
print('Wrote data to Sheet:')
rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
    range='Sheet1').execute().get('values', [])
for row in rows:
    print(row)

如果成功的话，会返回一个包含 'values' 键的 dict。get() 的默认值是一个空数组，这样在失败时，for 循环也不会出错。

如果你成功运行（末尾有附完整代码），并在浏览器 OAuth2 授权弹窗中同意 Google 表格修改权限的申请，你应该可以得到以下输出：

1
2
3
4
5
6
7
8
$ python3 sheets-toys.py # or python (2.x)
Created "Toy orders [Thu May 26 18:58:17 2016]" with this data:
['ID', 'Customer Name', 'Product Code', 'Units Ordered', 'Unit Price', 'Status']
['1', "Alice's Antiques", 'FOO-100', '25', '12.5', 'DELIVERED']
['2', "Bob's Brewery", 'FOO-200', '60', '18.75', 'SHIPPED']
['3', "Carol's Car Wash", 'FOO-100', '100', '9.25', 'SHIPPED']
['4', "David's Dog Grooming", 'FOO-250', '15', '29.95', 'PENDING']
['5', "Elizabeth's Eatery", 'FOO-100', '35', '10.95', 'PENDING']

总结

下面是完整脚本，兼容 Python2 和 Python3。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
'''sheets-toys.py -- Google Sheets API demo
    created Jun 2016 by +Wesley Chun/@wescpy
'''
from __future__ import print_function
import argparse
import sqlite3
import time

from apiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
    flow = client.flow_from_clientsecrets('client_id.json', SCOPES)
    creds = tools.run_flow(flow, store, flags)

SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))
data = {'properties': {'title': 'Toy orders [%s]' % time.ctime()}}
res = SHEETS.spreadsheets().create(body=data).execute()
SHEET_ID = res['spreadsheetId']
print('Created "%s"' % res['properties']['title'])

FIELDS = ('ID', 'Customer Name', 'Product Code', 'Units Ordered',
        'Unit Price', 'Status', 'Created at', 'Updated at')
cxn = sqlite3.connect('db.sqlite')
cur = cxn.cursor()
rows = cur.execute('SELECT * FROM orders').fetchall()
cxn.close()
rows.insert(0, FIELDS)
data = {'values': [row[:6] for row in rows]}

SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
    range='A1', body=data, valueInputOption='RAW').execute()
print('Wrote data to Sheet:')
rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
    range='Sheet1').execute().get('values', [])
for row in rows:
    print(row)

你可以根据你的需要修改定制这段代码，改成移动前端脚本、开发脚本、后端脚本，或是使用其他 Google API。如果你觉得例子太过复杂，可以看看这篇只涉及读取现有表格的快速入门 Python 部分。如果你熟悉 JavaScript，想做点更正式的东西，可以了解一下这个 Node.js 上手表格 API 代码实验，即上文中获取数据库文件的地方。本文就写到这里，希望能在最新表格 API 的入门了解上对你有所帮助。

附加题: 请自由尝试单元格格式化及其他 API 的功能。除了读写数值，API 还有很多功能，挑战一下你自己吧！

# Google, Google API, Google Sheets, Python
使用 Xposed 进行黑盒测试
Follow

Links

Categories

Tag Cloud

Android Front-End, Hexo Google Google API Google Sheets Java Python Reactive Xposed

Recent

Archives

Tags

Android 2
Front-End, Hexo 1
Google 1
Google API 1
Google Sheets 1
Java 2
Python 1
Reactive 1
Xposed 1

Recent

Archives

Tags

Android 2
Front-End, Hexo 1
Google 1
Google API 1
Google Sheets 1
Java 2
Python 1
Reactive 1
Xposed 1
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×