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は1番人気のプログラム言語です。
データ分析の分野では特にPythonが主流です。
理由はデータ分析用のパッケージが豊富にあるためです。
1. Pythonのダウンロードページにアクセスする
2. 「Download Python」ボタンを押してファイルをダウンロードする
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-93-1024x421.png)
3. ダウンロードしたpython-3.x.x.exeファイルを実行
4. インストール設定で
「Add Python 3.8 to PATH」
にチェック後、「Install Now」をクリックする。
このチェックは環境変数の”Path”にPythonを追加する設定です。
Pythonをどこのパスからでも実行が可能になります。
ですので、チェックがおすすめです。
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-95.png)
Power BIでPythonを使う設定をする
Power BIでPythonを使うには2つの初期設定が必要です。
- Power BI上でPythonの設定
- Pythonに必要なライブラリをインストール
Power BI上でPythonの設定
1. Power BIを起動後、
ファイル > オプションと設定 > オプション
を選択する
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-99.png)
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-100.png)
2. 「Pythonスクリプト」をする。
Pythonフームディレクトリは自動でセットされます。
「OK」ボタンを押す
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-101.png)
Pythonに必要なライブラリをインストール
1. Powershellを起動
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-102.png)
2.下記のコマンドを実行して、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. 新規プロジェクトを作成
プロジェクト未作成の人は画面上部から「新しいプロジェクト」を作成します。
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-103-1024x275.png)
3. 「APIとサービスを有効化」をクリック
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-104.png)
4. Google Sheets APIを選択
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-105.png)
5. Google Sheets APIを有効にする
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-106.png)
6. 認証情報の作成
1. 認証情報を選択
2.「認証情報を作成」をクリック
3.サービスアカウントを選択
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-108.png)
4. サービスアカウント名を入力
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-109.png)
5. サービスアカウントのアクセス許可設定は何も設定しないで「続行」をクリック
6.ユーザアカウントのアクセス許可設定は何も設定しないで「完了」をクリック
7. 作成したサービスアカウントを選択
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-111.png)
8. 鍵ファイルを作成
「鍵を追加」から「新しい鍵を作成」を選択
キータイプにJSONを選択して、「作成」する
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-112-1024x614.png)
ダウンロードしたファイルはパスワード情報です。
他の人から参照されない場所で管理してください
Google Spreadsheetsの共有設定
作成したサービスアカウントにGoogle Spreadsheetsの参照権を付与します。
- 「共有」を選択
- 「ユーザやグループと共有」にサービスアカウントで作成したメールを登録する
メールアドレスはGoogle API サービスで作成したサービスアカウントです。
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-118-1024x629.png)
3. 全員と共有する場合は「リンクを知っている人全員」をクリックする
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-113.png)
Power BIでPython接続
Power BIからPythonを使ってGoogle Spreadsheetsにアクセスして、データを取得します。
1. Power BIを起動
1.「データ取得」をクリック
2. phtyonで検索
3. Pythonスクリプトを選択して、「接続」をクリック
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-116.png)
2.Pythonのコードを張り付けて「OK」を実行
赤色のパラメータは自分の環境に合わせて変更してください
docid: google Spreadsheetsを開いたときのURLの一部
サンプル
https://docs.google.com/spreadsheets/d/15vKxg-lgQTw3HR7Q2qGQzMjSzoYyRhtomTKXmdeHdCQ/edit#gid=0
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-120.png)
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))")
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-117.png)
3. Google Spreadsheetsのリストが出たら取り込み成功です。
![](http://www.katalog.tokyo/wp-content/uploads/2020/07/image-119.png)
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接続
1回設定したら、次からの追加は4,5の設定だけで大丈夫です。