会計freeeの月次推移表データをGoogle Spreadsheetに転記する

Update

4/13：スプレッドシートへのAPIアクセスを一回にするため、スプレッドシートに書き出す処理を変更しました。
前回は会計月・科目名・数値等を１つずつ配列に格納→それぞれの配列にsetValuesを適用していましたが、スプレッドシートの1行ずつのデータごとに2元配列を作成し、setValuesの呼び出しを1回だけにすることでパフォーマンス向上を図ってみました。

はじめに

初投稿になります。文系非エンジニア・バックオフィス担当です。
財務経理や人事労務などのクラウドサービスでAPIを叩き、経営管理資料の効率的作成などの手法を広めて行きたく投稿いたしました。

現職についてからプログラミング独学で学習しております。
見よう見まねで作成・投稿しており、お作法などまだわかっておりません。申し訳ございません。。。

足りない点、コードが汚い点など多々あるかと存じますが、ご指摘などいただけますと幸いです。

投稿の目的

以下のようなことを目指し、経営管理に少しでも有用な情報を提供することを目的としています。

• バックオフィスは、CSVなどで手作業で管理資料など作る事が多くてやや前時代的
• 一方で、会計ソフトや人事労務管理ソフトは（ベンチャー中心に）クラウドサービスが用いられることが多くなり、APIを提供していることも多い。
• APIを使うと、ある程度コードをかければデータの入出力が自動化できるようになり、バックオフィスの手作業が減らせる
• データの入出力や加工は自動化し、中身の分析や示唆出し・提言などに集中できると、よりバックオフィスの存在意義が高まる

今回の対象業務

予実管理などで行う会計システムからの月次実績取得を自動化することを目的としています。

【課題】

• 経理・経営企画部門では、予実管理などを行うときに会計ソフトからCSVでデータを出力してきて、エクセルやスプレッドシートでかなりの時間をかけて実績集計を実施している。

【対策】

• 会計freeeのAPI経由でGoogle Speadsheetに日次・週次等で試算表・月次推移表が自動更新されるようになれば、わざわざCSVにして加工する工数が省ける

事前準備

①用意するもの

• 会計freeeアカウント
• Googleアカウント

GAS自体のやAPIなどの解説は詳細に記載できるレベルでは到底なく、関連書籍・他の方の投稿などに詳しく書かれているかと存じますので、この記事では割愛いたします。

freeeを選んだ理由は（私自身が所属組織などで使っていることもありますが）、財務諸表アウトプットのAPIが充実しているためです。

また、同様のことはエクセルVBAでも実現できますが、今回はGoogle Apps Script（GAS）を用い、Googleスプレッドシートで実施しています。

②実施の前提

GoogleスプレッドシートにAPI経由でfreeeのデータを落としてくるには、

1. freeeで自身が作成するプログラム（アプリ）の登録を行い、アプリのID、Secretを作成すること（freeeアプリストア。開発者ページにてアプリを作成します。）
2. Google Apps Scriptでfreeeでの認証プログラムを作成し、認証を行う（【freee API】GASを用いてGoogleスプレッドシートと連携する、freee APIのアクセストークンを取得する）

ことが必要です。これらはfreeeのヘルプに詳細に記載されていますので、今回は割愛いたします。
また、取得できるデータの構造などはfreee APIリファレンスをご参照ください。

#コード

事業所IDの取得

試算表（月次推移表）のデータを取得する前に、まずお使いのfreeeの事業所（会社）のIDを取得する必要があります。
取得した値を変数などに格納して再利用するほうが良いかと思いますが、今回はテストでログに出力した値をそのまま用います。

function getCompanyId() {
  var freeeApp = getService();
  var accessToken = freeeApp.getAccessToken();
  
  //リクエストURLとヘッダー、オプションの指定
  var requestUrl = "https://api.freee.co.jp/api/1/companies?";
  var headers = { 
     "Authorization" : "Bearer " + accessToken 
  };
  var options = {
     "method"  : "get",
     "headers" : headers
   };

  //データ取得・ログに表示
  var res = UrlFetchApp.fetch( requestUrl , options ).getContentText();
  Logger.log(res)
} 

これを実行すると、ログに以下のようなレスポンスが表示されます。この中の「id」を変数に格納し、用います。

