【Gスプシ・GAS】立て替えを精算するツールを作ってみた
この記事の概要
・お金の立て替えは頻繁に発生すると面倒
・立て替えを勝手に計算してくれるツールを作った
・ツールの使い方の説明
・GASコードの紹介と説明
経緯
こんにちは。
突然ですが、旅行はお好きですか？
私は結構好きです。
高校時代の友人や大学の同期とよく遊びに行きました。
とはいえ、ご時世もあってそう簡単には行けないですよね、少し悲しいです。
さて、旅行やデートに出かけた際、以下のようなことが頻繁にあるのではないでしょうか。
例：
・ひとりが代表して全員分の宿を予約した。
・食事をした際に誰かが一括で夕飯代を支払った。
お会計はまとめてするのがスマートでも、複数人だと会計前にお金のやり取りが発生しますね。
それも一度ならまだ良いのですが、
例：
・宿の予約：AさんがA, B, Cの分
・飛行機のチケット：BさんがA, B, Cの分
・スキューバダイビング：CさんがA, B, Cの分
・夕飯：BさんがA, Bの分、Cさんは自分で
・翌日昼：Aさんは自分で、CさんがB, Cの分
など、旅行やデートなどで一定期間に何回も発生すると、さすがに面倒です。
記録しておかないと忘れてしまいそう、
だけどメモとったらとったで後で計算するのが大変…。
人によっては忘れてしまっても気にしないかもしれませんが、
学生はお金について細かく気にすることも少なくありません。
「あれ、あの分払ってもらってたっけ…。」
「忘れてて借りた分返してなかったら申し訳ないな…。」
「おかしいな、あの人にもう少し貸してたと思うんだけど…。」
そんな不安が旅行中や旅行後につきまとったらせっかくの思い出が台無しです。
ということが過去に何度かあったので、以下のようにできたらなあと考えました。
イメージ：
ショートカットからGoogleフォームを開く
→Googleフォームに30秒でメモ
→スプシが勝手に計算
→ひと目で誰が誰にいくら返せばいいのかわかる
これなら、毎度記録する手間はありますが、1回30秒ですし、
毎度お金のやり取りをしたり、あとで計算したりしなくて済みます。
なにより、不安・罪悪感・疑念を感じなくて済むのは楽ですよね。
よーし作ろう、と思い立ちました。
作ったツールの使い方
さて、ここからは実際に作ったものを見ながら、
使い始めから使い終わりまでを説明させていただきます。
ダウンロードして簡単に使い始められる！…ようにはなっていません。
模倣して準備１．２．を経る必要があります。
もう少し頑張ったらコードだけでできそうなんですが、まだしてません。
流れ：
準備１．スプレッドシートを用意して、GASコードを埋め込む。
準備２．トリガーを設定する。
利用１．旅行などの予定ができたら、準備したスプレッドシートを開く。
利用２．参加者を入力する。
利用３．Googleフォームを開いて、回答用リンクを共有する。
利用４．立て替えが発生したら、Googleフォームで記入する。
利用５．精算するタイミングになったら、専用スプレッドシートを開く。
利用６．表示された金額を、矢印の先へ支払う。
準備１．スプレッドシートを用意して、GASコードを埋め込む。
このあと紹介するスプレッドシートを同じように作って（それが面倒）、「ツール ＞ スクリプトエディタ」からGASのエディタを開き、下で紹介するGASのコードを入力します。
準備２．トリガーを設定する。
GASは、いつ発動するかを選ぶことができます。
今回の場合、「起動時」ですが、PCでしか発動しません。
もしもスマホで実行したいな、という場合は「編集時」にしましょう。
利用１．旅行などの予定ができたら、準備したスプレッドシートを開く。
上記「準備」で用意したスプレッドシートを起動しましょう。
トリガーが「起動時」で正しく設定されていれば、10秒くらいで発動します。
利用２．参加者を入力する。
今回作ったものは4名まで対応しています。
表示される入力フォームに、参加者の名前を入れていきましょう。
ひとりずつ名前を入力します。
もう入力しなくていい場合は「キャンセル」を選ぶと次に進みます。
（起動しない場合は最初からキャンセルします。）
（最初からキャンセル、または１人だけ登録してキャンセルした場合はなにも起こりません。）
利用３．Googleフォームを開いて、回答用リンクを共有する。
参加者を２人以上入力して完了すると、「処理が正常に完了しました。」とポップアップが出ます。
いま開いたスプレッドシートがあるフォルダと同じ階層に、「精算シート＋日付」のスプレッドシートとフォームが新たに作成されているはずです。
他の参加者に、Googleフォームの回答用リンクを共有しましょう。
利用４．立て替えが発生したら、Googleフォームで記入する。
作成されたGoogleフォームには、先ほど入力した参加者の名前が選択肢として入っています。
記入の方法：
「項目」には、立て替えが発生した理由を書きます。
例えば、「宿の予約」「１日目の夕飯」「ふれあい動物のエサ代」など。
「金額」には、金額を入力します。
半角でも全角でも大丈夫ですが、数字だけで入力します。
「後で精算する人」には、費用を発生させた人にチェックを入れます。
この質問はチェックボックスになっていて、複数人同時に入力できます。
注意することは、「金額に支払いをした人の費用が入っているなら、支払いをした人にもチェックを入れる」ことです。
例えば割り勘なら、全員にチェックを入れます。AさんとBさんが同じ値段のご飯を食べて、Aさんが一緒にお会計をした場合は、AとBの両方にチェックを入れます。
「支払った人」には、その場でお会計した人を１名選択します。
各人が別の金額を発生させた場合は、少し面倒な仕様になっています。
例：
「AさんとBさんは1,200円のオムライスを食べて、Cさんは1300円のハンバーグを食べた。支払いはAさんがまとめてした。」
→この場合、2回回答する必要があります。とはいえ、BさんとCさんがそれぞれ自分の分を入力すればいいです。
Bさん：
項目：オムライス
金額：1200
後で精算する人：B
支払った人：A
Cさん：
項目：ハンバーグ
金額：1300
後で精算する人：C
支払った人：A
→このようにすると、正常に計算されます。
この後も、立て替えが発生する度に同様に記入すればOKです。
利用６．表示された金額を、矢印の先へ支払う。
「早見表」の指示に従って、矢印の元から先へ表示された金額を支払うと、記録した立て替えの生産が完了します。
これで終わり！おめでとうございます！
GASのコードとスプシの演算内容の紹介
以上の手続きは、GASでファイル操作をし、スプレッドシート内の関数で計算することで実現しました。
スプレッドシートの内容
入っている関数も見えるような撮り方をしたのですが…
見にくいですね…笑
恐らくもっと簡潔に記述できるのでしょうが、蛇足がたくさんついています。
入っている値もGASで入力するのが良いでしょうか…。
GASコード全体
function createForm() { const cs = SpreadsheetApp.getActiveSpreadsheet(); const csid = cs.getId(); // 参加者を入力 var parts = [4]; for (i = 0; i < 4; i++) { var name = Browser.inputBox(`参加者${i+1}の名前(設定しない場合は「キャンセル」)`, Browser.Buttons.OK_CANCEL); if ((i == 0 || i == 1) & name == 'cancel') { return; // 参加者が0または1の場合は作成しない } if (name == 'cancel') { break; } else { parts[i] = name; } } // 日付を取得 var today = new Date(); today = Utilities.formatDate(today, 'JST', 'yyMMdd'); // シートのファイルを取得 const ss = SpreadsheetApp.create(`立て替え精算シート${today}`); // シートのファイルIDを取得 const ssId = ss.getId(); // シートが入っているフォルダIDを取得 const parentFolder = DriveApp.getFileById(csid).getParents(); const folder = parentFolder.next(); const parentId = folder.getId(); // FOLDER_IDとしてセット PropertiesService.getScriptProperties().setProperty('FOLDER_ID', parentId); // 作成したシートを同じフォルダに移動 const id = PropertiesService.getScriptProperties().getProperty('FOLDER_ID'); const sheetFile = DriveApp.getFileById(ssId); DriveApp.getFolderById(id).addFile(sheetFile); DriveApp.getRootFolder().removeFile(sheetFile); // シートをコピー var copied = cs.getSheets() var copiedName = [] for (i = 0; i < 5; i++) { copiedName[i] = copied[i].getName(); } var newCopy = [] for (i = 0; i < 5; i++) { newCopy[i] = copied[i].copyTo(ss); newCopy[i].setName(copiedName[i]); } ds = ss.getSheetByName('シート1') ss.deleteSheet(ds); // シートに参加者の名前を反映 var partsArray = convertArray(parts); ss.getSheetByName('Participants').getRange(2, 1, partsArray.length).setValues(partsArray); // 参加者の名前を選択肢としてフォームに記載する情報を取得 const values = ss.getSheetByName('Values').getDataRange().getValues(); const partValues = ss.getSheetByName('Participants').getRange(2, 1, 4).getValues(); const participants = generateArray(partValues, 0); const formTitle = values[0][1]; const formDesc = values[1][1]; // フォームを作成 const form = FormApp.create(`${formTitle}${today}`); // 作成したフォームをシートと同じフォルダに移動 const formFile = DriveApp.getFileById(form.getId()); DriveApp.getFolderById(id).addFile(formFile); DriveApp.getRootFolder().removeFile(formFile); // フォームをシートにリンク form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId()); SpreadsheetApp.flush(); // 回答記録シートの名前を変更 var newName = 'フォームの回答 1'; var target = ss.getSheets(); target[0].setName(newName); // フォームの内容を作成 form.setDescription(formDesc); form.addTextItem().setTitle('項目').setRequired(true); form.addTextItem().setTitle('金額').setRequired(true); form.addCheckboxItem() .setTitle('後で精算する人') .setChoiceValues(participants) .setRequired(true); form.addMultipleChoiceItem() .setTitle('支払った人') .setChoiceValues(participants) .setRequired(true); // シートに参照用の値を入れる newSheets = ss.getSheets(); newSheets[3].getRange(1, 1).setValue("={'フォームの回答 1'!$A:$E}"); newSheets[4].getRange(1, 1).setValue("={'フォームの回答 1'!$A:$B}"); newSheets[4].getRange(1, 3).setValue("={'加工済'!$F:$F}"); newSheets[4].getRange(1, 4).setValue("={'加工済'!$D:$E}"); // SpreadsheetApp.flush(); Browser.msgBox('処理が正常に完了しました。'); } function generateArray(values, column) { return values.map(record => record[column]).filter(value => value); } function convertArray(values) { var array2d = []; for (i = 0; i < values.length; i++) { array2d[i] = [values[i]] } return array2d; }
コードの説明
初心者が初心者向けに説明します。
間違いや冗長な部分、ツッコミどころがあれば教えてください。
GASに紐付いたスプレッドシートを取得する
function createForm() { const cs = SpreadsheetApp.getActiveSpreadsheet(); const csid = cs.getId();
javascriptをベースにしているので、メソッドはfunctionで宣言します。実は最初から Myfunction が定義されています。
・準備したスプレッドシートを取得して cs とする。
スプレッドシートは SpreadSheetApp で扱えるようですね。
ライブラリなどのインポートも不要です。
getActiveSpreadSheet() でGASに紐付けられたスプシを取得できます。
・準備したスプレッドシートを扱いやすいように、getId() で idを取得し csid としておく。
GASでユーザに入力させる
// 参加者を入力 var parts = [4]; for (i = 0; i < 4; i++) { var name = Browser.inputBox(`参加者${i+1}の名前(設定しない場合は「キャンセル」)`, Browser.Buttons.OK_CANCEL); if ((i == 0 || i == 1) & name == 'cancel') { return; // 参加者が0または1の場合は作成しない } if (name == 'cancel') { break; } else { parts[i] = name; } }
・参加者の名前を入れておく配列 parts を作る。
・inputBox() で名前を入力してもらう。
Browserでブラウザに指示を出せるので、inputBox() を表示させ、戻り値は name として保持します。
また、inputBox() の第2引数に 'Browser.Buttons.OK_CANCEL' を指定すると、OKボタンとキャンセルボタンが表示されます。
ユーザが「キャンセル」を選択すると、戻り値として 'cancel' が得られます。
・ひとりも入力せずに終了する場合（i == 0 && name == 'cancel'）や、ひとりしか入力しなかった場合(i == 1 && name == 'cancel')には、returnしてツールを作成しないようにする。
・キャンセル以外が入力されれば（普通に参加者の名前が入力されれば）name の内容を parts に格納する。
・2人以上入力してから終了した場合には、breakしてその後の処理に移る。
・最大４人まで参加できるように、4回繰り返す。
最後まで完了すると、parts[] には参加者の名前が順番に格納されているはずです。
GASで日付を取得する
// 日付を取得 var today = new Date(); today = Utilities.formatDate(today, 'JST', 'yyMMdd');
・Dateクラスから新しいインスタンスを生成する。
・Utilities.formatDate() で日時を取得する。
'JST' を選択すると日本時間になります。
'yymmdd' で日付をどこまで取得するか指定できますが、月は大文字でMMとすると良いと誰かに教わりました。
GASで新規スプレッドシートを作成する
// シートのファイルを取得 const ss = SpreadsheetApp.create(`立て替え精算シート${today}`); // シートのファイルIDを取得 const ssId = ss.getId();
・SpreadSheetApp で create() して、新しくスプレッドシートを作成する。
ちなみに新しいファイルはルートフォルダ（一番外側）にできます。
・新しく作ったファイルも扱いやすいように getId() でIDを取得してssIdとする。
GASでファイルを操作する
// シートが入っているフォルダIDを取得 const parentFolder = DriveApp.getFileById(csid).getParents(); const folder = parentFolder.next(); const parentId = folder.getId(); // FOLDER_IDとしてセット PropertiesService.getScriptProperties().setProperty('FOLDER_ID', parentId); // 作成したシートを同じフォルダに移動 const id = PropertiesService.getScriptProperties().getProperty('FOLDER_ID'); const sheetFile = DriveApp.getFileById(ssId); DriveApp.getFolderById(id).addFile(sheetFile); DriveApp.getRootFolder().removeFile(sheetFile);
ファイル操作には DriveApp を利用します。
・DriveApp で getFileById() を使い、最初のファイル（cs）が入っているフォルダを取得し、parentFolderとする。
よく見たらその次の folder は next() の戻り値を入れていますね、これってフォルダではなくてファイル…？でもちゃんと動きます。
・用意した parentId を FOLDER_ID として扱えるようにする。（？）
正直まだ理解ができていません。
id を PropatiesService に管理してもらって外から見えないようにする？辞書的なイメージなのですが合ってるかな（笑）
・さきほど新しく作ったスプレッドシート（ss）を、入れたいフォルダに addFile() して、そのものはremoveFile() で削除する。
これで新しいファイルが移動できました！
GASでシートを別のファイルのシートにコピペする
// シートをコピー var copied = cs.getSheets() var copiedName = [] for (i = 0; i < 5; i++) { copiedName[i] = copied[i].getName(); } var newCopy = [] for (i = 0; i < 5; i++) { newCopy[i] = copied[i].copyTo(ss); newCopy[i].setName(copiedName[i]); } ds = ss.getSheetByName('シート1') ss.deleteSheet(ds);
・getSheets() で、元のファイル（cs）にあるシートをコピーして copied とする。
・元のファイルのシート名を copiedName[] に保持する。
シートを普通にコピーすると 'シート1のコピー' みたいな名前になるので、後で元の名前に戻せるようにします。
・copied それぞれを copyTo() で ss にコピペし、ファイル名をもとに戻す。
・新しいファイルには「シート1」という使わないファイルがあるので、deleteSheet() で削除する。
フォームに必要な情報を準備する
// シートに参加者の名前を反映 var partsArray = convertArray(parts); ss.getSheetByName('Participants').getRange(2, 1, partsArray.length).setValues(partsArray); // 参加者の名前を選択肢としてフォームに記載する情報を取得 const values = ss.getSheetByName('Values').getDataRange().getValues(); const partValues = ss.getSheetByName('Participants').getRange(2, 1, 4).getValues(); const participants = generateArray(partValues, 0); const formTitle = values[0][1]; const formDesc = values[1][1];
・参加者の名前を convertArray() メソッド（最下部に自分で作ってあります）で二次元配列に変換する。
parts[] は一次元配列なので、そのままだと setValues() が使えません。
・getRange() で入れたい場所を指定し、setValues() で参加者の名前を入れる。
・後でGoogleフォームを作る際に必要になる情報を取得しておく。
GASで新規にGoogleフォームを作成する
// フォームを作成 const form = FormApp.create(`${formTitle}${today}`);
・FormAppで create() して立て替えを登録するフォームを作成する。
Googleフォームの操作は FormApp で行います。
GASでGoogleフォームとスプレッドシートをリンクする
// 作成したフォームをシートと同じフォルダに移動 const formFile = DriveApp.getFileById(form.getId()); DriveApp.getFolderById(id).addFile(formFile); DriveApp.getRootFolder().removeFile(formFile); // フォームをシートにリンク form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId()); SpreadsheetApp.flush(); // 回答記録シートの名前を変更 var newName = 'フォームの回答 1'; var target = ss.getSheets(); target[0].setName(newName);
上は、前に取得した親フォルダのIDを使って新規作成したフォームを移動しています。
・setDestination() でリンク先のスプレッドシートを指定し、リンクする。
これでフォームの回答が自動的にスプレッドシートに蓄積されます。
flush() メソッドは一旦そこまでのスプレッドシートの変更を確定するおまじない？です。
・setName() で新しい回答を蓄積するシートの名前を「フォームの回答 1」に変更する。
フォームをシートにリンクすると、新たに回答記録用のシートが作られます。
その際の名前が「フォームの回答 2」になることがあったので、大事をとって指定しました。
このシートの名前は後で使います。
GASでGoogleフォームの内容を作成する
// フォームの内容を作成 form.setDescription(formDesc); form.addTextItem().setTitle('項目').setRequired(true); form.addTextItem().setTitle('金額').setRequired(true); form.addCheckboxItem() .setTitle('後で精算する人') .setChoiceValues(participants) .setRequired(true); form.addMultipleChoiceItem() .setTitle('支払った人') .setChoiceValues(participants) .setRequired(true);
フォームの質問内容までGASで作れます。
・setDescription() で「フォームの説明」を記入する。
・addTextItem() で短文入力形式の質問を追加する。setTitle() で質問を入力し、setRequired() で必須項目にする。
setRequired() は引数を true にすると回答が必須になります。
・addCheckboxItem() でチェックボックス形式の質問を追加する。setChoiceValues() で選択肢を participants に指定する。
・addMultipleChoiceItem() でラジオボタン形式の質問を追加する。
ユーザに入力してもらった参加者名がスプレッドシートに入力され、participants としてフォームの選択肢に追加されました。
参照問題（#REF!エラー）を解決する
// シートに参照用の値を入れる newSheets = ss.getSheets(); newSheets[3].getRange(1, 1).setValue("={'フォームの回答 1'!$A:$E}"); newSheets[4].getRange(1, 1).setValue("={'フォームの回答 1'!$A:$B}"); newSheets[4].getRange(1, 3).setValue("={'加工済'!$F:$F}"); newSheets[4].getRange(1, 4).setValue("={'加工済'!$D:$E}");
最後にここで苦労しました。
もともとこの参照はセルに直接入力していましたが、一番最初は「フォームの回答」シートがないので参照先が存在せず、#REF! とよく怒られていました。
シートを追加してもセルが気づかず、一度セルを編集状態にしてエンターするまで #REF! と怒っていたので、参照先のシートを作った後で参照を入れることにしました。
終わりに
長々と付き合ってくださりありがとうございました。
思いつきで始めたことも、イメージ通りにできるとなかなか嬉しいものですね。
もうお金のことで心配せずに旅行に行けそうです。
今後はGoogleに頼らずにWebアプリとして公開できないかと考え中です。
（なによりUIがダサいので…。）
いままでろくにプログラミングとかしたこともなかったのに簡単にできたので、みなさんもなにか便利なツールを作ってみてはいかがでしょうか。
それではまた。
