Google Apps Scriptを使ってメールの自動送付を設定してみた

些細なこと、面倒なこと、忘れることは自動化だ！

 はじめに

毎月のタフス会の後に開催される社内業務委員会では、各チームが順番に司会を担当しています。5か月に一度、チームに当番が回ってくるのですが、毎度、当番を忘れそうになります。都合により順番が変わることもあるので、毎度、確認するのも覚えておくのも面倒です。そこで、Google App Scriptsを利用して毎月１日に司会当番のお知らせをメールを自動送付するスクリプトを作成しました。

司会がやること

司会の当番が開催前にやることは、以下の２つです。

• アジェンダ表の作成
• 開催の周知と事前起案の依頼

担当の当番表とアジェンダ表は、異なるスプレッドシートを使用しています。

開催前の周知は、Google Chatスペースに投稿していました。

いざ、自動化

Google App Scriptsを利用して自動化してみました。
スクリプトの動作は以下の通りです。

• 日付の特定
• スクリプト実行時の日付から年・月を抽出
• 司会当番の特定
• 司会当番表のスプレッドシートで該当行を特定
• 特定した行から担当名を取得
• 担当名が”開催なし”になっている場合は、処理を中断
• アジェンダ用スプレッドシートの作成
• アジェンダ用スプレッドシートのテンプレートシートをコピー
• コピーしたスプレッドシートのシート名を変更
• コピーしたスプレッドシートを一番左から２番目に移動
• メール送信
• Google Docsのメールひな形を置換してメールを送信
• 自動実行の設定
• 毎月１日に動作するようにトリガーをセット

使用するスプレッドシート

以下２つのスプレッドシートを使用します。

• 毎月の司会当番が記載されている担当が記載されているスプレッドシート
• 毎月の社内業務委員会のアジェンダ＆議事メモのスプレッドシート

担当を特定するのに使うスプレッドシート

Google Apps ScriptsでA列の日付をからB列の担当名を特定します。

毎月のアジェンダで使用するスプレッドシート

シート名”Template”をコピーしてシート名をMM_YYYYへ名前変更、そのあとシートを左から２番目に移動します。

メールで利用したGoogle Docs

以下のファイルを用意しておき、変数で置換してメール送信しています。

ファイル名：{開催月} 月度 社内業務委員会開催のお知らせ

ファイル本文：

{開催月} 月度 社内業務委員会の開催について

お知らせいたします。

今月の司会は、{担当} です。

共有や相談事項がある場合は、事前にアジェンダに

ご記載いただきますようお願い申し上げます。

社内業務委員会アジェンダ＆議事メモ

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx2

社内業務委員会ToDoリスト

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx1

よろしくお願いいたします。

実際のコード

実際のコードはこんな感じです。

