部署メンバーの今日の出勤状況は？GASを使って電話の取り次ぎを簡単に！

こんにちは。商業ディベロッパーに新卒で入社して6年目のOLです！現在は本社に勤めています。
前回は趣味の課題解決に挑戦しましたが、今回は業務の課題解決に挑戦しようと思います！

前回の記事：ピラティスのレッスンを無断欠席しないために…！リマインド用のLINE Botを作ってみた

勤務形態とオフィス環境

早速ですが、私の会社の勤務形態とオフィス環境はこのような状況です

• 出勤日が自由（平日に休んだり土日に出勤したりしてOK！）
• テレワークも可能
• 固定席ではなくフリーアドレス

ワークライフバランスを保てる勤務形態も、色々な部署の人とコミュニケーションが取れるオフィス環境も、個人的には大満足です！

社外の方から電話がかかってきたときの困りごと

しかし問題点があります。
それは、オフィスに固定電話があることです。
その固定電話に社外の方から部署メンバー宛の電話がかかってくるのですが、電話の取り次ぎが大変なのです。

どのように大変なのかというと…

相手先：「Aさんはいますか？」
→私：Aさんが出社・テレワーク・出張・休みのどれかを調べる

Aさんがいない場合

相手先：「Aさんの携帯電話番号を教えてください。」
→私：Aさんの携帯電話番号を調べる

相手先：「Aさんの次回の出社日はいつですか？」
→私：Aさんの次回の出社日を調べる

相手先：「Aさんから折り返しの電話がほしいです。」
→私：Aさんに連絡する

このような状況です。
そのため、相手先をお待たせしてしまうことに対する申し訳なさや、自分自身の業務に早く戻りたいとい気持ちを、いつも感じています。

電話の取り次ぎを簡単にするシステムを作ってみた

そこで、2つのシステムを作ってみました！

部署メンバーの「今日の出勤状況・携帯電話番号・次回の出社日」を毎朝メールで受信
⇒相手先への回答時間を短縮するため

部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信
⇒取り次ぎ時間を短縮するため

使用ツール

• Google スプレッドシート
• Google Apps Script
• ChatGPT
  ※私はコードが書けないので、コードの書き出しをChatGPTに手伝ってもらいました！

「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法

まずは、「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法を説明していきます！

1.Google スプレッドシート（1シート目）に「部署メンバーの1ヶ月分の出勤情報と携帯電話番号」を記入する

2.Google スプレッドシート（2シート目）に「今日の部署メンバーの出勤状況」を送りたい宛先を記入する

3.Google Apps Scriptに入力するコードをChatGPTに教えてもらう

プロンプト（ChatGPTに対する指示）

ChatGPTが書きだしたコード

ChatGPTが書きだしたコード

function sendAttendanceEmails() {
  // まず、スプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var attendanceSheet = ss.getSheetByName('出勤情報');
  var recipientSheet = ss.getSheetByName('メールの宛先');
  
  // 出勤情報のデータを取得
  var attendanceData = attendanceSheet.getDataRange().getValues();
  
  // メールの宛先データを取得
  var recipientData = recipientSheet.getRange('B:B').getValues();
  
  // 今日の日付を取得
  var today = new Date();
  var todayStr = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy/MM/dd');
  
  // 曜日を日本語で取得
  var daysOfWeek = ['日', '月', '火', '水', '木', '金', '土'];
  var todayDayOfWeek = daysOfWeek[today.getDay()];
  
  // 件名のフォーマット
  var subjectFormat = todayStr + '（' + todayDayOfWeek + '）の出勤情報';
  
  // メール本文の情報を格納する変数
  var emailBody = '';

  // 出勤情報シートのヘッダーから名前と電話番号を取得
  var names = attendanceData[0].slice(2, 6);
  var phoneNumbers = attendanceData[1].slice(2, 6);

  // 出勤情報シートの各行をチェック
  for (var i = 2; i < attendanceData.length; i++) {
    var row = attendanceData[i];
    var date = Utilities.formatDate(new Date(row[0]), Session.getScriptTimeZone(), 'yyyy/MM/dd');
    
    // 今日の日付と一致する行を見つけたら
    if (date === todayStr) {
      for (var j = 2; j < 6; j++) {
        emailBody += names[j-2] + '\n';
        emailBody += '・今日の出勤状況：' + row[j] + '\n';
        emailBody += '・携帯電話番号：' + phoneNumbers[j-2] + '\n';

        // 次回の出社日を見つける
        for (var k = i + 1; k < attendanceData.length; k++) {
          if (attendanceData[k][j] === '出社') {
            var nextDate = Utilities.formatDate(new Date(attendanceData[k][0]), Session.getScriptTimeZone(), 'yyyy/MM/dd');
            var nextDayOfWeek = daysOfWeek[new Date(attendanceData[k][0]).getDay()];
            emailBody += '・次回の出社日：' + nextDate + '（' + nextDayOfWeek + '）\n';
            break;
          }
        }
        emailBody += '\n'; // 各人の情報の間に空行を挿入
      }
      break; // 今日の日付と一致する行が見つかったら、ループを抜ける
    }
  }

  // メールを送信
  for (var l = 1; l < recipientData.length; l++) {
    if (recipientData[l][0]) {
      MailApp.sendEmail({
        to: recipientData[l][0],
        subject: subjectFormat,
        body: emailBody
      });
    }
  }
}

