こんにちは、もりです！
以前に、「取引先別の勘定科目残高を取得したい」といったご要望をいただいたので、書いてみました。
この記事で紹介するスクリプトを使うと、「各取引先の今月の売掛金の残高は？」といった内容を、ワンクリックでスプレッドシートに出力することができます。
また、これらのパラメータを、スプレッドシート上で自由に指定できます。
- 事業所
- 会計年度
- 開始月～終了月
- 勘定科目
※この記事で紹介するスクリプトを動作させるには、freeeAPIの連携アプリの作成と認証が済んでいることを前提としています。
詳しくは下記記事をご覧ください。
【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）GoogleAppsScriptを使用して、クラウド会計ソフトfreeeのAPI連携が可能です。freee連携アプリの登録方法、認証方法、サンプルコードを紹介しています。...
スポンサーリンク
Contents
取引先別の指定期間の勘定科目残高を取得する
まずは事前準備からです。
※この記事で紹介するスクリプトはV8ランタイムの実行環境で動作します
【事前準備1】スプレッドシートの準備
スプレッドシートに、パラメータ設定欄と、取得結果の出力欄を用意します（A列・取引先名の一覧作成は後述します）
- 黄色セル → パラメータを設定（入力）する
- 水色セル → 取得結果を出力する
【事前準備2】freeeAPIのサービスを取得する関数
GASのプロジェクト内に、freeeAPIのサービスを取得する関数を書いておく必要があります。
【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）
具体的には、「getService」関数と、その中で使われている「Client_ID」「Client_Secret」の値です。
スプレッドシートのセルに取引先リストを作成する
スプレッドシートのA列のセルに、指定の事業所の取引先リストを作成します。
デモ動画をご覧ください。H2セルで事業所名を選択（入力）してスクリプトを実行すると、A列に取引先リストが作成されます。
A列に取引先リストを作るスクリプトがこちらです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
function createPartnersList() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('科目別残高'); const company_name = sheet.getRange('H2').getValue(); const company_id = getCompanyId_(company_name); const accessToken = getService().getAccessToken(); //テンプレートリテラルで記述 const requestUrl = `https://api.freee.co.jp/api/1/partners?company_id=${company_id}&limit=100`; const obj = accessfreeeAPI_(accessToken,requestUrl); const objPartners = obj.partners; let arr = []; //取引先名を格納する配列 for (const i in objPartners) { arr.push([objPartners[i].name]); } sheet.getRange(2, 1, arr.length, arr[0].length).setValues(arr); } |
ポイントは、H2セルで選択（入力）した「事業所名」を、getCompanyId関数で「事業所ID」に変換していることです。複数の事業所を使用する場合に、切り替えがラクになります。詳細はこちらの記事をご覧ください。
【freeeAPI×GAS】スプレッドシートに事業所名リスト(プルダウン)を作ってfreeeAPIの事業所切り替えをラクにするGASを使って「クラウド会計ソフトfreee」のAPIを操作するシリーズ。スプレッドシートのリストで選択した事業所名を事業所IDに変換してfreeeAPIにアクセスする方法を紹介しています。...
指定期間・指定科目の残高を取得する
H2～H6セルで、取得したい条件を設定し、スクリプトを実行します。デモ動画をご覧ください。
（H6の勘定科目名の設定は、手入力でも、データの入力規則でリストを作っておいてもかまいません）
スクリプトがこちらです。「getPartnersAccountBalance」関数を実行すると、各取引先の残高を取得できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 |
//エントリポイント function getPartnersAccountBalance() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('科目別残高'); const PARAM_COL = 8; //前回取得値のクリア sheet.getRange(2, 2, sheet.getLastRow(), 4).clearContent(); const parameters = sheet.getRange(2, PARAM_COL, 5, 1).getValues(); const account_item_name = parameters[4][0]; //勘定科目名 const requestUrl = createUrl_(parameters); const accessToken = getService().getAccessToken(); const obj = accessfreeeAPI_(accessToken,requestUrl); const objBalances = obj.trial_bs.balances; if (!isAccountItemName_(objBalances,account_item_name)) { const ui = SpreadsheetApp.getUi(); ui.alert('取得結果','指定の勘定科目データが存在しません',ui.ButtonSet.OK); return; } let objPartners; for (const i in objBalances) {//指定科目のpartnersプロパティを抽出 if (objBalances[i].account_item_name === account_item_name) { objPartners = objBalances[i].partners; break; } } OutputPartnerBalance_(sheet,objPartners); //スプレッドシートに出力 } /* * 指定パラメータを付与したtrial_bsのリクエストURLを作成する * * @param {object} パラメータ * @return {string} リクエストURL */ function createUrl_(parameters) { const company_name = parameters[0][0]; //事業所名 const fiscal_year = parameters[1][0]; //会計年度 const start_month = parameters[2][0]; //開始月 const end_month = parameters[3][0]; //終了月 const company_id = getCompanyId_(company_name); //事業所IDに変換 //テンプレートリテラルで記述 const url = `https://api.freee.co.jp/api/1/reports/trial_bs?company_id=${company_id}&fiscal_year=${fiscal_year}&start_month=${start_month}&end_month=${end_month}&breakdown_display_type=partner`; return url; } /** * 事業所名から事業所IDを返す * * @param {string} 事業所名 * @return {string} 事業所ID */ function getCompanyId_(company_name) { const accessToken = getService().getAccessToken(); const requestUrl = 'https://api.freee.co.jp/api/1/companies'; let obj = accessfreeeAPI_(accessToken,requestUrl); obj = obj.companies; //companiesプロパティだけ抜き出す let company_id; for (const i in obj) { if (obj[i].name === company_name) { company_id = obj[i].id; break; } } return company_id; } /** * 指定のURLにGETリクエストを送信してレスポンスを返す * * @param {string} アクセストークン * @param {string} リクエストURL * @return {object} freeeAPIのレスポンス */ function accessfreeeAPI_(accessToken,url) { //短時間の連続アクセスを回避するため待機 Utilities.sleep(1000); const params = { method : 'get', headers : {'Authorization':'Bearer ' + accessToken} }; const response = UrlFetchApp.fetch(url,params); const obj = JSON.parse(response); return obj; } /** * 取引先ごとの残高をスプレッドシートに書き出す * * @param {object} シート * @param {object} 取引先の残高データ */ function OutputPartnerBalance_(sheet,obj) { //取引先名(A列)の最終行を取得 //参考記事：https://moripro.net/gas-get-specified-lastcol-lastrow/ const lastRow = sheet.getRange(sheet.getMaxRows(), 1) .getNextDataCell(SpreadsheetApp.Direction.UP) .getRow(); const values = sheet.getRange(2, 1, lastRow-1, 1).getValues(); const arrPartners = values.flat(); //一次元配列に変換 let arrAll = []; //スプレッドシート出力用配列 for (const partner_name of arrPartners) { if (!isPartnerName_(obj,partner_name)) { //オブジェクトの中に取引先名が無い場合はゼロ埋めして次へ arrAll.push([0,0,0,0]); continue; } for (const i in obj) { if (obj[i].name === partner_name) { let arrOne = []; //期首残高,借方金額,貸方金額,期末残高 let {opening_balance,debit_amount,credit_amount,closing_balance} = obj[i]; arrOne.push(opening_balance,debit_amount,credit_amount,closing_balance); arrAll.push(arrOne); break; //次の取引先へ } } } sheet.getRange(2, 2, arrAll.length, arrAll[0].length).setValues(arrAll); } /** * 残高データに指定の勘定科目名があるか確認する * * @param {object} 残高データ * @param {string} 勘定科目名 * @return {bool} 残高データに勘定科目名がある場合true */ function isAccountItemName_(obj,account_item_name) { for (const i in obj) { if (obj[i].account_item_name === account_item_name) { return true; } } return false; } /** * 残高データに指定の取引先名があるか確認する * * @param {object} 残高データ * @param {string} 取引先名 * @return {bool} 残高データに取引先名がある場合true */ function isPartnerName_(obj,partner_name) { for (const i in obj) { if (obj[i].name === partner_name) { return true; } } return false; } |
CSVファイルのデータ加工で苦戦している方はぜひ使ってみてくださいね。
スポンサーリンク
スポンサーリンク
