メールをSpreadSheetに記録するだけ

GASアドベントカレンダー
https://qiita.com/advent-calendar/2020/google-apps-script
に参加しています。
いい記事ばっかりなので他も見てね。

概要

超初心者向けです。
メールをSpreadSheetに記録するだけのGASです。

ついでにそれを関数使って一覧表にするシート
の解説もしています。

作ったもの

複数人のメルマガが
毎日出せているかをチェックする
GoogleSpreadSheet

イメージ：

・5分おきにメールチェック
・メルマガ出せていたら◯印をつける
・誰がどのくらい出せているかを見える化
・GASでやってることはメールをSpreadSheetに記録するだけ

仕組み

イメージ

ソース：メールをSpreadSheetに記録するだけ

const DATE = 0;
const FROM = 1;
const SUBJ = 2;
const BODY = 3;
const LINK = 3;


/** メイン処理　5分おきトリガー入れておく*/
function doMailCheckAndRecord() {

  Logger.log('doMailCheckAndRecord started.');

  //(送信元)メールアドレス配列を取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('登録');
  var last = sheet.getLastRow();
  var values = sheet.getRange(2,2,last).getValues(); 

  var mailAddressList = [];
  for(var i=0; i<last; i++){
    if(values[i][0] != ''){
      mailAddressList.push(values[i][0]);
    }
  }
  //Logger.log(mailAddressList);


  //Gmail検索条件　未読で1日以内のもの　迷惑メールにいっちゃったやつも拾う
  var query = 'is:unread newer_than:1d in:anywhere'
  var myThreads = GmailApp.search(query,0,30);
  var myMessages = GmailApp.getMessagesForThreads(myThreads);

  //書き出し用データを用意する
  var valMsgs = [];
  for(var i=0; i<myMessages.length; i++){
    var myMsgLen = myMessages[i].length;
    for(var j=0; j<myMsgLen; j++){
      valMsgs[i] = [];
      valMsgs[i][DATE] = myMessages[i][j].getDate();
      valMsgs[i][FROM] = myMessages[i][j].getFrom();
      valMsgs[i][SUBJ] = myMessages[i][j].getSubject();
      //valMsgs[i][BODY] = myMessages[i][j].getPlainBody();
      //valMsgs[i][LINK] = myThreads[i].getPermalink();
    }
  }

  var len = valMsgs.length;  
  //Logger.log('メール件数:' + len);

  var outputs = [];

  var outCount = 0
  //出力用シートに書き込む
  for(var i in mailAddressList){
    var ad = mailAddressList[i];
    outputIfContains(ad, valMsgs, outputs);
    outCount+=1;
  }

  if(outputs.length == 0){
    //Logger.log('Main 0件');
    return;
  }


  //シートに書き出す
  var recordSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Main');
  var lastRow = recordSheet.getLastRow();
  var range = recordSheet.getRange(lastRow+1, 1, outputs.length, outputs[0].length);
  range.setValues(outputs);

  SpreadsheetApp.flush();

  //シートに書き出したGmail既読にする
  for(var i in myThreads){
    myThreads[i].markRead();
  }

  Logger.log('レコード数:' + outputs.length);
  Logger.log('doMailCheckAndRecord ended.');
}

/** 受信メールにリストのメアドがあれば出力 */
function outputIfContains(ad, valMsgs, outputs){
  var today = new Date();

  for(var i=0; i<valMsgs.length; i++){
    if (valMsgs[i][FROM].indexOf(ad) != -1){

      var out = [];
      out.push(valMsgs[i][DATE]);
      out.push(ad);
      out.push(valMsgs[i][SUBJ]);
      outputs.push(out);
      return;
    }
  }
}

コピペベースで2時間で作ったもの。
ちょっと汚いけどご愛嬌。

出力

A列が受信時刻
B列が送信元アドレス
C列がメルマガタイトル
D列は本文頭100文字いれようとしたけど、今回は使わず。

出力用シートを一覧表にするシート

B3セルに

=IF( COUNTIFS(Main!$A:$A,">="&B$1,  Main!$A:$A,"<="&B$1+1,  Main!$B:$B,"="&'登録'!$B2)>0,"○","")

って関数いれて、オートフィルで埋めてます。

誰が何日に出せたかを
COUNTIFSで抽出し、1件以上来ていたら◯をつけます。

（◯を緑にするのは条件付き書式です）

（他の人にシート壊されたくないので
　IMPORTRANGE　で別ファイルに一覧表だけ転載してます。表示遅いけど）

感想

GASは普段触らないので
コピペベースで作りましたが、
けっこう動いちゃうのですごいです。

SpreadSheetと組み合わせるなら
最強の手軽さなのでは？

自分がメルマガ毎日出すのサボっちゃうので
見える化するツールを作り、
友人もサボりがちだったので招待しました。

そして、このシートを作ってから

ぼくも友人も

あれだけサボりがちだったメルマガを
なんと２週間連続で出せています！

えらい！