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

GASappendrow,getLastRow,getMessage,getPlainBody,gmail,GmailApp,GmailApp.search,match,スプレッドシート

この記事では、Google Apps Script（GAS）を使ってGmailから特定の条件で抽出したメール情報（メール本文・件名・差出人アドレス etc…）をスプレッドシートに書き出し転記する方法を解説します。

メールの内容から一つ一つコピペして転記するのは膨大な手間がかかりますが、GASを使って自動化して工数を大幅に削減することができます。

自動抽出・転記作業手順の全体像

自動抽出・転記作業のステップ

Gmailの自動抽出・転記を行うために以下の手順を踏みます。

  • 抽出・転記先のスプレッドシートを用意
  • 条件を指定しGmailからメール一覧を取得（GmailApp.search）
  • メール本文から要素ごとに情報を抽出（getMessages、getPlainBody）
  • 抽出した情報をスプレッドシートに転記（appendRowまたはsetValues）


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

サンプルコード

コード全体像をご紹介した後に各ステップずつ解説を行います。

function myFunction(){

    // 抽出条件指定
    let query = 'subject:フォーム問い合わせ';

    //問合せごとの情報が要素になった二次元配列を取得
    let gmail = getGmail(query);

    // 書き込むシートを取得、アクティブシートを取得しているが任意のシートでOK
    let sheet = SpreadsheetApp.getActive().getActiveSheet();

    //要素ごとにappendRowメソッドを呼び出して最終行に書き込む繰り返し処理を実行
    gmail.forEach(info => sheet.appendRow(info));

}


function getGmail(query) {

  //Gmail取得ここから
  //Gmailの履歴からマッチするメールを一覧化
  let threads = GmailApp.search(query);

  let gmailInfo = new Array();  
  
  //一覧化したメールの件数分繰り返し 
  threads.forEach(function(thread) {

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

      // メール本文
      let plainBody = message.getPlainBody();
      // Logger.log(plainBody);
      
      // 問い合わせ日
      let date = plainBody.match(/問い合わせ日：(.*)/);
      // Logger.log(data);     

      // 問い合わせ内容
      let naiyo = plainBody.match(/問い合わせ内容：(.*)/);
      // Logger.log(naiyo);
      
      // 氏名
      let name = plainBody.match(/氏名：(.*)/);
      // Logger.log(name);      

      // 会社名
      let company = plainBody.match(/会社名：(.*)/);
      // Logger.log(company);

      // メールアドレス
      let mail = plainBody.match(/メールアドレス：(.*)/);
      // Logger.log(mail);

      // 電話番号
      let tel = plainBody.match(/電話番号：(.*)/);
      // Logger.log(tel);

      //各メール情報を組みとした二次元配列として格納
      gmailInfo.push([date[1],naiyo[1],name[1],company[1],mail[1],tel[1]]);

    });
  });

   return(gmailInfo); 
}

抽出・転記先のスプレッドシートを用意

抽出先のスプレッドシートを用意し、メールから転記したい要素ごとにカラムを用意します。
値を格納するカラムは列番号で指定するので、カラム名はなんでもOKです。

条件を指定しGmailからメール一覧を取得（GmailApp.search）

GmailApp.search(query [,start ,num])

query（必須）：検索条件　※検索窓で検索する時と同様に指定が可能
start（任意）：インデックス開始位置（「0」または指定しなければ最新から取得）
num（任意）：スレッドの最大取得数
戻り値：検索条件にマッチするメールのスレッド（GmailThreadオブジェクト）の配列

  //Gmailの履歴からマッチするメールを一覧化
  var threads = GmailApp.search('subject:フォーム問い合わせ');

GmailApp.search(query *検索条件)で自分のGmailボックスから条件に該当するメール一覧を取得できます。今回はメールの件名が「フォーム問い合わせ」のメールを取り出したいので、<'subject:フォーム問い合わせ’>と記述しました。

その他、お気に入り、本文内の文字列、送信者、期間等の条件で絞り込みしたい場合、gmailの検索ボックスと一緒のルールで記述可能です。

指定条件記述方法と例
送信者from:
（例 from:花子）
受信者to:
（例 to:太郎）
Cc や Bcc cc:
bcc:
（例 cc:太郎）
件名に含まれる単語subject:
（例 subject:夕食）
検索結果から除外するキーワードを指定–
（例 夕食 -映画）
複数の条件指定OR または { }
（例 from:花子 OR from:太郎）
（例 {from:花子 from:太郎}）

searchメソッドのより詳細の解説はこちらの記事で行っています。

メール本文から要素ごとに情報を抽出（getMessages、getPlainBody）

本文の取得

GmailThread.getMessages()

  • 戻り値：GmailThreadに含まれるメッセージの配列

GmailMessage.getPlainBody()

  • 戻り値：GmailMessageに含まれる本文（HTMLは含まずテキストのみを取得）
＜＜前半：本文取得部分＞＞

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

      // メール本文
      let plainBody = message.getPlainBody();
      // Logger.log(plainBody);
      
      // 問い合わせ日
      let date = plainBody.match(/問い合わせ日：(.*)/);
      // Logger.log(data);     

      // 問い合わせ内容
      let naiyo = plainBody.match(/問い合わせ内容：(.*)/);
      // Logger.log(naiyo);
      
      // 氏名
      let name = plainBody.match(/氏名：(.*)/);
      // Logger.log(name);      

      // 会社名
      let company = plainBody.match(/会社名：(.*)/);
      // Logger.log(company);

      // メールアドレス
      let mail = plainBody.match(/メールアドレス：(.*)/);
      // Logger.log(mail);

      // 電話番号
      let tel = plainBody.match(/電話番号：(.*)/);
      // Logger.log(tel);

