前言
Google Sheets 和傳統試算表最大的差異或許在於他的資料存放在遠端，所以可以從任何地方存取，也很方便和別人共享編輯。因為這個特性，使得我偶爾會將一些實驗資料放到 Google Sheets 上面。
有的時候這些實驗數據是隨著時間固定產生的，此時如果總是自己剪剪貼貼，難免會覺得有些浪費時間。幸好，Google 提供了 Sheets API，所以我們可以透過程式自動將資料寫進試算表裡。
gspread 將底層的 API 又包裝成更方便好用的界面，是使用 Python 來存取 Google Sheets 很好的選擇。
本文便使用 Python 3 配合 gspread 來展示寫入 Google Sheets 的方法。確切的來說，我將寫一個程式每天去某個網站檢查目前的記憶體價格，然後把每天查到的價格寫入一個試算表裡，藉此追蹤價格變化。
環境設置
首先使用 pyvenv 創立虛擬環境，好安裝自己的套件：
pyvenv-3.5 my_env
source my_env/bin/activate緊接著，安裝所有這次會用到的套件：
pip install beautifulsoup4 gspread oauth2client pyOpenSSL申請 API 用帳號
於是就開始參考教學，先申請一個可以用來存取 Google Sheets 的帳號。步驟如下：
- 先到 Google Developers Console 建立一個 project。
- 啟動該 project 的 Drive API。
- 在 Credentials 頁面按下 Create credentials -> Service account key。
- 取好名字，選擇 JSON 作為 Key type。
下載好 JSON 檔案，把他取名為 auth.json，之後會用到，它大概會長這樣：
{
"type": "service_account",
"project_id": "cool-236196",
"private_key_id": "ab18...24",
"private_key": "-----BEGIN PRIVATE KEY-----...-----END PRIVATE KEY-----\n",
"client_email": "name...@....gserviceaccount.com",
"client_id": "21..6",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/..."
}
建立試算表
接下來我們到 Google Sheets 建立一個試算表，同時將試算表分享給剛剛的 auth.json 裡 client_email 欄位提到的 email 帳號： name...@....gserviceaccount.com，並給予編輯的權限，這樣子才有辦法透過程式存取。
將試算表多餘的欄位刪除，只剩下 date, item, price 三欄，並留下新的一個空白行，好讓程式插入資料時，也能有白色背景。
最後我們把網址中 https://docs.google.com/spreadsheets/d/{key}/edit 的 {key} 複製到 spreadsheet_key 檔案裡，供之後程式要開啟試算表時存取。
抓取資料
這裡我們透過 BeautifulSoup 從某個價格看板挖出最便宜的記憶體價格當作指標指數。確切的來說，我們先搜尋「記憶體 RAM」關鍵字找出項目清單，然後一個一個搜尋價格，並紀錄最便宜的項目。
# update.py
import re
from urllib.request import urlopen
from bs4 import BeautifulSoup
def get_cheapest(url, text):
with urlopen(url) as response:
soup = BeautifulSoup(response.read(), 'lxml')
cheapest_price = cheapest_item = None
re_price = re.compile(r'\$(\d+)')
root = soup.find('td', text=re.compile(text)).parent
for option in root.find_all('option', text=re_price):
item = option.text.strip()
price = int(re_price.search(item).group(1))
if cheapest_price is None or price < cheapest_price:
cheapest_price = price
cheapest_item = item
return (cheapest_item, cheapest_price)
coolpc_url = 'http://www.coolpc.com.tw/evaluate.php'
ram_text = '記憶體 RAM'
(cheapest_item, cheapest_price) = get_cheapest(coolpc_url, ram_text)
更新 Google Sheets
接下來先撰寫帳號登入的部份，利用 oauth2client 中的 ServiceAccountCredentials 來讀取之前的 auth.json。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def auth_gss_client(path, scopes):
credentials = ServiceAccountCredentials.from_json_keyfile_name(path,
scopes)
return gspread.authorize(credentials)
auth_json_path = 'auth.json'
gss_scopes = ['https://spreadsheets.google.com/feeds']
gss_client = auth_gss_client(auth_json_path, gss_scopes)
接下來再用 open_by_key 打開試算表，用 sheet1 存取第一頁，再用 insert_row 插入更新的價格：
# update.py
import time
def update_sheet(gss_client, key, today, item, price):
wks = gss_client.open_by_key(key)
sheet = wks.sheet1
sheet.insert_row([today, item, price], 2)
spreadsheet_key_path = 'spreadsheet_key'
if cheapest_price is not None:
today = time.strftime("%c")
with open(spreadsheet_key_path) as f:
spreadsheet_key = f.read().strip()
update_sheet(gss_client, spreadsheet_key, today, cheapest_item,
cheapest_price)
測試一下，應該可以看到資料被成功插入：
python update.py
設定 crontab
當然，每次都要手動執行程式一點也不自動。所以我們會用 crontab 來自動執行我們的程式。
由於我們使用到了 pyvenv，而且很多檔案路徑都是用相對路徑，因此我另外寫了一個 bash script 來幫忙設定好執行 Python 的步驟。
#!/usr/bin/env bash
# start_update.sh
# get directory of the script
curr_dir=`dirname "$BASH_SOURCE"`
cd $curr_dir ;
source my_env/bin/activate
python update.py
用 chmod +x start_update.sh 打開執行權限，最後執行 crontab -e 並增加下面一行：
@daily {path_to_script_directory}/start_update.sh
如此一來就會每天自動更新了！
參考文件
- gspread API reference
- Access google sheets in python using Gspread
- https://github.com/shaform/experiments/tree/master/gspread
