こんにちは、もりです！
以前に、「取引先別の売上総利益を簡単にチェックしたい」といったご要望をいただいたので、試しにスクリプトを書いてみました。
この記事で紹介するスクリプトは、たとえば、「取引先Xの、2019年7月～9月の売上総利益は？」といった内容を、ワンクリックでスプレッドシートに出力することができます。
また、これらのパラメータを、スプレッドシート上で自由に指定できます。
- 事業所
- 取引先
- 会計年度
- 開始月～終了月
※この記事で紹介するスクリプトを動作させるには、freeeAPIの連携アプリの作成と認証が済んでいることを前提としています。
詳しくは下記記事をご覧ください。
Contents
取引先別の指定期間の売上総利益を取得する
スプレッドシートの構成・動作イメージを併せてご覧ください。
【事前準備】スプレッドシートの準備
スプレッドシートにパラメータ設定する枠を用意します。セルの色は下記の意味をもっています。
- 黄色セル → パラメータを設定（入力）するセル
- 水色セル → GASで取得結果が書き込まれるセル
※会社名の指定には、別シートに取引先マスタを用意し、データの入力規則でリスト化しています。
【動画】ツールの使い方デモ
指定の取引先名のPLを取得するイメージです（売上総利益まで）
B列に「事業所・対象期間」を入力して、E1セルから右に取引先名をリストから指定して、スクリプトを実行しています。（この動画では、「会計freee」という名前の独自メニューを作成してスクリプトを実行しています）
【スクリプト】
このスクリプトと同じプロジェクト内に、下記記事の認証スクリプト（認証用の関数）を書いておく必要があります。
【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）
具体的には、「getService」関数と、その中で使われている「Client_ID」「Client_Secret」の値です。
そして、下記スクリプトの「getPartnerGrossProfit」関数を実行すると、スプレッドシートに残高を取得できます。
|
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 |
//エントリポイント function getPartnerGrossProfit() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('売上総利益'); const START_COL = 5; //前回取得値のクリア(C列-G列) sheet.getRange(3, START_COL, 5, sheet.getLastColumn()).clearContent(); //パラメーターの設定値を取得 const parameters = sheet.getRange(2, 2, 4, 1).getValues(); //B列 //PL対象科目を二次元配列で取得 const accountList = sheet.getRange(3, 4, 5, 1).getValues(); //D列 //取引先名(1行目)の最終列を取得 //参考：https://moripro.net/gas-get-specified-lastcol-lastrow/ const lastCol = sheet.getRange(1, sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn(); //取引先IDを二次元配列で取得(2行目E列~) const cols = lastCol - START_COL + 1; const partnerIDs = sheet.getRange(2, START_COL, 1, cols).getValues(); //freeeAPIにリクエスト送信して、レスポンスをオブジェクト変換 const response = getTrialPL_(parameters); Logger.log(response); //trial_pl > balancesプロパティを抽出 const objBalances = JSON.parse(response).trial_pl.balances; let target_item_name; let objAccountItem; let arr = []; for (let i = 0; i < accountList.length; i++) { target_item_name = accountList[i][0]; objAccountItem = getObjAccountItem_(objBalances, target_item_name); let tmp = []; tmp = createAccountArray_(objAccountItem, partnerIDs); arr.push(tmp); //スプレッドシート書き込み用配列 } //スプレッドシートに書き込み sheet.getRange(3, START_COL, arr.length, arr[0].length).setValues(arr); } /* * 指定のパラメータでfreeeAPIにGETリクエストを * 送信してレスポンスを返す * * @param {Object} パラメータ * @return {Object} freeeAPIからのレスポンス */ function getTrialPL_(parameters) { const accessToken = getService().getAccessToken(); const company_id = parameters[0][0]; //事業所ID const fiscal_year = parameters[1][0]; //会計期間 const start_month = parameters[2][0]; //開始月 const end_month = parameters[3][0]; //終了月 const requestUrl = 'https://api.freee.co.jp/api/1/reports/trial_pl?' + 'company_id=' + company_id + '&fiscal_year=' + fiscal_year + '&start_month=' + start_month + '&end_month=' + end_month + '&breakdown_display_type=partner'; //リクエストに付与するパラメータ const params = { "method" : "get", "headers" : {"Authorization":"Bearer " + accessToken} }; const response = UrlFetchApp.fetch(requestUrl,params); return response; } /* * freeeAPIのレスポンスから、対象科目部分のみを抽出して返す * * @param {Object} レスポンスの全データ * @param {String} 対象科目名 * @return {Object} 対象科目のみのデータ */ function getObjAccountItem_(objBalances, target_item_name) { let objAccountItem; //対象科目のみを抽出して格納するオブジェクト for(let i = 0; i < objBalances.length; i++) { //オブジェクトの勘定科目名が、目的の勘定科目と一致したら if (objBalances[i].account_item_name == target_item_name) { //account_item_nameプロパティを抽出 objAccountItem = objBalances[i]; break; } } return objAccountItem; } /* * 対象の取引先の残高を取得して一次元配列に格納して返す * * @param {Object} 指定科目のデータ * @param {Object} 取引先IDの配列 * @return {Object} 残高データの配列 */ function createAccountArray_(objAccountItem, partnerIDs) { let objPartners; let arr = []; try{ //partnersプロパティを抽出 objPartners = objAccountItem.partners; } catch(e) { //partnersプロパティがない場合はpartnerIDsの数0で返却 for (let i = 0; i < partnerIDs[0].length; i++) arr.push(0); return arr; } let partner_id; let closing_balance; for (let j = 0; j < partnerIDs[0].length; j++) { partner_id = partnerIDs[0][j]; closing_balance = 0; for (let i = 0; i < objPartners.length; i++) { if (objPartners[i].id == partner_id) { closing_balance = objPartners[i].closing_balance; break; } } //取引先xのclosing_balanceを格納する一次元配列 arr.push(closing_balance); } return arr; } |
【ちょっと解説】trial_plのJSONデータの構成
このスクリプトは、freeeAPIの「損益計算書の取得（/api/1/reports/trial_pl）」を使用しています。（公式リファレンスはこちら）
また、リクエストパラメータの「breakdown_display_type（内訳の表示）」で「取引先: partner」を指定しています。
そのため、レスポンスの形式はこのようになっています。
「科目ごと」「取引先ごと」に金額を取得するため、このように二重ループをしています。
- ループ①：「対象科目」の件数分、ループ（売上高, 期首商品棚卸高, 仕入高, 他勘定振替高(商), 期末商品棚卸高）
- ループ②：「対象取引先」の件数分、ループ（テスト会社1, テスト会社2, テスト会社3）
※同じtrial_plでも、リクエストパラメータの指定によって、レスポンスの形式が異なります。レスポンスをログ出力して読み解いてみましょう。
【参考】取引先IDの確認方法
このスクリプトは、「取引先名」ではなく、「取引先ID」をパラメータとしています。
「会計freee連携アドオン」を使用すると、事業所の取引先一覧をスプレッドシートに出力することができます。
詳細はこちらの記事をご覧ください → ノンプログラミングでOK！「会計freee連携アドオン」でスプレッドシートにデータを取得する
