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

【译】如何应用最新版的谷歌表格 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 (v2v3) 来新建表格,但还需要传入 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' 键的 dictget() 的默认值是一个空数组,这样在失败时,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 还有很多功能,挑战一下你自己吧!

Your browser is out-of-date!

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

×