function mail_auto() {

    // スクリプト実行時のタフス会開催月を特定するための日付を指定

　　// 現在の日付を取得

    var currentDate = new Date();

    // 月の部分を抽出

    // 月は0から始まるため、+1を追加

    var currentMonth = currentDate.getMonth() + 1;

    // 年は1900を足す

    var currentYear = currentDate.getYear() + 1900 ;

    // ログ表示して確認（YYYY_MM）

    Logger.log(currentYear+"_"+currentMonth);

　　// 現在の日付を利用して"YYYY/MM"の文字列を作成

    var targetDate = Utilities.formatDate(currentDate, "JST", "yyyy/MM");

    // 作成した日付をログ出力して"YYYY/MM"の表示を確認

    Logger.log('ターゲットの日付は: ' + targetDate);

   

    ////////////////////////////////////////////////////////////////

    //２．司会当番の特定

    /////////////////////////////////////////////////////////////////

   

    //社内業務委員会ToDoリスト.xlsxより開催月の担当を特定

　　//該当スプレッドシートのURLとシート名を指定

　 duty_url = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx1/edit'

    duty_list = '司会のローテ'

    //GASで社内業務委員会ToDoリスト.xlsxを操作

    //GASでGoogle Workspaceの操作対象を指定する方法はいくつかありますが、今回はURLを使用しています。

  　var duty_sh = SpreadsheetApp.openByUrl(duty_url).getSheetByName(duty_list).activate();

    //検索する文字列を指定

    //スプレッドシート”duty_sh”は、検索しやすいようにセルの設定を”書式なしテキスト”に変更済み

    //視認性向上のため、以下の検索対象を指定

    var targetString = targetDate;

    Logger.log('検索文字列は: ' + targetString);

    // 見つからなかった場合のデフォルト値  

    var targetRowNumber = -1;

    // スプレッドシート”duty_sh”A列のデータを取得

    var columnA_Data = duty_sh.getRange("A:A").getValues();

    // A列のデータから文字列”targetString”を検索し、一致する行の行番号を取得

      for (var i = 0; i < columnA_Data.length; i++) {

        if (columnA_Data[i][0] === targetString) {

          // 行番号は1から始まるため+1する

          targetRowNumber = i + 1;

          // 検索対象の文字列が複数存在しない前提のため、一致する行が見つかったらループを終了

          break;

        }

      }

      // 行番号の検索結果をログに出力

      Logger.log("今月の担当の行番号は: " + targetRowNumber);

      // ”targetRowNumber”の行番号からB列の文字列（今月の担当名）を取得

      var duty_this_month = duty_sh.getRange(targetRowNumber, 2).getValue();

      // 取得した値をログに出力

      Logger.log('今月の司会担当は: ' + duty_this_month);

    //duty_this_monthの結果が”開催なし”の場合、以降の処理をすべて中止する

    if (duty_this_month === "開催なし") {

      console.log("開催なしのため処理を中止します。");

      // 以降の処理を中止

      return;}

   

    /////////////////////////////////////////////////////////////////

    //”社内業務委員会アジェンダ＆議事メモ”のシート作成

    /////////////////////////////////////////////////////////////////

   

    //スプレッドシートの操作対象を指定

    //スプレッドシート”社内業務委員会アジェンダ＆議事メモ”のURLを指定

    //本番用スプレッドシート

    var sh_url = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx2/edit';

    //テスト用スプレッドシート

    //var sh_url = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx3/edit';

    //スプレッドシートの特定はURLを利用する

    var sh = SpreadsheetApp.openByUrl(sh_url);

    //スプレッドシート”社内業務委員会アジェンダ＆議事メモ”のコピー基になるシート名”Template”を指定

    var template = "Template";

    //スプレッドシートの操作対象

    var seed_sh = SpreadsheetApp.openByUrl(sh_url).getSheetByName(template);

    //シートをコピー

    var current_month_sh = seed_sh.copyTo(sh);

    //コピーしたシート名を変更

    current_month_sh.setName(currentMonth+"_"+currentYear);

    var current_month_sh_name = currentMonth+"_"+currentYear

    // 左に移動させるため、コピーしたシートをアクティブシートにする

    var move_sh = sh.getSheetByName(current_month_sh_name);

    sh.setActiveSheet(move_sh);

    // アクティブシートを左から2番目に移動

    sh.moveActiveSheet(2);

   

    /////////////////////////////////////////////////////////////////

    //メール送付

    /////////////////////////////////////////////////////////////////

   

    //Google Docsにテンプレートファイルを用意してEメール送付で利用する

    //使用するGoogel DocsをIDで指定

    var maildoc = DocumentApp.openById('xxxxxxxxxxxx4');

    //メールの件名は、Google Docs ファイル名を使用

    var subject = maildoc.getName();

    //メール本文のひな形を取得

    var mail_text = maildoc.getBody().getText();

    // メール送信先（To）を追加

    // テスト用メール送付先

    //var maii_to = 'hogehoge@tafs.co.jp';

    // 本番用メール送付先

    var maii_to = '周知だよ@tafs.co.jp';

    // メールの件名を変数で開催月に書き換え

    var replacedsubject = subject.replace('{開催月}', currentMonth);

    //メールの本文を変数で書き換え

    var replacedtext = mail_text

          .replace('{開催月}', currentMonth)

          .replace('{担当}', duty_this_month);

    //メール送信

    MailApp.sendEmail(maii_to, replacedsubject, replacedtext);

    Logger.log('メールを送信しました ' + currentDate );

    }

どのように作成したか？

過去に自分で作成したコードやインターネット上に公開されているブログを参考にしたり、Chat GPTを利用しました。

作成までの所用時間は？

落ち着いてゆっくり取り組める土曜日、日曜日に行いました。

両日合わせておそらく７時間くらいで完成したと思います。

※私は日常業務でコードは書きません。

作成時に躓いたところ

開催月の担当を特定する動作が一番時間がかかりました。

最初、担当を特定するスプレッドシートA列の日付はYYYY/MM/01になっていました。Google Apps Scriptsで日付の変数からスプレッドシートの指定範囲を検索する方法を試みましたが、うまく動作しませんでした。そのため、スプレッドシートのA列の日付の文字列をYYYY/MM、セルの書式設定を”書式なしテキスト”に変更、スプレッドシートの検索方法を日付が記載されたA列からYYYY/MMを該当行を特定して、該当行から担当名が記載されたB列のセルを特定する方法へ変更したところ、無事動作しました。

今後、スプレッドシートで該当行の文字列を利用する場合、行番号特定して、その行から列のデータを取得すると簡単だったのでまた機会があればこの方法を利用したいと思います。

メール送信やシートのコピー、移動については、過去の経験や公開されているブログが多数あるので、さほど時間はかかりませんでした。

さいごに

今回のような内容は、比較的簡単に自動化できます。

自動化してしまえば、それに対処する作業がなくなり、”やらなきゃ” と気にすることもなくなるので運用が少し楽になります。自動化できるものは自動化して手離れできると些細なストレスからも解放されるかな、、と思います。毎月、「今月の当番誰だっけ」と考えなくて済みますので、”すっきり”しました。

それでは。よいお年を～。