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

前回Freeeからデータを取得する方法を整理しました。

予実管理ということで問題になったのが予算です。
予算のデータ構造と、実績の構造が違うのです…

ということで、今回は汎用性を考えて月別予算のシートのインポートファイルを作成するということをやってみました。

部門別、月別の残高試算表の入手

こういうやつですね。
会計システムを使っていれば、実績も同じ形で出ると思います。
「もし現行予算がこういった形になっていないよ！」という場合は、作ってください←

カテゴリ別にまとまっている可能性もありますが、その場合は実績の方を整理してあげるのが早いと思います。予算の管理単位に合わせるのが良いと思います。（勘定科目ごとは管理する上で細かすぎる場合もあるので）

エクスポート用のGASの作成

さて、それではGASで試算表を集計前に戻したいと思います。
項目は以下で設定しました。

年月,予実,勘定科目,部署,金額,シナリオ

機能別に分けて記載します。
まずメインから。取得対象のシートの取得と、シナリオ名を記載しています。
また、今回試算表のA1セルに部署名を入れている想定です。
※実務では対話形式にして、シナリオ名は入力してもらい、部署名は選択リスト値から選択し、選択した部署名の試算表を取得する動きにします。

//main関数
function dataexport() {
 //対象スプレッドシートの取得
 var ss = SpreadsheetApp.getActiveSpreadsheet();

 //シナリオ名
 var scenario = "デフォルト";
 
 //部署名
 var group = ss.getSheetByName("エクスポートシート").getRange(1, 1).getValue();
 
 //データの取得
 var data=get_data(ss);
 
 //データの出力
 var edata=edit_data(ss,scenario,group,data);  
}

まず処理するためのデータを取得します。
エクスポートシートというシート名で部署ごとの試算表を保管している想定です。
ここではシンプルに試算表の内容を取得して配列として戻しています。

//元データの取得
function get_data(ss){
 //対象シート名の取得
 var sheet = ss.getSheetByName("エクスポートシート");
 
 //最終行の取得
 var lastrow = sheet.getLastRow();
 
 //最終列の取得
 var lastcolumn = sheet.getLastColumn();
 
 //配列を取得
 var data = sheet.getRange(1, 1, lastrow,lastcolumn).getValues();
 
 //配列を返す
 return data;
}

次に出力用のデータを作っていきます。
最初にヘッダーとして項目名を入れ、そのあと取り込んだ配列を取り込むデータに加工していきます。
また0円、空白のデータは取り込まないようにしています。

//出力用シートへの出力
function edit_data(ss,scenario,group,data){
 //クロス集計を元に戻す
 var sheet = ss.getSheetByName("csv");
 sheet.clear();
 //ヘッダー：年月,予実,勘定科目,部門,金額
 var header = [];
 var param1 = ["年月"];
 var param2 = ["予実"];
 var param3 = ["勘定科目"];
 var param4 = ["部署"];
 var param5 = ["金額"];
 var param6 = ["シナリオ"];
 header.push([param1,param2,param3,param4,param5,param6]);
 var row = header.length;
 var colum = header[0].length;
 sheet.getRange(1, 1, row, colum).setValues(header);
 
 var arr = [];
 for(var i=1; i<data.length; i++){
   for(var j=1; j<data[i].length; j++){
     if(data[i][j]>0){
       param1 = data[0][j];//年月
       param2 = "予算";
       param3 = data[i][0];//勘定科目名
       param4 = group;
       param5 = data[i][j];//金額
       param6 = scenario;
       arr.push([param1,param2,param3,param4,param5,param6]);
     }
   }
 }
 //年月で並び替え
 arr.sort(function(a,b){return(a[0] - b[0]);});
 
 row = arr.length;
 sheet.getRange(2, 1, row, colum).setValues(arr);
}

実行するとこんな感じ。

前回作ったFreee x GASのデータがこちらなので、あとは「予実」って項目に「実績」と入るようにすればデータ形式が揃いました。

予実の比較

ということで、予算・実績のデータ形式が揃ったので、あとは分析ソフトやピボットテーブルを使ってわかりやすく表示すればOKです。
※ピボットテーブルで収支を見るために支出項目をマイナス表示させています。（気持ち悪いので何とかして直したい…）
※数値はランダム関数で入れています。

収支を予実比較するとこんな感じ。

（費用科目が多いから、ランダムの範囲が同じだとだとめっちゃ赤字になる…ｗ）

なぜGASで作ったか。

【初心者向けGAS】面倒なことはライブラリに任せよう！その概要と追加の方法

GASでプロジェクトをひとつ作っておけば、ライブラリとして活用することが可能です。やり方は上記リンクを見てください。

うちの場合予算を部署ごとにクローズで作っているので部署ごとにスプレッドシートのファイルが異なります。
こういうケースってそこそこあるんじゃないでしょうか？
少なくともスプレッドシートがバラバラなケース。

そういう時に作ったスクリプトを流用できると楽なのです。

今後の展望

今後の展望ですが、ここで作ったデータを手動でインポートするのはクソ面倒なので、全部署シート一括実行と、その実行結果をデータベースに入れるってことをしていきたいと思います。

あとはFreee側の方も直したいし、配賦仕訳のオン/オフによる変化とかも作っていきたい…

来年も頑張りますー！