2024/7/29 修正
記事中のコードの書き方を修正しました。
今までRubyで書いてしまっていたのですが、Google Apps ScriptのコードなのでJavascriptに修正しました。
※参考にさせていただいたサイト：JavascriptとRubyの違いについて

4.Google Apps Scriptにコードを記入し、実行する

• 該当のGoogle スプレッドシートの「拡張機能」＞「Apps Script」をクリックする。
  

• 「＜＞（エディタ）」＞「コード.gs」の部分にChatGPTが書きだしたコードをコピーアンドペーストする。
  

• 保存ボタンをクリックした後、「実行」をクリックする。
  

こうすることで、欲しい情報がメールで届きました！
このメールがあれば、あちこち探しまわることなく短時間で相手先に回答できそうです！

5.Google Apps Scriptのトリガーを設定する

このメールを毎日届くようにするために、トリガーを設定します。

• 「（トリガー）」をクリックし、「（トリガーの編集）」をクリックする。
  

• イベントのソースを選択：「時間主導型」を選択する

• 時間ベースのトリガーのタイプを選択：「日付ベースのタイマー」を選択する

• 時刻を選択：メールを送りたい時間帯を選択する
  

デフォルトでは1時間の範囲でしか設定できませんが、ぴったりの時間に定期実行を行う方法もあるようです。
※参考にさせていただいたサイト：GASの定期実行をピッタリの時間に行う方法

今回解決したい課題については、ぴったりの時間に行う必要はないので、割愛しました。

部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法

続いて、部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法を説明していきます！

1.Google スプレッドシート（1シート目）に「メールのテンプレート」を作成する

2.Google Apps Scriptに入力するコードをChatGPTに教えてもらう

プロンプト（ChatGPTに対する指示）

ChatGPTが書きだしたコード

ChatGPTが書きだしたコード

function sendEmails() {
  // スプレッドシートとシートの取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('シート1');
  
  // シートのデータ範囲を取得
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  
  // ヘッダー行をスキップし、データ行を処理
  for (var i = 1; i < data.length; i++) {
    var time = formatTime(data[i][0]); // A列の時間をフォーマット
    var companyName = data[i][1]; // B列の会社名
    var contactPerson = data[i][2]; // C列の担当者名
    var purpose = data[i][3]; // D列の用件
    var phoneNumber = data[i][4]; // E列の電話番号
    var remarks = data[i][5]; // F列の備考
    var recipientName = data[i][6]; // G列の宛先
    var recipientEmail = data[i][7]; // H列の宛先のメールアドレス
    var emailSubject = data[i][8]; // I列のメールの件名
    var signature = data[i][9]; // J列の署名

    // メール本文の作成
    var emailBody = recipientName + "\n\n";
    emailBody += "お疲れ様です。\n";
    emailBody += time + "に" + companyName + "の" + contactPerson + "から" + purpose + "について電話がありました。\n";
    emailBody += "つきましては、" + phoneNumber + "に折り返しのお電話をお願いします。\n";
    if (remarks) {
      emailBody += remarks + "\n";
    }
    emailBody += "\n" + signature;

    // メールの送信
    MailApp.sendEmail({
      to: recipientEmail,
      subject: emailSubject,
      body: emailBody
    });
  }
}

