Google API 学习

Python 修改 Google sheet

官方文档
记录一下自己调用 Google Api 的方法。

几个重要的概念

  1. spreadsheetId 整个总表的 ID 是很长的一串字符
  2. sheetId 单页的 ID 是纯数字

Get

获取数据get 方法

1
2
3
4
5
6
7
SAMPLE_SPREADSHEET_ID = spreadsheetId
SAMPLE_RANGE_NAME = 'feiyang!G1:G4'

sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range=SAMPLE_RANGE_NAME).execute()
values = result.get('values', [])

Append Data

1
2
3
4
5
6
7
8
9
10
11
12
range_ = 'capacity-raw!A:E'  # 表内的页名称 ! 范围
value_input_option = 'USER_ENTERED'
insert_data_option = 'INSERT_ROWS'

value_range_body = {
"range": "capacity-raw!A:E",
"values": getdata.get_data(today,product),
"majorDimension": "ROWS"
}

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body)
response = request.execute()

Update Data

举个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SAMPLE_SPREADSHEET_ID = 'xxxxxxxxx'
SAMPLE_RANGE_NAME = 'daily_report!A9:D9'
value_input_option = "RAW" # 还有其他的方式
value_body = {
"majorDimension": "ROWS",
"range": "daily_report!A9:D9",
"values": [["test","123","a","b"]],
}



sheet = service.spreadsheets()
result = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range=SAMPLE_RANGE_NAME, valueInputOption=value_input_option,body=value_body)

response = result.execute()
pprint(response)

Sheet Operation

删除行,插入行,复制一行,最重要的是 post body 格式。 官方文档写得不够详细。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
delete_body ={
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": 233333333,
"dimension": "ROWS",
"startIndex": 1,
"endIndex": 2
}
}
},
],
}

insert_body ={
"requests": [
{
"insertDimension": {
"range": {
"sheetId": 233333333,
"dimension": "ROWS",
"startIndex": 8,
"endIndex": 9
}
}
},
],
}


copy_body ={
"requests": [
{
"copyPaste": {
"source": {
"sheetId": 233333333,
"startRowIndex": 6,
"endRowIndex": 7,
"startColumnIndex": 1,
"endColumnIndex": 5
},
"destination": {
"sheetId": 1172952310,
"startRowIndex": 7,
"endRowIndex": 8,
"startColumnIndex": 1,
"endColumnIndex": 5
},
"pasteType": "PASTE_NORMAL",
"pasteOrientation": "NORMAL"
}
}
]
}

然后是 Python post 部分

1
2
3
request = sheet.batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body_item)
response = request.execute()
pprint(response)