【2024年版】スプレッドシートからメール送信！？　GASを使った自動返信メールの作り方！

みなさんこんにちは。ノベルティのエンジニア橋本です。

突然ですが、スプレッドシートって便利ですよね。多くの方に利用されている表管理ツールだと思います。

行列に分かれた情報管理ができるので、予約状況を手動管理したり、ちょっとしたデータベースのような使い方をしている人もいらっしゃると思います。

顧客情報などをCSVで出力したい場面でも、システムから直接スプレッドシートに自動連携できたりしたら便利ですよね。

今回はスプレッドシートを更新したらメールを送信する方法をご紹介します（更新以外の方法も！）

STUDIOのフォームから連携されたことをトリガーに、任意の内容でメールを送信することができるので自動返信メールとしても機能しますよ！

GASとは

まずは簡単にGASのご説明から。

GAS（Google Apps Script）は、Googleの提供する開発ツールです。

JavaScriptをベースに実装を行うことができ、ローコードでさまざまな処理を書くことができます。

構築環境の設定など難しいことは不要なので、何か事務的な処理を少ない工数で自動化したり、Googleのアプリケーション同士を連携して動作させることが可能です。(もちろん外部でも)

今回もGoogleのアプリケーションであるスプレッドシートの内容をもとに処理を行うので、通常の開発と比較すると短いコードかつ設定も少なくメールの送信を行います！

GASのセットアップ

設定が少なくて済む...とはいえ、基本的な仕組みなどは知っておいた方が良いです。

GASの目的は何かしらの処理を行うアプリケーションを作成することですが、二つの作成方法があります。

一つ目は既存のGoogleアプリケーションと連携させる方法で、今回の実装例にあたります。これをコンテナバインド型と呼んでいます。

二つ目はGoogleのアプリケーションに依存することなく動かすタイプ。これをスタンドアロン型と呼びます。

例えば毎日10時〜11時の間にチャットに何かメッセージを飛ばしたいと言った処理は、後者のスタンドアロン型で実装します。

今回はコンテナバインド型なので、まずはフォームからの送信情報や予約情報などを保存したいスプレッドシートを開きます。（STUDIOなどの外部システムを利用する場合、スプレッドシートの指定があるはずなのでそのシートを利用しましょう）

さっそく上部メニューにある「拡張機能」を選択し、Apps Scriptをクリックします。これでコンテナバインド型におけるGASのプロジェクト作成は完了します。

コードを実装

いざ、コード実装です！

コードそのものについては今回は触れませんが、JavaScriptベースの実装になります。

ベースこそJavaScriptですが、Googleアプリケーションと連携するさまざまな便利関数が用意されています。それらを駆使して実装しましょう。

今回の実装はこちらです。

function sendEmailOnNewRow(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('メール管理'); // シート名を指定
  var templateSheet = spreadsheet.getSheetByName('返信メール本文'); // 本文テンプレートのシート名を指定
  var emailBodyTemplate = templateSheet.getRange('A2').getValue(); // A2セルからメール本文を取得
  var range = sheet.getDataRange();
  var values = range.getValues();
  var lastRow = values[values.length - 1]
  var headers = values[0];

  // 列インデックスの取得
  var emailColumnIndex = headers.indexOf('Email'); // 列名「Email」のインデックス;

  if (emailColumnIndex != -1) {
      var emailAddress = lastRow[emailColumnIndex];

      if (emailAddress && isValidEmail(emailAddress)) {
        var emailBody = emailBodyTemplate
        for (var j = 0; j < headers.length; j++) {
          emailBody = emailBody.replace('[' + headers[j] + ']', lastRow[j]);
        }

        MailApp.sendEmail({
          to: emailAddress,
          subject: 'お問い合わせありがとうございます。',
          body: emailBody
        });

      }
  }
}

function isValidEmail(emailAddress) {
  var pattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
  return pattern.test(emailAddress);
}

コードには触れませんが、仕様だけ解説します。

