Edited at

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内の取引データを取得してみたいと思います。


前提


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

ディメンション・メジャー
項目
配置
備考

ディメンション(_account_items)
category
フィルタ
資本、差引損益計算、営業損益、経費

ディメンション(_account_items)
name

ディメンション(_walletables)
name
凡例

ディメンション(メジャー)
ammount

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


Excelからの利用

以下の手順はExcel2016を利用した場合のイメージです。他の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)から利用する方法について説明しました。前提として必要なサービスやソフトウェアは試用版があるので、是非、お試しください。