freeeの取引データをBIツール（PowerBI）やOfficeツール（Excel/Access）で活用してみよう

はじめに

本記事では、freee株式会社が提供するクラウド会計ソフトfreeeの取引データをCData ODBC Drivers for RESTを利用してMicrosoftのBIツール（PowerBI Desktop）、および、Officeツール（Excel/Access）から利用する方法について解説します。

実現イメージ

今回はBI & Officeツールからfreee APIに接続しますが、それらのツールからは単純に　freeeと接続することができません。それはBI & Officeツールがどのようにfreee API側へリクエストを投げるべきかの判断材料やメタデータの情報が無いためです。

そこで、CData REST ODBC Driverを用いて、このボトルネックを解決します。まず、BI & OfficeツールからはODBCインタフェースでSQL（Select文）をCData REST ODBC Driverに向けて発行してもらいます。そのリクエストを受け取った、CData REST ODBC Driverは、freeeのWebAPIのエンドポイントに対して、SQL文を解釈し、HTTPリクエスト形式に変換して発行します。リクエスト後、Jsonフォーマットで返ってきたデータセットはCData REST ODBC DriverがODBCインタフェースのResultsetに変換してBI & Officeツールに返します。

これにより、BI & OfficeツールからアドホックにSQLによるリクエストがあったタイミングで最新のデータを freee から取得することが可能です。それでは実際に、PowerBIやExcel、Accessからfreee内の取引データを取得してみたいと思います。

前提

• インターネットに接続可能なWindowsマシン
• クラウド会計ソフトfreeeのアカウント ※３０日間の無料試用版あり
• CData ODBC Drivers for REST ※３０日間の無料試用版あり
• PowerBI Desktop ※無料版あり
• Microsoft Office 2007以上(Excel/Access/Word)

freeeのAPIを利用するための準備

freee スタートガイド の「はじめに」の以下手順を実施して、AppIDとSecretを取得します。

CData ODBC Drivers for REST 製品のインストール、および、初期設定

CData ODBC Drivers for RESTWindows版のビルドをダウンロードします。ダウンロードすると、インストールファイル（.exe）がダウンロードされます。インストーラーを起動してソフトウェア使用許諾契約書を確認してデフォルトのままインストールします。

（注意） オンラインアクティベーションのため、インターネットへの接続が必要となります

インストールが完了すると、ODBCのDSN設定画面が起動します。一度、閉じてしまった場合には、WindowsのODBCデータソースアドミニストレーターを起動してください。Data Source Name(DSN)をセット（例：CData REST Source freee）して、下記の項目をセットします。

項目	値	備考
OAuthVersion	2.0
OAuthAccessTokenURL	https://api.freee.co.jp/oauth/token
OAuthAuthorizationURL	https://secure.freee.co.jp/oauth/authorize
OAuthClientId	***********************************	freee側のアプリケーション登録時のApp ID
OAuthClientSecret	***********************************	freee側のアプリケーション登録時のSecret
Callbackurl	http://localhost
Location	C:\Program Files\CData\freee	スキーマファイルの格納パス
Format	JSON
logfile	C:\Program Files\CData\freee\cdatalog\freee.log	(任意)CDataのログファイルを出力する場合、出力パスを指定
verbosity	3	(任意)CDataのログファイルを出力する場合、出力レベル（3）を指定

「接続のテスト」ボタンをクリックします。

ブラウザが起動して、freeeのログイン画面が表示されるので、freeeへのユーザIDおよびパスワードを入力してログインします。すると下記のような認証が出来た旨の画面と接続に成功して旨のダイアログが表示されれば、freeeへの一覧のOAuthによる認証認可のプロセスが完了したことになります。

「OK」ボタンをクリックして、「CData ODBC Driver for REST - DSN設定」の画面も「OK」ボタンをクリックして閉じます。

スキーマ定義の配置

Githubから「.rsd」ファイル群をダウンロードしてローカルディレクトリに配置します。

配置パスの例
C:\Program Files\CData\freee

