どうも。つじけ（tsujikenzo）です。この連載では 「会計freeeAPIを使って残高確認表を作成しよう」 について全7回でお送りします。今日は7回目で 「残高確認表を作成しよう」 をお届けします。
前回のおさらい
前回は、「取引（収入／支出）を登録しよう」 ということで、GASから取引を登録しました。
取引に不足している[取引先]などの情報は、手入力が必要ですが、いずれ自動化する方法をブログでご紹介したいと思います。
今回は、最終回で、「残高確認表を作成しよう」 をお届けします。
事前準備
取引先IDの確認:GAS
取引先IDを確認するために、[取引先一覧の取得]スクリプトファイルを作成します。
コードはこちらです。
/**
* 取引先IDを取得する関数
*
* @param none
* @return none
*/
function getMyPartner() {
//freeeAPIのサービスからアクセストークンを取得
const accessToken = getService().getAccessToken();
//HTTPリクエストを送る時に必要なパラメーター
const params = {
method: "GET",
headers: { "Authorization": "Bearer " + accessToken }
};
//取引先一覧を取得するためのリクエストURL
const requestUrl = 'https://api.freee.co.jp/api/1/partners?company_id=事業所ID';
//HTTPリクエストをリクエストURLに送信する
const response = UrlFetchApp.fetch(requestUrl, params);
//レスポンスをJSONオブジェクトに変換する
const partners = JSON.parse(response)['partners']; //[{},{}]
//取引先IDを確認したい取引先名を指定する
const name = 'Kデザインオフィス'; //例 'いづみ企画','IMV株式会社','カ）アマナイメージズ'
//JSONオブジェクトから、取引先IDを抽出する
const partner = partners.filter(partner => partner['name'] === name);
console.log(partner);
}
取引先コードを確認したい取引先名を入力して、getMyPartner()を実行すると、取引先IDを確認できます。
取引先IDの確認:ブラウザ
取引先IDは、ブラウザでも確認できます。
[設定]をクリックして、[取引先の設定]をクリックします。
取引先の[編集]アイコンをクリックします。
URLに、取引先IDが含まれています。
https://secure.freee.co.jp/partners/{取引先ID}/edit
必要に応じて、スクリプトかブラウザから、取引先IDを確認してください。
スプレッドシートの確認
第4回目の「取引（収入／支出）を取得しよう」では、取引の一覧をスプレッドシートに出力しました。
残高確認表とコンテナバインドスクリプトを開いて、作業開始です。
データの加工
取引一覧から、必要な取引先ごとのシート を作成するのが、今回のゴールです。
ライブラリの準備：SpreadsheetDateConverterToObject
表を見出し行で操作するために、以下のライブラリを使用します。スクリプトエディタにこちらのライブラリIDを追加してください。
スクリプトID：1s4kGJwF2eG-WGmeqgLWeqQtIBRAXUZbbh3fQ3f7QHvNywubO99nduFIY
このライブラリは、以下のような2次元配列を、
[ [ 'id', 'name', 'age', 'favorite', 'address' ],
[ 'tg001', 'Tsujike', 35, 'sushi', 'Hokkaido' ],
[ 'tg002', 'Etau', 37, 'coffee', 'Miyazaki' ],
[ 'tg003', 'Takahashi', 38, 'blog', 'Tokyo' ] ]
このような2次元配列に変換するものです。
[
{id:'id', name: 'name', age: 'age', favorite: 'favorite', address: 'address' },
{id:'tg001', name:'Tsujike',age:35,favorite:'salmon',address:'Hokkaido'},
{id:'tg002', name:'Etau',age:37,favorite:'coffee',address:'Miyazaki'},
{id:'tg003', name:'Takahashi',age:38,favorite:'blog',address:'Tokyo'}
]
使用するメソッドはこちらです。
//見出し行付きの2次元配列に変換するメソッド
const spreadsheetID = 'スプレッドシートID';
const sheetName = 'シート名';
SpreadsheetDateConverterToObject.getElementsArray(spreadsheetID, sheetName)
このライブラリを使うメリットは、将来的な列の移動や削除に対応するためのものです。
列を指定するさい、通常は配列のインデックスで指定すると思いますが、カラム名で指定しようというものです。
詳細はこちらのブログに掲載しています。
スクリプトファイル「取引先別レポート出力」を追加します。
filter()メソッド
取引先IDのみ抽出するために、.filter()メソッドを使います。
取引先IDは数値型 なので気を付けましょう。
//必要な取引先IDのみ抽出する（行方向）
const partnerId = 36205797;
values = values.filter(value => value['partner_id'] === partnerId);
日付のソート
データは降順になっていますので、昇順に並べ返します。
//日付のソート
values = values.sort((a, b) => {
if (a[0] > b[0]) {
return 1;
} else {
return -1;
}
}
);
見出し行の処理
filter()メソッドを掛けると、見出し列が省かれますので、行方向の処理の前後で見出しを付け直します。
その際に、初期値行となる空行を挿入しておきましょう。
//見出しを格納しておく
const headers = values[0];
//行方向の処理
//初期値行の空行を挿入
values.unshift(new Array(headers.length));
//見出しを戻す
values.unshift(headers);
type「支出」をマイナスにする
残高を取るさいに、支出がある場合は、マイナスする必要があります。.map()メソッドで2次元配列を作り直しましょう。
//type「支出」をマイナスにする
values = values.map(value => {
if (value['type'] === 'expense') {
value['due_amount'] *= -1;
return value;
} else {
return value;
}
}
);
必要なフィールドのみ抽出する
残高確認を取るのに、すべてのフィールドは必要ありませんので、.map()メソッドで2次元配列を作り直します。
//必要なフィールドのみ抽出する（列方向）
values = values.map(value => {
return [
value['issue_date'],
value['type'],
value['partner_id'],
value['payments_from_walletable_id'],
value['due_amount'],
value['payments_amount']
]
}
);
スプレッドシートへ出力
あらかじめ新しいシートを作成し（今回は「シート2」にしました）、フィルター掛けした2次元配列を貼り付けます。
これまでのコードをつなげたものがこちらです。
/**
* 取引先別レポートを出力する関数
*
* @param none
* @return none
*/
function getReport() {
//空の2次元配列
let values = [[]];
//見出し行付きの2次元配列に変換するメソッド
const spreadsheetID = '1uIind-5VSSAfFYNi3t500nCCrw5pbctpzGoHelKJTEY';
const sheetName = 'シート1';
values = SpreadsheetDataConverterToObject.getElementsArray(spreadsheetID, sheetName);
//見出しを格納しておく
const headers = values[0];
//必要な取引先IDのみ抽出する（行方向）
const partnerId = 36205797;
values = values.filter(value => value['partner_id'] === partnerId);
//日付のソート
values = values.sort((a, b) => {
if (a[0] > b[0]) {
return 1;
} else {
return -1;
}
}
);
//初期値行を挿入
values.unshift(new Array(headers.length));
//見出しを戻す
values.unshift(headers);
//type「支出」をマイナスにする
values = values.map(value => {
if (value['type'] === 'expense') {
value['due_amount'] *= -1;
return value;
} else {
return value;
}
}
);
//必要なフィールドのみ抽出する（列方向）
values = values.map(value => {
return [
value['issue_date'],
value['type'],
value['partner_id'],
value['payments_from_walletable_id'],
value['due_amount'],
value['payments_amount']
]
}
);
//スプレッドシートへ出力
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート2');
sheet.clear();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
スプレッドシートへ貼り付けされました。
スプレッドシートの整形
スプレッドシートを整形するための関数は別にします。setSheetView()としました。
引数として、sheetオブジェクト、繰越残高を[initial]として受け取ります。
[initial]にはデフォルト引数として、0を指定します。
function setSheetView(sheet, initial = 0) {
//処理
}
最終列の取得
最終列を取って、見出し「残高」を入力します。
//最終列の取得
const lastColumnNext = sheet.getLastColumn() + 1;
//見出しをつける
sheet.getRange(1, lastColumnNext).setValue('残高');
残高の初期値も設定しましょう。
//初期値を設定
sheet.getRange(2, lastColumnNext).setValue(initial);
式を入力
//式を入力
const values = sheet.getDataRange().getValues();
const newValues = values.map(value => {
value[lastColumnNext - 1] = `=R[-1]C[0] + R[0]C[-2] - R[0]C[-1]`;
return value;
}
);
//先頭2行を削除
values.shift();
values.shift();
//スプレッドシートの更新
sheet.getRange(3, 1, values.length, values[0].length).setValues(values);
最終的なコードはこちらです。
/**
* スプレッドシートを整形する関数
*
* @param {object} シートオブジェクト
* @param {number} 初期値（デフォルトは0）
* @return none
*/
function setSheetView(sheet, initial = 0) {
//最終列の取得
const lastColumnNext = sheet.getLastColumn() + 1;
//すでに残高列があったら処理を止める
const lastRowHeader = sheet.getRange(1, lastColumnNext - 1).getValue();
if (lastRowHeader === `残高`) return;
//見出しをつける
sheet.getRange(1, lastColumnNext).setValue('残高');
//初期値を設定
sheet.getRange(2, lastColumnNext).setValue(initial);
//式を入力
const values = sheet.getDataRange().getValues();
const newValues = values.map(value => {
value[lastColumnNext - 1] = `=R[-1]C[0] + R[0]C[-2] - R[0]C[-1]`;
return value;
}
);
//先頭2行を削除
values.shift();
values.shift();
//スプレッドシートの更新
sheet.getRange(3, 1, values.length, values[0].length).setValues(values);
}
スプレッドシートに式が入力され、残高が取れます。
後は、シートの見た目やPDFファイルとして保存する業務ですが、この連載では割愛いたします。
セルの書式設定はこちらのブログも参考にしていただければ幸いです。
まとめ
以上で、「残高確認表を作成しよう」 ということで、元になるデータから、必要な項目を抽出しました。
今回の連載で、すべての業務が自動化できたわけではありません。
しかしながら、今回は手入力とした以下のような項目は、将来的に自動化できます。
– すべての取引先IDを取得する
– すべての取引先シートを作成する
– 繰越残高を取得する
– PDFに出力して、メールで送信する
– 会計freeeの口座明細取得が[3日以内]になっているか確認する
また、別の連載を設けて、お届けできれば幸いです。
この連載の目次
[GAS]会計freeeAPIを使って残高確認表を作成しよう
1. [GAS][会計freee]連載の概要と準備運動
2. [GAS][会計freee]アプリとアクセストークンを準備する
3. [GAS][会計freee]会計freeeAPIを操作する
4. [GAS][会計freee]事業所IDを取得しよう
5. [GAS][会計freee]取引（収入／支出）を取得しよう
6. [GAS][会計freee]取引（収入／支出）を登録しよう
7. [GAS][会計freee]残高確認表を作成しよう
