RaspberryPi 3 Model B+からGoogleスプレッドシートへアクセスする方法

by souichirou · 公開済み 2019年6月2日 · 更新済み 2021年7月19日

2021年04月29日追記

こちらの記事では 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（任意の名前）
場所：組織なし

で「作成」をクリックする。

尚、無料アカウントの場合は割当可能なプロジェクト数に制限があるので「残り１０プロジェクト」とのメッセージが表示されている。

2020/01/06追記

尚、プロジェクト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スプレッドシート」を選択する。

名前：SK-RasPiTestSheet
シート：１行目に見出し、２行目以降に値をセット。A列のTermnalIDでマッチングする

名前（日本語も使用可能）はプログラム中で指定するので控えておく。

上記を入力後に「共有」ボタンをクリックする。

先程保存した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 oauth2client

Pythonプログラムの作成

秘密鍵ファイルのコピー

先程作成した秘密鍵ファイル（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セルに書き込まれている。

2020年8月18日追記

また座標指定の場合は行、列で指定する事もできる。

        wks = gc.open(sheet_name).sheet1        # sheetをオープン
        wks.update_cell(2, 5, 'E2 cell Update')

同じような事をしたい人の参考になれば幸いです。

追加

2020年5月28日　追記

プログラム実行時に”No module named gspread”等のエラーが出てしまう場合はプログラムからgspreadが見れていない可能性がある。

その際はpip show gspreadコマンドでgspreadのインストールpathを探してソース中に明示的にpathを指定する。

詳細はこちらの記事を参照の事

以上で今回の記事を終了とする

最後に

この記事が何処かで誰かの役に立つことを願っている。

尚、当記事中の商品へのリンクはAmazonアソシエイトへのリンクが含まれています。Amazonのアソシエイトとして、当メディアは適格販売により収入を得ていますのでご了承ください。

ゆより:
2020年5月27日 8:25 PM
まさしくやってみたい事だと思い試した結果、以下のエラーがでました。
宜しければアドバイス頂けますと幸いです。
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
返信
- souichirou より:
  2020年5月28日 7:50 AM
  ゆ　さんこんにちは
  gspreadがプログラムから見れない状態だと思います。
  記事中の
```
sudo pip3 install gspread
```
  のコマンドは正常に終了しましたか。
  またはインストールした時のアカウントと実行時のアカウントが異なっている事はありませんか？
```
pip show gspread
```
  でgspreadのインストール場所が分かります。
  もし正しくインストールされていてpathが通っていないだけであれば、プログラムソース中に
```
import sys
sys.path.append('上記コマンドで表示されたgspreadのインストールpath')
```
  を指定してみてください
  結果を教えて貰えるとありがたいです。
  返信
  - ゆより:
    2020年6月3日 7:05 PM
    丁寧にご返答頂きありがとうございます。
    :~ $ 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
    初心者なもので不明なことだらけです。
    ご教示頂けますと助かります。
    返信
    - souichirou より:
      2020年6月4日 7:59 AM
      ゆ　さん
      こんにちは。
      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. 元記事は分かりにくかったので追記を入れました
      返信
ゆより:
2020年6月6日 4:14 AM
アンインストールでおかしなことになったりしてしまい、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’)
返信
- souichirou より:
  2020年6月6日 7:18 AM
  ゆ　さん
  おはようございます。
  ソース中に、
  import gspread
  　　↑
  gspreadモジュールの読み込み
  が無いようです。
  ですので
  gc = gspread.authorize(credentials)
  でgspreadを使おうとして、「gspreadがありません」のエラーになっていると思います。
  P.S. Pythonのデフォルトは3.7になっていて、その環境にgspreadがインストールされているようです。
  返信
ゆより:
2020年6月8日 6:07 AM
アンインストール、インストールを丁寧にやり直したら無事できました。
ありがとうございます。お騒がせしました。
大変参考になりました。
今後も引き続き参考にさせてもらいます。応援しています☆
返信
- souichirou より:
  2020年6月8日 7:39 AM
  ゆ　さん
  無事に動いて良かったですね?
  返信
おじいちゃんより:
2020年8月16日 4:22 PM
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
返信
- souichirou より:
  2020年8月16日 6:11 PM
  おじいちゃん　さんへ
  プログラムからシートが見つからないようです。
  プログラム中の７行目のsheet_nameとGoogle Sheetのシート名（左上のシート名）は
  一致していますか？
  返信
  - おじいちゃんより:
    2020年8月16日 10:28 PM
    お返事ありがとうございます　現状は下記のとおりです
    プログラム中７行目は　sheet_name = ‘jsnpd1’ 　# シート名
    Google Sheetのシート名（左上のシート名）は　jsnpd1
    プロジェクト名も　jsnpd1
    にしています
    返信
    - souichirou より:
      2020年8月17日 7:20 AM
      おじいちゃん　さんへ
      シート名は一致していますか。。。
      シート名以外だとすると、
      JSONファイル中のメールアドレスはスプレッドーシートで共有されていますか？
      記事中の「Googleスプレッドシートを作成して共有設定する」の項です。
      一度、Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
      共有設定されているかどうかを確認してみて下さい。
      後、念の為の確認ですがシート１はありますよね。
      プログラム中のsheet1はシート１を表しています。
      シート名は変更しても大丈夫の様ですがシート１を削除しているようだとnot foundになるかも知れません。
      また、ちなみにシート名やメールアドレスはコピペで貼り付けていますか？
      返信