コメントアウト(//の部分)で注釈を加えている部分が状況に応じて変更するところです。

注釈を読んでいただいて、適宜変更ください。（シートの情報などについては、後ほど解説します）

今回はsendEmailOnNewRow()という関数がメールを送信するための処理です。

キャプチャのように、関数名を選択して「実行」ボタンをクリックすることでメール送信を実行できます。※コードをコピペした状態だと実行の部分がグレーアウトしていると思うので、⌘(CTRL)+Sでプロジェクトを保存します。

ただ、現状のままではシートの設定ができていないのでどこから値を取得してきていいか分からずエラーが起こります。

それでもまずは実行しましょう。目的は権限周りの設定です。

「権限を確認」をクリックすると、GASを実行して良いか聞かれます。内容をよく読んで、特に問題がなければ承認(許可)します。

ここから、エラーを解消するためにシートの設定をしていきましょう！

シートの設定

スプレッドシートとGASを連携させるとき、先ほどエラーの原因になったシートの設定が非常に重要です。

この設定内容は特に難しいものではなく、シートの名称やヘッダー行の名称などです。

まず、情報が貯まるシートを作成します。名称は「メール管理」とします。これは任意で問題ありません。コードと合わせてください。

キャプチャ例のように、行列が一定の規則性を持たせましょう。STUDIOのような外部システムを利用していれば、特にこちらから内容に対して手を加えることはありません。シートも作成しなくてOKです。

Email列が送信するメールアドレスを取得する列になります。「メールアドレス」のようにしたい場合、コードとあわせて適宜変更しましょう。

次に、自動返信メールの内容を記載するシートを「返信メール本文」とします。これも任意です。

返信メール本文のシートは、A2セルに自動返信メールの内容を記載します。このA2セルについても任意です。コードのA2のところを任意の場所で指定しましょう。

ここで自動返信メールの内容についてですが、[Message]のような書き方がされている場所があります。これはヘッダー列の名前を入れてください。対応する値が置換されるようにコーディングしています。この処理を使ってメールの送信内容を制御します。

そのほかはお返事したい内容を記載します。

ここまででシートまわりの設定は完了です！

トリガーの設定

あとはこのコードをどうやって動かすか、です。この動きをGAS上ではトリガーと呼びます。

さまざまなパターンがあるので、みていきます。

まずは、STUDIOなどの外部アプリケーションからの行追加パターンです。

フォームが送信されたことでこのスプレッドシートにデータが貯まる流れです。

このデータが貯まる、つまり書き込みされるタイミングを引き金にしてメール送信コードを動かします。

実際のトリガーの設定は、下記キャプチャのようにサイドメニュー＞トリガーから設定することが可能です。

トリガーの画面は下記の通りです。「トリガーを追加」ボタンから設定します。

まず下記キャプチャの実行する関数を選択します。今回の場合は「sendEmailOnNewRow」です。

次にイベントのソースは「スプレッドシート」に、イベントの種類は「変更時」にします。

これで問題なくデータの書き込み時にメールが送信される仕組みが完成します。

「保存」ボタンをクリックした段階でシートの内容を修正したらEmail列最下部の値へメールが飛ぶようになります。ご注意ください。

トリガー設定の注意点

ただ、もしSTUDIOから自動返信メールを送りたいケース、もしくは変更は検知したくないケースは下記の処理を追加してください。

今回のような要件で利用したいトリガーの種類は、上記の「変更」と「編集」という二種類があります。

それぞれのトリガーについて見ていきましょう。

変更

まず変更は文字通りセルの値を編集したり、行の追加をしたり、削除したり、とにかく動きがあれば反応するトリガーです。

変更トリガーはなかなか神経を使います。

ちょっと返信メールの文言を直したかった、値の書き換えをしたかった、そんな時にも全て関数が動きます。すると最終行のメールアドレスに対して機械的にメールが送られてしまうので、予期せぬ問題に発展してしまうかもしれません。

トリガーを削除すれば問題ないのですが、再設定を忘れてしまうと今後は起動しなくなります。

編集

次の「編集」は、セルの値を編集することでのみ反応します。

さらに2024年現在では、編集は利用者が直接シートへアクセスする必要があります。つまりアプリケーション経由の編集は無効です。

私は上記のように、変更トリガーしか扱えないという条件下で、かなり悩みました。

変更トリガーに対する改善

特に2024年現在、私がテスト用に設定したSTUDIOからの書き込みがヘッダー行も毎回最新のものに更新されるため、2回自動返信メールが送られてしまう展開もありました。

その対応策として、changeTypeの登場です。

このchangeTypeは、変更をトリガーにしたときに動くGASの機能です。

変更の内容を具体的に検知して、編集したのか行追加したのか、削除したのかなどの情報が処理の中で検査できるので、これを利用して「行追加のみ」に反応するようにしました。

行追加のみに限定して動かすのであれば、functionの直下、つまり本コードの2行目に下記のコードを追加します。

  if(e.changeType && e.changeType != "INSERT_ROW"){
    return
  }

これで編集対策もバッチリです！

ちょっとした修正や注意書きも気にせず行うことができます。

ただ、このコードはあくまでもトリガーで関数を動かしてからその内容を検知して処理を止めているものです。

トリガーにも実行制限があり(無料版なら90分/日)、基本的に何度も修正を加えるような大きな変更については、トリガーをきちんと切った方が良いです。

手動で実行

もう一つ、トリガーのパターンについて紹介します。GASの提供するトリガーを使わず、手動で実行するパターンです。

上記は外部システムからの連携を前提にしていましたが、手動でメールを送信したいケースもあると思います。

そのようなケースに対しても今回のコードをベースに開発できるので、あわせてご紹介します！

フォームの貯め先などではなく、スプレッドシートに予約状況を書き込み、簡易的なメール送信システムとして利用したい場合などですね。

この場合、これまでのコードを下記のようにコードに差し替えることで、特定行のみ送信対象とすることが可能です。

function sendEmailOnNewRow() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('メール管理'); // シート名を指定
  var templateSheet = spreadsheet.getSheetByName('返信メール本文'); // 本文テンプレートのシート名を指定
  var emailBodyTemplate = templateSheet.getRange('A2').getValue(); // A2セルからメール本文を取得
  var range = sheet.getDataRange();
  var values = range.getValues();
  var headers = values[0];

  var emailColumnIndex = headers.indexOf('Email'); // 列名「Email」のインデックス
  var sendFlgColumnIndex = headers.indexOf('sendFlg'); // 列名「sendFlg」のインデックス

  if (emailColumnIndex != -1) {
    for (var i = 1; i < values.length; i++) { // 1行目はヘッダーなのでスキップ
      var row = values[i];
      var emailAddress = row[emailColumnIndex];
      var sendFlg = row[sendFlgColumnIndex];

      if (sendFlg && emailAddress && isValidEmail(emailAddress)) {
        var emailBody = emailBodyTemplate
        for (var j = 0; j < headers.length; j++) {
          emailBody = emailBody.replace('[' + headers[j] + ']', row[j]);
        }

        MailApp.sendEmail({
          to: emailAddress,
          subject: 'お問い合わせありがとうございます。',
          body: emailBody
        });

        sheet.getRange(i + 1, sendFlgColumnIndex + 1).setValue("");

      }
    }
  }
}

