PythonでSpread Sheetを連携-初期設定と内容を読み取りDataFrameで出力

Python

皆さん、こんにちは。

今回は、Pythonを使って、Google Spread Sheetに書かれた内容を取り出して、PandasのDataFrameとして表示するところまでを解説していきます。

本来であれば、SpreadSheetに書き込んだりしたいところだと思いますが、この記事では最も基本的な操作として、PythonとGoogle Spread Sheet の連携をメインに据えて解説しています。

Google Cloud Platformでの設定

Google Spread Sheet にアクセスするためには、Google Cloud Platformにて設定を行う必要があります。

はじめてつかう場合は戸惑うかもしれませんが、Google Driveの各種コンテンツと接続するだけであれば、課金されることはありません。

Google Cloud は下記のリンクからアクセス可能です。

https://cloud.google.com/

プロジェクトの作成

Google Cloud への登録が完了したら、プロジェクトを作成します。

Google Cloud のホーム画面から、画面上部の▼をクリックし、「新しいプロジェクト」から新規にプロジェクトを作成しましょう。

各種APIの有効化

プロジェクトの作成が完了したら、Google Spread Sheetに接続するための各種APIを有効化します。

有効化する必要のあるものは、「Google Drive API」と「Google Sheets API」の2つです。

Google Drive APIの有効化

まずはマーケットプレイスでAPIを検索します。

画面左上の「三」のような部分を開き、「マーケットプレイス」を探します。

クリックすると、このような画面に遷移するはずです。

検索バーに「Google Drive API」を入力し検索します。

検索語は、おなじみのGoogle Driveのマークをクリックし、次のページで有効化してください。

Google Sheets APIの有効化

次はGoogle Sheets APIを有効化します。

検索バーに「Google Sheets API」を入力し検索します。

検索語は、「有効にする」を入力し有効にします。

有効になったかの確認をしたい場合、先ほどの「三」を開き、「APIとサービス」から確認することが出来ます。

※私の場合、既に色々なものが有効になっているので、わかりにくくなっていますが、初めて使う方であれば簡単に見つけられると思います。

Google Drive への認証情報の設定

これで、Google Cloud 上のサービスの有効化が完了しました。

ただ、今の状況はアクセスするためのゲートを有効にしただけです。

次は、ゲートを通っていいサービスを登録します。

Google Cloud Platformでは、”サービスアカウント”が非常に重要です。

手元でコードを書いてアクセスする場合も、Web上でアプリを運用する場合も、このサービスアカウントを使ってアクセスすると考えるとわかりやすいと思います。

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

それではさっそくサービスアカウントを作成していきましょう。

「APIとサービス」から、「認証情報」にアクセスします。

※私は既に運用しているものがあるので、クライアントが存在していますね。

初めての人は、「認証情報を作成」から作成します。

サービスアカウント名は、なんでもいいですが、わかりやすい名前にしておきましょう。

複数作るのであれば、説明があった方がいいですが、必須項目だけ記入すればOKです。

作成したら、画面下の「サービスアカウント」が追加されていることを確認しましょう。

鍵の追加・JSON秘密鍵のダウンロード

次は、作成したサービスアカウントの情報を手元に落とします。

多くの場合、「秘密鍵」や「鍵」と言って、これさえあれば接続できてしまうので、外部に漏らさないように注意しましょう。

「サービスアカウント」から作成したサービスアカウントを選択し、「鍵を追加」をクリックします。

「新しい鍵を作成」をクリックします。

次は鍵のタイプを選択します。

鍵のタイプは「JSON」でOKです。

JSONとは「JavaScript Object Notation」の略で、「JavaScriptのオブジェクトの書き方を元にしたデータ定義方法」のことです。

https://products.sint.co.jp/topsic/blog/json

鍵が作成されたら、パソコンにダウンロードされるはずです。

注意書きが出てきますが、注意書きの通り、重要な情報なのでしっかりと保存しておきましょう。

JSON秘密鍵を開いてみる

JSONファイルが大切と言われても、イマイチぴんと来ないと思うので、JSONファイルを開いてみましょう。

VS Codeで開いてみます。

下記の画像のように{}(’大かっこ)で囲まれた情報が書かれています。

重要な部分は隠していますが、後ほど作成するPythonファイルにこのファイルを読み込んで、Google Spread Sheet にアクセスします。

SpreadSheetの設定(共有)

次は、Spread Sheet の設定を行っていきましょう。

自分のアカウントで、適当にSpread Sheetを用意します。

その「共有」の部分をクリック。

client_emailを入力

共有相手を設定します。

そこに、先ほどのJSONファイルに書かれている「client_email」の部分をコピーして貼り付けます。

最後に「送信」をクリックすると共有の設定は完了です。

検証のためのSpread Sheetを作成

今回の目的はSpread Sheet の内容を読み込むためのものなので、中身を適当に作成しておきましょう。

私はこんな感じの、商品の売り上げシートを作成しました。

PythonでSpread Sheetを読み込む

長らくお待たせしました。

やっとPythonコードを書いていきます。

gspreadとoauth2clientをインストール

PythonでGoogl Spread Sheetを操作するときに必要なものは、gspread とoauth2clientです。

お使いのPython環境に合わせてインストールしてください。

pip install gspread
pip install oauth2client

ソースコード

次はソースコードです。

pandas をインポートする行までは、Spread Sheetの接続に必須のコードとなります。

import gspread
import json

#ServiceAccountCredentials:Googleの各サービスへアクセスできるservice変数を生成
from oauth2client.service_account import ServiceAccountCredentials 

#Spread SheetとGoogle Drive APIのトークンを用意
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

json_file = "○○○○○○.json"
#認証情報設定
#ダウンロードしたjsonファイルをクレデンシャルに設定(秘密鍵、Pythonファイルと同じ階層に置けばOKOK)
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file, scope)

############################################################

import pandas as pd

# 作成したクレデンシャルをauthrizeに渡して、Google Spreadsheetにアクセス可能にする
gc = gspread.authorize(credentials)
# SpreadSheetのURLを変数格納
url = "○○○○○○○○○○○○○○○○○○○○"

# 当該SpreadSheetの、0番目のワークシートをworksheet 変数に格納
worksheet = gc.open_by_url(url).get_worksheet(0)
# worksheetに記載されたデータをデータフレームに渡す
df = pd.DataFrame(worksheet.get_all_values())

DataFramreとして出力する

実行結果です。

このようにSpread Sheetの内容をデータフレームとして出力することが出来ました!

これであとはPythonでPandasを操作するだけで、可視化も集計も簡単に行えますね。

ただ、この状態だと、列名が0,1,2,3となっており、本来列名としたい、IDなどが要素に入ってしまっています。

対症療法的ではありますが、修正して終わりにしましょう。

columns = df.iloc[0].to_list()
df = df.shift(-1)
df.columns = columns
df = df[:-1]

まとめ

いかがでしたでしょうか!

Pythonを使って、Google Spread Sheetに記載された内容を読み込むことを試してみました。

参考サイト:

https://docs.gspread.org/en/latest/