Google Apps Script（GAS）を利用して、freeeとスプレッドシートをAPI連携してみた

isfuliti

2024年4月24日 23:47

　API連携を利用すればfreee上で作成した仕訳帳データをGASでスプレッドシート（以下スプシ）へ書き出し、必要情報のみCSVデータを作成し、他のシステムへ送ることができます。

　ここでは、はじめてAPI連携に挑戦する方に向けて、いったい何をしたら良いのか、簡単に最速に、説明していきたいと思います。

事前必要項目
①freee会計　管理権限やらアプリ権限やら
②google　一般的なアカウントが必要

freeeでのアプリ作成

まずは連携させるために、自分だけが使えるプライベートアプリを作る必要があります。下のリンクからアプリを作成してみましょう！

https://app.secure.freee.co.jp/developers

さっそくアプリを作成していきます。ログインしていない場合は、右上のログインを押して、自身のfreeeアカウントでログインしてください。
「アプリ名」「概要」はどちらも必須ですが、自分で何用のアプリなのか、認識できれば何でもオッケーです。
アプリタイプは「プライベートアプリ」を選択。
利用規約にチェックをいれたら、右上の「作成」ボタンを押すだけ
以上でアプリは作成されます。

スプレッドシートとGoogle Apps Script（GAS）の準備

下のリンクからスプシを作成しましょう。googleアカウントない方は作ってログイン。

https://sheets.google.com/create

スプシの名前も何でもオッケーです。左上の「無題のスプレッドシート」をクリックすると簡単に名前を編集できます。
今回はfreeeテストとしておきました。

スプシが作成できたら、次はGoogle Apps Script（GAS）の準備です。

上のメニューから「拡張機能」→「Apps Script」を選択し、GASを起動させてください。こちらも名前は何でも良いのですが、せっかくなのでスプレッドシートの名前と同じにしておきましょう。

ここまでくると、上のバーは「note（この記事）」「freeeアプリ」「スプシ」「GAS」の4つになります。

freeeアプリへGASのコールバックURL指定

freeeアプリストアのタブを開いて、基本情報のコールバックURLを編集します。
設定されている「urn:ietf:wg:oauth:2.0:oob」は削除して、以下のURLをコピペします。
黒枠内右上のボタンからコピーできます。

https://script.google.com/macros/d/ここ/usercallback

「ここ」の部分にはGASのスクリプトIDを入力します。
GAS画面からプロジェクトの設定を開きます。

コピーをクリック。コピーしたスクリプトIDを先ほどのコールバックURLの「ここ」をダブルクリックして「ここ」部分に貼り付けましょう。

コールバックURLの「ここ」部分にスクリプトIDが入力できたら右上の下書き保存を押しましょう。

freeeアプリ権限設定

続いてどこまで連携を許可するか、設定をします。

権限設定を選択します。
今回は
「[会計]事業所の参照」
「[会計]仕訳帳の参照」
にチェックを入れ、再び右上の下書き保存を押しましょう。

GASでOAuth2ライブラリを設定する

GASにOAuth2ライブラリを追加します。
GAS「freeeテスト」を再び開いて、「エディタ」をクリックします。ライブラリ横の「＋」をクリックします。ライブラリの追加、スクリプトIDに以下のIDを入力します。

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

「検索」をクリックし、IDが「OAuth2」であることを確認し、「追加」をクリックする。

GASからfreeeへの認証設定

いよいよGASにコードを書いていきましょう。
まずはAPI連携の第一歩、認証設定をおこないます。
初期で設定されているコード.gsの名前を変更します。

右の点をクリックして「名前を変更」を選択。
今回の名前も何でもよいのですが、「認証」という名前にしておきます。

続いて、function myFunction(){ }と書いてある部分全てを選択、次のコードをコピペして置き換えます。（元々書いてあるコードは全て削除してください）

const Client_ID = 'クライアントID';
const Client_Secret = 'クライアントシークレット';

function Auth() {
  const authUrl = getService().getAuthorizationUrl();
  console.log(authUrl);
}

function getService() {
  return OAuth2.createService('freee')
    .setAuthorizationBaseUrl('https://accounts.secure.freee.co.jp/public_api/authorize')
    .setTokenUrl('https://accounts.secure.freee.co.jp/public_api/token')
    .setClientId(Client_ID)
    .setClientSecret(Client_Secret)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
}

function authCallback(request) {
  const service = getService();
  const isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('認証成功。');
  } else {
    return HtmlService.createHtmlOutput('認証失敗。');
  };
}

さて、このままでは正しく動いてくれません。
「クライアントID」と「クライアントシークレット」の部分を先ほど作成したfreeeテストアプリのIDとシークレットに変更する必要があります。
まず、freeeテストアプリを開き、基本情報の「Client ID」をコピーします。

この時、IDが選択できないように思われますが、実は選択可能です。侵入禁止マークが出ても、構わず文字を全て選択してコピーをしましょう。
コピーをしたら、GASコードの「クライアントID」部分に貼り付けます。
前後の ' と ' は残します。

