本文將介紹用gspread來處理Google Spreadsheet(工作表)。
主要內容為,檢查gspread
版本與更新、連結Google Drive(Google硬碟)與驗證、gspread
的基本使用。
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的使用方法。
可以透過檔案名稱、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()
會抓到第一個檔案的內容,因此若是要抓取特定的檔案時建議用其他的方式。
workbook.worksheets
workbook.worksheet
workbook.get_worksheet
workbook.get_worksheet_by_id
先介紹如何讀取Spreadsheet內全部的sheet。
sheets = workbook.worksheets()
另外,也可以使用sheet name(工作表名)、index(工作表的位置序號), id(工作表ID)來讀取特定的sheet。
# 讀取表名為data的工作表
sheet = workbook.worksheet('data')
# 讀取第一個工作表
sheet = workbook.get_worksheet(0)
# 讀取ID為674349371的工作表
sheet = workbook.get_worksheet_by_id(674349371)
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
會除了第一行以外,把其他每一行都轉換成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
取得特定範圍的cell值。
可以使用A1 notation的標記方式,就如同一般使用Excel, spreadsheet一樣,可以用類似A1:B1
的描述來表示範圍。
如果不輸入參數的話,則會取得全部表格的值,作用如同get_all_values
# 讀取A欄至C欄中的值
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
函數來完成。
指定要取得行的序號即可。
比方說,要取得第二行表格的值,就輸入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']
另外,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'
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'>
update
: 可以進行單一或範圍更新。update_cell
, update_acell
: 單一表格更新。update_cells
: 範圍表格更新。首先介紹用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
另外,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)