Google Colab: 用gspread處理Google Spreadsheet的資料
Table of Contents
本文將介紹用gspread來處理Google Spreadsheet(工作表)。
主要內容為，檢查gspread版本與更新、連結Google Drive(Google硬碟)與驗證、gspread的基本使用。
Youtube
檢查gspread版本與更新
gspread是一個用來處理google spreadsheet的python程式庫。裡面提供了許多好用的API（應用工具），讓使用者可以迅速地操作Google spreadsheet。
Google Colab裡已預設安裝這個程式庫，但是版本並不是最新的，所以想要使用這個程式庫的新功能可能需要先更新版本。
查看版本
import gspread
print(gspread.__version__)
# output -> 3.0.1
更新版本
!pip install --upgrade gspread
# 如果已經載入過gspread，記得重新啟動runtime。
如果你在更新版本之前已經執行了import gspread的話，記得重新啟動runtime。
執行認證
執行認證讓使用者可以透過gspread存取Google Spreadsheet。
# 使用者認證，讓使用者可以透過gspread存取Google Spreadsheet
from google.colab import auth
from oauth2client.client import GoogleCredentials
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())
gspread的使用
接下來就是真正的主題，gspread的使用方法。
讀取Google Spreadsheet
可以透過檔案名稱、key、url，這三種方式去讀取Google Spreadsheet。
比方說，想要讀取的Google Spreadsheet的網址是
https://docs.google.com/spreadsheets/d/14WGAANNN-Dd94402Gre56GG/
（14WGAANNN-Dd94402Gre56GG的部分就是這個spreadsheet的key）
檔案名稱是my_spreadsheet。
workbookByKey = gc.open_by_key('14WGAANNN-Dd94402Gre56GG')
workbookByName = gc.open('my_spreadsheet')
workbookByUrl = gc.open_by_url('https://docs.google.com/spreadsheets/d/14WGAANNN-Dd94402Gre56GG/')
值得注意的是，若是檔案名稱有重複，也就是有多個檔案的名稱是一樣的時候，使用gc.open()會抓到第一個檔案的內容，因此若是要抓取特定的檔案時建議用其他的方式。
讀取sheet(工作表)
- 全部sheet:
workbook.worksheets - 表名:
workbook.worksheet - 位置序號:
workbook.get_worksheet - 工作表ID:
workbook.get_worksheet_by_id
先介紹如何讀取Spreadsheet內全部的sheet。
sheets = workbook.worksheets()
另外，也可以使用sheet name(工作表名)、index(工作表的位置序號), id(工作表ＩＤ)來讀取特定的sheet。
# 讀取表名為data的工作表
sheet = workbook.worksheet('data')
# 讀取第一個工作表
sheet = workbook.get_worksheet(0)
# 讀取ＩＤ為674349371的工作表
sheet = workbook.get_worksheet_by_id(674349371)
讀取cell(表格)
- 全部：
get_all_records,get_all_values - 範圍：
get_values,row_values,col_values - 單一：
cell,acell - 搜尋：
find,findall
先介紹讀取全部cell的方法。有兩種get_all_records、 get_all_values。
注意一下兩種方式的結果有所不同。
get_all_records & get_all_values
get_all_records會除了第一行以外，把其他每一行都轉換成dict物件。而dict中的鍵(key)就是第一行的值。
如同下面的範例。
sheet.get_all_records()
# output:
# [{'age': 14, 'id': 1, 'name': 'John', 'no': 2101},
# {'age': 14, 'id': 2, 'name': 'Tom', 'no': 2102},
# {'age': 15, 'id': 3, 'name': 'Andy', 'no': 2103},
# {'age': 15, 'id': 4, 'name': 'Mary', 'no': 2104},
# {'age': 14, 'id': 5, 'name': 'Lisa', 'no': 2105}]
get_all_values則是很單純地返回list物件。
sheet.get_all_values()
# output:
# [['id', 'no', 'name', 'age'],
# ['1', '2101', 'John', '14'],
# ['2', '2102', 'Tom', '14'],
# ['3', '2103', 'Andy', '15'],
# ['4', '2104', 'Mary', '15'],
# ['5', '2105', 'Lisa', '14']]
如果想要更方便查看工作表的內容，建議可以搭配pandas工具庫，將表格轉換成dataframe來處理。
import pandas as pd
values = sheet.get_all_values()
df = pd.DataFrame(values[1:], columns=values[0])
# 使用get_all_records的場合
# records = sheet.get_all_records()
# df = pd.DataFrame(records)
df
# output:
# id no name age
# 0 1 2101 John 14
# 1 2 2102 Tom 14
# 2 3 2103 Andy 14
# 3 4 2104 Mary 14
# 4 5 2105 Lisa 14
get_values
再來介紹用get_values取得特定範圍的cell值。
可以使用A1 notation的標記方式，就如同一般使用Excel, spreadsheet一樣，可以用類似A1:B1的描述來表示範圍。
如果不輸入參數的話，則會取得全部表格的值，作用如同get_all_values
# 讀取Ａ欄至Ｃ欄中的值
sheet.get_values('A:C')
# output:
# [['id', 'no', 'name'],
# ['1', '2101', 'John'],
# ['2', '2102', 'Tom'],
# ['3', '2103', 'Andy'],
# ['4', '2104', 'Mary'],
# ['5', '2105', 'Lisa']]
row_values & col_values
如果想要取得某一行的值，可以使用row_values函數來完成。
指定要取得行的序號即可。
比方說，要取得第二行表格的值，就輸入2作為參數，也就是row_values(2)。
sheet.row_values(2)
# output:
# ['1', '2101', 'John', '14']
相同地，若是要取得某一欄的值，可以使用col_values函數來完成。
指定要取得欄的序號即可。
比方說，要取得第二欄表格的值，就輸入2作為參數，也就是col_values(2)。
sheet.col_values(2)
# output:
# ['no','2101','2102','2103','2104','2105']
cell & acell
另外，gspread也有取得單一表格的方法。
使用cell函數，輸入行跟欄的序號，就可以取得gspread定義的cell物件。
cell物件中的value屬性就是我們要的值。
比方說，表格中第二行第三欄的值是'John'，可以用以下的方法取得。
sheet.cell(2, 3).value
# output:
# 'John'
如果想要用A1 notation的方式(也就是類似'A1:B1'的描述)來取得單一表格的值時，可以用acell函數。
下面用acell來取得第二行第三欄的值。
sheet.acell('C2').value
# output:
# 'John'
find & finall
gspread也提供一個好用搜尋表格的方法find。
透過find函數，可以設定搜尋關鍵字(也可用正則表達式)來搜尋特定表格。
find函數會返回第一個搜尋結果，如果要取得全部結果可以用findall函數。
cell = sheet.find('John')
cell
# output:
# <Cell R2C3 'John'>
cells = sheet.findall('14')
cells
# output:mysq
# <Cell R2C4 '14'>
# <Cell R3C4 '14'>
# <Cell R4C4 '14'>
# <Cell R5C4 '14'>
# <Cell R6C4 '14'>
修改cell(表格)值
update: 可以進行單一或範圍更新。update_cell,update_acell: 單一表格更新。update_cells: 範圍表格更新。
update
首先介紹用update來執行單一修改。
update用A1 notation的方式來指定表格。
下面將第一筆資料的no改成2201。
sheet.update('B2', '2201')
# result:
# id no name age
# 0 1 2201 John 16
# 1 2 2102 Tom 16
# 2 3 2103 Andy 16
# 3 4 2104 Mary 16
# 4 5 2105 Lisa 16
再來是範圍修改。
下面，把年齡通通改成16。
# 修改D2:D6的值
sheet.update('D2:D6', [[16], [16], [16], [16], [16]])
# result:
# id no name age
# 0 1 2201 John 16
# 1 2 2102 Tom 16
# 2 3 2103 Andy 16
# 3 4 2104 Mary 16
# 4 5 2105 Lisa 16
另外，也可以使用試算表中的函數描述來設定值，像是常用的SUM函數。
當要使用函數描述時，update函數中的raw參數要設為False，表示要使用試算表中的函數方程式。
下面示範使用SUM來計算年齡總和。
# 修改D7的值，將其設定為D2:D6的總和
sheet.update('D7', '=SUM(D2:D6)', raw=False)
# result:
# id no name age
# 0 1 2101 John 16
# 1 2 2102 Tom 16
# 2 3 2103 Andy 16
# 3 4 2104 Mary 16
# 4 5 2105 Lisa 16
# 80
update_cell & update_acell & update_cells
另外，gspread還提供了update_cell, update_acell, update_cells的方法來修改表格。
update_cell跟update_acell可以用來更新單一表格。
update_cell是用行欄的序號來指定目標表格。
update_acell是用A1 notation的方式來指定目標表格。
下面使用update_cell將第一筆資料的no改為2107。
sheet.update_cell(2, 2, 2107)
# result:
# id no name age
# 0 1 2107 John 16
# 1 2 2102 Tom 16
# 2 3 2103 Andy 16
# 3 4 2104 Mary 16
# 4 5 2105 Lisa 16
# 80
再來，介紹用update_cells執行範圍修改。
age_list = sheet.range('D2:D6')
for age in age_list:
age.value = 14
sheet.update_cells(age_list)