getPlainBodyメソッドで本文をテキストで抽出した後、事前に定義していた各問合せ項目（日付、氏名、アドレス等）ごとに変数に格納していきます。ここでは、matchメソッドを使い＜/[問合せ項目]（.*）/＞と正規表現で記述することで、本文内容を抽出しました。

補足：「getPlainBody」と「getBody」の違い

getPlainBodyメソッドと似た記述としてgetBodyメソッドがあります。記述方法は全く同じで役割も似ているのでややこしいですが、getPlainBodyがテキストのみを取得するのに対して、getBodyはHTMLタグを含めて本文を取得します。
今回はプレーンテキストで問題なかったので、getPlainBodyメソッドを使いました。

matchメソッドと正規表現については下記の記事をご覧ください。

メール情報を二次元配列として格納

Array.push(val1 [, val2,… , valN)

  • valN：配列に追加したい要素、配列の末尾に追加される
  • 戻り値：要素が追加された新しい配列の要素数
    元の配列は追加要素が加わった新しい配列に変更される
      //各メール情報を組みとした二次元配列として格納
      gmailInfo.push([date[1],naiyo[1],name[1],company[1],mail[1],tel[1]]);

    });
  });

   return(gmailinfo);

検索条件にヒットしたメールごとに、[date(日付), naiyo(問い合わせ内容), name(氏名) , company(会社), mail(メールアドレス), tel(電話番号)…] の組を一つの要素とした二次元配列 gmailInfoを作成。

抽出・取得した情報をスプレッドシートに転記

先ほど作成したメール一覧の二次元配列をmyFunction側で取得。

    // 抽出条件指定
    let query = 'subject:フォーム問い合わせ';

    //問合せごとの情報が要素になった二次元配列を取得
    let gmail = getGmail(query);

取得した二次元配列の各要素が、スプレッドシートに行単位で書き込みたい一つのメール情報となっているので、forEachメソッドを使って各要素に対してappendRowメソッドを実行。

    // 書き込むシートを取得、アクティブシートを取得しているが任意のシートでOK
    let sheet = SpreadsheetApp.getActive().getActiveSheet();

    //要素ごとにappendRowメソッドを呼び出して最終行に書き込む繰り返し処理を実行
    gmail.forEach(info => sheet.appendRow(info));
書き込み方法法の別解

今回は一行一行appendRowメソッドを使って格納しましたが、lastRowメソッド・setValuesメソッド等を使いながら二次元配列を一括で格納することも可能です。例えば下記の記載も上記と同じ結果になります。

　// 書き込むシートを取得、アクティブシートを取得しているが任意のシートでOK
  let sheet = SpreadsheetApp.getActive().getActiveSheet();

 //範囲
  let lastRow = sheet.getLastRow(); //既存データが入っている最終行番号
  let row = gmail.length; //書き込みたいデータの行数
  let col = gmail[0].length; //書き込みたいデータの列数

  let range = sheet.getRange(lastRow+1, 1, row, col); //書き込み範囲指定

　//書き込み実行
  range.setValues(gmail);

【サンプルプログラム】Googleアラートで受け取ったリンクをスプレッドシートに転記する

上記と同じような手順で、Gmailに届くGoogleアラートのメールに含まれるリンクを受信日とともにスプレッドシートに書き出すサンプルコードを書いてみました。

メールの内容によって情報取得処理方法を変える必要があるので全く同じようにコードは書けませんが、ぜひ参考にしてみてください。

function myFunction(){

  //「Google アラート」の文字列を検索条件に指定して抽出命令 
  let query = 'Google アラート';
  let gmail = getGmail(query);

  //スプレッドシートに書き出し
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let lastRow = sheet.getLastRow();
  let row = gmail.length;
  let col = gmail[0].length;

  let range = sheet.getRange(lastRow+1, 1, row, col);

  range.setValues(gmail);
  
}

function getGmail(query){

  //新着メール情報を格納する配列を宣言
  let array = [];

   //Gmailの履歴からマッチするメールをスレッド化、最深のメールから10通取得
  let threads = GmailApp.search(query,0,10);

  //一覧化したメールの件数分繰り返し 
  threads.forEach(function(thread) {

    // スレッド内のメール一覧を取得
    let messages = thread.getMessages();

    // メールを一つずつ取り出す
    messages.forEach(function(message) {

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

      // メール本文の取得
      let plainBody = message.getPlainBody();
      //リダイレクト先のURLを取得 ⇒ Parserライブラリのiterateメソッドを使うことで、単一のメール内に含まれるURLで配列を作成
      urls =Parser.data(plainBody).from('<https://www.google.com/url?rct=j&sa=t&url=').to('&ct=ga').iterate();

      for(let url of urls){  // URLごとに反復処理
        if(!url.match('%')){  //デコードが必要なURLは処理しない
          array.push([date, url]); //受信日とURLの組を配列に追加
        }
      }
    });
  });

  return array;
}

まとめ

いくつかgmail特有の記述がありましたが、下記を押さえておけばいろいろ応用可能になりそうです。

  • GmailApp.searchメソッドを用いて、特定条件のメールをスレッド化
  • getMessageメソッド・getPlainBody メソッドで本文を取得し、matchメソッドと正規表現を使って要素ごとに値を取得

こちらもおすすめ

この記事を書いた人

てつお
広告代理店出身、事業会社でWebマーケティングや開発の仕事をしている26歳です。プロフィール詳細はこちら。
■Google広告認定資格｜Google アナリティクス個人認定資格（GAIQ）｜TOEIC920点