おじいちゃんより:
2020年8月18日 7:24 AM
souichirou様
ありがとうございます。無事できました。
原因は、
『Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
共有設定されているかどうかを確認してみて下さい。』で
何度もやったほずなのですが、共有メール部に反映されていませんでした。本当にありがとうございます。感謝いたします
ラズベリーパイのプログラム中　
print(“設定値３”,records[i][2])
の後に　スプレッドシート上で　i行の最終列に相当するセルにラズパイで測定したデータを　update_acellしたいときのコードは、どのように書けばよろしいでしょうか。最後として教えていただけませんか
その都度測定値を空白のセルに右方向に移動しながら記録することを考えています
イメージとしては下記なのですが
wks.update_acell(i,　lastcolumn + 1, val)
よろしくお願いします。
返信
- souichirou より:
  2020年8月18日 9:20 AM
  おじいちゃん　さん
  無事に出来たとの事で何よりです。
  update_acellの件ですがちょっとやりたいことが分かりませんでした。
  該当行の最終列に書き込みたいのであればご提示のコードで良いように思います。
  wks.update_acell(i,　lastcolumn + 1, val)
  lastcolumnの値が行によって変わるという事ですかね。
  該当行を見つけて右に移動をしながら最初に空白が見つかった列に書き込めば良いのではないでしょうか。
  コードは適当ですが、
```
j = 0
while records[i][j] != '':
    j += 1
```
  で最初に見つかった空白の列を探せば良いのではないでしょうか。
  返信
  - 匿名より:
    2020年8月22日 10:19 AM
    souichirou 様
    いろいろ教えていただきましてありがとうございます。
    wks.update_cell(i, len(records[i]) + 1, val)
    としてみましたらうまくいくようになりました。
    次は、条件次第でLINEに送信したり、通知することができるように
    機能を追加していく予定です。自助努力で頑張りますが、わからないときは
    お助けください。
    今後ともよろしくお願いします。
    返信
    - souichirou より:
      2020年8月22日 10:57 PM
      コメントありがとうございます。
      列の最後に挿入するのであれば、len(records[i])+1が良いですね。
      こちらこそよろしくおねがいします。
      返信

RaspberryPi 3 Model B+からGoogleスプレッドシートへアクセスする方法

はじめに

手順

Google Cloud Platformにアクセスする

プロジェクトを作成してAPIを有効化する

認証情報

サービスアカウントを作成する

秘密鍵ファイルを作成する

Googleスプレッドシートを作成して共有設定する

RaspberryPiに必要なモジュールをインストールする

gspreadとoauth2clientのインストール

Pythonプログラムの作成

秘密鍵ファイルのコピー

プログラム内容

補足説明

実行結果

シートへの書き込み

追加

関連

おすすめ

16件のフィードバック

質問やコメントや励ましの言葉などを残すコメントをキャンセル

最近の投稿

カテゴリー

アーカイブ

最近のコメント

2023年12月
月	火	水	木	金	土	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

RaspberryPi 3 Model B+からGoogleスプレッドシートへアクセスする方法

はじめに

手順

Google Cloud Platformにアクセスする

プロジェクトを作成してAPIを有効化する

認証情報

サービスアカウントを作成する

秘密鍵ファイルを作成する

Googleスプレッドシートを作成して共有設定する

RaspberryPiに必要なモジュールをインストールする

gspreadとoauth2clientのインストール

Pythonプログラムの作成

秘密鍵ファイルのコピー

プログラム内容

補足説明

実行結果

シートへの書き込み

追加

関連

おすすめ

ラズパイのカメラモジュールに赤外線LEDモジュールを追加して夜間撮影

ラズパイZEROとモーションセンサーで見守りシステムをつくる（その５ プログラムを動作させるまで）

赤外線センサーの結果をRaspberryPi 3 Model B+からAWS IoT経由でQuickSightでグラフ化するまで（その５）

16件のフィードバック

質問やコメントや励ましの言葉などを残す コメントをキャンセル

最近の投稿

カテゴリー

アーカイブ

最近のコメント

ラズパイZEROとモーションセンサーで見守りシステムをつくる（その５　プログラムを動作させるまで）

質問やコメントや励ましの言葉などを残すコメントをキャンセル