Google Colab: 用gspread處理Google Spreadsheet的資料

Table of Contents

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

可以透過檔案名稱keyurl,這三種方式去讀取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(工作表ID)來讀取特定的sheet。

# 讀取表名為data的工作表
sheet = workbook.worksheet('data')

# 讀取第一個工作表
sheet = workbook.get_worksheet(0)

# 讀取ID為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_recordsget_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

# 讀取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 & 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來執行單一修改
updateA1 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_cellupdate_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)

參考資料

gspread