
起源:最近了使用flask和bootstrap写了测试小工具,数据全部使用excel存储,部署到测试环境。
问题:每次每个人在使用excel数据时都需要重新编辑好的excel通过upload按钮传到服务器,然后再选择自己上传的那个文件名,使用本地xlsx的缺点是操作太过于繁琐,且对于实时协作太不方便。
优化:实际使用场景是,多人需要使用不同的数据,每个人最好是维护一份excel文档,多人协作excel国外使用google sheets,国内的也有在线协作的表格
官网资料
环境准备
- 打开注册api项目页面,在谷歌开发者控制台创建或选择一个项目,点击继续。
- 在证书添加页面,点击取消按钮。
- 点击顶部的tab按钮
OAuth同意屏幕
按钮,选择邮件地址,填写向用户显示的产品名称,点击保存。 - 选择app类型为其它,输入项目名称,如:test,点击创建按钮。
- 弹出窗上点确定关闭结果弹窗。
- 点击生成的json文件右边的下载按钮,保存该认证文件,重命名为:client-secret.json。
安装gdata-python-client
1 | pip install --upgrade google-api-python-client |
首次运行生成credentials.json
过程:首次运行google sheets的时候,会寻找credentials.json文件,如果没有会主动打开浏览器,登陆验证后,会自动下载该文件。
步骤
步骤1 运行官方的示例
步骤2 命令行显示:
1 | no credentials.json file. |
步骤3 此时浏览器自动打开,如果打开失败则手动复制链接浏览器打开
步骤4 登陆当前google账号,如果当前没有登陆,则需要登录,如果有多个账号则需要选择一个账号
步骤5 点击接受按钮,会自动下载credentials.json,位置默认生成在~/.credentials.json
,该存储位置是定义在代码中的get_credentials()函数中的,也可以修改为其它位置保存
步骤6 下载成功后浏览器会显示,查看~/.credentials.json
文件已经成功下载
A1 表示法
一些 API 方法需要以 A1 表示法表示的范围。 这是一个类似 Sheet1!A1:B2 的字符串,它会引用电子表格中的一组单元格,并且一般用于公式。
例如,有效的范围如下所示:
1 | Sheet1!A1 指定单元格 |
也支持命名范围。 如果某个命名范围与一个工作表的名称冲突,命名范围的优先级更高。
注意:按照最后有值的读取,如果中间有空的则会读取为空字符串,但是如果最后一个有值的后面都是空,哪怕指定的范围包含该单元格也不会读取到。见官方说明,搜索
空白的拖尾行和列将被忽略。
, 如果想要读取这些空白,则需要在要读取的最后一个单元格加上一个任意的字符.
读取操作
读取数据
1 | result = self._service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, |
获取所有sheets
读取数据
查看如上代码:
1 | spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' |
示例中的在线数据链接为https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
中间一串代码为spreadssheetId。
rangeName中的前面Class Data
为要读取的sheet名称
如果读取的是第一个sheet,则该name也可以省略,如A2:E
.
加工数据
通过不同的spreed_ID和该文档下面的sheet name获取数据。
如:
main函数中rangeName
修改为
1 | rangeName '%s!%s:%s' % (sheetname, start, end) |
start 和 end使用A1表示法获取范围。
1 | values = result.get('values', []) |
该values为二维数组,结构如:
1 | [[a, b],[c, d],[c, d]] |
使用命名元祖加工为便读取的数据:
1 | from collections import namedtuple |
通过点操作取数据。
如需修改命名元组实例的_replace()
方法。
也可以使用构造dict的方法,但命名元组效率更好,字典存储需要更多的内存空间。
写入数据
Google Sheets API 提供的 spreadsheets.values 集合可以对值进行简单的读取和写入操作。
范围获取 | 方法 |
---|---|
单个范围 | spreadsheets.values.update |
多个范围 | spreadsheets.values.batchUpdate |
追加 | spreadsheets.values.append |
查看基本写入示例
1 | values = [ |
执行结果:
1 | { |
写入中文
直接写入:requets返回的数据,写入res.content即可,里面的中文能直接写入
总结
单个范围读写用get|update,多个范围读写用batchxxx.如果要对表格进行进一步的操作,如添加备注等,需要使用Access the API with Apps Script
格式化表格-修改表格属性
creating frozen rows
bolding cells
implementing currency formatting
performing cell validation
restricting cell values
基本结构
1 | sheet_id = '' |
具体结构
sheetId:第一个id为0,但是注意后面的ID并不是1,2,3这样的规律数.是一串码,通过spreadsheet.get()获取
如下的sheetId是一串数字2141796593
1 | "sheets": [ |
指定范围:不包含末尾值
fields: 指定写的改变属性,哪些需要真正应用改变,fields masks.
如下图,需要获取绿色部分的范围值
1 | "range": { |
updateSheetProperties
1 | { |
repeatCell
UserEnterFormat
就是用户输入的一些格式化啦。
rgba:这里的red,green,blue,alpha值在0-1之间,见文档,如果0-255的值要用0-1表示则使用:值/255得到。更改背景色,和字体颜色用到。
常用属性:
NumberFormat
TextFormat改变文本格式
setDataValidation
1 | { |
试调API
错误集锦
insufficient authentication scopes
如果直接使用的是google sheets官方示例文档quickstart.py,在写入数据是会报错:
1 | Request had insufficient authentication scopes. |
说明
google sheets api提供的权限有多种,具体查看authorizing中的scope说明表。
quickstart.py中使用的scope='https://www.googleapis.com/auth/spreadsheets.readonly'
,如下代码段
1 | SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly' |
只读权限,所以之前生成的credentials.json的权限是只读权限。
解决
步骤1:将代码中的scope修改为authorizing中的scope说明表中对应你想要的权限。
如,需要读写权限,修改scope:
1 | scope = 'https://www.googleapis.com/auth/spreadsheets' |
步骤2:然后,删除本地已有的credentials.json,然后运行修改过后的脚本,重新生成credentials.json文件,新生成的credentials.json就是读写权限都有的了。
注意:步骤2,很重要,很重要,很重要。
- 本文标题:通过API操作google sheets
- 创建时间:2018-12-10 12:20:02
- 本文链接:2018/12/10/python/通过api操作google_sheets/
- 版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!