見出し画像

会計Freee x google spreadsheetで作る予実管理part1

岩澤　樹/NPO業務Hack

Freee APIとGASを使って、会計Freeeのデータをスプレッドシートに出力したのでそのまとめ。
下手すれば手動でCSV出力して貼り付けないといけなかったのでプチ業務ハック。

今回実現したこと

会計Freee上の実績から、月ごとにエクスポートして、予算と付け合せる。
会計Freeeから取得するデータは次の4つ。

年月、勘定科目、部門、金額

そこからごちゃごちゃして、こんな感じのデータに仕上げました。
要するに、事業テーマ（サービスごと）にカテゴリ（科目のグルーピング）したものを予算と実績で毎月見ていきたい。

画像1

この行列のグルーピングやフィルタリングは見る人によって変わるので、Google spreadSheetに出力して、ピボットテーブルで変更を容易にしています。

そもそもアドイン使えばよかったのでは？

Google スプレッドシート連携アドオンという便利なものがあるので最初はこれを使おうと思っていました。

ただ、使ってみて2点問題が出てしまったので、API使うことにしました。

１）配賦仕訳の有無を選択できない
２）試算表を月ごとに出せない

特に（２）が予実管理する上で影響が大きかったです。

実装したコード

ということで上記サイトを参考に久々のGASのコーディング！（だいたい初見6時間くらい…）

コーディング１～事業所IDの取得～

そもそも事業所IDがわからないのかよ！ということで躓きました。
※後述するfunctionがないと動きません。
これを実行するとログに事業所IDが出力されます。

function get_companies() {
 var freeeApp = getService();
 var accessToken = freeeApp.getAccessToken();
 var requestUrl ="https://api.freee.co.jp/api/1/companies";
 var headers = { "Authorization" : "Bearer " + accessToken };
 var targetSheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var options ={
    "method"  : "get",
    "headers" : headers
  };
 var res = UrlFetchApp.fetch( requestUrl , options ).getContentText();
 //レスポンスのデータを配列に格納
 var parsedResponse = JSON.parse( res );
 var data = parsedResponse.companies;
 for ( var i = 0 ; i < data.length ; i++ ) {
   var company_id = [data[i].id];
   Logger.log(company_id);
 };
}

コーディング２～認証機能の実装～

【freee API】GASを用いてGoogleスプレッドシートと連携するの認証・アクセストークンの取得をそのまま使えばいけます。
ちなみに、freee APIのアクセストークンを取得するで作成したアプリケーションの公開ステータスは「非公開」、申請ステータスは「下書き」のままでOKです。

画像2

コーディング３～Freeeからのエクスポート～

そして、Freeeからのエクスポートと、スプレッドシートへの出力のコードです。
途中で毎月実行するためにfunction分けたときに、変数の定義をめんどくさがってグローバルなところに無駄が多かったりと、普通に怒られそうな内容になっていますが、ここまでやればきれいにするのは何とか出来るのでご容赦を…

var arr = [];
var param1 = "";
var param2 = "";
var param3 = "";
var param4 = "";
var freeeApp = getService();
var accessToken = freeeApp.getAccessToken();
var settingss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("設定シート");
var requestUrl =settingss.getRange(2,2).getValue();
var fiscal_year =settingss.getRange(7,2).getValue();
var startdate = settingss.getRange(8,2).getValue();
var enddate = settingss.getRange(9,2).getValue();
var year = fiscal_year;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("出力シート");
var numFormats = '#,##0;[Red]▲#,##0';

var headers = { "Authorization" : "Bearer " + accessToken };
var options ={
 "method"  : "get",
 "headers" : headers
}

