取引先別の売掛金（取引先コード順）の試算表を作ってみた〜会計ソフト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など）に通知する
・エラー内容をブラウザ上にアラートとして表示する
・エラー箇所を明示した上でエラー内容を通知する
まとめ
いかがでしたでしょうか。
今回は、取引先別の売掛金の試算表を取引先コード順でスプレッドシートに出力する方法をお伝えしました。
自社の業務に沿ったカスタマイズを行い、業務効率化などのお力になれれば幸いです。
＊本記事に関するご質問や感想はコメント欄で受け付けております。