function isValidEmail(emailAddress) {
  var pattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
  return pattern.test(emailAddress);
}

ここで重要なのが、送信する行の選定です。

sendFlgのような列を作り、ここに値(TRUE)を入れることで送信行の制御をしています。

送信したらTRUEが消える仕様なので、送信ごとに選んでいく仕組みですね。

このコードを実行する場合、GASを開いてテストを実行しても良いですが、スプレッドシートのメニューに追加することも可能です。

下記のコードを追加してみましょう。

function onOpen() {
  var ui = SpreadsheetApp.getUi(); // スプレッドシートのUIを取得
  // メニューを作成
  ui.createMenu('メール操作メニュー')
      .addItem('メール送信', 'sendEmailOnNewRow')
      .addToUi(); 
}

これでスプレッドシートのヘッダーメニューに「メール操作メニュー」という項目が増えたはずです。

ここで「メール送信」をクリックすると、メールが送信されます。

まとめ

以上がGASを使った自動返信にも対応した、メール送信機能の作り方でした！

このままコピペ＋必要に応じて簡単な変更を加えればもう少し柔軟な要望も叶えられると思います。

※本記事は2024年1月現在の仕様をもとに情報を発信しています。GoogleやSTDUIOなど、ツールの仕様変更などによって正常に動作しなくなる場合もございます。

本記事を書いていても思ったのですが、ローコードは簡単に実装できる分、設計が難しい部分もあります。

ユーザーの動きはどんなものが想定されるか、トリガーの解釈は最適か...などなど一つ間違えるとある日突然思った動きをしなくなったということもあると思います。

特に外部システムに依存したGAS構築だと、この辺りは顕著ですね。

ウェブ制作とあわせて簡単な業務改善を行いたいといったケースも、ノベルティではしっかりとしたヒアリングで実現いたします！

ぜひ一度お問い合わせくださいませ。

それではまた！