GASでSpread sheet上のメアドリストへ自動でメール送信したい

はじめに

「メアドリストに自動で添付ファイル付きのメール送信できないかな？」と言われたのでGAS初心者なりに頑張ってみた。 スプレッドシートのメーリングリスト上にあるメアドへ Google Documentsの内容を送る構成にした。

セットアップ

1. 必要なもの

• Google Account
• Google Spread Sheet
• Google Documents
• Google Drive(ファイルを添付するなら)

2. Spread Sheetの準備

1. 使いたいGoogle Accountで新規Spread Sheetを用意する
2. 以下の画像の通りに編集する。
   
   文字通りに書くだけ
3. 編集したシートの名前をsettingにする

ファイル名ではなくシート名を変更する
簡単にいうと上の無題のスプレッドシートを変更するのではなく、下にあるシート1を変更する。
ファイル名はご自由に。

4. 新しいシートを追加し、メールリストを以下の画像のように追加する
   
   メールと名前はもちろん本物を
5. 追加したリストに名前(ex. mail_list1)を付ける
   
   下のほうはこんな感じになってるはず

3. Apps Scriptの準備

1. メニューバーから [拡張機能]-> [Apps Script]をクリック

Bad request Error 400が出る場合
原因 : Spread Sheetのアカウントとブラウザのデフォルトアカウントが違うと発生する。
対処1： このサイトを参照してアカウントを追加する。筆者は上手くいかなかった。
対処2： プライベートブラウザを開き、使いたいアカウントのみでログインする。

2. コード.gsというファイルがひらかれている(はず)なので、そこに以下のコードを貼りつける。
   (注釈:このコードでは大量に送信する場合API制限にひっかかる場合がある。API制限を極力回避したプログラムは追記に書いた。)

autosend.js

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Auto Send").addItem("Send Emails", "sendEmails").addToUi();
}

function sendEmails() {
  const ss = SpreadsheetApp.getActive();
  const setting = ss.getSheetByName("setting");

  const listName = setting.getRange(2, 1).getValue();
  const title = setting.getRange(2, 2).getValue();
  const documentUrl = setting.getRange(2, 3).getValue();

  let file = getAttachments(setting);

  let from_email = setting.getRange(2, 7).getValue();

  if (from_email != "") {
    let aliases = GmailApp.getAliases();
    if (!aliases.includes(from_email)) {
      Logger.log("Invalid from email address:" + from_email);
      return;
    }
  } else {
    from_email = Session.getActiveUser().getEmail();
  }

  const list = ss.getSheetByName(listName);

  const body = DocumentApp.openByUrl(documentUrl).getBody().getText();

  const lastRow = list.getLastRow();

  for (let i = 2; i <= lastRow; i++) {
    const address = list.getRange(i, 1).getValue();
    const name = list.getRange(i, 2).getValue();

    try {
      sendEmail(address, name, title, body, file, from_email);
      Logger.log("Email sent to " + address);
    } catch (error) {
      Logger.log(
        "Failed to send email to " + address + " Error:" + error.message,
      );
    }
  }
}

function getAttachments(setting) {
  let file = [];
  for (let i = 4; i <= 6; i++) {
    if (setting.getRange(2, i).getValue() != "") {
      let fileId = setting
        .getRange(2, i)
        .getValue()
        .match(/[-\w]{25,}/);
      try {
        let blob = DriveApp.getFileById(fileId).getBlob();
        file.push(blob);
      } catch (error) {
        Logger.log("File not found:" + fileId + " Error:" + error.message);
      }
    }
  }
  return file;
}

function sendEmail(address, name, title, body, file, from_email) {
  let emailBody = body.replace("{{name}}", name);
  GmailApp.sendEmail(address, title, emailBody, {
    attachments: file,
    from: from_email,
  });
}

ここに

こう!

3. プロジェクトの名前を適当に変更して(ex. mail_autosend)、保存(ctrl+s)

4. 左にあるツールバー(画像参照)からトリガー(時計のマーク)を開く。
   
   青くなっているアイコン

5. 右下の「トリガーを追加」をクリック。下の画像と同じになるように設定を変更し、保存。
   
   多分最初からこうなってる

ここでアカウントサインインが求められるかもしれない。使いたいアカウントでログインすると下のような画像になるとおもう。

こうなったら下のAdvancedから Go to ~ (Unsafe)をクリックし、遷移するページでAllowを押せばよい。日本語でも同じ。

6. トリガーを保存し、追加されてるのを確認する。
   
   こんなかんじになってたらOK

7. Spread Sheetに戻り、一度再読み込みする。メニューバーにAuto Sendが追加されていれば成功
   
   こんな感じになってるはず

使い方

1. メール本文を同じアカウントで開いたGoogle Documentsで書く。

