【GAS】Gmailの内容をスプレッドシートに自動転記する方法

omame

2024年1月4日 20:11

この記事では、Google Apps Script（GAS）を使って、Gmailからお問い合わせフォームの内容（受領日・件名・お問い合わせ内容等）を抽出し、スプレッドシートに自動的に転記する方法を解説します。

メールの内容を手作業で一つ一つコピペして転記するのは手間が掛かりますが、GASを使って自動化することで、業務の効率を向上させることができます。

全体像

やりたいこと

今回は、会社に届いたお問い合わせフォームのメール通知をスプレッドシートに転記することを想定してGASを記述します。

Gmailから、受領日・件名・会社名・氏名・お問い合わせ概要の情報を抽出していきます。

転記後のスプレッドシートのサンプル
ピンクで塗りつぶされた見出し箇所を、GASで自動的に反映されるように設定します。

プログラムの主な流れは下記の通りです。

Gmailで特定の条件を満たすメールを検索する
対象のメールスレッドを取得し、各メールの情報を抽出する
メールの内容から必要な情報（受領日・件名・会社名・氏名・お問い合わせ概要）を抽出する
スプレッドシートに抽出した情報を転記する
メール受領日を基準にスプレッドシートを並べ替える
転記が完了したメールに「転記済み」のラベルをつける

完成プログラム

プログラムの全体像をお見せした後で、内容を一つずつ解説していきます。

function myFunction() {
  // 検索条件に該当するスレッド一覧を取得
  var threads = GmailApp.search('subject:フォーム「お問い合わせフォーム」に新しい回答があります。 -label:転記済み');
  
  // スレッドを一つずつ取り出す
  threads.forEach(function(thread) {
    // スレッド内のメール一覧を取得
    var messages = thread.getMessages();
    
    // メールを一つずつ取り出す
    messages.forEach(function(message) {

      //メール受領日の取得
      var date = message.getDate();

      //件名
      var subject = message.getSubject();

      // メール本文
      var plainBody = message.getPlainBody();

      // 会社名
      var company = plainBody.match(/会社名: (.*)/);
      
      // 氏名
      var name = plainBody.match(/氏名: (.*)/);

      //お問い合わせ概要
      var outline = plainBody.match(/お問い合わせ概要: (.*)/);
      
      // 書き込むシートを取得
      var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
  
      // 最終行を取得
      var lastRow = sheet.getLastRow() + 1;

      // セルを取得して値を転記
      sheet.getRange(lastRow, 1).setValue("=row()-1");
      sheet.getRange(lastRow, 2).setValue(date);
      sheet.getRange(lastRow, 4).setValue(subject);
      sheet.getRange(lastRow, 5).setValue(company[1]);
      sheet.getRange(lastRow, 6).setValue(name[1]);
      sheet.getRange(lastRow, 7).setValue(outline[1]);

      // 受領日の昇順に並べ替え
      var narabekae = sheet.getRange('A2:J');
      narabekae.sort({column: 2, ascending: true})
    });
    
    // スレッドに転記済みラベルを付ける
    var label = GmailApp.getUserLabelByName('転記済み');
    thread.addLabel(label);
  
  });
}

プログラムの全体的な構造については、下記の記事を参考にさせていただきました。

プログラム内容の解説

Gmailで特定の条件を満たすメールを検索する

// 検索条件に該当するスレッド一覧を取得
var threads = GmailApp.search('subject:フォーム「お問い合わせフォーム」に新しい回答があります。 -label:転記済み');

GmailApp.searchメソッドを使用して、特定の条件を満たすメールを検索しています。

今回は下記のような検索条件を指定します。
・件名が【フォーム「お問い合わせフォーム」に新しい回答があります。】であること
・かつ、「転記済み」ラベルがつけられていないこと

検索条件は、Gmailの検索窓で検索するときと同様に指定することができます。その他の検索条件については、下記の記事を参考にしてみてください。

対象のメールスレッドを取得し、各メールの情報を抽出する

// スレッドを一つずつ取り出す
threads.forEach(function(thread) {
  // スレッド内のメール一覧を取得
  var messages = thread.getMessages();
});

取得したスレッド一覧から、forEachメソッドを使用して各スレッドを取り出し、その中のメール一覧を取得します。

メールの内容から必要な情報を抽出する

