freee×GAS　キャッシュフロー計算書（間接法）の作成の自動化してみた

前提

環境

GoogleAppScript（V8エンジン未使用）
Googleスプレッドシート
freeeAPI（Ver.2020-06-15には未対応）

やりたいこと

freeeにはキャッシュフロー計算書の作成機能がないので、それをGASを使って自動化したい！

やらないこと

アクセストークンの取得や集計対象会社のidの取得などは割愛しています。
スクリプトを実行するとスプレッドシートのAPIを叩きすぎの注意が出るのですが、あくまで勉強目的で分かりやすさ重視だったので、結果良ければすべてよしということで細かいことは気にしていません。（これは一旦、配列にまとめ入出力を１回にするだけで解決できると思います。）
キャッシュフロー計算書は間接法を前提とし、直接法は検討しません。（直接法は標準レポートにあるような気もするので）
一つの科目が複数のキャッシュフロー区分の源泉となる場合は検討しません。

下準備

使いそうな関数の定義

まずは、freeeAPIからデータを取得するための関数を作ります。


function getRequest(endpoint) {
  ENDPOINT = 'https://api.freee.co.jp/api/1/' + endpoint
  
  var freeeApp = getService(); //getService関数はfreeeAPIドキュメント参照*1
  var accessToken = freeeApp.getAccessToken();
  var requestUrl =ENDPOINT;
  var headers = { "Authorization" : "Bearer " + accessToken };
  var options ={
     "method"  : "get",
     "headers" : headers
  };
  var res = UrlFetchApp.fetch( requestUrl , options ).getContentText();    
  var obj = JSON.parse(res);
  return obj;
}

function getTrialBS_two_years(company_id) {
  
  if (company_id) {
    var obj = getRequest('reports/trial_bs_two_years?company_id=' + company_id);
  } else {
    Logger.log('ERROR:getTrialBS_two_years関数は引数Company_idが必須です。')    
  }
  return obj 
}

function getTrialPL_two_years(company_id) {
  if (company_id) {
    var obj = getRequest('reports/trial_pl_two_years?company_id=' + company_id);
  } else {
    Logger.log('ERROR:getTrialPL_two_years関数は引数Company_idが必須です。')    
  }
  return obj 
}

function getTrialBS(company_id) {
  if (company_id) {
    var obj = getRequest('reports/trial_bs?company_id=' + company_id);
  } else {
    Logger.log('ERROR:getTrialBS関数は引数Company_idが必須です。');
  }
  return obj;
}

function getTrialPL(company_id) {
  if (company_id) {
    var obj = getRequest('reports/trial_pl?company_id=' + company_id);
  } else {
    Logger.log('ERROR:getTrialPL関数は引数Company_idが必須です。');
  }
  return obj;
}

*1 freee-APIのアクセストークンを取得する

これで、定義した関数にcompany_idを渡せば２期の試算表と単年度の試算表のオブジェクトを取得することができるようになりました。

本題

各科目とキャッシュフローの関連付けをする

試算表の取得ができるようになったので、その試算表に記載のある科目がキャッシュフロー計算書のどの区分に該当するか設定ができるようにします。
そのため、Googleスプレッドシートでキャッシュフロー計算書のひな型シートと設定用のシートを準備します。

キャッシュフロー計算書のひな型

シート名：cash_flow_statement

表示形式は私の趣味がはいっていますが、重要なことはB列に入っている項目をベースに集計し、それをE列に表示するコンセプトで行こうと思います。
C列D列は小計・合計列として使用したいので、あらかじめE列を対象としたSUM関数などを入れておきます。

科目を紐づけるための設定シート

シート名：setting_CFS
こちらはシートの作成のみとし、内容はGASで記載していく形にしようと思います。

キャッシュフロー計算書と各科目のルールを設定するためのシートを作成するスクリプト

科目はすべての科目を取得してもいいのですが、設定項目が多くなるめんどくささを回避するため、２期比較試算表から取得することで、今期又は前期使用した科目のみを設定対象とします。

また、cash_flow_statementシートのB列を、取得した科目の右の列にプルダウンリストとして表示し、設定しやすくしてみます。

function settingCashFlowStatement (company_id) {
  var company_id = '**********'; //集計対象会社のcompany_idを入力
  var bsTwoYears = getTrialBS_two_years(company_id);
  var plTwoYears = getTrialPL_two_years(company_id);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settingSheet = ss.getSheetByName('setting_CFS');
  var statementSheet = ss.getSheetByName('cash_flow_statement');
  var row = 1;

  //プルダウンリスト生成用関数
  var setValidationList = function (rng, list) {
    var rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(list, true)
    .build();
    rng.setDataValidation(rule);
  }
  
  //プルダウンリスト項目生成
  var ruleList = createRuleList(statementSheet);

  //現在の設定保存（２回目以降毎回設定しないため）
  var currentSetting = saveSetting(settingSheet);
  
  //settingSheet内容生成
  settingSheet.clearContents();
  settingSheet.getRange(row, 1).setValue('貸借対照表科目');
  row++;
  
  for each (account in bsTwoYears.trial_bs_two_years.balances) {
    if (!account.total_line) {
      settingSheet.getRange(row, 2).setValue(account.account_item_name);
      settingSheet.getRange(row, 4).setValue(currentSetting[account.account_item_name]);
    } else {
      settingSheet.getRange(row, 3).setValue(account.account_category_name);
      settingSheet.getRange(row, 4).setValue(currentSetting[account.account_category_name]);
    }
    setValidationList(settingSheet.getRange(row, 4), ruleList);
    row++;
  }

  settingSheet.getRange(row, 1).setValue('損益計算書科目');
  row++;
  
  for each (account in plTwoYears.trial_pl_two_years.balances) {
    if (!account.total_line) {
      settingSheet.getRange(row, 2).setValue(account.account_item_name);      
      settingSheet.getRange(row, 4).setValue(currentSetting[account.account_item_name]);
    } else {
      settingSheet.getRange(row, 3).setValue(account.account_category_name);
      settingSheet.getRange(row, 4).setValue(currentSetting[account.account_category_name]);
    }
    setValidationList(settingSheet.getRange(row, 4), ruleList);
    row++;
  }

}

