エクセルの話が途中になっているのでこのトピックはササッと終わらせるつもりだったのだけど、コードを載せるとなるとメールアドレスやオフィス名の部分を変える必要があり、思いの外、時間がかかってしまった。（語学学校の宿題もたくさん。。。）
基本的には、前に作ったMS TeamsのBotのコード。それに今回、リクエスト送信者の情報を取得するfunction【CheckRequest】を追加。そして、最後に、全体を見直して、自分にとって見やすい形に変更した。
Google Formsでリクエストフォームを作る
何はともあれ、まずはフォームを作ってみて、各質問への答えがどのように格納されるのかを確認することから始めた。面倒くさいのでGIFでご容赦ください。
簡単。すべての質問への回答を必須にすること。そうしないとメールを送信できない。 では、次に、回答がどのように格納されるのかを確認する。（選択すると選ぶという統一感のない質問文になってしまった。）
見にくいけれど、以下のようになっている。
| A列 | B列 | C列 | D列 | E列 |
|---|---|---|---|---|
| Timestamp | 希望期間 | 所属オフィス | 名前 | 名前 |
【Office A】を選べば【E列】には何も入力されない。
メール一覧
勝手に出来上がる【Form Responses１】というシートの他に【EmailAddress】というシートを作成し、スタッフの名前とメールアドレスを入力。
| A列 | B列 |
|---|---|
| 鈴木 一郎 | ichiro@abc.com |
| 鈴木 次郎 | jiro@abc.com |
| 山田 太郎 | taro@abc.com |
| 山田 花子 | hanako@abc.com |
同姓同名のスタッフがいないので、このようなリストで大丈夫だが、同姓同名のスタッフがいる場合には、名前の左の列にオフィス名を追加しておくなどの工夫が必要。（名前じゃなくて社員番号を使うのでもいいが、フォームから選択するときに迷いそう。）
リクエスト送信者の情報を取得する
さて、やっとメインパート、リクエスト送信者の情報を取得するFunciton。どのシートを見るかは決まっているので、引数なし。 リクエストフォームへの回答内容、メール情報ともに（１）配列に格納し、（２）リクエスト送信者の名前からメールアドレスを取得、（３）最後に、「リクエスト送信者の名前・メールアドレス・希望期間・希望期間の開始日・終了日」を配列で返す、というFunction。
| /** | |
| * フォームの回答から、リクエスト送信者情報を返す | |
| * @param () なし | |
| * @return (string, string, string, date, date) 依頼者、メールアドレス、依頼期間、開始日、終了日 | |
| * @customfunction | |
| */ | |
| function CheckRequest(){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var RequestSheet = ss.getSheetByName('Form responses'); | |
| var EmailSheet = ss.getSheetByName('EmailAddress'); | |
| // データを配列に格納 | |
| var RequestValues = RequestSheet.getDataRange().getValues(); | |
| var EmailValues = EmailSheet.getDataRange().getValues(); | |
| // RequestValuesの最終インデックス | |
| var rLastIndex = RequestValues.length-1; | |
| // リクエストしたスタッフの名前を取得する | |
| switch (RequestValues[rLastIndex][2]) { | |
| case 'Office A': | |
| var StaffName = RequestValues[rLastIndex][3]; | |
| break; | |
| case 'Office B': | |
| var StaffName = RequestValues[rLastIndex][4]; | |
| break; | |
| }; | |
| // スタッフのメールアドレス一覧のスタッフの名前の列を一元配列にする。 | |
| var EmailVal = EmailSheet.getRange(1,1,EmailValues.length).getValues(); | |
| EmailVal = Array.prototype.concat.apply([],EmailVal); | |
| // リクエストしたスタッフの名前が、メールアドレス一覧の配列の何番目にあるかを調べる。 | |
| var EmailIndex = EmailVal.indexOf(StaffName); | |
| // リクエストしたスタッフのメールアドレス | |
| var recipient = EmailValues[EmailIndex][1]; | |
| // リクエストしたスケジュールの種類 | |
| var CalendarType = RequestValues[rLastIndex][2]; | |
| // リクエストしたスケジュールの期間（何日後までのスケジュールがほしいのか） | |
| var CalDays; | |
| switch (CalendarType) { | |
| case '明日': | |
| CalDays = 1; | |
| break; | |
| case '今後7日間': | |
| CalDays = 7; | |
| break; | |
| case '今後30日間': | |
| CalDays = 30; | |
| break; | |
| }; | |
| var now = new Date(); | |
| var end = new Date(now.getTime() + (CalDays * 24 * 60 * 60 * 1000)); | |
| return [StaffName, recipient, CalendarType, now, end]; | |
| }; |
そして、このデータの検索方法については、本の情報を参考にした。ひょっとすると同じ内容がウェブサイトにも載ってるかも。
最初は単純にメールアドレスを返すことだけを目指して書き始めたのだけど、「配列で返したらラクかも」と思って途中で方針変更。同じようなfunctionを乱立させずに済んでややスッキリ。普通は、初めから、配列で格納しようとすぐに思いつくんだろうけど、私のレベルでは、それを思いついたことだけで自分を褒め倒す感じだ。
コード全部
では、全体のコード。以前作った部分も少し直した。スケジュールの期間をベタ打ちから、変数への格納に変更。これで、期間によってコードを分けるという馬鹿なことから解放された。（そんなことをする人はいないのか。）
| //予定を自動返信する | |
| function RespondingRequest() { | |
| var list = ""; | |
| var s; | |
| var CalInUse = [ | |
| // 使用するカレンダーのアドレスとそのラベリング | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com","■○○さんの予定\n"], | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com", "■不在予定（外出・休暇）\n"], | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com", "■本社・地域本社の予定\n"], | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com", "■○○オフィス予定\n"], | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com", "■〆切\n"], | |
| ["xxxxxxxxxxxxxxxxxxxxxxx@group.calendar.google.com", "■休業日\n"] | |
| ]; | |
| for(var i=0; i<CalInUse.length; i++){ | |
| s = ListUpEvents(CalInfo[i][0]); | |
| if (s != "") list += CalInfo[i][1] + s + "\n"; | |
| }; | |
| // カスタム関数でリクエストの種類を取得する。 | |
| var ReqType = CheckRequest(); | |
| var ReqSender = ReqType[0]; | |
| // メール本文の内容 | |
| var FirstPart = ReqSender +"さん\n\nお疲れ様です。\n" + ReqType[2] + "の予定は以下のとおりです。ご確認くださいませ。\n\n"; | |
| // 省略文字の説明や、予定登録・変更の際の連絡先（現状、各人が入力する方法を取っていないため。） | |
| var SecondPart = "Abbreviations:\n" + | |
| "* NR = No Return\n\n"+ | |
| "この他に外出予定がある場合には, チームメール <xxxxxxxxxx@abc.com>宛にメール願います。カレンダーを更新いたします。\n"+ | |
| "e.g.\n04/01 09:00-10:00 不在: 打合せ @場所\n\n"; | |
| // リクエストフォームのアドレス | |
| var ThirdPart = "最新の予定表が必要な場合には、下記リンクからリクエストをお送りください。\n" + | |
| "<https://goo.gl/forms/xxxxxxxxxxxxxxx>\n"; | |
| if (list != "") { | |
| var body = FirstPart + list + SecondPart + ThirdPart; | |
| EmailToStaff (body) | |
| }; | |
| }; | |
| /** | |
| * フォームの回答から、リクエスト送信者情報を返す | |
| * @param () なし | |
| * @return (string, string, string, date, date) 依頼者、メールアドレス、依頼期間、開始日、終了日 | |
| * @customfunction | |
| */ | |
| function CheckRequest(){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var RequestSheet = ss.getSheetByName('Form responses'); | |
| var EmailSheet = ss.getSheetByName('EmailAddress'); | |
| // データを配列に格納 | |
| var RequestValues = RequestSheet.getDataRange().getValues(); | |
| var EmailValues = EmailSheet.getDataRange().getValues(); | |
| // RequestValuesの最終インデックス | |
| var rLastIndex = RequestValues.length-1; | |
| // リクエストしたスタッフの名前を取得する | |
| switch (RequestValues[rLastIndex][2]) { | |
| case 'Office A': | |
| var StaffName = RequestValues[rLastIndex][3]; | |
| break; | |
| case 'Office B': | |
| var StaffName = RequestValues[rLastIndex][4]; | |
| break; | |
| }; | |
| // スタッフのメールアドレス一覧のスタッフの名前の列を一元配列にする。 | |
| var EmailVal = EmailSheet.getRange(1,1,EmailValues.length).getValues(); | |
| EmailVal = Array.prototype.concat.apply([],EmailVal); | |
| // リクエストしたスタッフの名前が、メールアドレス一覧の配列の何番目にあるかを調べる。 | |
| var EmailIndex = EmailVal.indexOf(StaffName); | |
| // リクエストしたスタッフのメールアドレス | |
| var recipient = EmailValues[EmailIndex][1]; | |
| // リクエストしたスケジュールの種類 | |
| var CalendarType = RequestValues[rLastIndex][2]; | |
| // リクエストしたスケジュールの期間（何日後までのスケジュールがほしいのか） | |
| var CalDays; | |
| switch (CalendarType) { | |
| case '明日': | |
| CalDays = 1; | |
| break; | |
| case '今後7日間': | |
| CalDays = 7; | |
| break; | |
| case '今後30日間': | |
| CalDays = 30; | |
| break; | |
| }; | |
| var now = new Date(); | |
| var end = new Date(now.getTime() + (CalDays * 24 * 60 * 60 * 1000)); | |
| return [StaffName, recipient, CalendarType, now, end]; | |
| }; | |
| //予定をリストアップ | |
| function ListUpEvents(cal_id){ | |
| var list = ""; | |
| var cal = CalendarApp.getCalendarById(cal_id); | |
| // カスタム関数でリクエストの種類を取得する。 | |
| var ReqType = CheckRequest(); | |
| // リクエストされた期間の予定を取得 | |
| var now = ReqType[3]; | |
| var end = ReqType[4]; | |
| var timezone = "JST"; | |
| switch (ReqType[2]) { | |
| case '明日': | |
| var events = cal.getEventsForDay(end); | |
| for(var i=0; i < events.length; i++){ | |
| s = ""; | |
| if (events[i].isAllDayEvent()) { | |
| s += Utilities.formatDate(events[i].getAllDayStartDate(),timezone,"MM/dd\b\b"); | |
| } else { | |
| s += Utilities.formatDate(events[i].getStartTime(),timezone,"MM/dd HH:mm"); | |
| s += Utilities.formatDate(events[i].getEndTime(), timezone,"-HH:mm\b\b"); | |
| } | |
| s += events[i].getTitle(); | |
| list += s + "\n"; | |
| }; | |
| break; | |
| case '今後7日間': | |
| case '今後30日間': | |
| var events = cal.getEvents(now, end); | |
| for(var i=0; i < events.length; i++){ | |
| s = ""; | |
| if (events[i].isAllDayEvent()) { | |
| var startDate = new Date(events[i].getStartTime()); | |
| var endDate = new Date(events[i].getEndTime()); | |
| if (((endDate - startDate)/(24 * 60 * 60 * 1000)) < 2) { | |
| s += Utilities.formatDate(events[i].getAllDayStartDate(),timezone,"MM/dd\b\b"); | |
| } else { | |
| endDate.setDate(endDate.getDate() - 1); | |
| s += Utilities.formatDate(events[i].getAllDayStartDate(),timezone,"MM/dd\b"); | |
| s += Utilities.formatDate(endDate, timezone,"-\bMM/dd\b\b"); | |
| } | |
| } else { | |
| s += Utilities.formatDate(events[i].getStartTime(),timezone,"MM/dd HH:mm"); | |
| s += Utilities.formatDate(events[i].getEndTime(), timezone,"-HH:mm\b\b"); | |
| } | |
| s += events[i].getTitle(); | |
| list += s + "\n"; | |
| }; | |
| break; | |
| }; | |
| return list; | |
| }; | |
| //リクエストしたスタッフへのメール返信 | |
| function EmailToStaff (emailBody){ | |
| // カスタム関数でリクエストの種類を取得する。 | |
| var ReqType = CheckRequest(); | |
| var recipient = ReqType[1]; | |
| var copiedRecipient = ''; | |
| var sender = 'XXXXXXXXX@abc.com'; | |
| var senderName = '山田 太郎'; | |
| var timezone = "JST" | |
| var now = Utilities.formatDate(ReqType[3], timezone,"MM/dd") | |
| var end = Utilities.formatDate(ReqType[4], timezone,"MM/dd") | |
| switch (ReqType[2]) { | |
| case '明日': | |
| var subject = ReqType[2] +'の予定 (' + end + ')'; | |
| break; | |
| case '今後7日間': | |
| case '今後30日間': | |
| var subject = ReqType[2] + 'の予定 (' + now + ' - ' + end + ')'; | |
| break; | |
| }; | |
| var body = emailBody; | |
| GmailApp.sendEmail(recipient, | |
| subject, | |
| body, | |
| { | |
| bcc:'', | |
| cc: copiedRecipient, | |
| from: sender, | |
| name: senderName | |
| }); | |
| }; |
所感
配列はやっぱりすごい、ということ。あと、配列に格納する方法を取ったら処理速度が若干上がった気がする。（以前は、10秒弱かかっていたのが、平均で６秒程度に。）その場しのぎの解決策としては、まあまあ使えるレベルではないか。
自分の所属するオフィスのスタッフようには、希望する期間と名前だけを答えるリクエストフォームとしているため、もっと簡単。今回のコードを使って、同じオフィスでもチームごとに分けたりといった応用は可能。
何気なく「シンプルなリクエストフォーム」という条件から考え始めたフローだったけど、リクエスト送信者の操作を簡単にしたがために、２次元配列の検索方法を考えたりと、作るこっちが面倒くさくなった作業だった。
You must log in to post a comment.