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

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

 2021年04月29日 追記

こちらの記事では Goggleスプレッドシートにアクセスするライブラリーに oauth2client を使用している。

しかし oauth2client は非推奨になってしまい、これ以上の機能追加もされないとの事なので、現在推奨の google.oauth2 を使用した方法で記事を書き直している。

詳しくは「ラズパイからGoogleスプレッドシートやドライブにアクセスする方法」の記事を参照して欲しい。

はじめに

RaspberryPi 3 ModelB+からGoogleスプレッドシートにアクセスする方法の備忘録。

複数のRaspberryPiからGoogelスプレッドシートにアクセスして自分の端末IDと一致する行の情報を取得するプログラムを作成する必要があった。

以下はイメージ図

RaspberryPiからGoogleスプレッドシートを読み込む

手順

大まかな手順は以下の通り

  1. Google Cloud Platformにアクセス
  2. プロジェクトを作成してAPIを有効化する
  3. 認証情報を作成する(サービスアカウント)
  4. アクセス対象のGoogleスプレッドシートを作成して共有設定する
  5. RaspberryPiにGoogleスプレッドシートの操作に必要なモジュールをインストール
  6. Pythonプログラムの作成

Google Cloud Platformにアクセスする

Google Cloud Platformにアクセスしてログインする。

  • Googleアカウントが必要
  • ログイン直後の画面はプロジェクト作成状況によって異なる(下記は既にプロジェクトがある状態)

Google Cloud Platform

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

上のメニューからプロジェクト名の横の三角をクリックして「新しいプロジェクト」をクリックする。

プロジェクト作成

  • プロジェクト名:SK-RasPiTest(任意の名前)
  • 場所:組織なし

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

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

2020/01/06追記

尚、プロジェクトIDは自動で設定されるが”編集”をクリックする事で好きなIDに変更することができる。

プロジェクト名設定して作成

プロジェクトが作成された。

”IAMと管理”ー>リソースの管理でプロジェクト一覧を見た状況

プロジェクトが作成された

上部のメニューから新しく作成したプロジェクトを選択する。

プロジェクトの選択

新しいプロジェクトが選択されている事を確認して、左側のメニューからAPIとサービスー>ライブラリを選択する。

APIとサービスー>ライブラリ

APIとサービスを検索欄に”sheet”と入力してGoogleスプレッドシートのAPIを検索する。

APIライブラリ一覧

GoogleスプレッドシートAPIが表示されるので選択する。

Google Sheet APIの検索

「有効にする」をクリックする。

Google Sheet APIを有効にする

プロジェクト(SK-RasPiTest)に対してGoogle Sheet APIが有効になった。

プロジェクトに対してGoogle Sheet APIが有効になった

同様の手順でGoogle Drive APIを検索して有効化する。

Google Drive APIの有効化

Google Drive APIが有効化された。

Google Drive APIの有効化

認証情報

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

メニューから”APIとサービス”ー>”認証情報”を選択する。

APIとサービスー>認証情報

”+認証情報を作成”ー>”サービスアカウント”を選択する。

認証情報を作成ー>サービスアカウントキー

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

  • サービスアカウント名:任意のアカウント名を設定する(このアカウントでGoogleスプレッドシートにアクセスする)
  • サービスアカウントID:上記のサービスアカウント名から自動的に作成される

「作成」ボタンをクリックする。

サービスアカウントキーの作成

ロールのプルダウンから”Project”ー>”編集者”を選択して「続行」ボタンをクリックする。

※プログラム中でGoogle Sheetsを参照のみであれば”閲覧者”でも良い

ロールの設定

サービスアカウントへのアクセス権は省略可能。

「完了」をクリックする。

サービスアカウントへのアクセス権

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

サービスアカウントが作成されるので選択する。

作成されたサービスアカウントを選択する

”鍵を追加”ー>”新しい鍵を作成”をクリックする。

鍵を追加

キーのタイプで”JSON”を選択して”作成”をクリックする。