function getRequest() {
 sheet.clear();
 param1 = ["年月"];
 param2 = ["勘定科目"];
 param3 = ["部門"];
 param4 = ["金額"];
 arr.push([param1,param2,param3,param4]);
 var row = arr.length; //タテ（行数）
 var colum = arr[0].length; //ヨコ（列数）
 sheet.getRange(1, 1, row, colum).setValues(arr);
 row++;
 var month = startdate;

 //9月～12月なら会計年度まま、それ以外なら、会計年度＋１
 if(startdate>8){
   var sDate=new Date(fiscal_year,startdate);
 }else{
   var sDate=new Date(fiscal_year+1,startdate);
   year++;
 }
 if(enddate>8){
   var eDate=new Date(fiscal_year,enddate);
 }else{
   var eDate=new Date(fiscal_year+1,enddate);
 }

 //指定期間のデータを取得
 for(;sDate<=eDate;sDate.setMonth(sDate.getMonth()+1)){
   row += export_freee(year,month,row);
   month++;
   if(month==13){
     month = 1;
     year++;
   }  
 }
 sheet.getRange(2, 4, row, 4).setNumberFormat( numFormats );
}

function export_freee(year,x,y){
   var arr=[];
   requestUrl += "&start_month"+x+"&end_month"+x;
   Logger.log(requestUrl);
   var res = UrlFetchApp.fetch( requestUrl , options ).getContentText();
   //レスポンスのデータを配列に格納
   var parsedResponse = JSON.parse( res );
   var data = parsedResponse.trial_pl.balances;
   for ( var i = 0 ; i < data.length ; i++ ) {
     param1 = new Date(year,x,0);
     param2 = data[i].account_item_name;
     if(typeof(data[i].sections) != "undefined"){
       for ( var j = 0; j < data[i].sections.length ; j++ ) {
         param3 = data[i].sections[j].name;
         param4 = data[i].sections[j].closing_balance;
         arr.push([param1,param2,param3,param4]);
       }
     }
   }
 var row = arr.length; //タテ（行数）
 var colum = arr[0].length; //ヨコ（列数）
 
 //配列arrをセットする
 sheet.getRange(y, 1, row, colum).setValues(arr);
 return arr.length;
}

参考までに「設定シート」シート

画像3

ちなみに、このままだとさっきのピボットテーブルはできません。
手動でピボットテーブル化しないといけないのと、テーマやカテゴリといったものの定義を別シートで行い、値を参照させています。

難航したポイント～難敵は「undefined」～

JSONすげーーー！！！ってなりました。ようやくJSONのすごさがわかりました。ただ、そこに至るまで意味がわからなかったり、そもそもFreeeの多次元配列の構造を理解していなかったりで悪戦苦闘しました。

最終的に私を救ってくれたのは、この1行のコード。

Logger.log(data[i]);

このコードでログに1行分のデータが出力されるの。リファレンスを読めばわかるんだけど、これが私を救ってくれました。

https://developer.freee.co.jp/docs/accounting/reference

ちなみに、何に引っかかっていたかって、「undefined」。
多次元配列どうこうをずっと調べていたんだけど、そうじゃなくて「undefined」でした。試算表を出力すると、勘定科目をグルーピングした分類まで引っ張ってくるようで、そのデータには勘定科目名がないみたい。

やっぱり取得したデータをログで確認するのって大事。

難航したポイント～日付計算～

普段スプレッドシート上だと、「edate」関数を使うんだけど、GAS上だと何使うんだろうーで色々調べました。

//日付変数の作成（会計年度と、取得開始月を指定）
var sDate=new Date(fiscal_year,startdate);
//1カ月後にする
sDate.setMonth(sDate.getMonth()+1)

くだらない話ですが、「getMonth」の後ろに「（）」がないことに気づかないで長々とさまよっていました。

以上、奮闘したよって報告の記事でした。

LINEWORKS API連携、SalesforceのAPIを使ったフォーム登録、そして今回。
だいぶAPIとかの理解が深まってきました。
認証周りとかわからないし、もっと効率の良いコードの書き方とかあると思うけどとにかくアウトプット。やりながら学ぶ。

さて、part1と書きましたが、ぶっちゃけ今回のだけだと予実管理としては不十分なんです。
まだまだ検討する部分は多くて、以下どうするかはこれから。
・事業KPIとの紐づけ
・予算取得をどうするか
・予実差と、収支計算をどう出力させるか
・配賦計算をどう出力させるか（配賦の有無で値を変えるのは実装済み）
・詳細（Freee）への繋ぎをどうするか

引き続き頑張ります！

この記事が気に入ったらサポートをしてみませんか？