操作google_sheets
起源:最近了使用flask和bootstrap写了测试小工具,数据全部使用excel存储,部署到测试环境。
问题:每次每个人在使用excel数据时都需要重新编辑好的excel通过upload按钮传到服务器,然后再选择自己上传的那个文件名,使用本地xlsx的缺点是操作太过于繁琐,且对于实时协作太不方便。
优化:实际使用场景是,多人需要使用不同的数据,每个人最好是维护一份excel文档,多人协作excel国外使用google sheets,国内的也有在线协作的表格
官网资料
环境准备
- 打开注册api项目页面,在谷歌开发者控制台创建或选择一个项目,点击继续。
- 在证书添加页面,点击取消按钮。
- 点击顶部的tab按钮
OAuth同意屏幕
按钮,选择邮件地址,填写向用户显示的产品名称,点击保存。 - 选择app类型为其它,输入项目名称,如:test,点击创建按钮。
- 弹出窗上点确定关闭结果弹窗。
- 点击生成的json文件右边的下载按钮,保存该认证文件,重命名为:client-secret.json。
安装gdata-python-client
pip install --upgrade google-api-python-client
首次运行生成credentials.json
过程:首次运行google sheets的时候,会寻找credentials.json文件,如果没有会主动打开浏览器,登陆验证后,会自动下载该文件。
步骤
步骤1 运行官方的示例
步骤2 命令行显示:
no credentials.json file.
C:\Users\lunah\.virtualenvs\mercku_qa-TKqmE-c_\lib\site-packages\oauth2client\_helpers.py:255: UserWarning: Cannot access credentials.json: No such file or directory
warnings.warn(_MISSING_FILE_MESSAGE.format(filename))
Your browser has been opened to visit:
https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets&redirect_uri=http%3A%2F%2Flocalhost%3A8080%2F&response_type=code&client_id=418130056826-sg1hqp565gqcmcqr3c3dtbpiebnnme9j.apps.googleusercontent.com&access_type=offline
If your browser is on a different machine then exit and re-run this
application with the command-line parameter
--noauth_local_webserver
Authentication successful.
步骤3 此时浏览器自动打开,如果打开失败则手动复制链接浏览器打开
步骤4 登陆当前google账号,如果当前没有登陆,则需要登录,如果有多个账号则需要选择一个账号
步骤5 点击接受按钮,会自动下载credentials.json,位置默认生成在~/.credentials.json
,该存储位置是定义在代码中的get_credentials()函数中的,也可以修改为其它位置保存
步骤6 下载成功后浏览器会显示,查看~/.credentials.json
文件已经成功下载
A1 表示法
一些 API 方法需要以 A1 表示法表示的范围。 这是一个类似 Sheet1!A1:B2 的字符串,它会引用电子表格中的一组单元格,并且一般用于公式。
例如,有效的范围如下所示:
Sheet1!A1 指定单元格
Sheet1!A1:A1 指定单元格,同第一个
Sheet1!A1:B2 斜角指定范围
Sheet1!A:A 引用 Sheet1 第一列中的所有单元格。
Sheet1!1:2 引用 Sheet1 前两行中的所有单元格。
Sheet1!A5:A 引用 Sheet1 第一列中从第 5 行开始的所有单元格。
A1:B2 引用第一个可见工作表前两行中的前两个单元格。
Sheet1 引用 Sheet1 中的所有单元格。
也支持命名范围。 如果某个命名范围与一个工作表的名称冲突,命名范围的优先级更高。
注意:按照最后有值的读取,如果中间有空的则会读取为空字符串,但是如果最后一个有值的后面都是空,哪怕指定的范围包含该单元格也不会读取到。见官方说明,搜索
空白的拖尾行和列将被忽略。
, 如果想要读取这些空白,则需要在要读取的最后一个单元格加上一个任意的字符.
读取操作
读取数据
result = self._service.spreadsheets().values().get(spreadsheetId=spreadsheet_id,
range="name!A1:H5".execute()
values = result.get('values')
if not values:
print('No data.')
else:
return values
获取所有sheets
读取数据
查看如上代码:
spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
rangeName = 'Class Data!A2:E'
示例中的在线数据链接为https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
中间一串代码为spreadssheetId。
rangeName中的前面Class Data
为要读取的sheet名称
如果读取的是第一个sheet,则该name也可以省略,如A2:E
.
加工数据
通过不同的spreed_ID和该文档下面的sheet name获取数据。
如:
main函数中rangeName
修改为
rangeName '%s!%s:%s' % (sheetname, start, end)
start 和 end使用A1表示法获取范围。
values = result.get('values', [])
该values为二维数组,结构如:
[[a, b],[c, d],[c, d]]
使用命名元祖加工为便读取的数据:
from collections import namedtuple
col_names = ['name', 'age', 'sex'] # 自定义命名
Tu = namedtuple("Tu", col_names)
datas = []
for value in values:
tmp = Tu(*value)
datas.append(tmp)
# get data
for data in datas:
print data.name, data.age, data.sex
通过点操作取数据。
如需修改命名元组实例的_replace()
方法。
也可以使用构造dict的方法,但命名元组效率更好,字典存储需要更多的内存空间。
写入数据
Google Sheets API 提供的 spreadsheets.values 集合可以对值进行简单的读取和写入操作。
范围获取 | 方法 |
---|---|
单个范围 | spreadsheets.values.update |
多个范围 | spreadsheets.values.batchUpdate |
追加 | spreadsheets.values.append |
查看基本写入示例
values = [
[
1, 2
],
# Additional rows ...
]
body = {
'values': values
}
result = self._service.spreadsheets().values().update(
spreadsheetId=self.spreadsheet_id,
valueInputOption="RAW",
range="router!G2:H2",
body=body).execute()
print result
执行结果:
{
'spreadsheetId': u'1EOQQjudspn7ZFTcOKA7gVh9Pk3CUrQ8agVB6NJ2aqdk',
'updatedRange': u'router!G2:H2',
'updatedCells': 2,
'updatedRows': 1,
'updatedColumns': 2
}
写入中文
直接写入: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
基本结构
sheet_id = ''
reqs = {
'requests':[
{'updateSheetProperties': '...'},
{'repeatCell': '...'},
{'setDataValidation': '...'},
{'sortRange': '...'},
{'addChart': '...'}
]
}
Sheets.spreadsheets().batchUpdate(spreadsheetId=sheet_id, body=reqs).execute()
具体结构
sheetId:第一个id为0,但是注意后面的ID并不是1,2,3这样的规律数.是一串码,通过spreadsheet.get()获取
如下的sheetId是一串数字2141796593
"sheets": [
{
"properties": {
"sheetId": 0,
"title": "user",
"index": 0,
......
}
{
"properties": {
"sheetId": 2141796593,
"title": "school",
.......
}
}
]
指定范围:不包含末尾值
fields: 指定写的改变属性,哪些需要真正应用改变,fields masks.
如下图,需要获取绿色部分的范围值
"range": {
"sheetId": 0,
"startRowIndex": 2, #行数要减一,从0开始
"endRowIndex": 4,
"startColumnIndex": 1,
"endColumnIndex": 3
}
updateSheetProperties
{
"updateSheetProperties": {
"properties": {
"sheetId": sheetId,
"gridProperties": {
"frozenRowCount": 1
}
},
"fields": "gridProperties.frozenRowCount"
}
}
repeatCell
UserEnterFormat
就是用户输入的一些格式化啦。
rgba:这里的red,green,blue,alpha值在0-1之间,见文档,如果0-255的值要用0-1表示则使用:值/255得到。更改背景色,和字体颜色用到。
常用属性:
NumberFormat
TextFormat改变文本格式
setDataValidation
{
"range": {
object(GridRange)
},
"rule": {
object(DataValidationRule)
},
}
试调API
错误集锦
insufficient authentication scopes
如果直接使用的是google sheets官方示例文档quickstart.py,在写入数据是会报错:
Request had insufficient authentication scopes.
说明
google sheets api提供的权限有多种,具体查看authorizing中的scope说明表。
quickstart.py中使用的scope='https://www.googleapis.com/auth/spreadsheets.readonly'
,如下代码段
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'
只读权限,所以之前生成的credentials.json的权限是只读权限。
解决
步骤1:将代码中的scope修改为authorizing中的scope说明表中对应你想要的权限。
如,需要读写权限,修改scope:
scope = 'https://www.googleapis.com/auth/spreadsheets'
步骤2:然后,删除本地已有的credentials.json,然后运行修改过后的脚本,重新生成credentials.json文件,新生成的credentials.json就是读写权限都有的了。
注意:步骤2,很重要,很重要,很重要。
最新文章
- kindeditor-4.1.3工具使用技巧:如何在编辑区上传图片并保存绝对路径
- pyqt5
- alter table的用法
- 记录一款不错的插件fullpage.js
- 2013MPD上海6.22 PM 陆宏杰:通往卓越管理的阶梯 &; 6.23AM Ray Zhang 产品创新管理的十八般武艺
- ORM框架是什么
- linux上安装apache
- bzoj3272 3638
- 负载均衡之DNS轮询
- bootstrap学习笔记之为导航条添加标题、二级菜单及状态 http://www.imooc.com/code/3120
- TCP--telnet为何在127s后返回?
- arcgis server 中Web墨卡托投影与WGS-84坐标的转换
- 剑指Offer--图的操作
- video.js 一个页面同时播放多个视频
- laravel 更新
- call/apply以及this指向的理解
- nopcommerce 4.1 net core 学习1
- Oracle RAC 集群环境下日志文件结构
- MsSql 游标 修改字段两个表关联 表向另个表插入记录
- 杂谈微服务架构下SSO&;OpenAPI访问的方案。