はじめに

会社に届く、「お問い合わせ」ってありますよね？
あれの管理って皆さん、どのようにして行こなっていますか？
もちろん、専用のツールで完結させているところもあると思いますが、そうでないところも多いと思います。
私の勤めている会社は、営業会社なので毎日、大量の問い合わせが来ます。

それを人力で見て、エクセルやスプレッドシートで管理するとなると非常にコストが掛かります。

そこで、お問い合わせをGmailで受信してスプレッドシートに自動で転記できれば便利なのではないか。

上記のようなことをエンジニアではない同僚から言われましてGoogle Apps Scriptを１ミリもやったことがない私が、Gmailに届いた「お問い合わせ」をGoogle Apps Scriptでスプレッドシートに転記できるようになった過程を紹介致します。

他のサイトと何が違うのか。

正直、「Gmail　スプレッドシート転送」などで調べると多くの記事がヒットします。
しかし、多くが、スプレッドシートに転記する際に、行で指定しているので行が崩れているメールが来ると対応できません。もっとわかり易くするために例を出すと

[会社名]〇〇株式会社
[タイトル]〇〇について
[内容]〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇

上記であれば正常に転記できますが、

[会社名]〇〇株式会社[タイトル]〇〇について
[内容]〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇〇

上記のようなお問い合わせも混ざって送られてくるところだと他のサイトのやり方では対応できません。
~~（お問い合わせの形式を統一すれば良い気もしますが・・・・）~~

Gmail に届いたメールをパースしてスプレッドシートに転記

前置きが長くなりましたが、実際にスプレッドシートに転記する方法を解説していきます。
お問い合わせがGmailで何らかの形で受信できているところからスタートします。

問い合わせから送られてくるメール型

「株式会社〇〇」にお問い合わせがありました。
お問い合わせ内容を確認し、ご連絡をお願い致します。

お問い合わせ内容の確認
───────────────
[ 氏名 ] 〇〇〇〇
[ 会社名 ] 株式会社　〇〇〇〇
[ 電話番号 ] 〇〇〇〇〇〇
[ メールアドレス ] 〇〇〇〇
[ 件名 ] 〇〇〇〇
[ お問い合わせ内容 ] 〇〇〇〇〇〇
[ 使用用途 ] 〇〇〇〇〇〇〇〇〇〇
───────────────
弊社では、上記のような型で送られてくるので、適宜、自分の環境に合わせて編集してください。

1.メールのラベルを作ってください。

まず、お問い合わせとそうではないメールを振り分けるため、Gmailでお問い合わせメールだけにラベルを張ります。

上記を参考にしてください。

2.以下のソースをスクリプトエディタに貼ります。

スクリプトエディタの場所がわからない方は上記のURLに参考にしてください。~~（THE 他力本願）~~

function myFunction() {
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var objSheet = objSpreadsheet.getSheetByName("シート1");//シート名をここに入力
  var sheet = SpreadsheetApp.setActiveSheet(objSheet);

  var start = 0;
  var max = 500;
  var threads = GmailApp.search('label:問い合わせ is:unread',start,max);
  var row = sheet.getLastRow() + 1;//最後の行探してそれ以降に追加
  
 for(var n in threads){
    var thd = threads[n];
    var msgs = thd.getMessages();

    for(m in msgs){
      var msg = msgs[m];
      var body = msg.getPlainBody();
      var date = msg.getDate();
      body = body.replace(/\r?\n/g, '');

      var ExtName = body.indexOf( '[ 氏名 ]' );
      var ExtCompany = body.indexOf( '[ 会社名 ]');
      var ExtTel = body.indexOf( '[ 電話番号 ]');
      var ExtEmail = body.indexOf( '[ メールアドレス ]');
      var ExtTitle = body.indexOf( '[ 件名 ]');
      var ExtContents = body.indexOf( '[ お問い合わせ内容 ]');
      var ExtPurpose = body.indexOf( '[ 使用用途 ]');
      var ExtEnd = body.indexOf( '───────────────', body.indexOf('───────────────') + 1);

      var Name = body.substring( ExtName+6,ExtCompany);
      var Company = body.substring( ExtCompany+7,ExtTel);
      var Tel = body.substring( ExtTel+8,ExtEmail);
      var FEmail = body.substring( ExtEmail+11,ExtTitle);
      var Title = body.substring( ExtTitle+6,ExtContents);
      var Contents = body.substring( ExtContents+12,ExtPurpose);
      var Purpose = body.substring( ExtPurpose+8,ExtEnd);

      sheet.getRange(row,1).setValue(date);
      sheet.getRange(row,6).setValue(Name);
      sheet.getRange(row,2).setValue(Company);
      sheet.getRange(row,7).setValue(Tel);
      sheet.getRange(row,9).setValue(FEmail);
      sheet.getRange(row,11).setValue(Title);
      sheet.getRange(row,12).setValue(Contents);
      sheet.getRange(row,10).setValue(Purpose);  
      row++
    }
  thd.markRead();  
  Utilities.sleep(1000);
  }
}

