読者です 読者をやめる 読者になる 読者になる

ごにょごにょブログ

カメラ、旅行、IT、鉄道、飛行機、投資など思ったまま語るブログ

GoogleドキュメントとSlackで夫婦間での立替払いを管理してみた

スマホ・PC

f:id:nekosogiradical:20170201235051j:plain

我が家は共働きなのもあって生活費の財布はなく、一般的に生活費とされる家賃・光熱費・食費(自炊のみ)の大凡の費用(1年間を通しての平均)を算出し、その半分を毎月自分の口座へ送金してもらい立替ている。

給与の振込先はそれぞれ会社指定の異なる銀行口座なので直接振込すると他行振込で振込手数料が発生するが、住信SBIネット銀行の自動入金機能で毎月指定日に一定額を給与口座から同行の口座に自動入金(無料)するようにして、さらに定額自動振込サービスで一定額を毎月振込む設定をして完全無料・自動で振込されるようにしている。

よって、会社指定の銀行口座は給与が振込まれても数日後には使い勝手がいい住信SBIネット銀行にゴッソリ移動している。

閑話休題。生活費以外の外食や旅行などの一時的に発生する支払については、別に何だかの管理する方法を考えなければならなかった。

 

メモアプリで管理

自分が立替えて支払うことが多かったのでメモアプリなどに都度記入して適当なタイミングで精算して、LINEに「この前の旅行代金のXXXXX円を振り込んでおいて」とメッセージを入れていた。

しかし、このメッセージを受信した直後に振込まれるとは限らず、振り込みがまだだからと精算した額に追加請求したりしていると、振り込まれないまま闇に葬り去られたり、入金されてもどの支払い分が含まれていたかがわからなくなった。

他にも、メモ帳だと件数が増えてくると電卓で計算しないといけないし、明細として出さずに合計金額でしか出さないので(信用されていないわけではないが)透明性がないことも問題としてあった。

 

Googleドキュメントのスプレッドシートで管理

メモ帳での問題を解決すべくGoogleドライブのスプレッドシートに共有のブックを作成し、これまで通り発生した都度記入してある程度たまったら精算していた。

これにより未精算分の計算が自動化されて互いにシートを共有している(閲覧できる)ので透明性も確保できてそこそこうまくいっていた。

f:id:nekosogiradical:20170201213542p:plain

しかし、時を重ねるごとにスプレッドシートの管理が煩雑になり(入力ルールを決めていなかったり、直近がどのセル範囲を使っているかがわかりづらかったり)、もっぱら嫁の立替え分も自分が入力していた。

すると、自分の立替え分は自分がよく覚えているし、財布にはレシートがあるので漏れることはないが、嫁の分は漏らせばなかったコトにできるので漏れて有耶無耶になることが度々発生した。

新たな問題を解決するのには、入力ルールを決めて、互いに自分が立替えた分を入力して、精算するタイミングを特定日に決める必要があると考え、さらなる改善に着手することにした。

 

Googleフォームとスプレッドシートのあわせ技

スプレッドシートのツールにはフォーム作成の機能がある。

f:id:nekosogiradical:20170201215145p:plain

この機能は『かんたんにWebブラウザでアンケートが取れる』ことをターゲットに、GUIで簡単に作成できて、ブラウザからアクセスして回答する仕組みとなっているのでスマホでもパソコンでもネットにつながっていれば入力できることから、立替えた分の入力する口として使えそうだった。 

登録フォームの作成

登録フォームの作成はすべてGUIでできるので、プログラムの経験やスキルは一切不要。入力項目は色々入れられるようにはせずに最低限必要な次の4項目に絞った。

  1. 何を買ったか
  2. いくら支払ったか
  3. どのように割るか(全額負担、折半)
  4. 自由記入欄(備考)

パソコンでの編集画面。

f:id:nekosogiradical:20170201215333p:plain

スマホでのプレビュー。

f:id:nekosogiradical:20170201214245p:plain

※上記のAとBは下記以降の説明とは逆転してます(近日中に修正予定)

「負担割合がわかりづらい」と嫁から言われたので解説をしておくと、

自分(A)がその場は立て替えて払ったが相手(B)と割り勘にするなら【1. Aが払っとくから、ワリカンにしよう!】となり、立て替えただけで相手に全額払って欲しい場合は【3. Bさん、ゴチになります!】となる。

この2択だと1:2や3:7のような折半以外で割れないが、それは計算して相手に負担して欲しい分を【3】に入れるのがいいかな、と。

理想を言えば、ログイン情報が使えたら質問項目を【払った】と【折半する】の2択にできたが、どうやらログインを必須にすると1アカウント1回しか登録できないようなので断念…

また、登録日付はフォームが送信された時点で自動的に記録されるので不要で、登録画面はこれで完成。

スプレッドシートの作成

次はフォームから登録されたデータを元に処理(精算)する部分。

