この記事は CAMPHOR- Advent Calendar の16日目の記事です。
ytmatsuge です。
普段は六本木の会社で新規事業のビジネスサイドを担当しております。
本日は、非エンジニアの私が、同窓会委員の負担を減らすべく、出欠確認・名簿更新の自動化に携わった話をまとめてみます。
上記のような技術的な話はもちろんですが、「母世代でも利用しやすいサービス企画・設計」「最小限のコストで問題解決する方法」など、業務上の目的で実際に利用されるサービスをつくるにあたっていくつかハードルがあったので、少しでも参考にしていただければと思います。
改善前の状況
改善前の状況では、同窓会事務局と卒業生がメールでやり取りをし、
その情報をエクセルに転記しリストを作成していました。図にすると次のような流れになります。
これを見た若者は
いや、 LINE でええやん！！！
となりそうですが、この同窓会組織は「京都府に住む OB/OG 」という類の集まりで、90歳近くの大先輩まで所属していたため、メールで連絡する必要があります。(それどころか、一部の方は手紙でのやりとりをしています。)
事務局の方の大きな負担になっていたのが、次の二つの業務でした。
- メールの回答をエクセルに転記すること(図①)
- 住所などの基本情報に変更があった場合の更新(図②)
つまり、次のようなメールをひたすらエクセルにコピー&ペーストしたり...
次のような名簿リストを一人一人検索して更新がある場合は転記していたわけです...
年に一度とはいえ、こんなことを数百人分毎年やってらっしゃったのかと思うと、事務局の先輩方には頭が上がりませんね。。。
そんな先輩方に少しでも楽をしてもらえるよう、若者(私)は立ち上がったわけです。
取り組んだこと
概要
主に次の3つのことに取り組みました。
- 出欠・基本情報の回答をそのままリストにする
- 入力内容の確認メールを回答者に自動で送信する
- 出欠確認リスト・名簿リストを自動作成するボタンを作る
出欠の回答のリスト化
こちらは、 Google Form を利用すれば一瞬で解決します。便利な世の中ですね。
注意事項としては次のようなことがあります。
- 並び替えする場合を考慮し、数字は全て「半角入力」で統一
- 電話番号は冒頭の「0」が消えてしまうので、「-(ハイフン)」を入れるように統一
Google Form を使うと、入力規制もとても簡単にできます。ｽｺﾞｲ
確認メール自動送信
回答者が自分の入力した内容を確認できるように、
Google Apps Script を使って、入力されたメールアドレスに自動で回答内容を返信するようにしました。
function autoreply() {
//自動返信メールの件名
var title = "出欠のご連絡ありがとうございます。";
//自動返信メールの本文 \nは改行。
var body
= "出欠のご連絡をいただきありがとうございます。\n"
+"下記のとおりお受け付けいたしました。\n\n"
+ "------------------------------------------------------------\n";
//自動返信メールの本文2 本文1と本文2の間に入力内容が入る
var body2
= "------------------------------------------------------------\n\n"
+ "今後、ご質問・ご不明点等ございましたら送信元のメールアドレスまでご連絡くださいませ";
//後の処理で使うため、変数を設定。（フォームと名称を一致させる必要あり）
var name = '氏名';
var mail = 'メールアドレス';
var address = "";
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getLastRow();
var column = sheet.getLastColumn();
var range = sheet.getDataRange();
Logger.log("row="+row+" column="+column);
for (var i = 1; i <= column; i++ ) {
//スプレッドシートの入力項目名
var item = range.getCell(1, i).getValue();
//スプレッドシートの入力値
var value = range.getCell(row, i).getValue();
//本文（body）に、フォームの入力項目を追加
body += "■"+item+"\n";
//本文にフォームの入力内容を追加
body += value + "\n\n";
//フォームの入力項目が、「お名前」の場合は、「様」をつけて、本文の前に追加
if ( item === name ) {
body = value+" 様\n\n"+body;
}
//フォームの入力項目が「メールアドレス」の場合は、変数addressに入れる
if ( item === mail ) {
address = value;
}
}
//本文1に本文2を追加
body += body2;
//宛名＝address、件名＝title、本文=bodyで、メールを送る
GmailApp.sendEmail(address,title,body,{
from:'hogehoge@hogemail.com',name:'同窓会京都支部'
});
}
これで、万一入力ミスをした場合でも、自分で確認して再度回答してもらうことができますね！
出欠リスト・名簿リストの自動更新
こちらは Google Spreadsheet のSORT関数と Google Apps Script とを利用し、次のような流れで出席リストを自動更新できるようにしました。
- アンケート回答の中で出欠リストに必要な回答項目のみを抽出する(中間シート1の作成)
- 抽出したリストを回答の新しい順に並び替える(中間シート2の作成)
- 「姓」「名」が一致する行がある場合古いものを削除する(出席リストの作成)
1と2では Google Spreadsheet の関数を使って、次のように中間シートを作成します。
1では、下記のようにコピーしたい行のみを指定し、中間シート1を作成し、
= 'アンケート回答原本'!A:A
2では、アンケートの回答を最新順に並び替えた、中間シート2を作成します。
=SORT('中間シート1'!A:M,1,FALSE)
単なるコピーと降順での並び替えなので簡単ですね。
3では、次のスクリプトで、同姓同名の人のアンケートの回答は最新のもの以外を削除するようにしました。
// ボタンの生成
function onOpen() {
var ui = SpreadsheetApp.getUi();
var addon = ui.createAddonMenu();
addon.addItem('2017出席生成ボタンを表示', 'showSidebar');
addon.addToUi();
}
function onInstall() {
onOpen();
}
function showSidebar() {
var ui = SpreadsheetApp.getUi();
var sidebar = HtmlService.createHtmlOutputFromFile('Slidber');
sidebar.setTitle('Remove Duplicates Lines');
ui.showSidebar(sidebar);
}
function removeDuplicatesLines() {
var ui = SpreadsheetApp.getUi();
ui.alert("処理を開始します。",'このダイアログを閉じると処理が始まります。10秒ほど時間がかかります。結果は「2017出席」データに出力されます．元に戻す場合は、完全に終了してからCtrl+Zで戻せます。', ui.ButtonSet.OK);
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2017出席");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("中間リスト2");
var rowNum = 3000,
// 判定行の指定(2行目=姓, 3行目=名)
targetCol = 2,
targetCol2 = 3,
maxCol = 15,
dataTel = sheet.getRange(1,targetCol,rowNum,1).getValues(),
dataTel2 = sheet.getRange(1,targetCol2,rowNum,1).getValues(),
data = sheet.getRange(1,1,rowNum,maxCol).getValues(),
newData = new Array(),
checkData = new Array();
checkData2 = new Array();
for (i in data) {
var row = data[i],
checkRow = dataTel[i],
checkRow2 = dataTel2[i],
duplicate = false;
for (j in checkData) {
if (checkRow.join() == checkData[j].join() && checkRow2.join() == checkData2[j].join()) {
duplicate = true;
}
}
if(!duplicate){
checkData.push(checkRow);
checkData2.push(checkRow2);
newData.push(row);
}
}
sh.clearContents();
sh.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
これで、ボタンを押せば、同じ「姓」「名」の人が入力した情報は、最新の情報のみが記載される出席リストをつくることができるようになりました。
名簿リストも、名簿リストに必要な項目のみの「中間シート1」を作成すれば、その後は同じ流れで作成することができます。
運用マニュアルの作成
最後に、これらの一連の流れを事務局の方が自分たちで運営できるようにマニュアルを作成しました。事務局の先輩方は私の母ぐらいの年齢の方もいらっしゃったので、母にもわかってもらえるくらいに、丁寧に一つ一つの手順を説明するよう心がけました。
(↑↑実際のマニュアルより一部抜粋)
成果と感想
改善後の成果としては、次のようなものがあります。
- 回答者の返信コストの削減
- コピペでのリスト作成という手間の削減
- 手動での名簿更新の手間の削減
Google Form を利用することで、回答者もいちいちメールに返信項目をコピーする必要がなくなり、
簡単に申し込みできるようになったようです。
また、事務局の方に次のような声をいただくことができました。
リスト作成の手間が大幅に減り、楽になりました
大幅に参加者が増えました。手軽に返信できるようになったことも一つの大きな理由と考えられます
実際に本年の参加者は大幅に増えたそうです。(受付の仕組みを変えたことはほとんど寄与してないと思いますが...笑)
このように、技術的にはド初心者の非エンジニアの私でも、コードを書くことで感謝されるという「エンジニアの幸せ」を疑似体験することができました。
また、課題の定義からマニュアルの作成まで何度も実際の事務局の方と打ち合わせをする中で、「業務の中で実際に利用されるシステム」を作成する難しさを肌で感じることができたという点でも貴重な体験となりました。
最後に、今後も、運用していきたいと考えておりますので、技術的に「もっと良くなる！」「こうすればいいのに！」といったご提案があればコメント頂けますと幸いです。