function saveSetting (settingSheet) {
  //現在の設定保存
  var currentSetting = {};
  for (var i = 1; i <= settingSheet.getLastRow(); i++) {
    var key = settingSheet.getRange(i, 2).getValue() + settingSheet.getRange(i,3).getValue();
    var value = settingSheet.getRange(i, 4).getValue();
    if (key) {
      currentSetting[key] = value;    
    }
  }
  currentSetting.undefined = '無視';
  return currentSetting;
}

function createRuleList (statementSheet) {
  var ruleList = ['無視'];
  for (var i = 1; i <= statementSheet.getLastRow(); i++) {
    if (statementSheet.getRange(i, 2).getValue()) {
      ruleList.push(statementSheet.getRange(i, 2).getValue());
    }
  }
  return ruleList;
}

実行後のsetting_CFSシート
undefinedになっている部分を適切な区分に変更します。
１度設定してしまえば、２回目以降は同じ科目について設定する必要はないようにしています。

設定した内容をもとに、計算を行いキャッシュフロー計算書を作成する

ここまでくればキャッシュフロー計算書に必要な情報はすべてそろったので、各科目についてルール毎に集計し、集計結果を出力します。

また、集計方法としては下記のルールで集計します。

資産科目は増加をマイナス、減少をプラスになるように集計
負債・純資産科目は増加をプラス、減少をマイナスになるように集計
収益項目は増加をプラス、減少をマイナスとなるように集計
費用項目は増加をマイナス、減少をプラスとなるように集計
キャッシュフロー計算書（cash_flow_statement）のA列に「*」があるものは上記集計ルールを逆転させる。


function createCashFlowStatement() {
  var company_id = '**********';
  var bs = getTrialBS(company_id);
  var pl = getTrialPL(company_id);
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settingSheet = ss.getSheetByName('setting_CFS');
  var statementSheet = ss.getSheetByName('cash_flow_statement');
  var setting = saveSetting(settingSheet);
  
  //各ルールの集計結果をresultオブジェクトへ
  var result = {};
  var ruleList = createRuleList(statementSheet);
  for each (rule in ruleList) {
    result[rule] = 0;
  }
  
  calNetIncrese = function (accountItem) {
    return accountItem.credit_amount - accountItem.debit_amount;
  }
  
  for each (accountItem in bs.trial_bs.balances) {
    if (setting[accountItem.account_item_name] != '無視') {
      var netIncrese = calNetIncrese(accountItem);
      result[setting[accountItem.account_item_name]] += netIncrese;    
    } else if (setting[accountItem.account_category_name] != '無視') {
      var netIncrese = calNetIncrese(accountItem);
      result[setting[accountItem.account_category_name]] += netIncrese;
    }
  }

  for each (accountItem in pl.trial_pl.balances) {
    if (setting[accountItem.account_item_name] != '無視') {
      var netIncrese = calNetIncrese(accountItem);
      result[setting[accountItem.account_item_name]] += netIncrese;    
    } else if (setting[accountItem.account_category_name] != '無視') {
      var netIncrese = calNetIncrese(accountItem);
      result[setting[accountItem.account_category_name]] += netIncrese;    
    }
  }  
  
  for (var row = 1; row < statementSheet.getLastRow(); row++) {
    var key = statementSheet.getRange(row, 2).getValue();
    if (key) {
      if (statementSheet.getRange(row, 1).getValue() == '*') {
        statementSheet.getRange(row, 5).setValue(result[key] * -1);      
      } else {
        statementSheet.getRange(row, 5).setValue(result[key]);
      }
    }
  }
  
}

本当はこんなにgetRangeしないほうがいいです。一旦配列にして、getRangeの回数を減らしましょう。for分の中にgetRangeを入れるなど見る人が見たら発狂ものな気もします。

これで、キャッシュフロー計算書のシート（cash_flow_statement）に必要な各区分の情報は出力されていると思います。（よく考えたら現金の期首残、期末残を表示してなかった！まぁそこは簡単なので適宜追加で）
あとはお好みでスプレッドシート上で編集するといいと思います。

まとめ

freeeAPI意外といい感じ
GASの定期実行を絡めて、毎月月末に提携取引を登録したり、その日の売上の情報をリストの送付先にGmailで送ったりは簡単にできそうな感じでした。

大変だったのはJSONの形式がちょっとややこしく感じたので、リクエスト段階で欲しい情報を絞れると使いやすい気がします。

現状認識している不具合

多分「法人税等」については、科目名とカテゴリー名が重複しているので、２回計算してしまうかもしれません。カテゴリー名が法人税等の時は無視するようにif文の追加が必要なようです。