同様にクライアントシークレットもコピペします。
クライアントシークレットを入力したら、
デバッグ右側「Auth」を選択し、実行ボタンを押すと認証を開始します。

googleにログインが表示されるので、アカウントを選択してください

びっくりマークで驚くかもしれませんが大丈夫です。
「安全なページに戻る」を押したくなるかもしれませんが、そうではなく、左下の「詳細」を押してください

これまた少し嫌な感じがしますが、下部にある「安全ではないページに移動」をクリックします

許可を求められるので、クリックします

「承認が必要です」と表示されたら、「権限を確認」をクリックします

実行ログが表示されたら「情報」の右部分を全て選択して、右クリックし、「……に移動」を選択します

アプリ連携の開始が表示されたら、許可するをクリックし、認証が成功したらタブを閉じてください

GASから事業所の取得

freeeとのアプリ連携ができたので、まずfreeeにある事業所データの取得をおこないます
再びGASを開きます
ファイルの右の＋ボタンを押して、「スクリプト」を選択
名前を「事業所」に変更します

function getCompanies() {

  //freeeAPIからアクセストークンを取得
  const accessToken = getService().getAccessToken();

  //事業所一覧を取得するリクエストURL  
  const requestUrl = 'https://api.freee.co.jp/api/1/companies';

  //freeeAPIへのリクエストに付与するパラメータ
  const params = {
    method: 'get',
    headers: { 'Authorization': 'Bearer ' + accessToken }
  };

  //リクエスト送信とレスポンス取得
  const response = UrlFetchApp.fetch(requestUrl, params).getContentText();
  console.log(response);

}

貼り付けたら、「保存」マークをクリックして、「実行」ボタンを押してください。

下記のようにログが出力されたら成功です
idに出力されている8桁の数字がその事業所のIDとなります
後ほど使用するのでメモしておいてください
複数事業所がある場合は、この後情報を引き出したい事業所のIDを選択してください

基本的にはこの事業所の取得方法を応用して、freeeから様々な情報を取り出すことができます。
次はいよいよfreeeの仕訳帳データを取り出します。

freee仕訳帳データを取り出す

再びファイルの右の＋ボタンを押して、「スクリプト」を選択
名前を「仕訳帳」に変更します。

//初期設定
const companyId = '先ほどメモした事業所ID';//事業所のID
const shname = 'シート1';//データを反映するシート名

//スプレッドシートにメニューを加えます
function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu("freee")
        .addItem("仕訳帳", "getJournals")
        .addItem("csvアップロード","DownloadCsvFile")
        .addToUi();
}
//仕訳帳データ取り出し
function getJournals() {
  
  //取り出したい仕訳帳の日付指定
  var str = Browser.inputBox("開始日を入力。例.yyyymmdd");
  var edr = Browser.inputBox("終了日を入力。例.yyyymmdd");
  let str1 = str.substring(0,4);//開始日の年を抽出
  let str2 = str.substring(4,6);//開始日の月を抽出
  let str3 = str.substring(6,8);//開始日の日を抽出
  let edr1 = edr.substring(0,4);//終了日の年を抽出
  let edr2 = edr.substring(4,6);//終了日の月を抽出
  let edr3 = edr.substring(6,8);//終了日の日を抽出
  var stDate = str1+'-'+str2+'-'+str3;//yyyy-mm-dd
  var edDate = edr1+'-'+edr2+'-'+edr3;

  //freeeAPIからアクセストークンを取得
  const accessToken = getService().getAccessToken();

  //状態表示
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast(`開始します`, 'リクエスト', 0);

  //仕訳帳のダウンロード要求リクエストURL  
  const requestUrl = 'https://api.freee.co.jp/api/1/journals?download_type=generic_v2&encoding=utf-8&company_id='+companyId+'&start_date='+stDate+'&end_date='+edDate;

  //freeeAPIへのリクエストに付与するパラメータ
  const params = {
    method: 'get',
    headers: { 'Authorization': 'Bearer ' + accessToken }
  };

  //リクエスト送信とレスポンス取得
  const response = UrlFetchApp.fetch(requestUrl, params).getContentText();
  let splitText = response.substring(18,26);//レスポンスからリクエストIDを抽出
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shname);

  //仕訳帳のダウンロードリクエストURL 
  var requestUrl1 = 'https://api.freee.co.jp/api/1/journals/reports/'+splitText+'/download?company_id='+companyId;
  //freeeAPIへのリクエストに付与するパラメータ
  var params1 = {
    method: 'get',
    headers: { 'Authorization': 'Bearer ' + accessToken }
  };

  //前回データのクリア
  sheet.clear();
  
  //エラーの場合繰返し50回
  const tryCount = 50;
  // 指定回数分ループ
  for(let count=1; count < tryCount+1; count++){
    try {
      ss.toast(`ファイルの確認中`, '待機中'+ count + '/50', 0);
      //仕訳帳のリクエスト送信
      const response1 = UrlFetchApp.fetch(requestUrl1, params1).getContentText();
      var values = Utilities.parseCsv(response1);
      sheet.getRange(1, 1, values.length, values[0].length).setValues(values);//シートにデータ反映
      ss.toast(`成功しました`, '成功', 3);
      break;

      // エラーが発生した場合
    } catch (e) {
      if (count < tryCount) {
        Utilities.sleep(20000);
        continue;
      }
      // 以下、成功しない場合エラー通知
      Browser.msgBox("指定期間を短くしてください"); 
      ss.toast(`再度を実行してください`, 'タイムアップ', 4);
    }
  }
}