JSON形式を選択

下記のメッセージが表示されてJSON形式の秘密鍵ファイルが作成されるので保存する。

秘密鍵の保存

鍵が発行されたので念の為、「保存」ボタンをクリックする。

Google Cloud Platformでの操作は以上で終了。

鍵が発行された

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

次に読み込まれる(書き込まれる)Googleスプレッドシートを作成する。

Google Driveから「新規」「Googleスプレッドシート」を選択する。

Googleスプレッドシートの新規作成

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

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

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

Googleスプレッドシート 名前の設定

先程保存したJSON形式の秘密鍵ファイルのclient_email欄をコピーする。

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:     # 1列目がTerminalIDと一致するまで
                print("設定値1",records[i][1])  # 値を取り出す
                print("設定値2",records[i][2])
                print("設定値3",records[i][2])
except KeyboardInterrupt:
    pass

補足説明

6行目は保存した秘密鍵のファイル名を指定する。

key_name = './cert/SK-RasPiTest-XXXXXX.json' # GoogleSheet認証キー

7行目のsheet_nameは前述の控えておいたGoogle Sheetの名前を指定する。

15行目のwks = gc.open(sheet_name).sheet1のsheet1はシート1を表している。

実行結果

実行結果は以下の通り。

TerminalId=”SK-RPi001”に一致する行の設定値1、2、3が取得される。

実行結果

また8行目のterminalIDを変更すると取得する行が変わる。

terminalID = "SK-RPi001"         # TerminalID

シートへの書き込み

シートの更新はupdate_acellを使用する。

15行目のシートのオープンの下でE2セルを更新するコーディングを加えた。

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

実行結果は以下の通り。

E2セルに書き込まれている。

Googleスプレッドシートの更新

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のアソシエイトとして、当メディアは適格販売により収入を得ていますのでご了承ください。

souichirou

やった事を忘れない為の備忘録 同じような事をやりたい人の参考になればと思ってブログにしてます。 主にレゴ、AWS(Amazon Web Services)、WordPress、Deep Learning、RaspberryPiに関するブログを書いています。 仕事では工場に協働ロボットの導入や中小企業へのAI/IoT導入のアドバイザーをやっています。 2019年7月にJDLA(一般社団法人 日本デイープラーニング協会)Deep Learning for GENERALに合格しました。 質問は記事一番下にあるコメントかメニュー上部の問い合わせからお願いします。

おすすめ

