取引先別の売掛金（取引先コード順）の試算表を作ってみた〜会計ソフトfreeeのAPI連携〜

はじめに

クラウド会計ソフトfreee（以下、freee）の試算表は、売掛金を取引先別で表示すると取引残高順で表示され、画面上では表示順を変更することはできません。しかし、「取引先コード順に試算表を確認したい」という方もいらっしゃるのではないでしょうか。

そこで、freeeAPIを活用してスプレッドシートに取引先コード順の取引先別の売掛金残高を出力・確認する方法を記事にしました。

なお、本記事では取引先別の売掛金（取引先コード順）の試算表の作り方ですが、例えば、売掛金を買掛金に置き換えるなどのカスタマイズは可能です。

対象読者

・freeeのユーザー

・freeeのAPI機能の仕組みを理解しているユーザー

・Google（スプレッドシート）のユーザー

・Google Apps Scriptを読み書き可能なユーザー

・経営者や財務、経理、経営管理などの管理職や担当者、会計・税理士事務所の関係者

を対象読者と想定して執筆しています。

この記事を読んでできるようになること

・freeeから取引先情報（取引先や取引先コード）を取得すること（パラーメーターの指定）

・freeeから期間指定した試算表（取引先別の売掛金）を取得すること（パラーメーターの指定）

・取得した取引先情報と試算表を連結させ、取引先コードを表示する試算表と作り変えること（データの加工）

・連結した取得情報をスプレッドシートに指定したシート、セルに出力し、並び替え、罫線などの必要な装飾を加えること（データの出力と表現）

実装方針

・プログラミング知識のないユーザーが操作でき、簡単に最新情報に更新できること

前提

・freeeのユーザーアカウント（権限は「取引先登録」以外）を保持しており、Googleアカウントでログインできる状態であること

・最新データはfreee上に登録してあること

・freeeのAPI認証済みであること。＊なお、本記事でのAPI認証方法はこちらの記事のサンプルを参考に実施しており、その方法は割愛しています。

・freeeの取引先設定で取引先コードを登録してあること

・freeeに登録済みの取引先数が500以下であること。*500を超える場合も対応可能ですが、一部、コードの書き換えが必要です。

・スプレッドシートに表示させるのは、取引先別の売掛金データとして、取引先コード、取引先名、期首残高、期中借方金額、期中貸方金額、期末残高を表示すること

目標成果物

取引先コード順に取引先別の売掛金の試算表を表示させる。

流れ

スプレッドシートの作成〜設定

スプレッドシートを新規作成

スプレッドシートを新規で作成します。＊次のURLをクリックすると簡単に作成することができます。spreadsheet.new

「売掛金残高」「日付」の名称でシートを作成する

・売掛金残高

・日付

という名称のシートを作成します。

Google Apps Scriptでコードを記述

スクリプトを表示

スプレッドシート　＝＞　ツール　＝＞　スクリプト　を選択

メインコード（Google Apps Script）

