GoogleAppsScriptでGmailの内容をSpreadsheetにコピーする

1. 概要

GoogleAppsScriptを用いて、Gmailで受信した定型メールの内容をGoogle Spreadsheetに転記する。
定期的に受信する定型メールをGoogle Spreadsheetで管理したいと思い、作成した。

メールの送信元・タイトルを元に記載先のシートに振り分けをするか判定をし、振り分けをする場合はメールの内容をSpreadsheetにを転記する。

2. GASへのコード設置

事前準備

最初に、設定と転記先のタブを用意したSpreadsheetを用意する。

次にSpreadsheetのURLにあるIDを後で利用するため、メモっておく。

https://docs.google.com/spreadsheets/d/ hogehogehogehogehoge /edit#gid=1234933391

スクリプトの設置

GASに以下のコードをGAS エディタに設置する。SHEET_IDには、先ほど確認したスプレッドシートのIDを埋めてください。
初回起動時は承認が必要であり、実行時に「このアプリは確認されていません」という警告が表示される。
警告のダイアログボックスの詳細から権限を付与することで、実行可能となる。

条件を指定してメールを取得するのは、以下のように記載することで可能である。queryで指定可能な条件はGmailヘルプのGmail で使用できる検索演算子で確認できる。

var threads = GmailApp.search(query, start, max);

startの部分には最初のスレッドのインデックスを、maxの部分には返すべきスレッド数の最大値を指定します。maxは仕様で最大500件と決まっているようです。
なお、この500件の制約とは別に1日あたりの読み取り可能最大件数が決まっている。

• 通常のGmail: 20,000 /日
• G Suite: 50,000 /日

var SHEET_ID = "xxxxx";
var COMMON_HEADER = ['問い合わせ日時','種類','録音日時','発信者番号','ボイスメールアクセス','会社/氏名','用件区分','メールアドレス','折り返し先','内容','都道府県市町村','備考'];
var MAX_THREADS = 200;

/*
 *
 */
// set this method to scheduler
function main() {
  var now = new Date();
  Logger.log("Start:" + now);

  var config = getConfig();
  if (config == null) {
    return;
  }
  
  // 1時間前のUnix Timeを取得する
  var after_unixtime = parseInt((now.getTime() - 10 * 60 * 60 * 1000) / 1000);
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
  
  for (var i in Object.keys(config)) {
    var pattern = Object.keys(config)[i];
    
    // 出力先のシート取得
    var sheet = getPatternSheet(spreadsheet, pattern)
    
    // メールの取得
    var from = config[pattern]["from"];
    var title_keyword = config[pattern]["title"];
    var query = "from:" + from + " subject:(" + title_keyword + ") after:" + after_unixtime;
    var messages = searchMail(pattern, query);
    
    // シートへの書き込み
    if (messages.length > 0){
      var row_start = sheet.getLastRow() + 1;

      // 書式設定
      var formats = ["yyyy/MM/dd H:mm:ss", "@", "yyyy/MM", "@", "@","@","@","@","@","@","@","@"];
      
      for(var j = 0; j < formats.length; j++){
        var myrange = sheet.getRange(row_start, j + 1, messages.length, 1);
        myrange.setNumberFormat(formats[j]);        
      }
      
      var range = sheet.getRange(row_start, 1, messages.length, messages[0].length);
      range.setBorder(true,true,true,true,true,true,"#000000",SpreadsheetApp.BorderStyle.SOLID); // 罫線
      range.setVerticalAlignment("top"); // 上揃え
      range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP) // 折り返し
      range.setValues(messages);
    }
  }
}

// get setting
function getConfig() {
  Logger.log("Do getConfig()");
  
  var spreadsheet;
  try {
    spreadsheet = SpreadsheetApp.openById(SHEET_ID);
  } catch(e) {
    console.error('SpreadsheetApp# ' + e);
    Logger.log("スプレッドシートを開けません。");
    
    return null;
  }
  
  var ss = spreadsheet.getSheetByName("Setting");
  var ssv = ss.getRange(2, 1, 3, 3).getValues();
  
  var config = {};
  for (var a in ssv) {
    if (ssv[a][0] == "") continue;
    config[ssv[a][0]] = {"from" : ssv[a][1], "title" : ssv[a][2]};
  }
  return config;
}


