1. top
2. blog
3. 2024
4. 03

Googleスプレッドシートから多数のメールを予約送信する

2024-03-24

• GoogleAppsScript
• メール

Gmailが使える環境で、特定の時間になったらいくつかのメールを送信する、というタスクがありました。 単発であったり数が少なかったりすれば予約送信機能を使えば良いのですが、毎日送信であったり宛先や文章のバリエーションが多いとそうも言っていられません。

そこで、以下のようなGoogleスプレッドシートをGoogle Apps Scriptで読み取って自動送信するスクリプトを作ってみました。 この記事では、この仕組みの設定方法を解説します。

沢山の人にメールを送りたいのであれば宛先の数だけ行を書けば良いだけですし、毎週や毎日送りたいのであれば送信したい日時の分だけ書いてあげればOKです。便利！

制約

とっても便利なGoogle Apps Scriptですが、いくつかの割り当て制限があります。 若干ややこしいのですが、重要なところを抜き出すと（おそらく）以下のようになります。

小規模に使う分には問題ないと思いますが、大きめの企業で一斉送信用に使おうとすると宛先の制限がネックになるかもしれませんね。

また、仕様上、送信日時にはだいたい1分以内の誤差が発生します。 秒単位でピッタリ送りたいという状況はあまり無さそうですが、もしそういった要件がある場合は別の方法を使ってください。

やりかた

設定は以下の手順で進めます。

1. スプレッドシートを作る
2. 権限を付与する
3. トリガーを設定する
4. スプレッドシートに予約を書く

1. スプレッドシートを作る

まずは、予約情報を書くためのスプレッドシートを作ってスクリプトを埋め込みます。

簡単に作れるようにテンプレートを用意しておきましたので、下記のURLを開いて 「コピーを作成」 をクリックしてください。
（テンプレートを使わずに自分でプロジェクトを設定したい場合は、このページの末尾にあるスクリプトをコピーしてお使いください。）

スプレッドシートのテンプレート (https://docs.google.com/spreadsheets/d/1s9JglBX3DEY-4ev9EYVX6PBicEmNgZOrzkt0HFYBwQU/copy)

コピーができたら、「To」列の一番最初（A2セル）にあなたのメールアドレスを入力してください。 このアドレスは、次の手順で送るメールの宛先になります。

2. 権限を付与する

次に、以下の手順でテスト実行をして、スプレッドシートの読み取りやメール送信の権限をスクリプトに付与します。

1. ツールバーの 「拡張機能」 から 「Apps Script」 を起動します。

   

2. Apps Scriptの画面が表示されたら、その上にある 「実行」 ボタンをクリックします。

   

3. 「承認が必要です」というダイアログが出るので、 「権限を確認」 をクリックします。

4. 「アカウントの選択」が出たら自分のアカウントを選びます。

5. 「このアプリは Google で確認れていません」という警告が出ますが、 「詳細を表示」 から 「メール予約一括送信（安全ではないページ）に移動」 をクリックして先に進んでください。

   

6. 実行に必要な権限として以下の3つが表示されますので、 「許可」 をクリックしてください。

   • Google ドライブのすべてのファイルの表示、ダウンロード: 添付ファイルを取得するために使う権限です。
   • Google スプレッドシートのすべてのスプレッドシートの参照、編集、作成、削除: スプレッドシートからメールの情報を読み取るために使う権限です。
   • ユーザー本人に代わってのメールの送信: メールの送信に使う権限です。

7. Apps Scriptの画面に戻ると、「実行ログ」という表示が出ているはずです。
   「実行完了」と表示されるまで待ってから、「メール予約一括送信のテスト」というタイトルのメールが届いていることを確認してください。

3. トリガーを設定する

権限の付与と動作テストができたら、今度はトリガー（＝定期実行のスケジュール）を設定します。

1. 画面左の目覚まし時計のようなアイコンから 「トリガー」 を開きます。

   

2. 右下に表示される 「トリガーを追加」 をクリックします。

3. 表示されたダイアログで、以下のように設定して 「保存」 をクリックします。

   項目	設定値
   実行する関数を選択	sendMails
   実行するデプロイを選択	Head
   イベントのソースを選択	時間主導型
   時間ベースのトリガーのタイプを選択	分ベースのタイマー
   時間の間隔を選択（分）	1分おき

   ※ 単発で使いたいだけの場合は「分ベースのタイマー」ではなく「特定の日時」にしても構いません。

4. トリガーの一覧に設定した内容が表示されたら設定完了です！

4. スプレッドシートに予約を書く

仕組みの準備ができたら、実際に送るメールの内容をスプレッドシートに書いてみましょう。 念のため、一度は自分のメールアドレスでテストしてみることをお勧めします。

それぞれの列は以下のような仕様になっています。

I列以降は読まないので、上記以外の情報を入れても問題ありません。 たとえばI列に相手の名前を入れておいて、E列のメール本文を =I2 & "様 お世話になっております。" のような計算式にしてみても良いかもしれませんね。

すべて上手くいっていれば、送信予定日時を過ぎるとメールが送られるはずです。

スクリプト全文

上記の手順ではテンプレートを使用しましたが、以下のスクリプトをコピー&ペーストしても同じ結果を得られます。 Apps Scriptのプロジェクトを自分で作りたい場合はこちらをお使いください。

function sendMails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getLastRow() < 2) {
    return;
  }

  const values = sheet.getSheetValues(2, 1, sheet.getLastRow() - 1, 8);

  values.forEach(([to, cc, bcc, subject, body, urls, scheduled, done], i) => {
    if (!to || !subject || !scheduled || done || scheduled > new Date()) {  // ← 特徴1つめ
      return;
    }

    const attachments = urls.trim().split(/[,\n]/g).map(x => x.trim()).filter(x => x !== '').map(x => {
      const m = x.match(/^https:\/\/(?:docs|drive)\.google\.com\/[a-z]+\/d\/([a-zA-Z0-9_-]+)\/.*$/);  // ← 特徴2つめ
      if (!m) {
        throw new Error('Googleドライブ以外のファイルを添付することはできません。');
      }
      return DriveApp.getFileById(m[1]);
    });

    MailApp.sendEmail({
      to: to.replace(/[,\n]+/g, ', '),
      cc: cc.replace(/[,\n]+/g, ', '),
      bcc: bcc.replace(/[,\n]+/g, ', '),
      subject,
      body,
      attachments,
    });
    sheet.getRange(i+2, 8).setValue(new Date());
  });
}

