串接Google Sheet API 操作試算表運算
線上表單越來越普及方便,有許多重複性高的行為總希望透過程式達到一個自動化流程,這篇來分享要怎麼透過Python操過API進行試算表中的運算讀寫。
💻 工具選擇
以下有兩個套件在程式中使用來連接spreadsheet。
☑️ Install
pip install gspread oauth2client
1. gspread
gspread
是一個為了使用Google Sheets的Python API,讓Python 可以開啟電子表格、讀寫編輯、和控制訪問及共享權等。
2. oauth2client
oauth2client
提供一個方法向Google API進行客戶身份驗證,但在2018年之後官方已經不更新 oauth2client
這個函式庫了,目前官方建議改為使用 google-auth
或 oauthlib
。
3. Google Sheets API
再來最重要的就是 Google Sheets API
,有憑證後才能驗證訪問者身份,從外部程式連結到試算表,畢竟不能讓來路不明的應用程式訪問、編輯你的個人表單啊!
憑證建立完成後要妥善保管喔,避免流入不明程式而被盜訪。
下面會詳細說明API啟用和建立憑證的步驟,流程有點繁瑣但一步步操作很快就能完成了,開始吧!
首先,進到 Google API Console 登入個人的使用者帳戶後就可以建立一個新專案,點選流程如下:
1 資訊主頁 → 點選2中的選取專案小箭頭 → 3 新增專案 → 輸入自己定義的專案名稱後建立 → 在4 專案中看到剛剛建立好的專案名稱
到資訊主頁選擇左上角箭頭進入剛剛建立的專案,再點選 +啟用API和服務
。
搜尋 Google Sheets API
後選擇啟用。
API啟用後就要建立憑證,獲得一組金鑰後續提供Google Sheet認證來訪使用者權限和身份, Google Sheets API
啟用後點選左邊選單中的 憑證
→ 建立憑證
按照所需要的憑證類型進行一些基本設定後,點選 我需要哪些憑證?
。
進到下個頁面輸入服務帳戶名稱
,這邊可以任意取名,不用特地和專案名稱一致,再來輸入角色
設定為擁有者; 服務帳戶ID
會依照帳戶名稱自動生成,金鑰類型
類型選擇JSON檔案格式是目前C覺得滿方便的形式。
按下確認後就完成服務帳戶的建立並得到一份可以下載的JSON檔案,可以將它存起來後保存在雲端硬碟中,避免弄丟喔!
開始Coding前有幾個設定要先完成,首先,在剛剛下載下來的JSON憑證檔案放到等等要編寫程式碼的路徑下,像下面這樣的階層。
-Test(folder)
-credentials.json
-apicode.py
在JSON中找到 client_email
(下圖紅色方框處),並複製後面一串類似email的文字。
接著,創建一個新的試算表後,點選右上角的共享
,將複製的文字新增至共同編輯者中。
以上,就萬事俱備,只欠coding了!
✍️ Coding
一開始要確認憑證,並透過程式連接到Google Sheet的方式有三種:
1️⃣ 方法一
直接使用 oauth2client
來確認憑證,接著由 gspread
經由憑證連接上目標的試算表,接著,要從試算表的URL中找到中間這串Google Sheet 金鑰(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 trans2
的 sheet2
分頁中。
使用 open
和 worksheet
就可以正確指定想要編輯的試算表分頁。
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:B4
及 C2: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)
再來示範一個數字類的運算式,分別在 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)
最後一個範例,就來分享一下怎麼自動化將Google Sheet下載為 csv
檔案!
使用 get_all_values()
可以獲得整個試算表的內容病存在一個list中,再將list轉為 Pandas
的 DataFrame
就可以存檔啦~
以下,附上完整程式碼~包含使用方法二get Google Sheet client,然後依序完成輸入字串、翻譯、計算、下載為csv檔案這五個流程喔!
💛 後記
Google Sheets一直是個很方便的工具,但是有時候大量資料要處理難免想要將流程自動化,這篇就和大家分享幾個實用的小工具,當然還有很多Google Sheets內建的運算式都可以如法炮製地加以應用,祝各位都能使用的得心應手啦~下次見 🙌
❕想從
GitHub
看到上述程式碼可以前往 GitHub
歡迎指教或分享您的經驗唷! 若小小心得有幫上您,請在底下長案拍手給予鼓勵,謝謝您的閱讀~