Pythonで日々勉強していく中で、スプレッドシートを操作出来たら素晴らしいなとおもったりします。今日はgspreadのライブラリを用いてGoogleスプレッドシートをPythonで操作する方法を紹介します。
Contents
前提:pythonのAPIに送信する大まかな基礎情報
多くの人は大丈夫かと思いますがpythonのAPIを操作するには大体下記が必要です。
- メソッド(GET,POST,PUT,DELETE)
- ヘッダー(データの種類、認証情報など)
- JSON形式で受信したいデータ情報
API操作に慣れていない人はこれを意識した状態で進むといいと思います。
GoogleスプレッドシートのAPIについて
GoogleCloudPlatformに登録してAPIを取得
GoogleスプレッドシートのAPIを利用するためには、GoogleCloudPlatformに登録が必要です。GoogleCloudPlatformのサービスにある「Google Sheets API」のAPIライブラリを有効にし、認証情報を取得できるサービスアカウントをAPIキーを取得します。
GoogleCloudPlatformのサービスアカウントは、アプリケーション(GoogleSheetsAPIを使って開発するアプリ)からGoogleCloudPlatformにアクセスするために必要な認証とアカウントが紐づいたものです。つまりこれに基づいて認証を行います。
APIに必要な認証キーを取得するまでのステップ
- GoogleCloudPlatformに登録
- 新しいプロジェクトを作成
- プロジェクト名は自由に設定
- 場所もいったん自由に指定(よく分からない)
- 「作成」をクリックしてプロジェクト作成する
- 作成したプロジェクトを表示する
- プロジェクト画面を表示したら左上のメニューボタンから「APIとサービス」⇒「有効なAPIとサービス」を選択します
- 「有効なAPIとサービス」の画面が表示されたら、画面上の方にある「APIとサービスの有効化」をクリックする
- キーワード検索ボックスに「sheet」と検索して「GoogleSheetsAPI」を表示させる。
- 表示させたら「GoogleSheetsAPI」をクリックして、「有効にする」をさらにクリックする
- 今回は「GoogleDriveAPI」も使用したいので、手順④以降の工程を繰り返し、「GoogleDriveAPI」を有効にするまで行う。※スプレッドシートがドライブの中にあったりするときに必要なので、一応設定しておきましょう。
- 「有効なAPIとサービス」のページから「認証情報」⇒「CREATE CREDENTIALS」⇒「サービスアカウント」をクリックする。
- サービスアカウントの「サービスアカウント名」「サービスアカウントID」は自由に設定してください。設定したら「作成して続行」をクリックします。
- 次は編集権限の設定を「ロール」の部分で設定します。「編集者」以上であれば問題ないかと思います。「ロール」を設定したら「続行」をクリックします。
- 「ユーザーにこのサービス アカウントへのアクセスを許可 (省略可)」は無視して「完了」をクリックして、サービスアカウントの作成を完了させましょう。
- サービスアカウントの作成が完了したら「認証情報」のページが表示されますが、先ほど作成したサービスアカウントが作成されたことを確認して、サービスアカウントをクリックしてみましょう。
- 該当のサービスアカウントが表示されたら「キー」⇒「鍵を追加」⇒「新しい鍵」をクリックします。
- 表示されたデータ形式で「JSON」を選択して「作成」をクリックします。
- API認証に必要なAPIキーが作成できました。jsonファイルで自分のパソコンにダウンロードされています。スプレッドシートの認証に必要な鍵ファイルなので、大事に保管しましょう。ちなみに、後でファイル名を読み込むときにファイル名が長すぎるので、ダウンロードしたJSONファイルのファイル名を「credentials.json」にしておきます。※この後に表示されるコードのファイル名にいきなり「credentials.json」が出てきてびっくりしないように認識をお願いします。
今回使用するスプレッドシートを用意してください
今回はGoogleスプレッドシートの操作を行うので、Googleスプレッドシートが必要です。一応、こんなスプレッドシートを用意しました。
同じデータで試したい場合は、「こちら」をクリックしてください。遷移したスプレッドシートは「閲覧」専用シートです。そのため、このスプレッドシートを「コピーを作成」することで、自身が編集権限を持ったスプレッドシートの操作ができます。
作成したスプレッドシートの共有権限にサービスアカウントのメールアドレスを追加する。
自分で編集可能なスプレッドシートを作成したら、次はスプレッドシートの設定によってサービスアカウントのメールアドレスに権限を付与します。
- サービスアカウントのメールアドレスをコピーしておく
- コピーしたサービスアカウントのメールアドレスに、スプレッドシートの権限を付与する
- スプレッドシート右上の「共有」からコピーしたサービスアカウントのメールアドレスを入力する
- メールアドレスを入力したら、「編集者」⇒「送信」をクリックします。
PythonでGoogleスプレッドシートのAPIを叩く
スプレッドシートの操作でなるべく見ておきたいリファレンス
- Google Sheets APIのリファレンス
- Pythonで使うgspreadライブラリ
GoogleCloudPlatformで作成したサービスアカウントの認証に必要なjsonファイルをpythonファイルと同じディレクトリに格納する
先ほど作成したGoogleCloudPlatformのサービスアカウント認証用のjsonファイルをpythonファイルと同じディレクトリに格納します。※読み込みパスを自由に変更できる方は別の場所に入れても大丈夫です。
私は、jupyternotebookのローカル環境でPythonを実行します。
事前にインストール必要なpythonのライブラリ
今回のAPIリクエストのために、必要なインストールを行います。まだ以下のライブラリをインストールしていない人は、pythonの環境で以下を操作する必要があります。
1 2 3 |
// 必要なライブラリをインストール pip install gspread pip install google-auth |
Pythonのコード
1 2 |
# gspreadをインポートします。 import gspread |
gspeadをインポートしたら、認証に必要なライブラリなどを設定します。
1 2 3 4 5 6 7 |
# 認証に必要なライブラリをインポート from google.oauth2.service_account import Credentials # 今回使用するAPIの範囲を指定します。 scopes = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive' ] |
次に、サービスアカウントの認証ファイルを読み込みます。
1 2 3 4 5 6 7 |
# 認証に必要なサービスアカウントのファイルを読み込み credentials = Credentials.from_service_account_file( 'credentials.json', scopes=scopes ) # 認証リクエスト gc = gspread.authorize(credentials) |
ここまでの認証情報の送信を実行してエラーがなければ、おそらく問題なくできているかと思います。
スプレッドシートのデータを取得する
認証を突破した次は、データを取得したいスプレッドシートを指定します。スプレッドシートにはIDが存在します。IDはスプレッドシートURLの「/d/」以降の「/」に囲まれた部分です。以下のURLでは赤文字の部分がスプレッドシートのIDとなります。
https://docs.google.com/spreadsheets/d/1dIxhhcl3qeUD_YB4_qyQFJdCobn5Z0IrSw1g9zidaG8/edit#gid=0
操作を行いたいスプレッドシートを指定するには、このIDを用います。コードではIDを用いて以下のように、スプレッドシートを指定します。
1 2 3 4 |
# スプレッドシートのIDを指定する sht1 = gc.open_by_key('1dIxhhcl3qeUD_YB4_qyQFJdCobn5Z0IrSw1g9zidaG8') # 対象スプレッドシートのシート名を指定する worksheet = sht1.worksheet('名簿') |
ここで対象のスプレッドシートのワークシートのデータが取得できた状態です。本当に取得できているかは、get_all_values()メソッドを使ってみてみましょう。
1 2 3 |
# データを見てみる data = worksheet.get_all_values() data |
問題なければ、以下のデータが表示されます。
このデータをもとに、数字を変更してみましょう。
スプレッドシートのデータを変更してみる
では、塩見さんの年齢を29歳から30歳にしてみましょう。C2のセルの数値を30に変更しているのが以下の操作です。
1 2 |
# 29歳の年齢を30歳に更新する worksheet.update('C2', '30') |
この操作に問題がなければ、スプレッドシートを見てみると数値が更新されていることが分かります。一応、jupter notebookでデータを見てみます。
1 2 3 |
# 更新したデータを再度読み込んでみてみる data = worksheet.get_all_values() data |
操作するとこんな表示になります。
新しいシートを作成する
新しいスプレッドシートのシートを作成するなら、以下で可能です。以下は「打率」という新しいシート名を作成しています。
1 2 |
# 新しいシート「打率」を作成する sht1.add_worksheet(title="打率", rows=100, cols=20) |
まとめ:APIは素晴らしい
これで、Pythonのスクレイピングで取得した毎日のデータをスプレッドシートに記載できたり、色々クリエイティブな作業が出来ると思ったのではないでしょうか。作業の効率化に励みましょう。