フォームから登録されたデータのシート(フォームの回答 1)は直接触らずに別シートから参照するようにしたが、Googleフォームの仕様では登録されたデータはセルに直接上書きされず行の追加となり、別シートからセルを参照するやり方ではうまくいかないので、精算日にフォームから登録されたデータのシートを一括で別シート(フォームの回答をコピー)にコピー&ペーストして、そこを参照するようにした(別シートへのコピー方法は後述)。

シートは4つでフォームから登録されたデータを貼り付ける【フォームの回答 1】、フォームの回答 1をまるまるコピーした【フォームの回答をコピー】、抽出・計算する【精算シート】、メールのテンプレート【メール報告】シートの構成。

f:id:nekosogiradical:20170201215647p:plain

わかりづらいとは思うがこれが集まったデータから対象年月分だけを抽出して計算する精算シート。

f:id:nekosogiradical:20170201220440p:plain

A2セル:当日日時 [=TODAY()]

当日の日付を取得

A4セル:前月の年月 [=A2-DAY(1)]

月初に前月分を一括で精算するので、前月が何年何月だったかを算出するために当日日付から1日前の日で算出

A6セル:総件数 [=MAX(B:B)]

これまでに登録された件数

A8セル:対象件数 [=MAX(D:D)]

精算対象となっている件数

B2セル:通番の取得 [=IF(COUNTA('フォームの回答をコピー'!$A$2:$A$1007)>=ROW()-1,ROW()-1,"")]

フォームの回答をコピーシートから通番を取得する

わざわざ取得せずに最初から入れておいてもいいが、データのない行に番号が振られるのは好きじゃないので、フォームの回答をコピーシートに登録されているデータ数から1引いた分(タイトル行)までを表示するようにする

C2〜1000セル:精算対象の判断 [=IF(YEAR('フォームの回答をコピー'!$A2)&"/"&MONTH('フォームの回答をコピー'!$A2)='精算シート'!$A$4,"精算対象","")]

フォームの回答をコピーシートから通番をキーにして登録日付を参照し、その日付の年月がA4セル(前月の年月)と同じであれば【精算対象】と表示する

D2〜1000セル:連番の割当 [=IF($C2<>"",COUNTIF($C$2,"精算対象"),"")]

通番とは別に精算対象のデータに対してのみ連番を割り当てる

通番と連番を区別してるのはなぜだって?なんでろうね…

E2〜1000セル:お品 [=IF($C2<>"",'フォームの回答をコピー'!$B2,"")]

精算対象となっていれば、買ったものの品名を表示する

F2〜1000セル:負担 [=IF($C2<>"",LEFT('フォームの回答をコピー'!$C2,1),"")]

精算対象となっていれば、どのように割るか(折半、全額)の1文字目を取得 

G2〜1000セル:A [=IF(C2<>"",IF($F2="1",'フォームの回答をコピー'!$D2/2,IF($F2="3",'フォームの回答をコピー'!$D2,0)),"")]

少し複雑だがどうってことはなくて、精算対象となっていてかつ【1】の折半であれば1/2にした金額を表示し、【3】の全額負担であればそのまま表示する

H2〜1000セル:B [=IF(D2<>"",IF($F2="2",'フォームの回答をコピー'!$D2/2,IF($F2="4",'フォームの回答をコピー'!$D2,0)),"")]

G2セルの逆パターン

I2〜1000セル:メモ[=IF($D2<>"",'フォームの回答をコピー'!$E2,"")]

精算対象となっていれば、買ったものの品名を表示する

 

K列から再びB~I列と同じような表があるが、こっちが実際に対象年月分だけを抽出していてこの結果をもとに精算する(現在はまだ1ヶ月分しか稼働していないので表示されている数は同じ)。

K2〜51セル:No. [=IF(MAX($D$2:$D$1000)>=ROW()-1,ROW()-1,"")]

D列の連番の最大値を取得して、その最大値になるまでカウントアップする