16件のフィードバック

  1. より:

    まさしくやってみたい事だと思い試した結果、以下のエラーがでました。
    宜しければアドバイス頂けますと幸いです。

    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 より:

      ゆ さんこんにちは

      gspreadがプログラムから見れない状態だと思います。
      記事中の

      sudo pip3 install gspread

      のコマンドは正常に終了しましたか。
      またはインストールした時のアカウントと実行時のアカウントが異なっている事はありませんか?

      pip show gspread

      でgspreadのインストール場所が分かります。
      もし正しくインストールされていてpathが通っていないだけであれば、プログラムソース中に

      import sys
      sys.path.append('上記コマンドで表示された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

        初心者なもので不明なことだらけです。
        ご教示頂けますと助かります。

        • souichirou より:

          ゆ さん
          こんにちは。
          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にインストールされていますので、
          1.アンインストール
          2.上記記事を元にデフォルトを3.0に変更
          3.再度インストール
          を試して見て下さい。

          P.S. 元記事は分かりにくかったので追記を入れました

  2. より:

    アンインストールでおかしなことになったりしてしまい、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 より:

      ゆ さん
      おはようございます。

      ソース中に、
      import gspread
        ↑
      gspreadモジュールの読み込み
      が無いようです。

      ですので
      gc = gspread.authorize(credentials)
      でgspreadを使おうとして、「gspreadがありません」のエラーになっていると思います。

      P.S. Pythonのデフォルトは3.7になっていて、その環境にgspreadがインストールされているようです。

  3. より:

    アンインストール、インストールを丁寧にやり直したら無事できました。
    ありがとうございます。お騒がせしました。
    大変参考になりました。
    今後も引き続き参考にさせてもらいます。応援しています☆

  4. おじいちゃん より:

    souichirou様
    サイトを参考にさせていただいています。
    ラズパイから、ロードセルで定期的に測定するデータをスプレッドシートに記録することを目指して
    この夏休み期間中にいろいろやってみました。
    まずはサイトとまるっきり同じことをやってみようと思い、プログラムを実行させたら下記のエラーがでました。
    お忙しいところ申し訳ないのですが、対処法などご教示お願いします。

    現在、python3をインストールしています。
    なお、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 より:

      おじいちゃん さんへ

      プログラムからシートが見つからないようです。
      プログラム中の7行目のsheet_nameとGoogle Sheetのシート名(左上のシート名)は
      一致していますか?

      • おじいちゃん より:

        お返事ありがとうございます 現状は下記のとおりです
        プログラム中7行目は sheet_name = ‘jsnpd1’  # シート名
        Google Sheetのシート名(左上のシート名)は jsnpd1
        プロジェクト名も jsnpd1
        にしています

        • souichirou より:

          おじいちゃん さんへ
          シート名は一致していますか。。。
          シート名以外だとすると、
          JSONファイル中のメールアドレスはスプレッドーシートで共有されていますか?
          記事中の「Googleスプレッドシートを作成して共有設定する」の項です。

          一度、Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
          共有設定されているかどうかを確認してみて下さい。

          後、念の為の確認ですがシート1はありますよね。
          プログラム中のsheet1はシート1を表しています。
          シート名は変更しても大丈夫の様ですがシート1を削除しているようだとnot foundになるかも知れません。

          また、ちなみにシート名やメールアドレスはコピペで貼り付けていますか?

  5. おじいちゃん より:

    souichirou様
    ありがとうございます。無事できました。
    原因は、
    『Google Sheetsを開いてみて右上の共有ボタンを押してJSONファイル中のメールアドレスが
    共有設定されているかどうかを確認してみて下さい。』で
    何度もやったほずなのですが、共有メール部に反映されていませんでした。本当にありがとうございます。感謝いたします

    ラズベリーパイのプログラム中 
    print(“設定値3”,records[i][2])
    の後に スプレッドシート上で i行の最終列に相当するセルにラズパイで測定したデータを update_acellしたいときのコードは、どのように書けばよろしいでしょうか。最後として教えていただけませんか
    その都度測定値を空白のセルに右方向に移動しながら記録することを考えています
    イメージとしては下記なのですが
    wks.update_acell(i, lastcolumn + 1, val)
    よろしくお願いします。

    • souichirou より:

      おじいちゃん さん
      無事に出来たとの事で何よりです。
      update_acellの件ですがちょっとやりたいことが分かりませんでした。
      該当行の最終列に書き込みたいのであればご提示のコードで良いように思います。
      wks.update_acell(i, lastcolumn + 1, val)

      lastcolumnの値が行によって変わるという事ですかね。
      該当行を見つけて右に移動をしながら最初に空白が見つかった列に書き込めば良いのではないでしょうか。
      コードは適当ですが、

      j = 0
      while records[i][j] != '':
          j += 1

      で最初に見つかった空白の列を探せば良いのではないでしょうか。

      • 匿名 より:

        souichirou 様
        いろいろ教えていただきましてありがとうございます。
        wks.update_cell(i, len(records[i]) + 1, val)
        としてみましたらうまくいくようになりました。

        次は、条件次第でLINEに送信したり、通知することができるように
        機能を追加していく予定です。自助努力で頑張りますが、わからないときは
        お助けください。
        今後ともよろしくお願いします。

        • souichirou より:

          コメントありがとうございます。
          列の最後に挿入するのであれば、len(records[i])+1が良いですね。
          こちらこそよろしくおねがいします。

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

名前、メール、サイト欄は任意です。
またメールアドレスは公開されません。