文章中に{{name}}と書いた場合、メールを送るときに自動的にメーリングリスト上の名前に変更される(画像参照)

こんなかんじに{{name}}を使用できる

2. 書きおわったらドキュメントのURLをスプレッドシート上の本文URLの下に貼る。
   

次のリンクと置き換えますという案内がでるかもしれないが、置き換えない

3. メールを送りたい人達のリストを書いたシートを選び(ex. mail_list1)、使用するリストの下に書く

4. メールのタイトルをタイトルの下に書く。

もし画像/動画/PDFをアップロードしたいなら

1. これまでと同じアカウントのGoogle Driveにそのファイルをアップロードする
2. ドライブからそのファイルを選び、︙をクリックし、[共有] -> [リンクをコピー]を選択
   
3. スプレッドシートに戻り、ファイル1URLの下にペーストする。

ここまでの作業をするとこんなかんじになるはず

もしGoogle Accountとは別のメアドから送信したい場合

1. 送りたいアドレスの下にそのアドレスを書く

2. メールアドレスを自分のエイリアスに追加する
   追加方法はここを参照するとよいかも(実験してないのでわかりません)

5. メニューバーの[Auto Send] -> [Send Emails]でメールが送られる

使う前にちゃんと思い通りに動くかを確認する
筆者はこのプログラムによって生じた一切の問題について責任を負いかねます。

以上!

コードの中身

追記予定
といっても簡単なのであまり書くこともない......

最後に

ここにコードのgithubを貼っておく。なにかご不満があればここまで。

追記

どうも一日に送れるメールの数が限られているらしい。他のブログを参照してAPI制限を回避できるらしいコードを書いてみた(動作未保証)

autosend_draft.js

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Auto Send").addItem("Send Emails", "sendEmails").addToUi();
}

function sendEmails() {
  const ss = SpreadsheetApp.getActive();
  const setting = ss.getSheetByName("setting");

  const listName = setting.getRange(2, 1).getValue();
  const title = setting.getRange(2, 2).getValue();
  const documentUrl = setting.getRange(2, 3).getValue();

  let file = getAttachments(setting);

  let from_email = setting.getRange(2, 7).getValue();

  let aliases = GmailApp.getAliases();
  if (!aliases.includes(from_email)) {
    Logger.log("Invalid from email address:" + from_email);
    return;
  }

  const list = ss.getSheetByName(listName);

  const body = DocumentApp.openByUrl(documentUrl).getBody().getText();

  const lastRow = list.getLastRow();

  for (let i = 2; i <= lastRow; i++) {
    const address = list.getRange(i, 1).getValue();
    const name = list.getRange(i, 2).getValue();

    try {
      sendEmail(address, name, title, body, file, from_email);
      Logger.log("Email sent to " + address);
      Utilities.sleep(1000);
    } catch (error) {
      Logger.log(
        "Failed to send email to " + address + " Error:" + error.message,
      );
    }
  }
}

function getAttachments(setting) {
  let file = [];
  for (let i = 4; i <= 6; i++) {
    if (setting.getRange(2, i).getValue() != "") {
      let fileId = setting
        .getRange(2, i)
        .getValue()
        .match(/[-\w]{25,}/);
      try {
        let blob = DriveApp.getFileById(fileId).getBlob();
        file.push(blob);
      } catch (error) {
        Logger.log("File not found:" + fileId + " Error:" + error.message);
      }
    }
  }
  return file;
}

function sendEmail(address, name, title, body, file, from_email) {
  const draft = makeDraft(address, name, title, body, file, from_email);
  const draftId = draft.getId();
  GmailApp.getDraft(draftId).send();
}

function makeDraft(address, name, title, body, file, from_email) {
  const emailBody = body.replace("{{name}}", name);
  const draft = GmailApp.createDraft(address, title, emailBody, {
    attachments: file,
    from: from_email,
  });
  return draft;
}

1. API制限を回避するために下書きを一度経由する(!)
2. 一秒間に一回送るように制限する

の2つをした。 一回あたり90件ぐらい送るとApps Scriptの実行時間制限がくるのでその都度リストを更新するのがちょっと大変(送信ログはApps Scriptの[実行数]から一番上のログの▽を押すと見られる。送信ログは最新ではないので、自分のメールからちゃんと送信できてるか確認したほうがよさそう)

トラブルシューティング

送信履歴をみるとエラーを吐いているメアドがある！

• gmailが対応している暗号化方式以外の方法でのみメールを受け取るドメインもあるようです。この場合自動送信が使えないので手動送信となります。

参考

https://daily-coding.com/mail-with-files/
https://jp.tdsynnex.com/blog/google/options-sending-email-by-gas/
https://zenn.dev/tatsuya_okzk/articles/259203cc416328