通过API操作google sheets
Whisper Lv4
  2018-12-10 12:20:02 2018-12-10 12:20  

起源：最近了使用flask和bootstrap写了测试小工具，数据全部使用excel存储，部署到测试环境。
问题：每次每个人在使用excel数据时都需要重新编辑好的excel通过upload按钮传到服务器，然后再选择自己上传的那个文件名，使用本地xlsx的缺点是操作太过于繁琐，且对于实时协作太不方便。
优化：实际使用场景是，多人需要使用不同的数据，每个人最好是维护一份excel文档，多人协作excel国外使用google sheets，国内的也有在线协作的表格

官网资料

API文档

环境准备

  1. 打开注册api项目页面，在谷歌开发者控制台创建或选择一个项目，点击继续。
  2. 在证书添加页面，点击取消按钮。
  3. 点击顶部的tab按钮OAuth同意屏幕按钮，选择邮件地址，填写向用户显示的产品名称，点击保存。
  4. 选择app类型为其它，输入项目名称，如：test，点击创建按钮。
  5. 弹出窗上点确定关闭结果弹窗。
  6. 点击生成的json文件右边的下载按钮，保存该认证文件，重命名为：client-secret.json。

安装gdata-python-client

1
pip install --upgrade google-api-python-client

更多安装选项

首次运行生成credentials.json

过程：首次运行google sheets的时候，会寻找credentials.json文件，如果没有会主动打开浏览器，登陆验证后，会自动下载该文件。

步骤

步骤1 运行官方的示例
步骤2 命令行显示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 的字符串，它会引用电子表格中的一组单元格，并且一般用于公式。

例如，有效的范围如下所示：

1
2
3
4
5
6
7
8
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 中的所有单元格。

也支持命名范围。 如果某个命名范围与一个工作表的名称冲突，命名范围的优先级更高。

注意：按照最后有值的读取，如果中间有空的则会读取为空字符串，但是如果最后一个有值的后面都是空，哪怕指定的范围包含该单元格也不会读取到。见官方说明,搜索空白的拖尾行和列将被忽略。, 如果想要读取这些空白，则需要在要读取的最后一个单元格加上一个任意的字符.

读取操作

读取数据

使用values().get

1
2
3
4
5
6
7
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

具体查看Method: spreadsheets.get

读取数据

查看如上代码：

1
2
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修改为

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
2
3
4
5
6
7
8
9
10
11
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

查看基本写入示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

执行结果：

1
2
3
4
5
6
7
{
  '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

基本结构

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
"sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "user",
        "index": 0,
        ......
    }
    {
      "properties": {
        "sheetId": 2141796593,
        "title": "school",
        .......
        }
      }
  ]

指定范围：不包含末尾值
fields: 指定写的改变属性，哪些需要真正应用改变,fields masks.

如下图，需要获取绿色部分的范围值

1
2
3
4
5
6
7
"range": {
                    "sheetId": 0,
                    "startRowIndex": 2,  #行数要减一，从0开始
                    "endRowIndex": 4,
                    "startColumnIndex": 1,
                    "endColumnIndex": 3
                }

updateSheetProperties

1
2
3
4
5
6
7
8
9
10
11
{
      "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

1
2
3
4
5
6
7
8
{
  "range": {
    object(GridRange)
  },
  "rule": {
    object(DataValidationRule)
  },
}

GridRange
DataValidationRule

试调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
2
3
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：

1
scope = 'https://www.googleapis.com/auth/spreadsheets'

步骤2：然后，删除本地已有的credentials.json，然后运行修改过后的脚本，重新生成credentials.json文件，新生成的credentials.json就是读写权限都有的了。

注意：步骤2，很重要，很重要，很重要。

  • 本文标题：通过API操作google sheets
  • 本文作者：Whisper
  • 创建时间：2018-12-10 12:20:02
  • 本文链接：2018/12/10/python/通过api操作google_sheets/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  1. 官网资料
  2. 环境准备
  3. 安装gdata-python-client
  4. 首次运行生成credentials.json
    1. 步骤
  5. A1 表示法
  6. 读取操作
    1. 读取数据
  7. 获取所有sheets
  8. 读取数据
  9. 加工数据
  10. 写入数据
    1. 写入中文
  11. 总结
  12. 格式化表格-修改表格属性
    1. 基本结构
    2. 具体结构
    3. updateSheetProperties
    4. repeatCell
    5. UserEnterFormat
  13. setDataValidation
  14. 试调API
  15. 错误集锦
    1. insufficient authentication scopes