これだけです。やっている内容のわりに驚くほど簡単なコードですね！ Googleサービスとの連携の楽さが流石という感じです。

特徴1つめ
このスクリプトで少し特徴的なのは、特定の時間に予約送信するような機能が無いので「毎分実行して、指定時間を過ぎていたら処理する」という戦略を取っていることでしょうか。 それだけだと指定時間以降何度でも送信されてしまうので、完了日時列を作って参照することで一回しか実行されないように制御しています。

特徴2つめ
添付ファイルの処理部分も癖があるというか、若干直感的ではないかもしれません。 ここでは、スプレッドシート内で指定してもらったURLからファイルIDを抜き出す処理をしています。 https://drive.google.com/file/d/XXXXX/view というURLのうちの XXXXX の部分がこのIDに相当します。

ファイルIDさえ分かれば DriveApp.getFileById() でオブジェクトを取得して、そのまま MailApp.sendEmail() の attachments に渡して添付することができます。なんて楽なんでしょう。

FAQ

• Gmailで大量のメールを予約送信する方法は？

  GoogleスプレッドシートとGoogle Apps Scriptを組み合わせて予約送信ツールを作っておくと簡単です。 記事内のテンプレートを使えば、すぐにツールを使い始めることができます。

• Google Apps Scriptでは一日に何通までのメールを送ることができる？

  メールを何通送るかではなく、メールを送る宛先の数で制限されているようです。 無料の個人向けアカウント(@gmail.comなど)であれば一日にのべ100人まで、有料の企業向けアカウント(Google Workspace)であれば一日にのべ1,500人まで送ることができます。

• Google Apps Scriptで送るメールの宛先はいくつまで設定できる？

  50人まで設定することができます。

• Google Apps Scriptで送るメールに添付できるファイルのサイズは？

  一通あたりの合計が25MBまで添付することができます。

• Google Apps Scriptでメールを送信する方法は？

  MailAppクラスの MailApp.sendEmail() を使うと送信できます。 引数には "to", "cc", "bcc", "subject", "body", "attachments" などなどを含んだオブジェクトを渡すことができます。

• Google Apps Scriptでスプレッドシートを読み書きする方法は？

  SpreadsheetAppクラスの SpreadsheetApp.getActiveSheet() を使う方法が簡単です。

  得られたシートオブジェクトの getSheetValues() メソッドを使うと、シート内の値をまとめて取得することができます。

  また、 getRange().setValue() を使えば値をセットすることもできます。

• Google Apps ScriptでGoogleドライブにあるファイルを開く方法は？

  DriveAppクラスの DriveApp.getFileById() で開くことができます。 引数のファイルIDはファイルの共有や編集のURLから取得できます。

• Google Apps Scriptで使うGoogleドライブのファイルIDはどうすれば分かる？

  ファイルのURLが https://drive.google.com/file/d/XXXXX/view だとしたら、 XXXXX の部分がファイルIDです。

参考

• Google Apps Scriptで指定時刻にメールを送信するシステムを作ってみた - Qiita
• Google サービスの割り当て | Apps Script
• Class MailApp | Apps Script
• Class SpreadsheetApp | Apps Script
• Class DriveApp | Apps Script