L/M/N/O2〜51セル:お品・A・B・メモ [=VLOOKUP($K2,$D$2:$I$1000,#)] #は2/4/5/6

この4列は連番とNo.で一致したものをVLOOKUPで抽出

 

多く見積もってもひと月で50件以上も立替が発生するのは考えづらいので、現在は50件をひと月の上限にして集計するように枠を作っている。

f:id:nekosogiradical:20170201224050p:plain

M52・N52セル:A合計 [=SUM(M2:M51)]・B合計 [=SUM(N2:N51)]

それぞれの未払い分を単純に足し算。

L53セル:精算結果 [=IF(M52-N52=0,"今月の精算はありません",IF(M52-N52<0,N1&" さんは、"&M1&" さんに "&ROUND*1]

Aさんの未払い分からBさんの未払い分を引いた解が【0】(ツケの金額が同じ)であれば【今月の精算はありません】と表示し、【0より小さい】であれば【Bさんは、AさんにXXX円 お支払いください。】、【0より大きい】であれば【Aさんは、BさんにXXX円 お支払いください。】と表示する。

また、金額によっては小数点が出る場合があるので四捨五入している。

 

メールからのSlack連携

ここまででやりたいことはできたのだが、毎月1日に必ずこのスプレッドシートを開き「誰がいくら払うか」を確認するのは面倒なので自動化するする。

メールでの送信スクリプト

GoogleのスプレッドシートにはツールのスクリプトエディタでGmailからメール送信することができる。

f:id:nekosogiradical:20170201225510p:plain

// データ取得対象シートを決定
var book = SpreadsheetApp.getActiveSpreadsheet();
var sheet = book.getSheetByName("メール報告");

// A1:A3のセル範囲を取得
// getRange(始点:行, 始点:列, 終点:行, 終点:列,)
var dataRange = sheet.getRange(1, 2, 3, 1)

// 値を取得
var data = dataRange.getValues();
var row = data[0]

// メール送信
MailApp.sendEmail(data[0,0],data[0,1], data[0,2]);

 

メール送信に必要な送信先アドレスとタイトル、本文はスプレッドシートから取得する。

f:id:nekosogiradical:20170201230028p:plain

B2セル:送信先

メールアドレスなので関数を入れずにそのままメールアドレスを入れる

C2セル:タイトル [=YEAR('精算シート'!A4)&"年"&MONTH('精算シート'!A4)&"月分 ワリカン精算のご報告 [ゴチになります!]"]

年月日は精算シートから取得

D2セル:本文 [=YEAR('精算シート'!A4)&"年"&MONTH('精算シート'!A4)&"月分 ワリカン精算のご報告です。"&CHAR(10)&CHAR(10)&"先月の登録された件数は "&'精算シート'!A8&" 件でした。"&CHAR(10)&'精算シート'!L53&CHAR(10)&CHAR(10)&"(現在の登録数: "&'精算シート'!A6&" 件です)"]

精算シートのA6とA8セルの総件数と対象件数はここで使っている

総件数は現在1,000件としているので、1,000件を超えた場合は拡張するなりクリアするなりの手立てが必要となる

対象件数についてもひと月50件の上限を設けているので、それが超えていなかったかの確認をする意味で表示している

シート間のデータコピーのスクリプト

スプレッドシートを作成している過程で後回しにしておいた、フォームの回答を参照用シート(フォームの回答をコピーシート)にコピーする処理をメール送信する前に記述しておく。

//
//フォームから入力されたのシートをデータをコピー
//
var SS = SpreadsheetApp.getActiveSpreadsheet();

//コピー元の選択
var sheet = SS.getSheetByName("フォームの回答 1");
var from_range = sheet.getRange("A1:E1000");

//コピー先の選択
var to_sheet = SS.getSheetByName("フォームの回答をコピー");
var to_range = to_sheet.getRange("A1:E1000");

//コピーする
from_range.copyTo(to_range, {contentsOnly:true});

f:id:nekosogiradical:20170201231340p:plain

 

Google Apps Scriptビギナーズガイド: Googleをプログラミングせよ! PRIMERシリーズ (libroブックス)

Google Apps Scriptビギナーズガイド: Googleをプログラミングせよ! PRIMERシリーズ (libroブックス)

 

 

トリガーの設定

最後に自動実行するためのトリガーを設定。

f:id:nekosogiradical:20170201231434p:plain

毎月1日の午後11~正午までに実行

メールの内容をSlackに連携

今日日メールで送るってのもイマイチなので、夫婦間ではLINEとは別にSlackを使っているので、IFTTTで指定の送信先からメールが来たらその中身をSlackの#generalチャネルに投稿するレシピを作る。

f:id:nekosogiradical:20170201232038p:plain

すると無事に昨日(2/1)メールが届いてその内容がSlackに投稿された。

f:id:nekosogiradical:20170201231818p:plain

 

今後やりたいこと

これでひとまず使えるレベルのものになったが、既に追加したい機能がある。

送信した内容の編集機能

フォームから送信した直後は修正画面に遷移するリンクが回答後のページにあるが、閉じてしまうと修正はできないのでスプレッドシートを直接修正しなければならない。

しかし、編集用ページのURLを取得する方法があればフォームから送信したタイミングで本文にそのURLを付加すればいいのかな、と。

 

最後に

スマホアプリでもこのような貸し借りをするアプリは探すといくつか見つかるけど、多くは1対1ではなく1対多の作りとなっていて「誰に貸したか?」の情報を入力しなければならず、それは1対1では無駄になるので不採用とした。

 

それはそうと、夫婦間でSlackを使っていて色んな情報を投げて共有しているので、そんな話もできたらな…と。

 

Slack入門 [ChatOpsによるチーム開発の効率化]

Slack入門 [ChatOpsによるチーム開発の効率化]

 

 

*1:M52-N52)*-1,-1)&" 円をお支払いください。",M1&" さんが、"&N1&" さんに "&ROUND((M52-N52),-1)&" 円 お支払いください。"