function getAccountReceivavle() {
  //スプレッドシートの設定
  var ss = SpreadsheetApp.openById("*********************************"); //スプレッドシートを指定する＊＊＊にはスプレッドシートIDを入力してください
  var date_sheet = ss.getSheetByName('日付'); //スプレッドシートのシートを指定する
  var start_date = date_sheet.getRange('A2').getDisplayValue(); //期首の日付を指定する
  var end_date = date_sheet.getRange('B2').getDisplayValue(); //期末の日付を指定する
  var sheet = ss.getSheetByName("売掛金残高"); //売掛金情報を出力したいシートを指定する

  //合鍵を制作する
  var freeeApp = getService(); //freeeのAPI認証
  var accessToken = freeeApp.getAccessToken();

  //パラメーターを指定する ***にはcompany_idが入ります
  var requestUrlBs = 
      "https://api.freee.co.jp/api/1/reports/trial_bs?company_id=**********&breakdown_display_type=partner" + '&end_date=' + end_date + '&start_date=' + start_date; //期間指定した取引先別のBS情報を取得するパラメーター
  var requestUrlPartners = "https://api.freee.co.jp/api/1/partners?company_id=**********&limit=500"; //取引先情報を取得するパラメーター
  //データをリクエストする
  var headers = { "Authorization" : "Bearer " + accessToken};
  var options = 
  { 
    "method" : "get",
    "headers" : headers
  };

  //レスポンスデータを受け取る
  var JSON_response_bs = UrlFetchApp.fetch( requestUrlBs , options ).getContentText();
  var JSON_response_partners = UrlFetchApp.fetch( requestUrlPartners , options ).getContentText();
  var Data_bs = JSON.parse( JSON_response_bs );
  var Data_partners = JSON.parse( JSON_response_partners );

  //レスポンスデータを加工する
  var Target = Data_bs.trial_bs.balances;
  var partners = Data_partners.partners;
  var partners_name_only = []; //取引先名の名前を保管する配列
  for(var p = 0;  p < partners.length; p++) {
    partners_name_only.push(
      partners[p].name
    );
  };
  var search = "売掛金"
  //加工済みのレスポンスデータの中で売掛金が配列の何番目に保存されているか検索し、該当する番号で繰り返し処理から抜ける
  for(var i = 0; i < Target.length; i++) {
    if(Target[i]["account_item_name"] == search) {
      var index = i;
      break;
    }
  }
  var Target = Target[index]["partners"]; //BSの中の売掛金だけを取引先別で変数に保存し直す
  var header_data = [[ //スプレッドシートへの出力用のヘッダー
    'コード',
    '取引先名',
    start_date + '時点',
    '借方金額',
    '貸方金額',
    end_date　+ '時点'
  ]];
  
  var account_receivavle_data = []; //売掛金情報を保存する配列

  for (var i = 0 ; i < Target.length ; i++) {
    if(Target[i].name == "未選択"){ //取引先が未選択の場合
      account_receivavle_data.push([
        "00000", //未選択用の仮コード
        "未選択", //取引先名を未選択で表示
        Target[i].opening_balance, //期首残高
        Target[i].debit_amount, //借方金額
        Target[i].credit_amount, //貸方金額
        Target[i].closing_balance //期末残高
      ]);    
    } else {
      account_receivavle_data.push([
        partners[partners_name_only.indexOf(Target[i].name)].code, //取引先一覧の配列の中から、売掛金のある取引先の取引先コードを出力
        Target[i].name, //取引先名
        Target[i].opening_balance, //期首残高
        Target[i].debit_amount, //借方金額
        Target[i].credit_amount, //貸方金額
        Target[i].closing_balance //期末残高
      ]);    
    }
  };
  
  //出力初期設定
  var body_row_length = account_receivavle_data.length; //中核となるデータの行数
  var all_row_length = body_row_length + 2;  //全データの行数
  var column_length = account_receivavle_data[0].length; //全タータの列数
  var color_black = '#000000'; //罫線の色
  var border_style_solid = SpreadsheetApp.BorderStyle.SOLID; //罫線のスタイル
  var range_all = sheet.getRange(1, 1, body_row_length + 2, column_length); //全データの範囲を指定
  var range_header = sheet.getRange(1, 1, 1, column_length); //見出しに当たる部分を指定
  var range_body = sheet.getRange(2, 1, body_row_length, column_length); //中核となるデータの範囲を指定
  var range_number = sheet.getRange(1, 3, all_row_length, 4); //データが数字である範囲を指定
  var range_footer = sheet.getRange(body_row_length + 2, 1, 1, column_length); //最終行となるフッターの範囲を指定
  sheet.clear(); //データをクリアする
  sheet.clearFormats(); //データのフォーマットをクリアする

  //出力する　データ
  range_header.setValues(header_data); //見出し部分にデータをセット（出力）する
  range_body.setValues(account_receivavle_data); //中核となる部分のデータをセット（出力）する
  range_body.sort(1); //データを昇順で並べ替える
  range_footer.setFormulas([[ //フッターのデータをセット（出力）する
    '',
    '',
    '=sum(C2:C'+ (body_row_length + 1) + ')', //SUM関数で各列の合計
    '=sum(D2:D'+ (body_row_length + 1) + ')',
    '=sum(E2:E'+ (body_row_length + 1) + ')',
    '=sum(F2:F'+ (body_row_length + 1) + ')'
  ]])
  range_number.setNumberFormat('#,##0'); //データが数字部分のフォーマットをセットする
  range_all.setBorder(true, true, true, true, true, true, color_black, border_style_solid); //罫線をセット（出力）する
  sheet.setColumnWidth(1, 100); //列の幅をセットする
  sheet.setColumnWidth(2, 180);
  sheet.setColumnWidth(3, 125);
  sheet.setColumnWidth(6, 125);
  for(var i = 0; i < all_row_length; i++) { //データが保存されている行の高さをセットする
    sheet.setRowHeight(i+1, 30);
  };
};

コマンドを表示させるコードを記述

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('売掛金コマンド');
  menu.addItem('売掛金情報を取得', 'getAccountReceivavle');
  menu.addToUi();
}

カスタマイズ事例

上記のコードをベースに、以下のようなカスタマイズも可能です。

他の勘定科目を取得する

・取引先別の買掛金（取引先コード順）の試算表を取得する

・取引先別の未払金（取引先コード順）の試算表を取得する

エラーを表示する

・取引先コードが未入力の場合、エラーを表示する

・取引先名が”未選択”の場合、エラーを表示する

・期末残高が金額が一定額以上であればエラーを表示する

・期首残高と期末残高の増加率が一定以上であればエラーを表示する

ユーザーに通知する

・エラー内容をMailやチャットツール（slackやchatworkなど）に通知する

・エラー内容をブラウザ上にアラートとして表示する

・エラー箇所を明示した上でエラー内容を通知する

まとめ

いかがでしたでしょうか。

今回は、取引先別の売掛金の試算表を取引先コード順でスプレッドシートに出力する方法をお伝えしました。

自社の業務に沿ったカスタマイズを行い、業務効率化などのお力になれれば幸いです。

＊本記事に関するご質問や感想はコメント欄で受け付けております。