PowerBI Desktopからの接続

事業所の確認

PowerBI Desktopの「データを取得」から「その他 > ODBC」を選択して「接続」ボタンをクリックします。

上記手順で作成したDSN（例：CData REST Source freee）を選択して「OK」ボタンをクリックします。

「ナビゲーター」ダイアログに移動するので、左側のツリーを開いて表示された表の中から「_companies（事業所）」テーブルを選択します。右側に、事業所一覧が表示されることを確認します。id(事業所ID)は後の手順で使用するので手元に控えておきます。

取引データ、および、各種マスターデータの確認

同じ手順で、今度は「_deals（取引）」テーブルを選択してデータを取得してみましょう。すると、「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示されます。

これは「_deals（取引）」テーブルに該当するAPIエンドポイントには、リクエスト時のパラメータとして事業所IDが必要です。今回は本事業所IDを指定していないため本エラーが発生しました。

（ご参考）freee API （収入／支出）一覧の取得

そこで、上記で取得した事業所IDをODBCのDSNにセットします。ODBCデータソースアドミニストレーターを開いて、DSNに下記のパラメータを追加してDSNを保存します。

追加する項目	値	備考
CustomUrlParams	company_id=******	上記手順で取得した事業所ID

再度、PowerBI Desktopの「ナビゲーター」ダイアログにて「_deals（取引）」テーブルを選択して、「最新の情報に更新」ボタンをクリックします。今度は、正常に「_deals（取引）」データがプレビューで表示することが出来ました。

続いて、他のテーブルも全て選択して「読み込み」ボタンをクリックします。

取引データを中心に、各マスターデータを以下の通り、結合キーを指定します。

カーディナリティ	テーブル（左）	結合キー（左）	テーブル（右）	結合キー（右）
多対一（*:1）	_deals（取引）	detailes_account_item_id	_account_items（勘定科目）	id
多対一（*:1）	_deals（取引）	conpany_id	_companies（事業所）	id
多対一（*:1）	_deals（取引）	partner_id	_partners（取引先）	id
多対一（*:1）	_deals（取引）	details_tax_id	_taxes（税区分）	id
多対一（*:1）	_deals（取引）	payments_from_walletable_id	_walletables（口座）	id

ビジュアライズ

Tableauワークブックを保存して、「シート１」に移動します。ドラッグ＆ドロップ操作で積み上げ棒グラフを選択して以下の通り、ディメンションおよびメジャー内の項目を右側ペインに配置します。

ディメンション・メジャー	項目	配置	備考
ディメンション（_account_items）	category	フィルタ	資本、差引損益計算、営業損益、経費
ディメンション（_account_items）	name	軸
ディメンション（_walletables）	name	凡例
ディメンション（メジャー）	ammount	値

取引データを、経費の勘定科目と口座毎に集計した積み上げグラフが作成出来ました。作成したファイルを保存してPowerBIを閉じます。

Excelからの利用

以下の手順はExcel201６を利用した場合のイメージです。他のExcelのバージョンをご利用の場合には各バージョンのイメージに読み替えてお進みください。Excelを起動して空白のブックを開きます。

「その他のデータソース」内の「データの接続ウィザードから」を開きます。

データ接続ウィザードが起動するので、「ODBC DSN」、作成したDSN（例：CData REST Source freee）を選択して「次へ」をクリックします。

　

データを取得するテーブルを選択します。今回は「_deals(取引)」を選択して次へ進みます。

データをインポートするセルを指定します。

Excelシートに取引データをインポートする事が出来ました。

（注意）　「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示される場合には、「PowerBI Desktopからの接続 - 取引データ、および、各種マスターデータの確認」記載の通りODBCのDSNに「CustomUrlParams」を追加してください。

「データ」タブ内の「更新」ボタンをクリックするとfreee内の最新の取引データを取得することが出来ます。必要に応じてExcelブックを保存してExcelを閉じます。

Accessからの利用

