この記事は、EEIC Advent Calendar 2023の16日日、12/16の記事として書かれています。
目次
はじめに
こんにちは、EEIC3年のツイ廃、ライルです。
EEICは毎年、5月に本郷キャンパスで開催される「五月祭」において、「近未来体験」という企画を行っています。
(以下は去年のHP)
私は現在、近未来体験2024の会計を担当しています。
そこで、今回は近未来体験の会計システムをGASで作ってみました。
会計システムの概要
近未来体験の会計システムは、以下のような流れで動作します。
予算使用申請フェーズでは、申請者Aが「これ買いたいよ」という申請を行い、会計担当者はその許可の可否を決定します。
許可した場合はその取引のIDを発行して申請者Aに連絡します。
経費返金フェーズでは、予算使用申請フェーズで発行されたIDを用いて、経費の返金の申請を行います。
予算使用申請フェーズの実装
予算使用申請フォームの作成
まず、予算使用申請フォームを作成します。
フォームの内容は以下の通りです。
- メールアドレス
- 新規回答 or 編集回答
- 予算使用申請者の名前
- 予算使用申請者のチーム
- 予算使用予定金額
- 購入予定品・サービス名
- 購入予定品・サービスのURL
- 申請理由
この時、フォームが入力されたら、会計担当者にメールが送信されるようGASで設定します。
フォームの設定画面から、スクリプトエディタを開きます。
GASのコードは以下の通りです。
EMAIL_ADDRESS_OF_ACCOUNTANT = "会計担当者のメールアドレス";
function onFormSubmitted(event) {
try {
myFunction(event);
} catch (_error) {
try {
myFunction(event);
} catch (error) {
errorNotice();
}
}
}
class answerList {
constructor (emailAddress, isNewAnser, name, team, amount, item, url, reason) {
this.emailAddress = emailAddress;
this.isNewAnser = isNewAnser;
this.name = name;
this.team = team;
this.amount = amount;
this.item = item;
this.url = url;
this.reason = reason;
}
}
function myFunction(event) {
// fetch answer
const answer = fetchAnswer(event);
// send email
sendEmail(answer);
}
function fetchAnswer(event) {
const emailaddress = event.response.getRespondentEmail();
const itemResponses = event.response.getItemResponses();
const isNewAnser = (itemResponses[0].getResponse() == "新規回答") ? 1 : 0;
const answer = new answerList (
emailaddress,
isNewAnser,
itemResponses[1].getResponse(),
itemResponses[2].getResponse(),
itemResponses[3].getResponse(),
itemResponses[4].getResponse(),
itemResponses[5].getResponse(),
itemResponses[6].getResponse()
);
return answer;
}
function sendEmail(answer) {
if (answer.isNewAnser == 1) {
// メールの件名
const subject = "【近未来体験2024】予算使用申請新規回答の連絡";
// メール本文
const body = `会計担当へ
予算使用申請がありました。
内容を確認し、申請の受理の可否を考えてください。
下記の内容が申請内容です。
-----
氏名: ${answer.name}
メールアドレス: ${answer.emailAddress}
チーム: ${answer.team}
申請金額: ${answer.amount}
申請品: ${answer.item}
申請品のURL: ${answer.url}
申請理由: ${answer.reason}
-----
ご確認お願い致します。
これは近未来体験2024会計システムからの自動送信です。
本メールに対するお問い合わせは下記メールアドレスまでお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
} else {
// メールの件名
const subject = "【近未来体験2024】予算使用申請編集の連絡";
// メール本文
const body = `会計担当へ
予算使用申請の編集がありました。
内容を確認し、申請の受理の可否を考えてください。
下記の内容が再申請内容です。
-----
氏名: ${answer.name}
メールアドレス: ${answer.emailAddress}
チーム: ${answer.team}
申請金額: ${answer.amount}
申請品: ${answer.item}
申請品のURL: ${answer.url}
申請理由: ${answer.reason}
-----
ご確認お願い致します。
これは近未来体験2024会計システムからの自動送信です。
本メールに対するお問い合わせは下記メールアドレスまでお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
}
}
function errorNotice() {
const subject = "【近未来体験2024】予算使用申請フォームにおけるエラー連絡";
// メール本文
const body = `会計担当へ
予算申請フォームにおいて、回答がなされましたが、エラーが発生しました。
原因究明をお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
}
次に、フォームの設定画面から、トリガーを設定します。
このままではエラーがでるので、以下の手順でエラーを解消します。
設定を開いて、「「appsscript.json」マニフェスト ファイルをエディタで表示する」のチェックを入れます。
次に、appsscript.jsonを以下のように書き換えます。
{
"timeZone": "Asia/Tokyo",
"dependencies": {
},
"oauthScopes" : [
"https://www.googleapis.com/auth/forms.currentonly",
"https://www.googleapis.com/auth/forms",
"https://www.googleapis.com/auth/gmail.send",
"https://www.googleapis.com/auth/gmail.compose",
"https://www.googleapis.com/auth/gmail.modify",
"https://mail.google.com/",
"https://www.googleapis.com/auth/gmail.addons.current.action.compose"
],
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
これで、予算使用申請フォームの作成は完了です。
問題無く動作するか、実際にフォームを送信して確認してみるといいでしょう。
予算使用許可フォームの作成
申請内容が妥当だと判断したら、会計担当者は予算使用許可フォームを入力してデータベースに登録します。
そこで、予算使用許可フォームを作成します。
フォームの内容は以下の通りです。
- 申請者の氏名
- 申請者のメールアドレス
- 申請者のチーム
- 申請金額
- 申請品・サービス
申請理由や申請品のURLはあくまで申請の認可を決める際に参考にする情報であるため、わざわざデータベースに登録する必要はないでしょう。
次に、フォームが送信されたら、以下の動作をするようにGASを設定します。
- フォームの内容をデータベースに登録する
- データベースで予算オーバーになっていないか確認する
- 予算オーバーになっていない場合は、取引IDを発行し、申請者にメールで連絡する
- 予算オーバーになっている場合は、申請者にメールで連絡する
GASのコードは以下の通りです。
EMAIL_ADDRESS_OF_ACCOUNTANT = "会計担当者のメールアドレス";
DATABASE_ID = "スプレッドシートのID";
function onFormSubmitted(event) {
try {
myFunction(event);
} catch (_error) {
try {
myFunction(event);
} catch (error) {
errorNotice();
}
}
}
function myFunction(event) {
// fetch answer
const answer = fetchAnswer(event);
// send database and determin id
const id = sendDatabase(answer);
// send email
sendEmail(answer, id);
}
class answerList {
constructor (name, emailAddress, team, amount, item) {
this.name = name;
this.emailAddress = emailAddress;
this.team = team;
this.amount = amount;
this.item = item;
}
}
function fetchAnswer(event) {
const itemResponses = event.response.getItemResponses();
const answer = new answerList (
itemResponses[0].getResponse(),
itemResponses[1].getResponse(),
itemResponses[2].getResponse(),
Number(itemResponses[3].getResponse()),
itemResponses[4].getResponse(),
)
return answer;
}
function sendDatabase(answer) {
const sht = SpreadsheetApp.openById(DATABASE_ID).getSheetByName(answer.team);
const lastRow = sht.getLastRow();
const lastSum = (lastRow == 1) ? 0 : Number(sht.getRange(lastRow, 6).getDisplayValue())
const nowSum = answer.amount+lastSum;
let id = setTeamId(answer)+lastRow;
if (checkBudgetOver(nowSum, answer)) id = 'Y';
sht.getRange(lastRow+1, 1).setValue(id);
sht.getRange(lastRow+1, 2).setValue(answer.name);
sht.getRange(lastRow+1, 3).setValue(answer.emailAddress);
sht.getRange(lastRow+1, 4).setValue(answer.item);
sht.getRange(lastRow+1, 5).setValue(answer.amount);
sht.getRange(lastRow+1, 6).setValue(nowSum);
return id;
}
function setTeamId(answer) {
let teamId = 'Z';
switch(answer.team) {
case "teamA":
teamId = 'A';
break;
case "teamB":
teamId = 'B';
break;
case "teamC":
temaId = 'C';
break;
case "teamD":
temaId = 'D';
break;
case "teamE":
teamId = 'E';
break;
case "teamF":
teamId = 'F';
break;
case "teamG":
teamId = 'G';
break;
case "teamH":
teamId = 'H';
break;
default:
console.log("team may not be existed.")
break;
}
return teamId;
}
function checkBudgetOver(nowSum, answer) {
let BudgetOver = true;
switch(answer.team) {
case "teamA":
if (nowSum <= 10000) BudgetOver = false;
break;
case "teamB":
if (nowSum <= 20000) BudgetOver = false;
break;
case "teamC":
if (nowSum <= 30000) BudgetOver = false;
break;
case "teamD":
if (nowSum <= 40000) BudgetOver = false;
break;
case "teamE":
if (nowSum <= 50000) BudgetOver = false;
break;
case "teamF":
if (nowSum <= 60000) BudgetOver = false;
break;
case "teamG":
if (lastSum <= 70000) BudgetOver = false;
break;
case "teamH":
if (lastSum <= 80000) BudgetOver = false;
break;
default:
console.log("team may not be existed.")
break;
}
return BudgetOver;
}
function sendEmail(answer, id) {
if (id == 'Y') {
// メールの件名
const subject = "【近未来体験2024】予算使用申請の却下";
// メール本文
const body = `${answer.name}様
以下の内容の予算申請が却下されました。
理由は予算オーバーです。
下記の内容が却下された申請内容です。
-----
氏名: ${answer.name}
チーム: ${answer.team}
申請金額: ${answer.amount}
申請品: ${answer.item}
-----
ご確認お願い致します。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
GmailApp.sendEmail(answer.emailAddress, subject, body);
} else {
// メールの件名
const subject = "【近未来体験2024】予算使用申請の承認";
// メール本文
const body = `${answer.name}様
以下の内容の予算申請が受理されました。
商品の購入、サービスの使用をして構いません。
領収書が無いと返金できないため、領収書は失くさずに保管してください。
また、取引終了後は経費申請フォームの回答を忘れずに行ってください。
なお、今回の予算使用IDは「${id}」です。
経費申請フォームで使用するため、忘れないようにしてください。
下記の内容が受理された申請内容です。
-----
氏名: ${answer.name}
チーム: ${answer.team}
申請金額: ${answer.amount}
申請品: ${answer.item}
-----
ご確認お願い致します。
これは近未来体験2024会計システムからの自動送信です。
本メールに対するお問い合わせは下記メールアドレスまでお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
GmailApp.sendEmail(answer.emailAddress, subject, body);
}
}
function errorNotice() {
const subject = "【近未来体験2024】予算使用許可フォームにおけるエラー連絡";
// メール本文
const body = `会計担当へ
予算使用許可フォームにおいて、回答がなされましたが、エラーが発生しました。
原因究明をお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
}
スプレッドシートのIDは、後述のデータベースの作成で作成したスプレッドシートのIDを入力してください。
checkBudgetOver関数は、予算オーバーになっていないか確認する関数です。
各チームの予算をここに記入していきましょう。
IDの発行は「チーム名+発行番号」で行います。(例えば、teamAの場合はA1,A2,A3...となります。)
トリガーの設定は予算使用申請フォームと同様に行います。
これで、予算使用許可フォームの作成は完了です。
データベースの作成
次にデータベースという名のスプレッドシートを作成します。
スプレッドシートはチームごとにシートを分けて以下のように作ってあげるといいでしょう。
これで、予算使用申請フェーズの実装は完了です。
予算使用許可フォームを送信して、データベースにちゃんと登録されているか、メールが申請者に送信されているか確認してみましょう。
経費返金フェーズの実装
お買い物が終わったら、申請者は経費返金フォームを送信します。
フォームの内容は以下の通りです。
- メールアドレス
- 氏名
- チーム
- 予算使用ID
- 使用金額
- 領収書の画像
次に、フォームが送信されたら、以下の動作をするようにGASを設定します。
- 実際に掛かった金額をデータベースに登録する
- 現在までに使用した金額を計算する
- 会計担当にメールで連絡する
本来はここで、使用した金額が予算を超えていないか確認する必要がありますが、今回は省略します。
(予算超えても何とかなるように予算を設定したので。)
GASのコードは以下の通りです。
EMAIL_ADDRESS_OF_ACCOUNTANT = "会計担当者のメールアドレス";
DATABASE_ID = "スプレッドシートのID";
function onFormSubmitted(event) {
try {
myFunction(event);
} catch (_error) {
try {
myFunction(event);
} catch (error) {
errorNotice();
}
}
}
function myFunction(event) {
// fetch answer
const answer = fetchAnswer(event);
// send Database
sendDatabase(answer);
// send Email
sendEmail();
}
class answerList {
constructor (name, team, id, amount) {
this.name = name;
this.team = team;
this.id = id;
this.amount = amount;
}
}
function fetchAnswer(event) {
const itemResponses = event.response.getItemResponses();
const amount = (Number(itemResponses[3].getResponse()) > 0) ? Number(itemResponses[3].getResponse()) : 0 ;
const answer = new answerList (
itemResponses[0].getResponse(),
itemResponses[1].getResponse(),
itemResponses[2].getResponse(),
amount,
)
return answer;
}
function sendDatabase(answer) {
const sht = SpreadsheetApp.openById(DATABASE_ID).getSheetByName(answer.team);
let idNumber = parseInt((answer.id).replace(/[^0-9]/g, ""));
const lastRow = sht.getLastRow();
sht.getRange(idNumber+1, 7).setValue(answer.amount);
let sum = 0;
for (let i=2; i <= lastRow; i++) {
sum = sum + Number(sht.getRange(i, 7).getDisplayValue())
sht.getRange(i, 8).setValue(sum);
}
}
function errorNotice() {
const subject = "【近未来体験2024】経費申請フォームにおけるエラー連絡";
// メール本文
const body = `会計担当へ
経費申請フォームにおいて、回答がなされましたが、エラーが発生しました。
原因究明をお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
}
function sendEmail() {
const subject = "【近未来体験2024】経費申請フォームの提出";
// メール本文
const body = `会計担当へ
経費申請フォームにおいて、回答がなされました。
対応をお願いいたします。
MailTo: ${EMAIL_ADDRESS_OF_ACCOUNTANT}`
// 会計担当にメール送信
GmailApp.sendEmail(EMAIL_ADDRESS_OF_ACCOUNTANT, subject, body);
}
トリガーの設定は予算使用申請フォームと同様に行います。
これで、経費返金フォームの作成は完了です。
実際にフォームを送信して、データベースにちゃんと登録されているか確認してみましょう。
最後に
いかがでしたか?
今回は、GASを用いて会計システムを作成してみました。
GASは、JavaScriptを用いて簡単にWebアプリケーションを作成できるので、ぜひ使ってみてください。
また、今年も近未来体験は五月祭で開催されます。
実際に電子工作を体験したり、プログラミング教室に参加したり、私達EEIC生の作ったVRゲームを遊んだり、EEICの研究内容に触れてみたり……、様々な体験ができます。
ぜひ、遊びに来てくださいね。
余談
今回初めてJavaScriptを触ったのですが、1000+0=10000となり驚きましたね。
動的型付けは私には向いていないようです。