價格追蹤:使用 gspread 自動更新 Google Sheets

By Shaform, Sat 19 March 2016, in category Notes

Google Sheets, gspread, Python 3

前言

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 的帳號。步驟如下:

  1. 先到 Google Developers Console 建立一個 project。
  2. 啟動該 project 的 Drive API
  3. Credentials 頁面按下 Create credentials -> Service account key。
  4. 取好名字,選擇 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.jsonclient_email 欄位提到的 email 帳號: name...@....gserviceaccount.com,並給予編輯的權限,這樣子才有辦法透過程式存取。

將試算表多餘的欄位刪除,只剩下 date, item, price 三欄,並留下新的一個空白行,好讓程式插入資料時,也能有白色背景。

Create a spreadsheet

最後我們把網址中 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

Insert data

設定 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

如此一來就會每天自動更新了!

參考文件