function formatTime(date) {
  var formattedTime = Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), 'HH:mm');
  return formattedTime;
}

3.Google Apps Scriptにコードを記入し、実行する

Google Apps Scriptにコードを記入し、実行することで、欲しい情報がメールで届きました！
※コードの記入方法・実行方法は同上なので割愛します。

Google スプレッドシートをテンプレートと見立てて情報のみを記入することで、メールの作成時間を大幅に削減できそうです！

部署のメンバーからの感想

実は今回は7日間でシステムを作ることを目標にしていたのですが、ChatGPTにコードの書き出しを手伝ってもらったことで、構想から3日でシステムが出来上がったので、浮いた4日間で部署のメンバーから感想をもらいました！

1.「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法についての感想

「1ヶ月毎の出勤情報を入力するのが大変。予定も頻繁に変わるし…」
「会議の時間も分かったらいいよね！」
これは本当にその通りです…。
私の会社ではOutlookの予定表に各自の予定を書いているので、今回の運用をする場合はOutlookからGoogle スプレッドシートに転記しなければなりません。
調べてみると、Outlookの予定表をGoogleカレンダーに共有することができるそうです！
そうすればGoogleカレンダーとGoogle Apps Scriptで連携できるかもしれないので、いつか挑戦してみたいと思います！
※参考にさせていただいたサイト：GoogleカレンダーとOutlook予定表を同期する方法

「出社の時、どの座席に座っているまで分かれば、保留にしたまま繋げるよね！」
これもその通りです…。
私の会社はフリーアドレスなのですが、システムで座席を予約しているので、その座席番号が連携できるのが1番の理想です。
しかし、その予約システムとGoogle Apps Scriptが連携できなさそうだったので今回は諦めることにしました。
ざっと席を見渡してその人がいなければ、折り返し電話で対応したいところです。

2.部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法についての感想

「これは他の業務でも使えそうだね！」
ポジティブな感想をもらえてとても嬉しかったです！
しかし、今回のシステムではメールの送信者がGmailのアドレスになってしまい、普段使っている会社のメールアドレスとは異なるので、部署内の業務連絡くらいにとどめるのが良さそうです。

私の会社はMicrosoft系なので、会社全体でこのシステムを使うためには、Microsoft系と連携できるGoogle Apps Scriptのようなものがあればいいのではないかと思いました。（デジタル初心者すぎて日本語が変だったらすみません。）
もしかしたら既にあるかもしれないので、また色々調べてみます！

2024/7/29 追記①

"Microsoft系と連携できるGoogle Apps Scriptのようなもの"について調べたところ、「VBA」や「マクロ」というものがヒットしました。
次から同じようなシステムを作る時は、Google Apps ScriptとVBAを上手に使い分けていきます！

※参考にさせていただいたサイト：

• GoogleSpreadsheetとExcelのマクロの違いは？機能や使い方について
• いまさら聞けないIT用語：「マクロ」とは？ VBAとの違い、できること、Excelマクロの初歩など基本を解説
• VBAとマクロの違いとは？VBA・マクロができること、マクロの作り方を徹底解説！

2024/7/29 追記②
今回作った2つ目のシステムである「メールのテンプレート」について調べたところ、Outlookにもいくつか機能があることが分かりました。
自分発信でメールを作る時はGoogle Apps ScriptやVBAを使うのも良いと思うのですが、人からもらったメールの返信の時はOutlookの定型句機能を使う方が便利だと個人的に感じました！

※参考にさせていただいたサイト：

• Outlookのテンプレート作成・呼び出し方法
• 【Outlook】定型句機能を使ってテンプレートを２秒で呼び出し

まとめ

今回はじめてChatGPTでコードを書き出してみて、「プロンプト」の重要性を学びました。
「コード」と聞くと勝手に理系のイメージを抱いていましたが、ChatGPTに的確に指示するためには文章力が必要なんですね。

まるで後輩に業務を伝えるときと同じように、
「本当はこうしてほしかったのに何でこうやって動くんだろう…伝え方が悪かったかな」
と思うことが何度もあり、実務にも生きる訓練になりました！

これからもChatGPTを相棒に、効率よく課題解決策を生み出していきたいと思います！