3.実行と完了

スクリプトエディタに貼ったら、▶︎のマークをクリックして諸々の許可をしたら動くと思います。
これで完了です。あとは、
https://tonari-it.com/gas-timed-driven-trigger/
上記を参考にするなどして定期的に実行が可能になります。

4.他力本願すぎだと怒られそうなので少し解説します。

  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var objSheet = objSpreadsheet.getSheetByName("シート1");//シート名をここに入力
  var sheet = SpreadsheetApp.setActiveSheet(objSheet);

上記でスプレッドシートを使う宣言と、シート名をしています。シート名はスプレッドシートの下のタブについている名前です。

  var start = 0;
  var max = 500;
  var threads = GmailApp.search('label:問い合わせ is:unread',start,max);
  var row = sheet.getLastRow() + 1;//最後の行探してそれ以降に追加

上記で「問い合わせ」というラベルが付いた未読のメールを５００個まで読み込んでくれます。（５００というのは適当な数字なので適宜変更してください。）

 for(var n in threads){
    var thd = threads[n];
    var msgs = thd.getMessages();

    for(m in msgs){
      var msg = msgs[m];
      var body = msg.getPlainBody();
      var date = msg.getDate();
      body = body.replace(/\r?\n/g, '');

上記はfor文が入れ子になっています。
最初のforでメッセージを検索して２つ目のforでメッセージの中身と
日付をとってきて最後に改行をなくしています。

      var ExtName = body.indexOf( '[ 氏名 ]' );
      var ExtCompany = body.indexOf( '[ 会社名 ]');
      var ExtTel = body.indexOf( '[ 電話番号 ]');
      var ExtEmail = body.indexOf( '[ メールアドレス ]');
      var ExtTitle = body.indexOf( '[ 件名 ]');
      var ExtContents = body.indexOf( '[ お問い合わせ内容 ]');
      var ExtPurpose = body.indexOf( '[ 使用用途 ]');
      var ExtEnd = body.indexOf( '───────────────', body.indexOf('───────────────') + 1);

上記で氏名〜使用用途までの文字数を調べています。（名前等を抜き出すため）

      var Name = body.substring( ExtName+6,ExtCompany);
      var Company = body.substring( ExtCompany+7,ExtTel);
      var Tel = body.substring( ExtTel+8,ExtEmail);
      var FEmail = body.substring( ExtEmail+11,ExtTitle);
      var Title = body.substring( ExtTitle+6,ExtContents);
      var Contents = body.substring( ExtContents+12,ExtPurpose);
      var Purpose = body.substring( ExtPurpose+8,ExtEnd);

      sheet.getRange(row,1).setValue(date);
      sheet.getRange(row,6).setValue(Name);
      sheet.getRange(row,2).setValue(Company);
      sheet.getRange(row,7).setValue(Tel);
      sheet.getRange(row,9).setValue(FEmail);
      sheet.getRange(row,11).setValue(Title);
      sheet.getRange(row,12).setValue(Contents);
      sheet.getRange(row,10).setValue(Purpose);  
      row++

最後に、先程の文字数から文字数まで切り抜き、それを指定した列に入れています。
rowが行で、その隣が列です。

最後に

普段、やっている業務の一部を自動化することによって生産性をあげましょう！