串接Google Sheet API 操作試算表運算

線上表單越來越普及方便,有許多重複性高的行為總希望透過程式達到一個自動化流程,這篇來分享要怎麼透過Python操過API進行試算表中的運算讀寫。

Cindy C
10 min readMar 16, 2021
串接Google Sheet API 操作試算表運算

💻 工具選擇

以下有兩個套件在程式中使用來連接spreadsheet。

☑️ Install

pip install gspread oauth2client

1. gspread

gspread 是一個為了使用Google Sheets的Python API,讓Python 可以開啟電子表格、讀寫編輯、和控制訪問及共享權等。

2. oauth2client

oauth2client 提供一個方法向Google API進行客戶身份驗證,但在2018年之後官方已經不更新 oauth2client 這個函式庫了,目前官方建議改為使用 google-authoauthlib

3. Google Sheets API

再來最重要的就是 Google Sheets API,有憑證後才能驗證訪問者身份,從外部程式連結到試算表,畢竟不能讓來路不明的應用程式訪問、編輯你的個人表單啊!

憑證建立完成後要妥善保管喔,避免流入不明程式而被盜訪。

下面會詳細說明API啟用和建立憑證的步驟,流程有點繁瑣但一步步操作很快就能完成了,開始吧!

首先,進到 Google API Console 登入個人的使用者帳戶後就可以建立一個新專案,點選流程如下:

1 資訊主頁 → 點選2中的選取專案小箭頭 → 3 新增專案 → 輸入自己定義的專案名稱後建立 → 在4 專案中看到剛剛建立好的專案名稱

Google API專案建立流程
新增專案細節

資訊主頁選擇左上角箭頭進入剛剛建立的專案,再點選 +啟用API和服務

啟用API服務

搜尋 Google Sheets API 後選擇啟用。

Google Sheets API啟用

API啟用後就要建立憑證,獲得一組金鑰後續提供Google Sheet認證來訪使用者權限和身份, Google Sheets API 啟用後點選左邊選單中的 憑證 建立憑證

建立憑證步驟

按照所需要的憑證類型進行一些基本設定後,點選 我需要哪些憑證?

憑證設定1

進到下個頁面輸入服務帳戶名稱,這邊可以任意取名,不用特地和專案名稱一致,再來輸入角色 設定為擁有者; 服務帳戶ID 會依照帳戶名稱自動生成,金鑰類型類型選擇JSON檔案格式是目前C覺得滿方便的形式。

憑證設定2

按下確認後就完成服務帳戶的建立並得到一份可以下載的JSON檔案,可以將它存起來後保存在雲端硬碟中,避免弄丟喔!

憑證金鑰與帳戶設定完成

開始Coding前有幾個設定要先完成,首先,在剛剛下載下來的JSON憑證檔案放到等等要編寫程式碼的路徑下,像下面這樣的階層。

-Test(folder)
-credentials.json
-apicode.py

在JSON中找到 client_email(下圖紅色方框處),並複製後面一串類似email的文字。

憑證內容

接著,創建一個新的試算表後,點選右上角的共享,將複製的文字新增至共同編輯者中。

新增共享者

以上,就萬事俱備,只欠coding了!

✍️ Coding

一開始要確認憑證,並透過程式連接到Google Sheet的方式有三種:

1️⃣ 方法一

直接使用 oauth2client 來確認憑證,接著由 gspread 經由憑證連接上目標的試算表,接著,要從試算表的URL中找到中間這串Google Sheet 金鑰(Key)。

URL中的Key

得到金鑰後就可以使用 open_by_key 連接到表單路徑了,程式碼片段如下:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
sheet = client.open_by_key("11vUSw8F7zyhMABkXCkHMv5R5nKTwRgZcsPtXiNaA5BA").sheet1

2️⃣ 方法二

第二種方法也是使用 oauth2client ,不過最後開啟試算表時改成指定試算表名稱,若此時試算表中有多個分頁表也可以指定分頁,向下圖要指定我設定的Google Sheet trans2sheet2 分頁中。

指定試算表名稱和分頁

使用 openworksheet就可以正確指定想要編輯的試算表分頁。

import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
sheet = client.open('trans2').worksheet('sheet2')

3️⃣ 方法三

第三種方式更簡潔,只需要使用到 gspread複製整串Google Sheet URL就可以指定要編輯第幾個分頁的試算表。在下列範例中的 get_worksheet若設定 0就會連到第一個分頁 sheet2,,而設定 1就會連接到第二個分頁 sheet3

import gspread
creds = gspread.service_account(filename='credentials.json')
client = creds.open_by_url(
"https://docs.google.com/spreadsheets/d/11vUSw8F7zyhMABkXCkHMv5R5nKTwRgZcsPtXiNaA5BA/edit#gid=0")
sheet = client.get_worksheet(0) # get the first sheet, idx=0

透過Python依然可以操作Google Sheet中的內建的運算式喔!

現在開始是在試算表中的操作,可以用 insert_row指定要寫入的字串要從第一行的哪一列,範例程式分別將三行字串寫進 A2:A4列。

# insert stringsheet.insert_row(["english", "chinese(zh-cn)", "chinese(zh-tw)"], 1)
sheet.insert_row(["hello"], 2)
sheet.insert_row(["world!"], 3)
sheet.insert_row(["coding"], 4)

第二步來示範怎麼使用Google Sheet內建的翻譯運算式 =GOOGLETRANSLATE(A2, "en", "zh-tw") 分別將 A2:A4的英文字串轉成簡體中文、繁體中文並寫入 B2:B4C2:C4表格中。

# translate EN to zh-cn/zh-twfor insert_idx in range(2,5):
sheet.update(f"B{insert_idx}", f'=GOOGLETRANSLATE(A{insert_idx}, "en", "zh-cn")', raw=False)
sheet.update(f"C{insert_idx}", f'=GOOGLETRANSLATE(A{insert_idx}, "en", "zh-tw")', raw=False)
insert string & translate results

再來示範一個數字類的運算式,分別在 A2:A4中寫入10,20,30,使用 =COUNTIF()的運算方式來計數>10的欄位有幾格並寫入 B1格中。

# calculatesheet.insert_row(["numbers", "countif>10"], 1)
sheet.insert_row([10], 2)
sheet.insert_row([20], 3)
sheet.insert_row([30], 4)
sheet.update(f"B1", f'=COUNTIFS(A2:A4,">10")', raw=False)
calculate results

最後一個範例,就來分享一下怎麼自動化將Google Sheet下載為 csv檔案!

使用 get_all_values() 可以獲得整個試算表的內容病存在一個list中,再將list轉為 PandasDataFrame 就可以存檔啦~

csv file

以下,附上完整程式碼~包含使用方法二get Google Sheet client,然後依序完成輸入字串、翻譯、計算、下載為csv檔案這五個流程喔!

💛 後記

Google Sheets一直是個很方便的工具,但是有時候大量資料要處理難免想要將流程自動化,這篇就和大家分享幾個實用的小工具,當然還有很多Google Sheets內建的運算式都可以如法炮製地加以應用,祝各位都能使用的得心應手啦~下次見 🙌

❕想從GitHub看到上述程式碼可以前往 GitHub

歡迎指教或分享您的經驗唷! 若小小心得有幫上您,請在底下長案拍手給予鼓勵,謝謝您的閱讀~

--

--

Cindy C
Cindy C

Written by Cindy C

熱愛嘗試新事物,從機器人跳到人工智慧領域的工程師,分享生活體驗,分享專業~

No responses yet