PythonのgspreadでGoogleスプレッドシートを操作する方法

Pythonで日々勉強していく中で、スプレッドシートを操作出来たら素晴らしいなとおもったりします。今日はgspreadのライブラリを用いてGoogleスプレッドシートをPythonで操作する方法を紹介します。

ここではGoogleCloudPlatformのAPIキーの取得方法も解説していきますよ！

前提：pythonのAPIに送信する大まかな基礎情報

多くの人は大丈夫かと思いますがpythonのAPIを操作するには大体下記が必要です。

• メソッド（GET,POST,PUT,DELETE）
• ヘッダー（データの種類、認証情報など）
• JSON形式で受信したいデータ情報

API操作に慣れていない人はこれを意識した状態で進むといいと思います。

GoogleスプレッドシートのAPIについて

GoogleCloudPlatformに登録してAPIを取得

GoogleスプレッドシートのAPIを利用するためには、GoogleCloudPlatformに登録が必要です。GoogleCloudPlatformのサービスにある「Google Sheets API」のAPIライブラリを有効にし、認証情報を取得できるサービスアカウントをAPIキーを取得します。

APIに必要な認証キーを取得するまでのステップ

1. GoogleCloudPlatformに登録
2. 新しいプロジェクトを作成
   1. プロジェクト名は自由に設定
   2. 場所もいったん自由に指定（よく分からない）
   3. 「作成」をクリックしてプロジェクト作成する
3. 作成したプロジェクトを表示する
4. プロジェクト画面を表示したら左上のメニューボタンから「APIとサービス」⇒「有効なAPIとサービス」を選択します
5. 「有効なAPIとサービス」の画面が表示されたら、画面上の方にある「APIとサービスの有効化」をクリックする
6. キーワード検索ボックスに「sheet」と検索して「GoogleSheetsAPI」を表示させる。
7. 表示させたら「GoogleSheetsAPI」をクリックして、「有効にする」をさらにクリックする
8. 今回は「GoogleDriveAPI」も使用したいので、手順④以降の工程を繰り返し、「GoogleDriveAPI」を有効にするまで行う。※スプレッドシートがドライブの中にあったりするときに必要なので、一応設定しておきましょう。
9. 「有効なAPIとサービス」のページから「認証情報」⇒「CREATE CREDENTIALS」⇒「サービスアカウント」をクリックする。
   1. サービスアカウントの「サービスアカウント名」「サービスアカウントID」は自由に設定してください。設定したら「作成して続行」をクリックします。
   2. 次は編集権限の設定を「ロール」の部分で設定します。「編集者」以上であれば問題ないかと思います。「ロール」を設定したら「続行」をクリックします。
   3. 「ユーザーにこのサービス アカウントへのアクセスを許可 (省略可)」は無視して「完了」をクリックして、サービスアカウントの作成を完了させましょう。
10. サービスアカウントの作成が完了したら「認証情報」のページが表示されますが、先ほど作成したサービスアカウントが作成されたことを確認して、サービスアカウントをクリックしてみましょう。
11. 該当のサービスアカウントが表示されたら「キー」⇒「鍵を追加」⇒「新しい鍵」をクリックします。
12. 表示されたデータ形式で「JSON」を選択して「作成」をクリックします。
13. API認証に必要なAPIキーが作成できました。jsonファイルで自分のパソコンにダウンロードされています。スプレッドシートの認証に必要な鍵ファイルなので、大事に保管しましょう。ちなみに、後でファイル名を読み込むときにファイル名が長すぎるので、ダウンロードしたJSONファイルのファイル名を「credentials.json」にしておきます。※この後に表示されるコードのファイル名にいきなり「credentials.json」が出てきてびっくりしないように認識をお願いします。

今回使用するスプレッドシートを用意してください

今回はGoogleスプレッドシートの操作を行うので、Googleスプレッドシートが必要です。一応、こんなスプレッドシートを用意しました。

同じデータで試したい場合は、「こちら」をクリックしてください。遷移したスプレッドシートは「閲覧」専用シートです。そのため、このスプレッドシートを「コピーを作成」することで、自身が編集権限を持ったスプレッドシートの操作ができます。

作成したスプレッドシートの共有権限にサービスアカウントのメールアドレスを追加する。

自分で編集可能なスプレッドシートを作成したら、次はスプレッドシートの設定によってサービスアカウントのメールアドレスに権限を付与します。

1. サービスアカウントのメールアドレスをコピーしておく
2. コピーしたサービスアカウントのメールアドレスに、スプレッドシートの権限を付与する
   1. スプレッドシート右上の「共有」からコピーしたサービスアカウントのメールアドレスを入力する
   2. メールアドレスを入力したら、「編集者」⇒「送信」をクリックします。

PythonでGoogleスプレッドシートのAPIを叩く

スプレッドシートの操作でなるべく見ておきたいリファレンス

• Google Sheets APIのリファレンス
  • https://developers.google.com/sheets/api
• Pythonで使うgspreadライブラリ
  • https://docs.gspread.org/en/latest/

GoogleCloudPlatformで作成したサービスアカウントの認証に必要なjsonファイルをpythonファイルと同じディレクトリに格納する

先ほど作成したGoogleCloudPlatformのサービスアカウント認証用のjsonファイルをpythonファイルと同じディレクトリに格納します。※読み込みパスを自由に変更できる方は別の場所に入れても大丈夫です。

私は、jupyternotebookのローカル環境でPythonを実行します。

事前にインストール必要なpythonのライブラリ

今回のAPIリクエストのために、必要なインストールを行います。まだ以下のライブラリをインストールしていない人は、pythonの環境で以下を操作する必要があります。

Pythonのコード

gspeadをインポートしたら、認証に必要なライブラリなどを設定します。

次に、サービスアカウントの認証ファイルを読み込みます。

ここまでの認証情報の送信を実行してエラーがなければ、おそらく問題なくできているかと思います。

スプレッドシートのデータを取得する

認証を突破した次は、データを取得したいスプレッドシートを指定します。スプレッドシートにはIDが存在します。IDはスプレッドシートURLの「/d/」以降の「/」に囲まれた部分です。以下のURLでは赤文字の部分がスプレッドシートのIDとなります。

https://docs.google.com/spreadsheets/d/1dIxhhcl3qeUD_YB4_qyQFJdCobn5Z0IrSw1g9zidaG8/edit#gid=0

操作を行いたいスプレッドシートを指定するには、このIDを用います。コードではIDを用いて以下のように、スプレッドシートを指定します。

ここで対象のスプレッドシートのワークシートのデータが取得できた状態です。本当に取得できているかは、get_all_values()メソッドを使ってみてみましょう。

問題なければ、以下のデータが表示されます。

このデータをもとに、数字を変更してみましょう。

スプレッドシートのデータを変更してみる

では、塩見さんの年齢を29歳から30歳にしてみましょう。C2のセルの数値を30に変更しているのが以下の操作です。

この操作に問題がなければ、スプレッドシートを見てみると数値が更新されていることが分かります。一応、jupter notebookでデータを見てみます。

操作するとこんな表示になります。

新しいシートを作成する

新しいスプレッドシートのシートを作成するなら、以下で可能です。以下は「打率」という新しいシート名を作成しています。

まとめ：ＡＰＩは素晴らしい

これで、Pythonのスクレイピングで取得した毎日のデータをスプレッドシートに記載できたり、色々クリエイティブな作業が出来ると思ったのではないでしょうか。作業の効率化に励みましょう。