freee会計の試算表APIを叩き、月次・日次の損益計算書をつくる

freee会計のAPIの一つ・試算表APIの活用例を作ってみました。

やること

freee会計の試算表APIを利用して

• 6ヶ月分の月毎損益計算書
• 30日分の日毎損益計算書

の二種類を作成する。

実装は Google Apps Script + Googleスプレッドシートで行う。
スクリプトを実行すると、実行日を起点として、過去6ヶ月＋過去30日の損益計算書を作成する。

(画像はデモ事業所のダミーデータを利用したものです)

実装手順

以下の手順で実装・操作しました。

• freeeアプリストアでアプリを作成
• OAuth2.0ライブライをGoogleスプレッドシートに設定
• 損益計算書を記述するシートを作成
• Google Apps Script でコードを記述
  • 今回の構成
• 実行してみる

freeeアプリストアでアプリを作成

基本的な流れは 拙稿と同じです。

契約しているfreeeアカウントを利用して始めます。

freeeアカウントがない場合は「freee API スタートガイド」 を見ながらテスト用の事業所を作ると良いでしょう。

freee API を操作するためには、最初に「freeeアプリ」を作る必要があります。
作成の手順は、公式サイトの「チュートリアル」に詳しくまとまっています。

freee API チュートリアル

このチュートリアル見ながら進めれば、迷うことはないでしょう。順番としては

• freee アプリストアの開発者ページにアクセス
• freeeアカウントでログイン
• 事業所を選ぶ
• アプリ管理のページから、アプリを新規追加する

の流れでアプリを作成します。

アプリ名、説明、利用規約のチェックを行い、新規作成し、アプリを下書き保存しておきます。

「コールバックURL」
「Client ID」
「Client Secret」

は、後の設定で利用します。

Googleスプレッドシートの準備とapps-script-oauth2のインストール

次に、Googleスプレッドシートの準備行い、freee　APIのデータを利用する「OAuth2.0」用のライブラリをインストールします。新しいGoogle スプレッドシートを作成し、「ツール」→「スクリプトエディタ」を選んで、GASのエディタを開きます。

エディタ画面が開いたら、「ライブラリ」を選び、apps-script-oauth2 のインストール準備をします。

「Add a library」欄に、apps-script-oauth2のスクリプトIDを入力して追加します。IDは一意に決まっており

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

を入力。最新バージョンを選んで保存します。

保存ができたら、インストールは完了です。

freeeアプリの コールバックURLを設定する

次に、先ほど作成したfreee アプリのコールバックURLを修正します。
OAuth2.0認可処理後の遷移先を設定します。

apps-script-oauth2を使った場合、コールバックURLは必ず

https://script.google.com/macros/d/{SCRIPT ID}/usercallback

になります。「script ID」は、エディタ画面の「プロジェクトの概要」→「ID(スクリプトID)」で確認できます。

コールバックURLのSCRIPT IDを書き換え、freeeアプリの「コールバックURL」に入力し、下書き保存します。

損益計算書を記述するシートを作成

APIから取得したデータを書き込むための、ワークシートを作成します。

今回のサンプルでは、「月次推移表」「日次推移表」という2つのワークシートを作成し、そこに対してデータを書き込んでいいきます。

各シートのA列には、データ項目を見出しとして入力しておきました。

Google Apps Script でコードを記述する

前準備ができたら、Google Apps Script でコードを記述します。

今回の構成

今回のサンプルアプリはコード量が多いため、4つのスクリプトに分割しました。

• 初期設定.gs
  • freeeアプリストアのClientID, Client Secret を最初に設定する
• header.gs
  • freee APIとOAuth2.0でやり取りするためのヘッダー設定を行う
• メニュー拡張.gs
  • Googleスプレッドシートに操作用のメニューを表示する
• 試算表作成.gs
  • 試算表を作成するためのメインロジック

初期設定.gs

freeeのOAuth認証に必要な情報を、GASの プロパティサービス に保存します。

初期設定.gs

function registKeys() {

    const Properties = PropertiesService.getScriptProperties();

    Properties.setProperty('ClientId', '${freeeアプリのClient ID文字列}');
    Properties.setProperty('ClientSecret', '${freeeアプリのClient ID文字列}');

    console.log(Properties.getProperty('ClientId'));
    console.log(Properties.getProperty('ClientSecret'));
}

上記を実行すると、freeeの OAuth アプリの Client ID、Client Secret が保存・ログに表示されます。
一度保存したClientID、ClientSecret は、以下のコードで取り出すことができます。

const Properties = PropertiesService.getScriptProperties();

// ClientID を取得

Properties.getProperty('ClientId');

//Client Secret を取得

Properties.getProperty('ClientSecret');

header.gs

freee の REST API へ、アクセストークンを送信するためのヘッダースクリプトを記述します。

header.gs

