Zenn
📧

Googleスプレッドシートから複数のメールアドレスへ一斉送信する方法（Google Apps Script）

2022/02/27に公開
TypeScript
GAS
tech

複数の宛先にメールを一斉送信したいけど、メーラーから直接編集なんてしてたら宛先間違いそうだし面倒だな〜と思う時はないでしょうか？
もちろん社内の関係者へメールを送るくらいであればチャットツールやメーリングリストなどでも対応できますが、色々な事情でそうこう言ってられないケースもあるかと思います。

この記事では Google スプレッドシートで送信リストやメールの内容を作成し、一元管理と一斉送信ができるツールを作成します。

作成ツールの要件

今回の課題を解決するために設定した要件は以下になります。

  • 送信先情報、メールの内容がすべてスプレッドシートで管理されている
  • 不正なメールアドレス（間違った文字列入力）やメールアドレスの重複が検知され、誤送信を未然に防げる
  • CC, BCC の管理が簡単
  • メール本文で会社名、部署名、担当者名を宛先ごとに出し分けできる
  • テストメールを送信できる

https://support.google.com/a/answer/166852?hl=ja

スプレッドシートの作成

まずスプレッドシートを新規で作成します。
https://www.google.com/intl/ja_jp/sheets/about/

以下 2 つのシートを用意してください。

  • Email List
  • Email Content

シート名

Email List シートの編集

シートの 1 行目にツールの使用方法などを記載します。2 行目には以下項目名に間違いがないよう入力します。
3, 4 行目以降の内容は自由に入力してください。Company には会社名、Department には部署名、Person in charge には担当者名、Email address には送り先のメールアドレスを入力します。

No. Company Department Person in charge Email address
1 Test Inc. QA Test Name example@test.com
2 Smaple Inc. Marketing Sample Name example@test2.com

挿入 > 図形描画から送信ボタンを作成します。テキストや色、大きさなどは自由に調整いただて問題ありません。

送信ボタン

ボタンが作成できればスクリプトの割り当てを行います。「スクリプトを割り当て」という項目を選択し、mainと入力してください。スクリプトは後ほど実装します。

スクリプト割り当て

最終的に以下のような画面になれば OK です。

Email List シート

Email Content シートの編集

以下のような表を Email Content シートに作成します。1 列目にはメールで送信するコンテンツの項目名、2 列目にはメールのコンテンツを登録します。1 行目には先ほどと同じく、シートの解説などを記載します。

Item Description
Test email address example@test.com
CC example@testcc.com
BCC example@testbcc.com
Body Email content

Test email address にはテスト送信したいメールアドレスを指定します。指定できるメールアドレスは１つまでになります。CC, BCC にも指定したいメールアドレスを入力します。複数指定したい場合はコンマで繋げて指定してください。

Body にはメールの本文を入力します。宛先ごとに会社名、部署名、担当者名を出し分けたい場合は、以下変数を用意しているのでご活用ください。

Company: {COMPANY}
Department: {DEPARTMENT}
Person in charge: {PIC}

例えば{COMPANY}と記載すると送信時にはリストの会社名がそれぞれ出力されます。

Email List の編集時と同様に挿入 > 図形描画から送信ボタンを作成します。スクリプトの割り当てではtestEmailを指定してください。

Email Content シート

Google Apps Script の作成

スプレッドシートの拡張機能 > Apps Script を開きます。
ファイルにsendEmail.gsとtestEmail.gsを作成してください。

Apps Script

sendEmail.gsのファイル内には以下コードをコピペしてください。

