Google SpreadsheetsのデータをPower BIに取り込み方法を解説します。
この記事ではPythonを使った取り込み方法を詳細に説明します。
概要を知っていて、結論を知りたい方はこのコードを使ってください。
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
docid = "Google SpreadsheetsのID"
keyfile = "Google Spreadsheetsのセキュリティファイルのパス"
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(keyfile, scope)
client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(docid)
for i, worksheet in enumerate(spreadsheet.worksheets()):
exec(worksheet.title + "= pd.DataFrame(data=worksheet.get_all_values()[1:], columns=worksheet.row_values(1))")
Pythonのインストール
2020年の現在ではPythonは１番人気のプログラム言語です。
データ分析の分野では特にPythonが主流です。
理由はデータ分析用のパッケージが豊富にあるためです。
1. Pythonのダウンロードページにアクセスする
2. 「Download Python」ボタンを押してファイルをダウンロードする
3. ダウンロードしたpython-3.x.x.exeファイルを実行
4. インストール設定で
「Add Python 3.8 to PATH」
にチェック後、「Install Now」をクリックする。
このチェックは環境変数の”Path”にPythonを追加する設定です。
Pythonをどこのパスからでも実行が可能になります。
ですので、チェックがおすすめです。
Power BIでPythonを使う設定をする
Power BIでPythonを使うには２つの初期設定が必要です。
- Power BI上でPythonの設定
- Pythonに必要なライブラリをインストール
Power BI上でPythonの設定
1. Power BIを起動後、
ファイル > オプションと設定 > オプション
を選択する
2. 「Pythonスクリプト」をする。
Pythonフームディレクトリは自動でセットされます。
「OK」ボタンを押す
Pythonに必要なライブラリをインストール
1. Powershellを起動
２．下記のコマンドを実行して、pandasとmatplotlibをインストールする
pip install pandas pip install matplotlib
コマンド説明
1) pip install
Python用のパッケージをインストールするコマンドです。
パッケージはPythonやサードパーティーが提供している拡張機能です。
2) pandas
データ分析用のパッケージです。
データをcsvやExcelなどから読み込んで、分析しやすい形に整えるのに主に使います。
3) matplotlib
グラフ表示用のパッケージです。
pandasとmatplotlibはPythonをする上でよく使われるパッケージです。
ですので、インストールして問題ないです。
その他：Google Spreadsheets取り込み用のパッケージのインストール
今回PythonでGoogle Spreadsheetsにアクセスするので、そのためのパッケージのインストールをします。
pip install gspread pip install oauth2client
gspreadはgoogle Spreadsheetsにアクセスするためのパッケージです
oauth2clientは認証用のパッケージです
PythonからGoogle Spreadsheetsにアクセスする設定
Google Spreadsheetにアクセスするには認証が必要です。
Pythonから自動で認証できるように設定を追加します。
1. Google APIサービスに接続
2. 新規プロジェクトを作成
プロジェクト未作成の人は画面上部から「新しいプロジェクト」を作成します。
3. 「APIとサービスを有効化」をクリック
4. Google Sheets APIを選択
5. Google Sheets APIを有効にする
6. 認証情報の作成
1. 認証情報を選択
2.「認証情報を作成」をクリック
3.サービスアカウントを選択
4. サービスアカウント名を入力
5. サービスアカウントのアクセス許可設定は何も設定しないで「続行」をクリック
6.ユーザアカウントのアクセス許可設定は何も設定しないで「完了」をクリック
7. 作成したサービスアカウントを選択
8. 鍵ファイルを作成
「鍵を追加」から「新しい鍵を作成」を選択
キータイプにJSONを選択して、「作成」する
ダウンロードしたファイルはパスワード情報です。
他の人から参照されない場所で管理してください
Google Spreadsheetsの共有設定
作成したサービスアカウントにGoogle Spreadsheetsの参照権を付与します。
- 「共有」を選択
- 「ユーザやグループと共有」にサービスアカウントで作成したメールを登録する
メールアドレスはGoogle API サービスで作成したサービスアカウントです。
3. 全員と共有する場合は「リンクを知っている人全員」をクリックする
Power BIでPython接続
Power BIからPythonを使ってGoogle Spreadsheetsにアクセスして、データを取得します。
1. Power BIを起動
1.「データ取得」をクリック
2. phtyonで検索
3. Pythonスクリプトを選択して、「接続」をクリック
２．Pythonのコードを張り付けて「OK」を実行
赤色のパラメータは自分の環境に合わせて変更してください
docid: google Spreadsheetsを開いたときのURLの一部
サンプル
https://docs.google.com/spreadsheets/d/15vKxg-lgQTw3HR7Q2qGQzMjSzoYyRhtomTKXmdeHdCQ/edit#gid=0
keyFile: サービスアカウント作成時にダウンロードしたjsonファイルのパス
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
docid = "Google SpreadsheetsのID"
keyfile = "Google Spreadsheetsのセキュリティファイルのパス"
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(keyfile, scope)
client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(docid)
for i, worksheet in enumerate(spreadsheet.worksheets()):
exec(worksheet.title + "= pd.DataFrame(data=worksheet.get_all_values()[1:], columns=worksheet.row_values(1))")
3. Google Spreadsheetsのリストが出たら取り込み成功です。
Pythonコードの詳細説明
Google Spreadsheetに認証します
credentials = ServiceAccountCredentials.from_json_keyfile_name(keyfile, scope) client = gspread.authorize(credentials)
指定したGoogle Spreadsheetのファイルにアクセス
spreadsheet = client.open_by_key(docid)
Power BIがSheetごとにテーブルとして認識するように取り込みます。
for文でファイル内のSheetに順番にアクセスします。
Sheet名がPower BI取り込み時のテーブル名となるように設定。
execは動的にパラメータを作成する関数。
（）内の文字列の処理を実行します。
Power BIはpandasのDataFrame型の変数をテーブルとして取り込みます。
Sheet名がテーブル名となるように、execを利用して変数名を動的に変えています。
worksheet.titleはSheetの名前をとります。
DataFrameの設定では、dataにデータ、columnsに項目名を指定します。
get_all_values()でSheetの全データを取得します。
1行目は項目名のため、[1:]で2行目以降をとるようにしています。
row_values(1)は1行目のデータを取ります。columnsにデータをセットする事で、1行目を項目名としています。
for i, worksheet in enumerate(spreadsheet.worksheets()):
exec(worksheet.title + "= pd.DataFrame(data=worksheet.get_all_values()[1:], columns=worksheet.row_values(1))")
まとめ
Power BIでGoogle Spreadsheetsの情報を取り込むには、下記の5つの設定が必要という事を解説しました。
- Pythonのインストール
- Power BIでPythonを使う設定をする
- PythonからGoogle Spreadsheetsにアクセスする設定
- Google Spreadsheetsの共有設定
- Power BIでPython接続
１回設定したら、次からの追加は4,5の設定だけで大丈夫です。