// freee API 通信時に必要な Bearer Token を定義してパラメータとして渡す
function header(){
  const params = {
    headers: {
      Authorization: 'Bearer ' + getDriveService().getAccessToken(),
    },
  }
  return params;
}

メニュー拡張.gs

スプレッドシート上に、操作用の拡張メニューを表示するためのスクリプトです。
このコードで表示された操作メニューから、OAuthの認可操作を行なったり、事業所のCompany IDを取得したりします。

メニュー拡張.gs

const scriptProperties = PropertiesService.getScriptProperties();
const ClientId = scriptProperties.getProperty('ClientId');
const ClientSecret = scriptProperties.getProperty('ClientSecret');
const driveService = getDriveService ();

function getDriveService () {

  return OAuth2.createService ('freeeAPI')
    .setAuthorizationBaseUrl (
      'https://accounts.secure.freee.co.jp/public_api/authorize'
    )
    .setTokenUrl ('https://accounts.secure.freee.co.jp/public_api/token')
    .setClientId (ClientId)
    .setClientSecret (ClientSecret)
    .setCallbackFunction ('authCallback')
    .setPropertyStore (PropertiesService.getUserProperties ());
}

function onOpen () {
  SpreadsheetApp.getUi ()
    .createMenu ('freee API連携')
    .addItem ('認可処理', 'showAuth')
    .addItem('事業所確認', 'showCompanies')
    .addItem('試算表作成', 'getPl')
    .addItem ('ログアウト', 'logout')
    .addToUi ();
}

function createModelessDialog (html, title) {
  const htmlOutput = HtmlService.createHtmlOutput (html)
    .setWidth (360)
    .setHeight (120);
  SpreadsheetApp.getUi ().showModelessDialog (htmlOutput, title);
}

function showAuth () {
  if (!driveService.hasAccess ()) {
    const authorizationUrl = driveService.getAuthorizationUrl ();
    const template = HtmlService.createTemplate (
      '<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
        'freee APIの認可をします。'
    );
    template.authorizationUrl = authorizationUrl;
    const page = template.evaluate ();
    const title = 'freeeアプリの認可処理';

    createModelessDialog (page, title);
  } else {
    showUser ();
  }
}

