スプレッドシートで完結！メアド取得からアンケート送信までGASで全部自動化する

今まで以下のスプレッドシートとGoogle App Script（以降GAS）でメールアドレス自動取得、Bcc一括送信などご紹介しましたが、この記事ではGASを使ってメールアドレス自動取得からBcc一括送信を毎日行うGASをご紹介します。

作業順の洗い出し確認

アンケート自動送信化を行うにあたり必要な作業を順番に確認します。

1.件名と送信者でフィルタリングした送信先メールアドレスの取得
2.取得したメールアドレスをスプレッドシートに記載する
3.件名、本文、送信時間を記載する
4.送信する

おおまかに以上の作業が必要になります。

送信先メールアドレスの取得〜件名、本文、送信時間を記載する

以下の記事の応用スクリプトです。

スクリプトを設定する

スプレッドシート>ツール>スクリプトエディタで以下をコピペします。xxxxについては後述します。

function searchEmail(sheet_name, title_s, date_s, from_s, send_title, desc) {
 const LIMIT_MAX_SEARCH = 1000 // 1日5万件の制限あり
 var today_noon = new Date();
 today_noon.setHours(12);
 today_noon.setMinutes(0);
 today_noon.setSeconds(0);
 const title_s2 = "subject:(" + title_s + ")"
 const from_s2 = "from: " + from_s
 const search_condition = from_s2 + " " + date_s + " " + title_s2
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var n = 0
 const GMAIL_MAX_GET = 500
 const sheet = ss.getSheetByName(sheet_name);
 const messages_out = sheet.getDataRange().getValues();
 do {
   // GmailApp.search(search_condition, start_position, max=500)
   var threads = GmailApp.search(search_condition, n, GMAIL_MAX_GET)
   for (var t in threads) {
     // GAS has bug of using `const` and `let` inside for-loop. only `var` is acceptable.
     var thread = threads[t]
     var msgs = thread.getMessages()
     var i = messages_out.length
     for (var m in msgs) {
       i++;
       var msg = msgs[m]
       var to_ = msg.getTo()
       if(!messages_out.some(row => row.includes(to_))){
         messages_out.push([to_, send_title, desc, Utilities.formatDate(today_noon, 'Asia/Tokyo', "yyyy/MM/dd HH:mm:ss"), "", "", "=COUNTIF(A:A,A" + i + ")"])
     }}
   }
   n = n + GMAIL_MAX_GET
 } while (threads.length > 0 && n <= LIMIT_MAX_SEARCH)
 if (messages_out.length === 0) return;
 sheet.getRange(1, 1, messages_out.length, messages_out[0].length).setValues(messages_out);
}

function addEmail(){
 const from_s = "xxxx@xxxx"; //送信元のメールアドレス
 const title_xxxx = "xxxx" //回収したいメアドに送っている件名
 const sheet_xxxx1 = "xxxx2" //以下の内容を記載するシート名
 const send_title_xxxx = "xxxx" // 送信したい件名
 const desc_xxxx = "xxxx"  //送信したい本文 \nは改行指示
 var today = new Date();
 var startday = new Date();
 startday.setDate(startday.getDate() - 1);
 //var startday = new Date(2020, 5, 12, 0, 0, 0);
 const date_s = "after:" + Utilities.formatDate(startday, 'Asia/Tokyo', "yyyy/MM/dd") + " before:" + Utilities.formatDate(today, 'Asia/Tokyo', "yyyy/MM/dd")
 searchEmail(sheet_xxxx, title_xxxx, date_s, from_s, send_title_xxxx, desc_xxxx); //41行目〜44行目のconst @@@@_xxxxと同じ文字列にしてください。
}

40行目以降のxxxx or xxxx数字を適宜変えてください。

※searchEmailとaddEmailは適宜変えて問題ありません。この記事ではsearchEmailとaddEmailとして記載を続けます。

・条件に合致した前日のメールアドレスを取得し
・const send_title、const desc_の内容を記載
・4行目の送信希望時間を反映
・重複したメールアドレスを排除（28行目）
・排除した証跡に1を記載
します。

成功すると以下のような結果がスプレッドシートに反映します。

自動送信するスクリプトを設定する

スクリプトエディタ>ファイル>New>スクリプトファイルで新しいスクリプトファイルを作り、以下をコピペします。xxxxについては後述します。

function calledByCron() {
 var sheet = SpreadsheetApp.getActiveSheet();  // シートの取得
 const sheet_xxxx1 = "xxxx2" //
 sendEmailFromSheet(sheet_xxxx1);
}
function sendEmailFromSheet(sheet_name){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName(sheet_name);
 var startRows = 2;  // 開始行数
 var sheetData = sheet.getSheetValues(startRows, 1, sheet.getLastRow(), sheet.getLastColumn());  // シートのデータを取得（2次元配列）
 var dtLimit = new Date();  // 現在時刻を取得
 var options = {"from": "xxxx3 <xxxx@xxxx>"};
 // シートの各行ごとにデータを取り出す
 sheetData.forEach(function(value, index) {
   // 送信完了していない、かつ送信予定日時が現在時刻より前ならば、メールを送信する
   if (!value[4] && (new Date(value[3])).getTime() < dtLimit.getTime()) {
     GmailApp.sendEmail(value[0], value[1], value[2],options);  // メールを送信する
     sheet.getRange(startRows + index, 4 + 1).setValue(dtLimit);  // 送信完了日時をシートに書く
   }
 });
}

xxxx1とxxxx2は前述の42行目と同じです。
xxxx3は送信者名と送信者メールアドレスです。
CalledByCronは適宜変えて問題ありません。この記事ではCalledByCronとして記載を続けます。

トリガーを設定する

スクリプトエディタ>編集>現在のプロジェクトのトリガーを開き、トリガーを追加します。

・実行する関数 > addEmail
・デプロイ時に実行 > Head
・イベントのソースを選択 > 時間主導型
・時間ベースのトリガーのタイプを選択 > 日付ベースのタイマー
・時刻を選択 > 午前9時〜10時
毎日午前9時〜10時の間にスクリプトが走り、メアド回収〜件名等を記載します。

CalledByCronスクリプトのトリガーを設定する

前述と同様にトリガーを追加します。

以下設定します。

・実行する関数 > addEmail
・デプロイ時に実行 > Head
・イベントのソースを選択 > 時間主導型
・時間ベースのトリガーのタイプを選択 > 日付ベースのタイマー
・時刻を選択 > 午後12時〜1時
毎日午後12時〜1時の間にスクリプトが走り、メールを送信します。

スクリプトが走ったあとの結果

done

終わりに

アンケートメールの他にもイレギュラーな内容を100ユーザーに送りたいというような、緊急度が高いけどBcc使うの怖いときなどに使えます。