以下の手順はAccess2016を利用した場合のイメージです。他のAccessのバージョンをご利用の場合には各バージョンのイメージに読み替えてお進みください。Accessを起動して空のデータベースを開きます。

「新しいデータソース > 他のソースから > ODBCデータソース」を開きます。

「外部データの取り込み - ODBCデータソース」ダイアログが開くので、「リンクテーブルを作成してソース　データにリンクする」を選択して「OK」ボタンをクリックします。

「データソースの選択」ダイアログで「コンピュータデータソース」内の作成したDSN（例：CData REST Source freee）を選択して「OK」ボタンをクリックします。

「テーブルのリンク」ダイアログで全テーブルを選択して「OK」ボタンをクリックします。

各テーブルの「固有レコード識別子の選択」ダイアログが表示されるので「id」を選択します。

緑色のアイコンのリンクテーブルが作成されたら左側のテーブルリスクから「REST_deals(取引)」を選択してダブルクリックして開きます。右側のペインに取引一覧が表示されることを確認します。

（注意）　「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示される場合には、「PowerBI Desktopからの接続 - 取引データ、および、各種マスターデータの確認」記載の通りODBCのDSNに「CustomUrlParams」を追加してください。

次に選択クエリーを作成します。

以下の通り、テーブル間のリレーションを作成します。

テーブル（左）	結合キー（左）	テーブル（右）	結合キー（右）	レコードを含める
_deals（取引）	detailes_account_item_id	_account_items（勘定科目）	id	REST_dealsを全レコード、 REST_account_itemsの同じ結合フィールドのレコードのみ
_deals（取引）	conpany_id	_companies（事業所）	id	REST_dealsを全レコード、 REST_account_itemsの同じ結合フィールドのレコードのみ
_deals（取引）	partner_id	_partners（取引先）	id	REST_dealsを全レコード、 REST_partnersの同じ結合フィールドのレコードのみ
_deals（取引）	details_tax_id	_taxes（税区分）	id	REST_dealsを全レコード、 REST_taxesの同じ結合フィールドのレコードのみ
_deals（取引）	payments_from_walletable_id	_walletables（口座）	id	REST_dealsを全レコード、 REST_walletablesの同じ結合フィールドのレコードのみ

以下の通り、表示する項目を作成します。

フィールド	テーブル	集計	並び替え	表示
names	REST_account_items	グループ化		ON
names	REST_walletables	グループ化		ON
account	REST_deals	合計		ON

クエリーを実行すると、勘定科目毎・口座毎の合計金額が表示されます。

（補足）freee用スキーマ定義の生成方法

上記手順「スキーマ定義の配置」で使用したスキーマファイル「．rsd」ファイルは下記のストアドプロシージャを実行して生成することも可能です。ストアドプロシージャを実行できるツール（VisualStudioのサーバーエクスプローラーなど)から実行ください。

EXEC CreateSchema @TableName='companies', @URI='https://api.freee.co.jp/api/1/companies', @XPath='/companies', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='companies.rsd';
EXEC CreateSchema @TableName='deals', @URI='https://api.freee.co.jp/api/1/deals', @XPath='/deals', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='deals.rsd';
EXEC CreateSchema @TableName='partners', @URI='https://api.freee.co.jp/api/1/partners', @XPath='/partners', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='partners.rsd';
EXEC CreateSchema @TableName='account_items', @URI='https://api.freee.co.jp/api/1/account_items', @XPath='/account_items', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='account_items.rsd';
EXEC CreateSchema @TableName='taxes', @URI='https://api.freee.co.jp/api/1/taxes', @XPath='/taxes', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='taxes.rsd';
EXEC CreateSchema @TableName='walletables', @URI='https://api.freee.co.jp/api/1/walletables', @XPath='/walletables', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='walletables.rsd';

まとめ

本記事では、freee内の最新の取引データをCData Drivers for RESTを利用してBIツール（PowerBI Desktop）、および、Officeツール（Excel/Access）から利用する方法について説明しました。前提として必要なサービスやソフトウェアは試用版があるので、是非、お試しください。