各月の試算表データを取得し、スプレッドシートに転記

①freeeから特定の月の試算表を取得する関数を作成、②パラメータで指定した会計月を集計用に見やすくする関数を作成、③各月の試算表PLデータを取得してスプレッドシートに転記、の３つの処理に分けています。
試算表データにおける勘定科目は、個別科目と段階損益でデータの形が異なるので、if文で処理を分けています。

//パラメータ・仕様するシートの指定。会計年度は、２０１９年１２月を最終月とする１２ヶ月の想定です。
var COMPANY_ID = 9999999 //左記はダミー。上記で取得した事業所idを整数で入力します;
var FISCAL_YEAR = 2019;
var FISCAL_MONTHS = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data_sheet = ss.getSheetByName("Data");

//各月の試算表を取得し、スプレッドシートに書き出す処理
function getMonthlyPl() {
  //各月の試算表を配列に格納
  var monthly_pls = [getPl(month) for each (month in FISCAL_MONTHS)];
  //スプレッドシートで会計期間がわかりやすく表示されるように整形
  var fiscal_month = setFiscalMonth();

  //取得した各値を空の配列に挿入
  row_data_list = [];

  for (var pl = 0; pl < monthly_pls.length; pl++){
    for (var item = 0; item < monthly_pls[pl].length; item++){
      if(!monthly_pls[pl][item].account_item_id){
        row_data_list.push([fiscal_month[pl],
                       monthly_pls[pl][item].account_category_id,
                       monthly_pls[pl][item].account_category_name,
                       monthly_pls[pl][item].parent_account_category_id,
                       monthly_pls[pl][item].parent_account_category_name,
                       monthly_pls[pl][item].closing_balance
                      ])
      }else{
        row_data_list.push([fiscal_month[pl],
                       monthly_pls[pl][item].account_item_id,
                       monthly_pls[pl][item].account_item_name,
                       monthly_pls[pl][item].parent_account_category_id,
                       monthly_pls[pl][item].parent_account_category_name,
                       monthly_pls[pl][item].closing_balance
                      ])
      }
    }
  };
  //スプレッドシートに転記
  data_sheet.getRange(1, 1, row_data_list.length, row_data_list[0].length).setValues(row_data_list);
  
}

//PLをfreeeから取得する処理
function getPl(month) {
  var freeeApp = getService();
  var accessToken = freeeApp.getAccessToken(); 
 
  //パラメータの指定
  var url = "https://api.freee.co.jp/api/1/reports/trial_pl?"
  url += "company_id=" + COMPANY_ID;
  url += "&fiscal_year=" + FISCAL_YEAR;
  url += "&start_month=" + month;
  url += "&end_month=" + month;
  
  var headers = { 
      "Authorization" : "Bearer " + accessToken 
  };
  
  var options = {
     "method"  : "get",
     "headers" : headers
  };
 
  //データを取得し、JSONに変換
  var res = UrlFetchApp.fetch( url , options ).getContentText();
  res = JSON.parse(res);
  var pl = res.trial_pl.balances;
  return pl;
}

//パラメータで指定した月を会計月に変換。
function setFiscalMonth(){
  var fiscal_month = [];
  FISCAL_MONTHS.forEach(function(e){
    if(e < 10){
      fiscal_month.push(FISCAL_YEAR + "-0" + e)
    }else{
      fiscal_month.push(FISCAL_YEAR + "-" + e)
    }
  })

  return fiscal_month;
}

実行結果

こんな感じでスプレッドシートにデータが転記されます。あとは、GASのトリガーで日次更新などにしておけば最新のデータが更新されます。
取得したデータの整形・加工については、このデータに基づきスプレッドシート上で実行しても良いですし、別のコードを書いてそこも自動化してしまっても良いと思います（今回は長くなるので割愛します）。

おわりに

とりあえず実用的に「動くもの」を作る段階での投稿となってしまいましたが、まだまだコードの書き方が汚かったり、様々至らぬ点があるかと存じますが、何卒ご容赦ください。
これの応用によりう、部門別・セグメント別データの取得や、（連結手続の複雑さにもよりますが）簡易的な連結PLも作成可能ですので、どこかで記載できればと思います。