//
function getPatternSheet(spreadsheet, pattern) {
  Logger.log("Do getPatternSheet(): " + pattern);

  var sheet = spreadsheet.getSheetByName(pattern);
  
  if (sheet == undefined) {
    Logger.log("Insert sheet: " + pattern);
    
    sheet = spreadsheet.insertSheet(pattern);
    var range = sheet.getRange(1, 1, 1, COMMON_HEADER.length)
    range.setValues([COMMON_HEADER]);
    
    // 書式設定
    header_color = '#c5dbf0'
    range.setBackground(header_color)
    range.setBorder(true,true,true,true,true,true,"#000000",SpreadsheetApp.BorderStyle.SOLID);
    range.setFontWeight("bold");
    range.setHorizontalAlignment("center");
  }

  return sheet;
}

// search
function searchMail(pattern,query,after) {
  Logger.log("Do searchMail(): " + pattern);

  var messages = [];

  // メールの取得(最大MAX_THREADS件を取得する)
  var threads = GmailApp.search(query, 0, MAX_THREADS);
  if (threads.length === 0){
    Logger.log("There are 0 messages");
    return messages;
  }
  
  threads.forEach(function (thread) {
      thread.getMessages().forEach(function (message) {      
        var msid = message.getId();
        var date = message.getDate();
        var from = message.getFrom();
        var subj = message.getSubject();
        var text = message.getPlainBody();
        
        switch (pattern) {
          case "問い合わせ":
            var name = getValueFromHtml(text,"差出人","：","都道府県");
            var province = getValueFromHtml(text,"都道府県","：","市区町村") + getValueFromHtml(text,"市区町村","：","メールアドレス"); 
            var email = getValueFromHtml(text,"メールアドレス","：","電話番号")
            var phone2 = getValueFromHtml(text,"電話番号","：","題名");
            var content = getValueFromHtml(text,"題名","：","");           
            break;
          case "取り次ぎ":
            var name = getValueFromHtml(text,"お客様名","：","用件区分");
            var type = getValueFromHtml(text,"用件区分","：","折り返し先"); 
            var phone2 = getValueFromHtml(text,"折り返し先","：","内容");
            var content = getValueFromHtml(text,"内容","：","※このメールは配信専用です");
            if ((content != "") && (content.indexOf("地域") > -1)) {
              content = content.substring(0,content.indexOf("地域"));
            }
            var province = getValueFromHtml(text,"地域","：","※このメールは配信専用です");            
            break;
          case "留守電着信":
            var phone = getValueFromHtml(text,"発信者番号",":","録音日時");
            var time = getValueFromHtml(text,"録音日時",":","・ボイスメールアクセス");
            var access = getValueFromHtml(text,"・","ボイスメールアクセス","（登録端末番号");
            break;
          default: // その他
            break
        }
        messages.push([date, pattern, time, phone, access, name, type, email, phone2, content, province, ""]);
      });
    });

  // 受信日時の昇順でソート
  messages.sort(function(a,b){
        if( a[0].getTime() < b[0].getTime() ) return -1;
        if( a[0].getTime() > b[0].getTime() ) return 1;
        return 0;
  });

  return messages;
}

function getValueFromHtml(html,fromText,nameEndText,toText){
  var value;
  var indexKeyBegin = html.indexOf(fromText);
  if (indexKeyBegin !== -1) {
    var cuttedHtml = html.substring(indexKeyBegin + fromText.length,html.length);
    var indexKeyEnd = cuttedHtml.indexOf(nameEndText);
    if (toText == "") {
      var indexValueEnd = cuttedHtml.length - indexKeyEnd;
    } else {
      var indexValueEnd = cuttedHtml.indexOf(toText);
    }
    var indexFrom = indexKeyEnd + nameEndText.length;
    if (indexKeyEnd !== -1) {
      value = nonSpaceStrVal(cuttedHtml.substring(indexFrom, indexValueEnd));
    };
  } else {
    value = "";
  };
  return value;
};

function nonSpaceStrVal(origin){
try {
  if (typeof origin != "string") return origin;
  if (origin == '') return origin;
  var str = origin.replace(/\s+/g, "");
  return str;
} catch(e) {
  console.error('nonSpaceStrVal# ' + e);
  return '';
}
}

トリガーの設置

トリガーを設定するときは、メソッドを「Main」で、時間主導型の１時間ごとで設定してください。

最後に

メール本文を取り込む部分は、開始・終了の判定に使用している文字が他の箇所にもあると、正しく動作しない恐れがあります。
実際に正しく動作するか確認の上、ご利用ください。