RaspberryPi 3 Model B+からGoogleスプレッドシートへアクセスする方法
こちらの記事では Goggleスプレッドシートにアクセスするライブラリーに oauth2client を使用している。
しかし oauth2client は非推奨になってしまい、これ以上の機能追加もされないとの事なので、現在推奨の google.oauth2 を使用した方法で記事を書き直している。
詳しくは「ラズパイからGoogleスプレッドシートやドライブにアクセスする方法」の記事を参照して欲しい。
はじめに
RaspberryPi 3 ModelB+からGoogleスプレッドシートにアクセスする方法の備忘録。
複数のRaspberryPiからGoogelスプレッドシートにアクセスして自分の端末IDと一致する行の情報を取得するプログラムを作成する必要があった。
以下はイメージ図
手順
大まかな手順は以下の通り
- Google Cloud Platformにアクセス
- プロジェクトを作成してAPIを有効化する
- 認証情報を作成する（サービスアカウント）
- アクセス対象のGoogleスプレッドシートを作成して共有設定する
- RaspberryPiにGoogleスプレッドシートの操作に必要なモジュールをインストール
- Pythonプログラムの作成
Google Cloud Platformにアクセスする
Google Cloud Platformにアクセスしてログインする。
- Googleアカウントが必要
- ログイン直後の画面はプロジェクト作成状況によって異なる（下記は既にプロジェクトがある状態）
プロジェクトを作成してAPIを有効化する
上のメニューからプロジェクト名の横の三角をクリックして「新しいプロジェクト」をクリックする。
- プロジェクト名：SK-RasPiTest（任意の名前）
- 場所：組織なし
で「作成」をクリックする。
尚、無料アカウントの場合は割当可能なプロジェクト数に制限があるので「残り１０プロジェクト」とのメッセージが表示されている。
尚、プロジェクトIDは自動で設定されるが”編集”をクリックする事で好きなIDに変更することができる。
プロジェクトが作成された。
”IAMと管理”ー＞リソースの管理でプロジェクト一覧を見た状況
上部のメニューから新しく作成したプロジェクトを選択する。
新しいプロジェクトが選択されている事を確認して、左側のメニューからAPIとサービスー＞ライブラリを選択する。
APIとサービスを検索欄に”sheet”と入力してGoogleスプレッドシートのAPIを検索する。
GoogleスプレッドシートAPIが表示されるので選択する。
「有効にする」をクリックする。
プロジェクト（SK-RasPiTest）に対してGoogle Sheet APIが有効になった。
同様の手順でGoogle Drive APIを検索して有効化する。
Google Drive APIが有効化された。
認証情報
サービスアカウントを作成する
メニューから”APIとサービス”ー＞”認証情報”を選択する。
”＋認証情報を作成”ー＞”サービスアカウント”を選択する。
サービスアカウントを作成する。
- サービスアカウント名：任意のアカウント名を設定する（このアカウントでGoogleスプレッドシートにアクセスする）
- サービスアカウントID：上記のサービスアカウント名から自動的に作成される
「作成」ボタンをクリックする。
ロールのプルダウンから”Project”ー＞”編集者”を選択して「続行」ボタンをクリックする。
※プログラム中でGoogle Sheetsを参照のみであれば”閲覧者”でも良い
サービスアカウントへのアクセス権は省略可能。
「完了」をクリックする。
秘密鍵ファイルを作成する
サービスアカウントが作成されるので選択する。
”鍵を追加”ー＞”新しい鍵を作成”をクリックする。
キーのタイプで”JSON”を選択して”作成”をクリックする。
下記のメッセージが表示されてJSON形式の秘密鍵ファイルが作成されるので保存する。
鍵が発行されたので念の為、「保存」ボタンをクリックする。
Google Cloud Platformでの操作は以上で終了。
Googleスプレッドシートを作成して共有設定する
次に読み込まれる（書き込まれる）Googleスプレッドシートを作成する。
Google Driveから「新規」「Googleスプレッドシート」を選択する。
名前（日本語も使用可能）はプログラム中で指定するので控えておく。
上記を入力後に「共有」ボタンをクリックする。
先程保存したJSON形式の秘密鍵ファイルのclient_email欄をコピーする。
ユーザ欄にメールアドレスを貼り付けて「送信」ボタンをクリックする。
これで先程作成したサービスアカウントからこのシートへの共有設定が完了した。
尚、下記のメールの配信エラーがgmailに届くが問題なく共有設定は出来ていた。
Googleスプレッドシート側の操作は以上で終了。
RaspberryPiに必要なモジュールをインストールする
gspreadとoauth2clientのインストール
pip3コマンドでGoogleスプレッドシート用のモジュールをRaspberryPiにインストールする。
尚、事前にPythonのデフォルトをPython3にしてある（これは重要なのでPython2.7がデフォルトの場合はgspreadモジュールインストール前に必ず実施すること）
変更方法については以前の記事を参照。
sudo pip3 install gspread続いてOAuth 2.0によって保護されているリソースにアクセスするためのPythonライブラリをインストールする。
sudo pip3 install oauth2clientPythonプログラムの作成
秘密鍵ファイルのコピー
先程作成した秘密鍵ファイル（JSON形式）をプログラム配下のcert/ディレクトリにコピーしておく
プログラム内容
Pythonのプログラムは以下の通り
#!/usr/bin/python
# -*- coding: utf-8 -*-
import gspread
from oauth2client.service_account import ServiceAccountCredentials
key_name = './cert/SK-RasPiTest-XXXXXX.json' # GoogleSheet認証キー
sheet_name = 'SK-RasPiTestSheet' # シート名
terminalID = "SK-RPi001" # TerminalID
try:
if __name__ == '__main__':
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(key_name, scope)
gc = gspread.authorize(credentials) # JSONキーファイルで認証
wks = gc.open(sheet_name).sheet1 # sheetをオープン
records = wks.get_all_values() # 中身を取り出して配列に保存
for i in range(1, len(records)): # sheetの行数分だけ繰り返す
if records[i][0] == terminalID: # １列目がTerminalIDと一致するまで
print("設定値１",records[i][1]) # 値を取り出す
print("設定値２",records[i][2])
print("設定値３",records[i][2])
except KeyboardInterrupt:
pass補足説明
６行目は保存した秘密鍵のファイル名を指定する。
key_name = './cert/SK-RasPiTest-XXXXXX.json' # GoogleSheet認証キー７行目のsheet_nameは前述の控えておいたGoogle Sheetの名前を指定する。
１５行目のwks = gc.open(sheet_name).sheet1のsheet1はシート１を表している。
実行結果
実行結果は以下の通り。
TerminalId=”SK-RPi001”に一致する行の設定値１、２、３が取得される。
また８行目のterminalIDを変更すると取得する行が変わる。
terminalID = "SK-RPi001" # TerminalIDシートへの書き込み
シートの更新はupdate_acellを使用する。
１５行目のシートのオープンの下でE2セルを更新するコーディングを加えた。
wks = gc.open(sheet_name).sheet1 # sheetをオープン
wks.update_acell('E2', 'E2 cell Update')実行結果は以下の通り。
E2セルに書き込まれている。
また座標指定の場合は行、列で指定する事もできる。
wks = gc.open(sheet_name).sheet1 # sheetをオープン
wks.update_cell(2, 5, 'E2 cell Update')同じような事をしたい人の参考になれば幸いです。
追加
プログラム実行時に”No module named gspread”等のエラーが出てしまう場合はプログラムからgspreadが見れていない可能性がある。
その際はpip show gspreadコマンドでgspreadのインストールpathを探してソース中に明示的にpathを指定する。
詳細はこちらの記事を参照の事
以上で今回の記事を終了とする
この記事が何処かで誰かの役に立つことを願っている。
尚、当記事中の商品へのリンクはAmazonアソシエイトへのリンクが含まれています。Amazonのアソシエイトとして、当メディアは適格販売により収入を得ていますのでご了承ください。
まさしくやってみたい事だと思い試した結果、以下のエラーがでました。
宜しければアドバイス頂けますと幸いです。
python test.pyを実行した結果
Traceback (most recent call last):
File “test.py”, line 3, in
import gspread
File “/home/pi/gspread.py”, line 10, in
gc = gspread.authorize(credentials)
NameError: name ‘gspread’ is not defined
ゆ さんこんにちは
gspreadがプログラムから見れない状態だと思います。
記事中の
のコマンドは正常に終了しましたか。
またはインストールした時のアカウントと実行時のアカウントが異なっている事はありませんか？
でgspreadのインストール場所が分かります。
もし正しくインストールされていてpathが通っていないだけであれば、プログラムソース中に
を指定してみてください
結果を教えて貰えるとありがたいです。
丁寧にご返答頂きありがとうございます。
:~ $ pip show gspread
WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip.
Please see https://github.com/pypa/pip/issues/5599 for advice on fixing the underlying issue.
To avoid this problem you can invoke Python with ‘-m pip’ instead of running pip directly.
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Name: gspread
Version: 3.6.0
Summary: Google Spreadsheets Python API
Home-page: https://github.com/burnash/gspread
Author: Anton Burnashev
Author-email: fuss.here@gmail.com
License: MIT
Location: /home/pi/.local/lib/python2.7/site-packages
Requires: google-auth-oauthlib, requests, google-auth
Required-by:
と出たので、ソースを以下のようにしました。
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
sys.path.append(‘/home/pi/.local/lib/python2.7/site-packages’)
import json
from oauth2client.service_account import ServiceAccountCredentials
scope = [‘https://www.googleapis.com/auth/drive’]
credentials = ServiceAccountCredentials.from_json_keyfile_name(‘./python.json’, scope)
gc = gspread.authorize(credentials)
worksheet = gc.open(“シート1”).sheet1
worksheet.update_cell(1,1, ‘Hello World’)
結果は、以下でした。
:~ $ python gs.py
Traceback (most recent call last):
File “gs.py”, line 12, in
gc = gspread.authorize(credentials)
NameError: name ‘gspread’ is not defined
初心者なもので不明なことだらけです。
ご教示頂けますと助かります。
ゆ さん
こんにちは。
pip show gspreadの結果を見るとpython2.7にインストールされていて警告が出ていますね。
※2.7は2020年1月1にサポートが終了しています。
元の記事中に
尚、事前にPythonのデフォルトをPython3にしてある。
変更方法については以前の記事を参照。（https://www.souichi.club/raspberrypi/aws-iot-01/#DefaltPython3）
とありますがこの手順を抜かしたのだと思います。
PythonのデフォルトをPython3に変更した上でインストールするとPython3の環境にgspreadがインストールされます。
今、2.7にインストールされていますので、
１．アンインストール
２．上記記事を元にデフォルトを3.0に変更
３．再度インストール
を試して見て下さい。
P.S. 元記事は分かりにくかったので追記を入れました
アンインストールでおかしなことになったりしてしまい、OS自体を入れなおして0からやりましたが、症状は変わりませんでした。
何の設定？がおかしいのか分からない状態です。。。すみません。
:~ $ python gs.py
Traceback (most recent call last):
File “gs.py”, line 10, in
gc = gspread.authorize(credentials)
NameError: name ‘gspread’ is not defined
:/usr/bin $ ls -l
lrwxrwxrwx 1 root root 7 6? 6 03:18 python -> python3
lrwxrwxrwx 1 root root 14 3? 5 2019 python-config -> python2-config
lrwxrwxrwx 1 root root 9 3? 5 2019 python2 -> python2.7
lrwxrwxrwx 1 root root 16 3? 5 2019 python2-config -> python2.7-config
-rwxr-xr-x 1 root root 154 12? 30 2018 python2-pbr
-rwxr-xr-x 1 root root 2984816 10? 11 2019 python2.7
lrwxrwxrwx 1 root root 36 10? 11 2019 python2.7-config -> arm-linux-gnueabihf-python2.7-config
lrwxrwxrwx 1 root root 9 3? 26 2019 python3 -> python3.7
lrwxrwxrwx 1 root root 16 3? 26 2019 python3-config -> python3.7-config
-rwxr-xr-x 2 root root 4275580 12? 21 03:57 python3.7
lrwxrwxrwx 1 root root 36 12? 21 03:57 python3.7-config -> arm-linux-gnueabihf-python3.7-config
-rwxr-xr-x 2 root root 4275580 12? 21 03:57 python3.7m
lrwxrwxrwx 1 root root 37 12? 21 03:57 python3.7m-config -> arm-linux-gnueabihf-python3.7m-config
lrwxrwxrwx 1 root root 10 3? 26 2019 python3m -> python3.7m
lrwxrwxrwx 1 root root 17 3? 26 2019 python3m-config -> python3.7m-config
lrwxrwxrwx 1 root root 10 3? 26 2019 pyvenv -> pyvenv-3.7
-rwxr-xr-x 1 root root 436 12? 21 03:57 pyvenv-3.7
lrwxrwxrwx 1 root root 29 3? 5 2019 pyversions -> ../share/python/pyversions.py
:~ $ python –version
Python 3.7.3
:~ $ pip show gspread
Name: gspread
Version: 3.6.0
Summary: Google Spreadsheets Python API
Home-page: https://github.com/burnash/gspread
Author: Anton Burnashev
Author-email: fuss.here@gmail.com
License: MIT
Location: /usr/local/lib/python3.7/dist-packages
Requires: requests, google-auth-oauthlib, google-auth
Required-by:
と出たので、ソースを以下のようにしています。
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
sys.path.append(‘/usr/local/lib/python3.7/dist-packages’)
import json
from oauth2client.service_account import ServiceAccountCredentials
scope = [‘https://spreadsheets.google.com/feeds’,’https://www.googleapis.com/auth/drive’]
#
credentials = ServiceAccountCredentials.from_json_keyfile_name(‘./python.json’, scope)
gc = gspread.authorize(credentials)
#
worksheet = gc.open(“シート1”).sheet1
#
worksheet.update_cell(1,1, ‘Hello World’)
ゆ さん
おはようございます。
ソース中に、
import gspread
↑
gspreadモジュールの読み込み
が無いようです。
ですので
gc = gspread.authorize(credentials)
でgspreadを使おうとして、「gspreadがありません」のエラーになっていると思います。
P.S. Pythonのデフォルトは3.7になっていて、その環境にgspreadがインストールされているようです。
アンインストール、インストールを丁寧にやり直したら無事できました。
ありがとうございます。お騒がせしました。
大変参考になりました。
今後も引き続き参考にさせてもらいます。応援しています☆
ゆ さん
無事に動いて良かったですね😁
souichirou様
サイトを参考にさせていただいています。
ラズパイから、ロードセルで定期的に測定するデータをスプレッドシートに記録することを目指して
この夏休み期間中にいろいろやってみました。
まずはサイトとまるっきり同じことをやってみようと思い、プログラムを実行させたら下記のエラーがでました。
お忙しいところ申し訳ないのですが、対処法などご教示お願いします。
現在、python３をインストールしています。
なお、pip show gspreadにてVersion: 3.6.0 Location: /usr/local/lib/python3.7/dist-packages
Requires: google-auth, requests, google-auth-oauthlib とまではわかっています。
ここからエラーメッセージーー
Traceback (most recent call last):
File “/usr/local/lib/python3.7/dist-packages/gspread/client.py”, line 119, in open
self.list_spreadsheet_files(title),
File “/usr/local/lib/python3.7/dist-packages/gspread/utils.py”, line 97, in finditem
return next((item for item in seq if func(item)))
StopIteration
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File “/home/pi/hx711py/GoogleSheet-Ethqeq”, line 15, in
wks = gc.open(sheet_name).sheet1 # sheetをオープン
File “/usr/local/lib/python3.7/dist-packages/gspread/client.py”, line 127, in open
raise SpreadsheetNotFound
gspread.exceptions.SpreadsheetNotFound
おじいちゃん さんへ
プログラムからシートが見つからないようです。
プログラム中の７行目のsheet_nameとGoogle Sheetのシート名（左上のシート名）は
一致していますか？
お返事ありがとうございます 現状は下記のとおりです
プログラム中７行目は sheet_name = ‘jsnpd1’ # シート名
Google Sheetのシート名（左上のシート名）は jsnpd1
プロジェクト名も jsnpd1
にしています
おじいちゃん さんへ
シート名は一致していますか。。。
シート名以外だとすると、
JSONファイル中のメールアドレスはスプレッドーシートで共有されていますか？
記事中の「Googleスプレッドシートを作成して共有設定する」の項です。
一度、Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
共有設定されているかどうかを確認してみて下さい。
後、念の為の確認ですがシート１はありますよね。
プログラム中のsheet1はシート１を表しています。
シート名は変更しても大丈夫の様ですがシート１を削除しているようだとnot foundになるかも知れません。
また、ちなみにシート名やメールアドレスはコピペで貼り付けていますか？
souichirou様
ありがとうございます。無事できました。
原因は、
『Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
共有設定されているかどうかを確認してみて下さい。』で
何度もやったほずなのですが、共有メール部に反映されていませんでした。本当にありがとうございます。感謝いたします
ラズベリーパイのプログラム中
print(“設定値３”,records[i][2])
の後に スプレッドシート上で i行の最終列に相当するセルにラズパイで測定したデータを update_acellしたいときのコードは、どのように書けばよろしいでしょうか。最後として教えていただけませんか
その都度測定値を空白のセルに右方向に移動しながら記録することを考えています
イメージとしては下記なのですが
wks.update_acell(i, lastcolumn + 1, val)
よろしくお願いします。
おじいちゃん さん
無事に出来たとの事で何よりです。
update_acellの件ですがちょっとやりたいことが分かりませんでした。
該当行の最終列に書き込みたいのであればご提示のコードで良いように思います。
wks.update_acell(i, lastcolumn + 1, val)
lastcolumnの値が行によって変わるという事ですかね。
該当行を見つけて右に移動をしながら最初に空白が見つかった列に書き込めば良いのではないでしょうか。
コードは適当ですが、
で最初に見つかった空白の列を探せば良いのではないでしょうか。
souichirou 様
いろいろ教えていただきましてありがとうございます。
wks.update_cell(i, len(records[i]) + 1, val)
としてみましたらうまくいくようになりました。
次は、条件次第でLINEに送信したり、通知することができるように
機能を追加していく予定です。自助努力で頑張りますが、わからないときは
お助けください。
今後ともよろしくお願いします。
コメントありがとうございます。
列の最後に挿入するのであれば、len(records[i])+1が良いですね。
こちらこそよろしくおねがいします。