freee x GASでBS明細の作成を自動化したお話 #GoogleAppsScript

はじめに

こんにちは。
iOSやGAS、たまにPythonを触る人です。

今回は弊社の経理担当が毎月1人日掛かっていたBS明細の作成を自動化したお話です。

今回の内容は下記の内容を参考にさせていただきました。
会計freee×GASで取引先別の月次貸借対照表データを取得してみる
 【freee API】GASを用いてGoogleスプレッドシートと連携する
 freee 公式リファレンス

技術内容

freeeAPI
GAS (Google App Script)

主な機能

自動でfreeeから当月分の試算表を取得し月単位のシートに書き込み
年度と月を指定してfreeeから当月分の試算表を取得し月単位のシートに書き込み

実装内容

今回使用するfreeeAPIは二つです。
reports/trial_bs : 貸借対照表の取得
reports/trial_bs_two_years : 貸借対照表(前年比較)の取得

freeeとの連携

今回はfreeeがサンプルとして用意していたスプレッドシートを雛形に使用したのでほぼ自分では書いていません。
詳しい実装内容やサンプルのスプシは下記ページで確認できます。
【freee API】GASを用いてGoogleスプレッドシートと連携する

貸借対照表の取得

取得したデータを格納する変数を定義

var resBs;

取得する年月を指定
date.getMonth()は0~11を返すので+1
取得年の指定は年度で指定するため取得月が1~3月だった場合-1
下記の処理は当月分の取得


var date = new Date();
var getMonth = date.getMonth() + 1;

// 2019年1月を取得したい場合2019年1月は2018年度なので年数の指定は2018を指定しなければならない
var getYear = date.getFullYear();
if (getMonth < 4) {
    getYear -= 1
}

更新関数を作成し、年月を指定した場合の処理を実装


function setToSheet() {
    const manualSheet = ss.getSheetByName('手動更新');
    var year = manualSheet.getRange("B3").getValue();
    if (year != ''){
        var month = manualSheet.getRange("C3").getValue();
        getYear = year
        getMonth = month
    }
}

貸借対照表の取得関数を作成


/**
 * 貸借対照表の取得関数
 */
function getBs() {
    //トークン取得
    const freeApp = getService();
    const accessToken = freeApp.getAccessToken();

    //取得情報のパラメータ指定
    var url = 'https://api.freee.co.jp/api/1/';
    const targetItem = 'reports/trial_bs?';
    const companyIdInt = getCompanyIdFromCompanyName("B4", "設定");
    const companyId = 'company_id=' + companyIdInt;
    const fiscalYear = '&fiscal_year=' + getYear;
    const startMonth = '&start_month=' + getMonth;
    const endMonth = '&end_month=' + getMonth;
    const breakdown = '&breakdown_display_type=' + 'partner';

    //url結合
    url += targetItem + companyId + fiscalYear + startMonth + endMonth + breakdown;

    //option, headers
    const options = {
        'method': 'get',
        'headers': {
            'Authorization': 'Bearer ' + accessToken
        }
    };

    const res = UrlFetchApp.fetch(url, options).getContentText();
    resBs = JSON.parse(res).trial_bs.balances;
}

貸借対照表の取得関数を呼び出し、必要な科目名の配列を用意する。
月シートの中身を削除し科目名の個数分、取得したデータから取り出しと書き込みを繰り返す。
完了したら手動更新シートの年月を削除。


function setToSheet() {
    getBs();
    var accountNameArray = ['売掛金','未払金','前払費用','立替金','仮払金','未収入金','預け金','前払金',
                            '長期前払費用','敷金　保証金','預託金','創立費　開業費','1年以内返済借入金',
                            '買掛金','未払費用','預り金','前受収益','前受金','仮受金'];
    const monthlySheet = ss.getSheetByName(getMonth + '月');
    monthlySheet.clear()

    accountNameArray.forEach(function(accountName, index) {
      var details = getDetails(accountName);
      if (details === undefined) {
        return
      }
      var column = (index == 0)? 1 : index * details[0].length + 1;
      monthlySheet.getRange(2, column, details.length, details[0].length).setValues(details);
      monthlySheet.getRange(1, column).setValue(accountName);
      var suppliersNameArray = createNameArray(accountName,details);
      const accountSheet = ss.getSheetByName(accountName);
      accountSheet.getRange(4, 1, suppliersNameArray.length, 1).setValues(suppliersNameArray);
    });
    ss.getSheetByName('手動更新').getRange("B3").clearContent();
    ss.getSheetByName('手動更新').getRange("C3").clearContent();
}

毎月取引先名を単純にシートに追記していくと取引先名が重複する場合があるので、
取得したデータと既に勘定科目シートに入力されている取引先名を比較して重複分を削除し取引先名だけの配列を返して勘定科目シートに記入。

/**
 * 名前だけの配列を作成
 */
function createNameArray(name, detailArray) {
    var nameArray = detailArray.map(function(details) {
        return [details[0]];
    })

    nameArray = checkName(name, nameArray);

    return nameArray
}

/**
 * 勘定科目シートの取引先と更新月分の取引先の名前を比較して重複分を削除
 * @namesArray  [[string],[Int],[Int],[Int]]
 * @sheetName  string
 * @return [[string],[string]...]
 */
function checkName(sheetName, namesArray) {

    const sheet = ss.getSheetByName(sheetName);
    const columnBVals = sheet.getRange('A:A').getValues();
    const LastRow = columnBVals.filter(String).length; 
    if (LastRow == 0) { return namesArray; }

    // 当月分の取引先配列を二次元配列から一次元配列に変換
    const namesAfter = Array.prototype.concat.apply([], namesArray);

    // 勘定科目シートからすでに存在する取引先を取得
    const oldNames = sheet.getRange(3, 1, LastRow).getValues();
    // 二次元配列から一次元配列に変換
    var oldNamesAfter = Array.prototype.concat.apply([], oldNames);

    // 先月分と今月分の取引先配列を比較して重複しているものを削除
    oldNamesAfter = namesAfter.filter(function(val) {
        return oldNamesAfter.indexOf(val) == -1;
    });

    // 重複を削除したoldNamesAfterをnamesAfterに連結
    namesAfter.concat(oldNamesAfter);

    // namesAfterを二次元配列に変換
    const names = namesAfter.map(function(name) {
        return [name];
    });

    return names;
}

これで勘定科目シートには取引先名、
月シートには勘定科目名の配列で定義した情報が記入されているのであとは、VLOOKUPで参照すれば更新が完了する。
数字は適当なものを入れています。

毎日夜中に実行したい場合は、最初に呼ばれる関数setToSheetにトリガーをセットすればBS自動更新表の出来上がり。

所感

freeeAPIに思ったより苦戦しました。
APIが少し使いにくい印象もありましたが、
フォーラムで質問をすれば1~2営業日でfreeeの担当者から返答があったのですごく助かりました。
https://developer.freee.co.jp/community/forum/community

今後もfreeeAPIやGASを触っていくので、何かお手伝いできることがあれば私のTwitterにDMでご連絡ください。
https://twitter.com/srknra