由Python程式來存取Google sheets (試算表)

 可以在Raspberry Pi上讀取TMP102的温度值後, 我想要把温度值存放在那裡? 存在本機的檔案上, 網路上已有現成Python程式, 放在MQTT Server, 以前就玩過。想到放到Google 試算表應該也不錯, 以前也沒試過, 不如來試試!

首先找到這一貼文Reading and writing to Google Spreadsheets using Python, 此文言簡意賅, 只有3個步驟就做完, 文章印出來也只要2頁! 但是第一個步驟因為要操作Google API Console, 而這個網頁看來操作方式有些修改過了, 所以照著做會有些困難。自己摸索一陣時間後, 才弄清楚! 其實網路上討論這個部份的貼文不少, 【Python 網頁爬蟲 #1】設定 google sheet API 並取得 json 金鑰，讓我們的資料能同步更新至雲端 google sheet 表格 (內含完整圖片說明)這篇貼文就很詳細, 文章是去年寫的, 還算新, Google API Console的操作界面和現在的一致。另外, How to Read and Write Google Sheets Data Using Python這篇貼文在設定Google Cloud Console部份, 描述的更詳細, 而且Python程式部份使用不同的套件。還有一篇陳濤半年前寫的[筆記] 使用 pygsheets 記錄實驗數據到 google sheet也很不錯, 特別是把Python pygsheets套件的幾個常用操作點出, 值得看一下。

我就不重複講了, 只提幾個重點:

===  步驟 1 ===

1. 要先建專案 

2. 在建好的專案裡, 啟用2個API。Google Drive API, Google Sheets API (有些文章只提到要啟用Google Sheets API, 那就要看你之後的程式的寫法, 我因為後面Python程式有用到, 所以要這2個都啟用)

3. 建立憑證 (有3個可以選: API, OAuth ID, 服務帳戶。要選服務帳戶, 建完後會得到一個電子郵件地址, 類似xxx@xxx.iam.gserviceaccount.com)

4. 針對這個服務帳戶, 建立金鑰, 並下載其JSON檔案 (命名為creds.json)

5. 到Google 試算表去, 建一個新文件(這裡, 我選一個好記的文件檔名: gsheet test), 或選一個你想用來被Python程式存取的文件。按右上角的"共用", 新增之前建立的服務帳戶的郵件地址, 就是類似xxx@xxx.iam.gserviceaccount.com的那個。這樣, 這個試算表文件就可以被這個服務帳戶存取了。

===  步驟 2 ===

可以來寫Python程式了, 我在Raspberry Pi上面操作, 首先要把之前服務帳戶的金鑰creds.json檔案存到Raspberry Pi機器上面去。這可以用scp指令來完成。

$ scp creds.json ops@192.168.1.2:creds.json

接著Python程式會用到的套件要裝一下:

$ sudo pip install pygsheets

$ sudo pip install pandas

用指令模式來嘗試: (配合的試算表文件要先建4個工作表!)

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44)
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pygsheets
>>> import pandas as pd
>>> gc = pygsheets.authorize(service_file='creds.json')
>>> df = pd.DataFrame()
>>> df['name'] = ['John', 'Steve', 'Sarah']
>>> sh = gc.open('gsheet test')
>>> wks = sh[1]
>>> wks.set_dataframe(df,(1,1))
>>> df['name'] = ['John', 29.5, 30.3]
>>> wks = sh[2]
>>> wks.set_dataframe(df,(1,1))
>>> wks.set_dataframe(df,(2,1))
>>> wks.set_dataframe(df,(1,2))
>>> wks.set_dataframe(df,(1,3))
>>> wks.update_value('A7', 29.3)
>>> wks = sh[3]
...
>>> wks.insert_rows(7, number=1, values=['17:08',24.5])
>>> wks.insert_rows(8, number=1, values=['17:09',23.5])
>>> wks.insert_rows(9, number=1, values=['17:10',21.2])
>>> wks.insert_rows(10, number=1, values=['17:12',25.2])
>>> wks.insert_rows(11, number=1, values=['17:12',22.2])
>>> wks.insert_rows(12, number=1, values=['17:13',20.7])
>>> wks.insert_rows(13, number=1, values=['17:14',23.2])
>>> wks.insert_rows(14, number=1, values=['17:15',24.2])
>>> wks.insert_rows(15, number=1, values=['17:16',22.2])
>>> wks.insert_rows(16, number=1, values=['17:17',20.8])

===  步驟 3 ===

打開Google 試算表, 觀察, 可以看到在Python寫入的資料, 出現在指定的試算表文件中了。

到這裡, 結合之前取得TMP102温度值的方法和這裡的作法, 就可以做出一個即時回報温度值的自動機制, 而我們可以在任何地點, 任何時間, 只要能存取網路, 就可以由Google試算表查看最新狀況了。

具體作法:

這個試算表文件放1年份的資料, 每個月的資料放在一個工作表, 所以最多可能有12個工作表, 命名為01, 02, ..., 12。每個工作表最多可能有31天的資料, 每天24小時x60分鐘/5分鐘=288筆資料。所以一個月最多有288x31=8928筆資料。我們只要知道現在的日期時間, 就可以知道資料要放在那個工作表的那個位置上。

由上面Python程式改寫, 在連上Google試算表, 打開文件後(sh = gc.open('gsheet test'))可先取得該文件的工作表list (wks_list = sh.worksheets()), 由此可以檢查是否已存在所需要的工作表, 若不存在則建立該工作表(sh.add_worksheet(sheet_name, rows=N)), 否則就往下跑。

取得温度值後, 加上已知的日期和時間, 就可以把資料放到該特定位置。

例如:1月的1號00:05, 就會是放在工作表'01'的第1筆, 所以:

wks = sh[0]

wks.insert_rows(1, number=1, values=['1', '00:05',24.5])

有些小細節還要修改, 但大致上這個想法應該可行。