コピペ後に確認・修正する項目があります。
初期設定部分の「先ほどメモした事業所ID」に先ほどメモした事業所ID（8桁の数字）を置き換えてください。
シート名は変更しなければ「シート1」なので変更する必要はありません。

下記、onOpenが選択された状態で「実行」を押してください。

問題なく実行されれば、スプレッドシート上にメニュー「freee」が出現します。

「freee」→「仕訳帳」をクリックすると、抽出する期間を求められます。

取り出したい仕訳帳データの抽出する期間を指定します。
「開始日」、「終了日」それぞれyyyymmdd形式で入力してください。
例 2024年2月20日　→　20240220

取り出す仕訳帳データの期間やデータ量によって待機時間は異なりますが、しばらくするとスプレッドシートに仕訳帳データが反映されます。

１行目には各項目が２行目以降はデータが反映されます。
A列からCN列まで様々なデータを取り出すことができます。
スプレッドシートのQUERY関数などを利用して、必要なデータのみ別シートへ自動転記させることで、簡単に定期的なデータ抽出を実現できます。

データを加工しCSV出力する

スプレッドシート左下の＋を押し「シート2」を作成します。

「シート2」のA1セルに「=QUERY('シート1'!A:CN,"Select(A),(B),(G)")」と入力します。

=QUERY('シート1'!A:CN,"Select(A),(B),(G)")

すると、「シート2」に「シート1」の列A,B,Gのみが反映されました。
このQUERY関数のSelect部分を加工することにより、取り出したい列を指定することができます。

それでは、ここからは抽出したデータをCSV形式でファイル出力する方法を紹介します。

ファイルの右の＋ボタンを押して、「スクリプト」を選択
名前を「csv」に変更します

function DownloadCsvFile() {
  const html = HtmlService.createTemplateFromFile("dialog").evaluate()
  SpreadsheetApp.getUi().showModalDialog(html, "ダウンロード中")
}

function getFileName() {
  const now = new Date();
  const datetime = Utilities.formatDate( now, 'Asia/Tokyo', 'yyyyMMddHHmm');
  return datetime + '.csv';
}

function getData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート2');
  const values = sheet.getDataRange().getDisplayValues();
  let dataArray = [];
  for (let i = 0; i < values.length; i++) {
    dataArray.push(values[i].join(","));
  }
  var dataStr = dataArray.join("\r\n");
  var blob = Utilities.newBlob("", "text/csv", "temp").setDataFromString(dataStr,"Shift_JIS");
  return Utilities.base64Encode(blob.getBytes());
}

さらに、htmlファイルも作成します。
ファイルの右の＋ボタンを押して、「HTML」を選択
名前を「dialog」に変更します

こちらも全て選択し、次のコードに置き換えます。

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script type='text/javascript'>
    window.onload = function(){
        handleDownload();
      }
      function handleDownload() {
        var content = <?= getData(); ?>;
        var bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
        var blob = new Blob([new Uint8Array([].map.call(atob(content), function(c) {return c.charCodeAt(0)})).buffer]);
        // var blob = new Blob([ content ], { "type" : "text/csv" });
        var link = document.createElement('a');
        link.download = <?= getFileName(); ?>;
        link.href = window.URL.createObjectURL(blob);
        link.click();
        google.script.host.close();
      }
  </script>
</head>
</html>

設定は、ここまでです。
スプレッドシートから実際にCSV変換を実行していきましょう。
スプレッドシート上のメニュー「freee」を選択し「csvアップロード」をクリックします。

「ダウンロード中」のダイアログが表示され、しばらくするとcsvファイルがダウンロードされます。

csvファイル名はyyyyMMddHHmm(年月日時分)で作成されます。

中身を確認すると、無事csvファイルが作成されました。

「シート1」から「シート2」へQUERY関数を利用することによって、必要なデータを絞り込み、最終的に欲しいデータのみcsv出力をすることが可能になります。

今回のGoogle Apps Script（GAS）を利用して、freeeとスプレッドシートをAPI連携してみたを実践することにより、まずはfreeeから「シート1」へ取り出せるデータ全てを取り出し、「シート2」へ関数を利用して絞り込み、最終的に必要データのみ、csvデータとして出力することが可能になります。

基本的にはコピペで対応できると思うので、ぜひ一度実践してみてください。

この記事が気に入ったらサポートをしてみませんか？