function authCallback (request) {
  const isAuthorized = driveService.handleCallback (request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput ('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput ('Denied. You can close this tab');
  }
}

function showUser () {
  const response = UrlFetchApp.fetch (
    'https://api.freee.co.jp/api/1/users/me',
    {
      headers: {
        Authorization: 'Bearer ' + driveService.getAccessToken (),
      },
    }
  );

  const myJson = JSON.parse (response);

  Browser.msgBox ('OAuth認可済みです。\\n認可されたユーザー名：' + myJson.user.display_name);
}

// 事業所情報を一覧で表示する

function showCompanies() {

    let response = UrlFetchApp.fetch('https://api.freee.co.jp/api/1/companies', header())
    let myCompanies = JSON.parse(response);
    let cids = '';
    myCompanies.companies.forEach ( items=>{
        cids += items.display_name;
        cids += ' : ';
        cids += items.id;
        cids += '\\n'; 
    })
    Browser.msgBox('認可中のユーザーが操作できる事業所：\\n' + cids);
}


function getPl(){

  six_month_loop();
  thirty_days_loop();

}

function logout () {
  driveService.reset ();
  const mes = 'freeeアプリからログアウトしました。';
  const logoutTitle = 'ログアウト終了';

  createModelessDialog (mes, logoutTitle);
}

試算表作成.gs

最後に、試算表を作成するロジックルーチンを記述します。
4行目の「companyId」には、試算表を取得したい事業所のID番号を入力します。

試算表.gs

// シート情報の取得

 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const monthlySheet = ss.getSheetByName('月次推移表');
 const dailySheet = ss.getSheetByName('日次推移表');
 const companyId = '${試算表を取得したいCompnaayIDを整数で記述する}';

//  当月から遡って6ヶ月分のループを回し、月次推移表を更新

function six_month_loop(){

  Logger.log(companyId);

  for (let i = 0; i < 6; i++ ){
    let date = new Date();
    date.setMonth(date.getMonth()-5+i);
    let year = Utilities.formatDate(date, 'JST', 'yyyy');
    let month = date.getMonth() + 1;
    createTrial(month, year, i+2)
  }
}

//  30日分のループを回し、日次推移表を更新

function thirty_days_loop(){
    for (let j = 0; j < 30; j++ ){
      let date = new Date();
      date.setDate(date.getDate()-30+j);
      let requestdate = Utilities.formatDate(date, 'JST', 'yyyy-MM-dd');
      createDailyTrial(requestdate, j+2)
    }
}

// 月次推移表作成ルーチン

function createTrial(thismonth, thisyear, thiscell){

  // 試算表用の変数をまとめて定義
  let Uriage_daka = '';
  let Uriage_genka = '';
  let Uriage_sorieki = '';
  let Hanbai_kanrihi = '';
  let Eigyo_rieki = '';

  // 今日の日付を取得
  let date = new Date();

  // 今月の試算表を取得
  let year = Utilities.formatDate(date, 'JST', 'yyyy');
  let month = date.getMonth() + 1;

  // 今月のPLを取得
      let requestUrl = 'https://api.freee.co.jp/api/1/reports/trial_pl?company_id='+ companyId + '&fiscal_year=' + thisyear + '&start_month=' + thismonth + '&end_month=' + thismonth;
      let response = UrlFetchApp.fetch(requestUrl, header());
      let pl_data = JSON.parse(response);
      let balances = pl_data.trial_pl.balances;


  // 売上高を取得
    Uriage_daka = balances.find(data =>  data.account_category_name == '売上高' && data.total_line == true);
    Uriage_daka = Uriage_daka.credit_amount - Uriage_daka.debit_amount;

  // 売上原価を取得
    Uriage_genka = balances.find(data =>  data.account_category_name == '売上原価' && data.total_line == true);
    Uriage_genka = Uriage_genka.debit_amount - Uriage_genka.credit_amount;

  // 売上総利益を取得
  　Uriage_sorieki = Uriage_daka - Uriage_genka;

  // 販売管理費を取得
    Hanbai_kanrihi =  balances.find(data =>  data.account_category_name == '販売管理費' && data.total_line == true);
    Hanbai_kanrihi　= Hanbai_kanrihi.debit_amount - Hanbai_kanrihi.credit_amount;

  // 営業利益
    Eigyo_rieki = Uriage_sorieki - Hanbai_kanrihi;

  // シートに書き込み
    monthlySheet.getRange(1, thiscell).setValue(thisyear + '年' + thismonth + '月');
    monthlySheet.getRange(2, thiscell).setValue(Uriage_daka);
    monthlySheet.getRange(3, thiscell).setValue(Uriage_genka);
    monthlySheet.getRange(4, thiscell).setValue(Uriage_sorieki);
    monthlySheet.getRange(5, thiscell).setValue(Hanbai_kanrihi);
    monthlySheet.getRange(6, thiscell).setValue(Eigyo_rieki);

}

// 日次推移表作成ルーチン
  function createDailyTrial(requestdate, dailycell) {

    // 日次のPLを取得
    let requestUrl = 'https://api.freee.co.jp/api/1/reports/trial_pl?company_id=' + companyId + '&start_date=' + requestdate + '&end_date=' + requestdate;
    let response = UrlFetchApp.fetch(requestUrl, header());
    let pl_data = JSON.parse(response);
    let balances = pl_data.trial_pl.balances;

    // 売上高を取得
    Uriage_daka = balances.find(data =>  data.account_category_name == '売上高' && data.total_line == true);
    Uriage_daka = Uriage_daka.credit_amount - Uriage_daka.debit_amount;
    Logger.log(requestdate + '売上高: ' + Uriage_daka);

    // 売上原価を取得
    Uriage_genka = balances.find(data =>  data.account_category_name == '売上原価' && data.total_line == true);
    Uriage_genka = Uriage_genka.debit_amount - Uriage_genka.credit_amount;

   // 売上総利益を取得
  　Uriage_sorieki = Uriage_daka - Uriage_genka;

    // 販売管理費を取得
    Hanbai_kanrihi =  balances.find(data =>  data.account_category_name == '販売管理費' && data.total_line == true);
    Hanbai_kanrihi　= Hanbai_kanrihi.debit_amount - Hanbai_kanrihi.credit_amount;

    // 営業利益
    Eigyo_rieki = Uriage_sorieki - Hanbai_kanrihi;

    // シートに書き込み
    dailySheet.getRange(1, dailycell).setValue(requestdate);
    dailySheet.getRange(2, dailycell).setValue(Uriage_daka);
    dailySheet.getRange(3, dailycell).setValue(Uriage_genka);
    dailySheet.getRange(4, dailycell).setValue(Uriage_sorieki);
    dailySheet.getRange(5, dailycell).setValue(Hanbai_kanrihi);
    dailySheet.getRange(6, dailycell).setValue(Eigyo_rieki);

  }

実行してみる

以上で設定完了です。GASのタブごとに、それぞれのコードが記述されます。

全ての設定が正常に終了していると、OAuth認可処理→CompanyID設定後、「試算表作成」をクリックすると、6ヶ月分＋30日分の試算表データが転記されます。

freee会計上の6ヶ月分の試算表(デモ事業所のダミーデータ)

Googleスプレッドシートに転記した6ヶ月分の月次試算表(デモ事業所のダミーデータ)

注意事項と免責について

本記事で公開しているコードは個人的に開発したものです。十分なテストを行っていないため、誤動作があるかもしれません。ご理解の上ご利用ください。

本コードは自由に利用いただいてかまいません。