function main(){}(()=>{"use strict";var e,t,a,r={708:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.getUserName=void 0,t.getUserName=function(){var e=Session.getActiveUser(),t=ContactsApp.getContact(e.toString());return{fullName:t.getFullName(),familyName:t.getFamilyName(),givenName:t.getGivenName()}}},598:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.sendEmailToAll=void 0;var r=a(708);t.sendEmailToAll=function(e,t,a,n){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),l=SpreadsheetApp.getUi(),o=(0,r.getUserName)(),s=o.fullName,c=o.familyName,u=o.givenName;if(i){for(var d="",p="",m="",g="",f=i.getLastRow()-1,v=0;v<f;v++){var h=i.getRange(2+v,1).getValue(),A=i.getRange(2+v,2).getValue();"CC"===h&&(m=A),"BCC"===h&&(g=A),"Subject"===h&&(d=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n)),"Body"===h&&(p=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n).replace("{MY_FULL_NAME}",s).replace("{MY_FAMILY_NAME}",c).replace("{MY_LAST_NAME}",u))}var E={cc:m,bcc:g};GmailApp.sendEmail(e,d,p,E)}else l.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},690:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.checkDuplicate=void 0,t.checkDuplicate=function(e){return e.filter((function(e,t,a){return a.indexOf(e)===t&&t!==a.lastIndexOf(e)}))}},506:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},n={};function i(e){var t=n[e];if(void 0!==t)return t.exports;var a=n[e]={exports:{}};return r[e](a,a.exports,i),a.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),a=i(690),i.g.main=function(){var r=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email List"),n=SpreadsheetApp.getUi(),i=Browser.msgBox("Confirmation","Are you sure to send email？👀",Browser.Buttons.YES_NO);if(r&&"yes"==i){for(var l=2,o=3,s=4,c=5,u=r.getLastColumn(),d=1;d<=u;d++){var p=r.getRange(2,d).getValue();"Company"===p&&(l=d),"Department"===p&&(o=d),"Person in charge"===p&&(s=d),"Email address"===p&&(c=d)}var m=r.getLastRow()-2,g=[],f=[],v=[];for(d=0;d<m;d++){var h=r.getRange(3+d,l).getValue(),A=r.getRange(3+d,o).getValue(),E=r.getRange(3+d,s).getValue(),N=r.getRange(3+d,c).getValue(),y={company:h,department:A,pic:E,address:N};(0,t.validateEmail)(N)?(g.push(y),f.push(N)):""===N||(0,t.validateEmail)(N)||v.push(N)}if(v.length>0)return n.alert("🚨 Invalid email address: ".concat(v.join(", ")));var _=(0,a.checkDuplicate)(f);if(_.length>0)return n.alert("🚨 Duplicate email address: ".concat(_.join(", ")));g.forEach((function(t){""!=t.address&&(0,e.sendEmailToAll)(t.address,t.company,t.department,t.pic)})),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else"no"==i?n.alert("Send canceled!"):n.alert("🚨 The sheet name may be incorrect. It should be Email List.")}})();

sendEmail.gs のコード

testEmail.gsのファイル内には以下コードをコピペしてください。

function testEmail(){}(()=>{"use strict";var e,t,a={598:(e,t)=>{t.__esModule=!0,t.sendEmailToAll=void 0,t.sendEmailToAll=function(e,t,a,r){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),n=SpreadsheetApp.getUi();if(i){for(var l="",o="",s="",d="",c=i.getLastRow()-1,p=0;p<c;p++){var g=i.getRange(2+p,1).getValue(),u=i.getRange(2+p,2).getValue();"CC"===g&&(s=u),"BCC"===g&&(d=u),"Subject"===g&&(l=u),"Body"===g&&(o=u.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",r))}var m={cc:s,bcc:d};GmailApp.sendEmail(e,l,o,m),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else n.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},506:(e,t)=>{t.__esModule=!0,t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},r={};function i(e){var t=r[e];if(void 0!==t)return t.exports;var n=r[e]={exports:{}};return a[e](n,n.exports,i),n.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),i.g.testEmail=function(){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),r=SpreadsheetApp.getUi();if(a){for(var i="",n=a.getLastRow()-1,l=0;l<n;l++){var o=a.getRange(2+l,1).getValue(),s=a.getRange(2+l,2).getValue();"Test email address"===o&&""!==s&&(i=s)}(0,t.validateEmail)(i)?(0,e.sendEmailToAll)(i,"Test Inc.","QA","Test Name"):r.alert("🚨 Invalid email address!")}}})();

testEmail.gs のコード

Google Apps Script の内容をカスタマイズしたい場合はテンプレートを公開しているのでご活用ください。

https://github.com/Kazuki-tam/gas-spreadsheet-mail

機能テスト

送信ボタンを押してスクリプトが正しく実行されるか確認してみましょう。初回実行時には Google アカウント毎に応じたスクリプト実行許可が求められます。

以下のような項目に問題がないか確認しましょう。

  • 各リストに応じた内容が問題なく出しわけされているか
  • 送信先に間違いがないか
  • 不正なメールアドレス（間違った文字列入力）やメールアドレスの重複が検知されるか

エラー時には以下のようなポップアップが表示されます。
メールアドレスの重複エラー

まとめ

Google Apps Script はちょっとしたツールを作成したい時にとても便利なので色んな場面で利用していきたいですね。

Discussion