【GAS】Googleスプレッドシート上のリストにメールを自動送信する方法

こんにちは、業務自動化ツール開発担当の吉池（@valmore_myoshi）です。

今回はGoogle Apps Script（GAS）を使って、Googleスプレッドシートで集計したリストへメール送信する方法を解説します。Googleフォームとスプレッドシートを連携させてメールアドレスを収集したけど、１件ずつメール送信するのが面倒といったときに役立ちます！

例えばフォームで作ったアンケートの回答をスプレッドシートに集計し、回答者にメルマガを送るといった使い方もできます。あるいはセミナー申込フォームを作って、開催が近づいたらリマインドメールを送るといったこともできちゃいます！

たとえ話のようにスプレッドシートのリストにメール送信することはさまざまな場所で流用できるので覚えておいて損はありません。ぜひ本記事で習得してください！

完成イメージ

完成形はこちらの動画をご覧ください。スプレッドシートにメール送信用の独自メニューを設け、ワンクリックでリストにメールを送っています。

最終的なコードは下記のようになります。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'リストにメール送信', functionName: 'sendMail'}
  ];
  spreadsheet.addMenu('メール送信', menuItems);
}

function sendMail() {
  // Googleドキュメントからメールの件名と本文を取得
  var doc = DocumentApp.openById('ドキュメントのID');
  var subject = doc.getName();
  var text = doc.getBody().getText();
  
  // Googleスプレッドシートからデータ取得
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();
  var rangeValues = range.getValues();
  
  for(var i = 1; i < rangeValues.length; i++) {
    var company = rangeValues[i][1];
    var name = rangeValues[i][2];
    var email = rangeValues[i][3];
    
    var replacedText = text.replace('{会社名}', company).replace('{氏名}', name);
    
    MailApp.sendEmail(email, subject, replacedText);
  }
  
  Browser.msgBox('メール送信が完了しました');
}

事前準備

利用イメージは、フォームの回答をスプレッドシートに集計し、集めたリストあてにメール送信するといった感じです。

ただし、今回はスプレッドシートのリストへメール送信することにフォーカスしているため、フォームの作成手順については解説しませんし、あえて作る必要もありません。本記事と環境を合わせたい方は作ってください。

今回作成したフォームはこちら。会社名と氏名、メールアドレスのみのシンプルなフォームです。

そして、フォームと連携させたスプレッドシートがこちら。フォームの項目にはないタイムスタンプがデフォルトで設定されています。

フォームとスプレッドシートを連携するにはフォームの回答タブから「スプレッドシートの作成」ボタンをクリックするだけです。

メールの件名と本文の作成にはGoogleドキュメントを使います。件名はドキュメントのタイトル、本文はドキュメントに書いています。

独自メニューの作り方

まずはスプレッドシートにメール送信用の独自メニューを設置しましょう。独自メニューもGASのスクリプトを書くことで作れます。

早速独自メニューを作っていきたいのですが、その前にイベントという概念を理解する必要があります。

イベントとは、特定の操作を意味します。ただの操作ではありません。イベントをもとにGASの関数を実行させることができる特別な操作です。

例えばスプレッドシートを開くこともイベントです。スプレッドシートを開くというイベントを起こすことで、GASの関数を自動的に実行させることができるのです。

今回はスプレッドシートを開いたタイミングで独自メニューの作成関数を実行します。

それでは、独自メニューを作るスクリプトを作っていきましょう。スプレッドシートのメニューから、ツール＞スクリプトエディタをクリックしてGASを記述するエディタを開いてください。

スプレッドシートを開いたときに自動実行する関数にはonOpenという名前をつけるルールがあります。そのため、デフォルトのmyFunctionという関数名をonOpenに置き換えています。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'リストにメール送信', functionName: 'sendMail'}
  ];
  spreadsheet.addMenu('メール送信', menuItems);
}

onOpen関数では、スプレッドシートの取得とメニュー追加を行っています。変数menuItemsには配列形式でメニュー名と実行する関数名のオブジェクトを格納します。今回は「リストにメール送信」というメニューを追加し、sendMail関数を実行するよう指定しました。

これでスプレッドシートを開いたタイミングでonOpen関数が実行され、独自メニューが追加されます。プロジェクトを保存してスプレッドシートをリロードしてみてください。

メール送信スクリプトの書き方

onOpen関数とは別にメール送信用の関数、sendMail関数を作りましょう。

function sendMail() {
  // メール送信の処理
}

以降のコードはsendMail関数内に書いていきます。

メールの件名と本文をドキュメントから取得する

まずはメール件名と本文を入力したドキュメントを特定しましょう。個々のドキュメントには一意のIDが振られており、アドレスバーから読み取れます。

ドキュメントのIDがわかったので、openByIdに指定して該当するドキュメントを取得し、件名と本文を取得します。

// Googleドキュメントからメールの件名と本文を取得
var doc = DocumentApp.openById('ドキュメントID');
var subject = doc.getName();
var text = doc.getBody().getText();

リストから会社名、氏名、メールアドレスを取得する

次はスプレッドシートから会社名、氏名、メールアドレスのリストを取得します。このスクリプトはすでにスプレッドシートに紐づいているため、先ほどのドキュメントのように改めてスプレッドシートを特定する必要はありません。

// Googleスプレッドシートからデータ取得
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getDataRange();
var rangeValues = range.getValues();

getActiveメソッドでアクティブなスプレッドシートを取得できます。また、シート上のリストはgetDataRangeで取得しています。getDataRangeメソッドはシート上のデータが存在する範囲をすべて取得してくれます。今回のケースだと下記のA1からD3の範囲を取得しています。

メール本文の会社名と氏名を置き換える

残りの作業はメール本文の置き換えとメール送信の繰り返しなのでforループのなかに処理を書いていきます。

for(var i = 1; i < rangeValues.length; i++) {
  // メール本文の置き換えとメール送信の処理
}

変数rangeValuesには二次元配列の形で会社名、氏名、メールアドレスが格納されているので、それぞれ変数に代入します。

var company = rangeValues[i][1];
var name = rangeValues[i][2];
var email = rangeValues[i][3];

あらかじめ取得しておいたメール本文の会社名と氏名を置き換えます。replaceメソッドで置き換えたい文字列と新しい文字列を指定することで本文を書き換えています。

var replacedText = text.replace('{会社名}', company).replace('{氏名}', name);

宛先、件名、本文を指定してメール送信する

宛先と件名、会社名と氏名を置き換えた本文が用意できたので、sendEmailメソッドに指定します。これでメール送信の処理は完了です。

MailApp.sendEmail(email, subject, replacedText);

最後にメール送信の完了を知らせるポップアップを表示するようにしましょう。msgBoxメソッドに表示させたい文字列を指定します。

Browser.msgBox('メール送信が完了しました');

それでは、実際にGASで作ったメール送信スクリプトを実行してみましょう。エディタの実行ボタンをクリックすると、初回は認証画面が表示されるのですべて許可してください。実行が完了するとポップアップが表示されます。

メールも下記のように届いていることを確認してください。

まとめ

スプレッドシートのリストにメール送信する方法を解説しました。フォームで受付したセミナー参加者にリマインドメールを送ったり、アンケート回答者にお礼のメールを送るなど、さまざまな使い道が考えられます。

メール送信のロジックも簡単だったと思います。GASを使いこなせるようになればGoogleサービスを利用した業務を自動化できるので、ぜひお試しください。