スプレッドシートにLogを書き込むと便利
スプレッドシートとはGoogleアカウントで無料で使える表計算シートですが、これをPythonなどでデータの収集に使えます。
以前、お薬記録にも使っています。
s51517765.hatenadiary.jp
M5StackではCでhttp requestでしたが、今回ラズベリーパイでPython3で使ってみました。
やってみると、出来てしまえばPythonのほうが簡単なのですが、APIの初期化が少してこずりました。
参考記事も情報が不足している（もしかしたら執筆当時はそれでよかった？）ところがありましたので、改めてここにまとめました。
スプレッドシートの設定
こちらからプロジェクトを作成します。
console.developers.google.com
名前は自由に付けます。
Firebaseなどと共通の管理で、無料では最大5つまでになるようです。
私はすでにFirebaseで使っているプロジェクトをそのまま使いました。
左上のナビゲーションメニューから、APIとサービス￥ライブラリと選択し、下記2つを有効化します。
「sheet」で検索
Google sheet APIを選択し、有効化
「Drive」で検索
Google Drive APIを選択し、有効化
左上のナビゲーションメニューから、APIとサービス →認証情報を開く。
「認証情報を作成」をクリック、サービスアカウント
サービスアカウント名を適当に入力し「作成して続行」。
「ロールを選択」を選択し、「Project」 → 「編集者」を選択。
続行し完了。
ここでFilterしようと思うとなぜか「Project」が出てこず、手動でドロップダウンリストを送ります。
サービスアカウントメニューにもどって 今作ったアカウントを選択。
メニューの「キー」を選択。
鍵を追加 → JSONを選択
鍵を保存。ラズパイのspread.pyと同じフォルダに転送します。
spread.pyにこのファイル名を入力します。
Google Driveでスプレッドシートを新規に作成し、スプレッドシートを開きます。
右上の緑の共有ボタンを押すと共有するユーザーのメールアドレスを入力するエリアが表示されるので
先ほどのJSONファイルを開いて「client_email」行のメールアドレスをコピーして貼り付けます。
"client_email": "xxxxx@gspread-sample-202808.iam.gserviceaccount.com"
また、リンクを取得の下にある「変更」を開き「リンクを知っている人全員」を編集者にします。
スプレッドのURLhttps://docs.google.com/spreadsheets/d/xxxxxxxxx/edit#gid=0のxxxxの部分を
spread.pyに入力します。
API Ratelimit
スプレッドシートのRatelimitは100秒で100回となっています。
This version of the Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.
Usage Limits | Sheets API | Google Developers
10秒のWaitを入れているので大丈夫、と思っていたら5分で16*5件の書き込みでRateLimitに引っかかってしまいました。
これは、書き込みだけではなく読み込みもカウントの対象になっているため、のようです。
sprd_row = 2 # スプレッドシートのスタート行 while worksheet.cell(sprd_row, 1).value != None: # 空白でなければ1行進む → 行が増えるとRATELIMITに引っかかる sprd_row += 1
実際に引っかかってしまったのがこのようなコードで「上から探索して空白行に書き込む」というものですが、30行程度あるとこれが1分あたり3回程度動いたときに100回を超えてしまいます。
これは、以下のようにして回避できました。
これでA列のデータをカウントし、次の行の行番号を取得します。
sprd_row = 2 # スプレッドシートのスタート行 A_COL_ARRAY = worksheet.col_values(1) sprd_row = len(A_COL_ARRAY)+1
まとめ
PythonでGoogleスプレッドシートを使うAPIの使い方をまとめました。
「1分で100回」等のRasteLimitは書き込みだけでなく、読み込みの回数も含みます。
データが多くなったときは、探索のときにデータ読み込みを効率的に（まとめて取得してローカルでカウントする等）する必要があります。
$sudo pip3 install gspread $sudo pip3 install oauth2client
#!/usr/bin/env python3 import gspread import datetime from oauth2client.service_account import ServiceAccountCredentials def main(data=[0, 0]): scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name('phonic-embassy-xxxxxx-xxxxxxxxx.json', scope) # jsonのファイル名 gc = gspread.authorize(credentials) SPREADSHEET_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxx' # スプレッドシートのファイル名 worksheet = gc.open_by_key(SPREADSHEET_KEY).worksheet('シート1') # スプレッドシートのシート名 sprd_row = 2 # スプレッドシートのスタート行 #while worksheet.cell(sprd_row, 1).value != None: # 空白でなければ1行進む → 行が増えるとRATELIMITに引っかかる # sprd_row += 1 A_COL_ARRAY = worksheet.col_values(1) sprd_row = len(A_COL_ARRAY)+1 t_now = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S') worksheet.update_cell(sprd_row, 1, t_now) # 時刻 if data[0] != 0: worksheet.update_cell(sprd_row, 2, data[0].temperature) # 温度0 worksheet.update_cell(sprd_row, 3, data[0].humidity) # 湿度0 if data[1] != 0: worksheet.update_cell(sprd_row, 4, data[1].temperature) # 温度1 worksheet.update_cell(sprd_row, 5, data[1].humidity) # 湿度1 if data[0] == 0 and data[1] == 0: worksheet.update_cell(sprd_row, 2, "Nodata") if __name__ == '__main__': main()