// メールを一つずつ取り出す
messages.forEach(function(message) {
  
  //メール受領日の取得
  var date = message.getDate();
  
  //件名
  var subject = message.getSubject();
  
  // メール本文
  var plainBody = message.getPlainBody();
  
  // 会社名
  var company = plainBody.match(/会社名: (.*)/);
  
  // 氏名
  var name = plainBody.match(/氏名: (.*)/);
  
  //お問い合わせ概要
  var outline = plainBody.match(/お問い合わせ概要: (.*)/);
});

各メールの本文から、正規表現を使用して必要な情報を抽出します。

メール受領日は'yyyy/MM/dd HH:mm:ss'の形で返ってきます。
ここでは受領日を基準に並べ替えるために、時間まで取得し、スプレッドシートの表示形式で'yyyy/MM/dd'の形に見せています。

Utilities.formatDate()を使うことで、必要な情報だけを取得することも可能です。

//メール受領日の取得
var date = message.getDate();
    date = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd');

スプレッドシートに抽出した情報を転記する

// 書き込むシートを取得
var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');

// 最終行を取得
var lastRow = sheet.getLastRow() + 1;

// セルを取得して値を転記
sheet.getRange(lastRow, 1).setValue("=row()-1");
sheet.getRange(lastRow, 2).setValue(date);
sheet.getRange(lastRow, 4).setValue(subject);
sheet.getRange(lastRow, 5).setValue(company[1]);
sheet.getRange(lastRow, 6).setValue(name[1]);
sheet.getRange(lastRow, 7).setValue(outline[1]);

取得した情報をスプレッドシートに書き込みます。

getSheetByNameメソッドを使用して、シート名（'シート1'）で情報を書き込むシートを指定しています。シート名の変更の際には注意してください。

次にgetLastRowメソッドでシートの最終行を取得し、そこに「+ 1」を加えることで、自動的に新しい行にデータを追加することができます。

最後にgetRangeメソッドで、セルの位置（行番号, 列番号）を指定して値を書き込みます。

ここでは関数を指定することも可能です。
スプレッドシートの1列目（A列）には項番を振りたいため、見出しの1行を引いた"=row()-1"を指定しています。

メール受領日を基準にスプレッドシートを並べ替える

// 受領日の昇順に並べ替え
var narabekae = sheet.getRange('A2:J');
narabekae.sort({column: 2, ascending: true})

ここではシートの範囲を指定し、2列目（受領日）の昇順に並べ替えを行っています。
ascending: trueが昇順、falseが降順です。

転記が完了したメールに「転記済み」のラベルをつける

// スレッドに転記済みラベルを付ける
var label = GmailApp.getUserLabelByName('転記済み');
thread.addLabel(label);

転記が完了したメールには、Gmail上で「転記済み」のラベルをつけます。
これにより、同じメールが再度処理されないようにしています。

※ラベルは事前にGmail上で作成しておいてください。
作成していないと、プログラム実行時にエラーになるため注意してください。

トリガーを設定する

最後にトリガーを設定すれば完成です。

メールを受信したタイミングで実行するトリガーは残念ながらないようなので、時間主導型で設定しています。
ここでは「時間ベースのタイマー」で「1時間おき」に設定していますが、
「分ベースのタイマー」で「1分おき」に設定すればリアルタイムにスプレッドシートに情報を反映することもできそうです。

お問い合わせ件数がそれほど多くない場合は、実行頻度は少なめでも問題ないかと思います。適宜変更してください。

今後改善したいところ

ここまでで、「動くプログラム」を作ることができました。
今後メールの件数が増えた場合なども想定し、下記のような点を改善していきたいと考えています。

スプレッドシートを行ごとに更新しているのを、データを配列でまとめてから一括で更新する形にしたい
そうすることで、多くのメールを処理する際に効率的になるようです。
また、並べ替えの処理でシートの2行目以降と指定しているため、データが増えてきた際に処理が重くならないか懸念しています。
そうした点からも、配列にデータを格納し、並べ替えてからスプレッドシートに反映できると良いのではないかと考えています。

ChatGPTにプログラムの改善点を指摘してもらったところ、
①エラーハンドリングの追加や、②Gmailの検索条件・シート名・ラベル名などの要素に対して変数や定数を使用して、スクリプトを柔軟に設定できるように〜、などなど、他にも色々と改善点が挙げられていました。

今後プログラムを改善し、より使い勝手の良いものにしていきたいと思います。

まとめ

GASを使用して、特定の条件を満たすメールの情報を、スプレッドシートに自動的に転記する方法について解説しました。

些細な作業でも、積み重なると膨大な時間がかかります。
GASを活用し、作業を自動化することで、業務改善に繋げていきましょう。