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

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

前回の記事：[ピラティスのレッスンを無断欠席しないために…！リマインド用のLINE Botを作ってみた](https://qiita.com/126485/items/9ef4c678c6015452c885)

# 勤務形態とオフィス環境
早速ですが、私の会社の勤務形態とオフィス環境はこのような状況です:arrow_down:
* 出勤日が自由（平日に休んだり土日に出勤したりしてOK！）
* テレワークも可能
* 固定席ではなくフリーアドレス 

**ワークライフバランス**を保てる勤務形態も、色々な部署の人と**コミュニケーション**が取れるオフィス環境も、個人的には**大満足**です！
![zaitaku_telework_woman.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/13a9ee7d-7328-66f5-d3bc-b81275e18e7e.png)


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

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

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

:arrow_down:Aさんがいない場合

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

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

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

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

![phone_kotei_denwa.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/ed220584-33be-ef2a-7d0f-282e8f0e0e9c.png)

# 電話の取り次ぎを簡単にするシステムを作ってみた
そこで、2つのシステムを作ってみました！

**:envelope:部署メンバーの「今日の出勤状況・携帯電話番号・次回の出社日」を毎朝メールで受信**
⇒相手先への回答時間を短縮するため
![0717_001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/f76170ad-6856-da30-0c73-1a195c04c7a1.png)




**:envelope:部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信**
⇒取り次ぎ時間を短縮するため
![0717_002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/a2320922-c582-134b-1539-3cac0f440186.png)





# 使用ツール
* [Google スプレッドシート](https://docs.google.com/spreadsheets/)
* [Google Apps Script](https://script.google.com/home)
* [ChatGPT](https://chatgpt.com/)
※私はコードが書けないので、コードの書き出しをChatGPTに手伝ってもらいました！

# 「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法
まずは、「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法を説明していきます！
#### 1.Google スプレッドシート（1シート目）に「部署メンバーの1ヶ月分の出勤情報と携帯電話番号」を記入する

![0717_003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/cfbb1b63-10ed-c495-4452-47aad7bb8fa8.png)




#### 2.Google スプレッドシート（2シート目）に「今日の部署メンバーの出勤状況」を送りたい宛先を記入する
![0717_004.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/02356193-ced8-1e51-65bb-1943bfea9177.png)



#### 3.Google Apps Scriptに入力するコードをChatGPTに教えてもらう
**プロンプト（ChatGPTに対する指示）**
![0717_005.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/518f1824-b777-82f1-c830-a8083d921730.png)

**ChatGPTが書きだしたコード**
```javascript: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
      });
    }
  }
}
```
:::note info
2024/7/29 修正
記事中のコードの書き方を修正しました。
今までRubyで書いてしまっていたのですが、Google Apps ScriptのコードなのでJavascriptに修正しました。
※参考にさせていただいたサイト：[JavascriptとRubyの違いについて](https://qiita.com/Hashimoto-Noriaki/items/01a1cf4a77f3dfb2d425)
::: 


#### 4.Google Apps Scriptにコードを記入し、実行する
* 該当のGoogle スプレッドシートの「拡張機能」＞「Apps Script」をクリックする。
![0717_006.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/76aa21ad-d97c-01b0-22b9-0b7bacde0f15.png)

* 「＜＞（エディタ）」＞「コード.gs」の部分にChatGPTが書きだしたコードをコピーアンドペーストする。
![0717_008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/98ef8890-e603-1d93-9b3d-be891f231fdf.png)



* 保存ボタンをクリックした後、「実行」をクリックする。
![0717_009.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/14a287a7-3242-bc31-e6c6-037e39f83c67.png)


こうすることで、欲しい情報がメールで届きました！
このメールがあれば、あちこち探しまわることなく短時間で相手先に回答できそうです！
![0717_010.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/95dc8856-43df-7583-e833-65c58d380989.png)




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

* 「:alarm_clock:（トリガー）」をクリックし、「:pencil2:（トリガーの編集）」をクリックする。
![0717_011.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/0c8176a3-53e2-1128-de68-ea549bc52ace.png)


* イベントのソースを選択：「時間主導型」を選択する
* 時間ベースのトリガーのタイプを選択：「日付ベースのタイマー」を選択する
* 時刻を選択：メールを送りたい時間帯を選択する
![0717_012.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/d1b03ae9-3b74-16bd-993f-f99a7bafd270.png)


:::note info
デフォルトでは1時間の範囲でしか設定できませんが、ぴったりの時間に定期実行を行う方法もあるようです。
※参考にさせていただいたサイト：[GASの定期実行をピッタリの時間に行う方法](https://qiita.com/kakakaori830/items/c06674252513b8d1c42e)

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


# 部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法
続いて、部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法を説明していきます！
#### 1.Google スプレッドシート（1シート目）に「メールのテンプレート」を作成する
![0717_013.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/857ae403-b72b-b859-ab7d-58e2c2b9acf3.png)


#### 2.Google Apps Scriptに入力するコードをChatGPTに教えてもらう
**プロンプト（ChatGPTに対する指示）**
![0717_014.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/ec31b99a-0cea-dc8d-81ba-4fa83551dbd7.png)

**ChatGPTが書きだしたコード**
```j: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 スプレッドシートをテンプレートと見立てて情報のみを記入することで、メールの作成時間を大幅に削減できそうです！
![0717_015.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3802235/39f2e6b4-e1c6-9e56-5ccc-0dc83f20996c.png)





# 部署のメンバーからの感想
実は今回は7日間でシステムを作ることを目標にしていたのですが、ChatGPTにコードの書き出しを手伝ってもらったことで、構想から3日でシステムが出来上がったので、浮いた4日間で部署のメンバーから感想をもらいました！
#### 1.「今日の部署メンバーの出勤状況・携帯電話番号・次回の出社日」をメールで受信する方法についての感想

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

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

#### 2.部署メンバーに対する「折り返し電話の依頼メール」を簡単に送信する方法についての感想
**:speech_balloon:「これは他の業務でも使えそうだね！」**
ポジティブな感想をもらえてとても嬉しかったです！
しかし、今回のシステムではメールの送信者がGmailのアドレスになってしまい、普段使っている会社のメールアドレスとは異なるので、部署内の業務連絡くらいにとどめるのが良さそうです。


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

:::note info
2024/7/29 追記①

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

※参考にさせていただいたサイト：
* [GoogleSpreadsheetとExcelのマクロの違いは？機能や使い方について](https://www.acrovision.jp/service/gas/?p=265)
* [いまさら聞けないIT用語：「マクロ」とは？ VBAとの違い、できること、Excelマクロの初歩など基本を解説](https://data.wingarc.com/about-macro-25915)
* [VBAとマクロの違いとは？VBA・マクロができること、マクロの作り方を徹底解説！](https://www.hitachi-solutions.co.jp/rpa/sp/column/rpa_vol26/)
:::

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


※参考にさせていただいたサイト：
* [Outlookのテンプレート作成・呼び出し方法](https://mailwise.cybozu.co.jp/column/148.html)
* [【Outlook】定型句機能を使ってテンプレートを２秒で呼び出し](https://blog.formzu.com/streamline_your_work_with_outlook_fixed_phrase)
:::

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

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

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