楽天の購入履歴の一覧作成プログラム！gmailから作成スプレッドシート(エクセル)で作成

function MailRist() {

  var book = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1 = book.getSheetByName("メール確認")

  //console.log(book);

  book.toast("対象メール検索中　※少々時間がかかります。", "実行中", -1)

  //  Utilities.formatDate( date, 'Asia/Tokyo', 'yyyyMMdd: hhmmss'));

  const dateafter = Utilities.formatDate(sheet1.getRange(1, 2).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd');
  const fr = sheet1.getRange(1, 4).getValue()
//  const KENSU = sheet1.getRange(1, 6).getValue()

  var findkey = "after:" + dateafter + " FROM:" + fr
  var kensakukekka = GmailApp.search(findkey, 0, 500);
  var Msg = GmailApp.getMessagesForThreads(kensakukekka);//二次元配列

  var OutputMsg = [];
  var maxc = 0;

  book.toast("findkey", 5, 5)

  /* 各メールから日時、送信元、件名、内容を取り出す */
  for (var i = 0; i < Msg.length; i++) {

    if (i % 5 == 0) { book.toast("スクリプトを実行中です。" + i + "/" + Msg.length, "実行中", 5) }

    for (var j = 0; j < Msg[i].length; j++) {


      var data = Msg[i][j].getBody().split("\n");
      for (var l = 0; l < data.length; l++) {
        if (data[l].indexOf('支払い金額') != -1) {
          var body = data[l];

          var S_num = body.indexOf('+1"><b>');
          var E_num = body.indexOf('（円）');

          var body = body.substring(S_num + 7, E_num);


        }

        if (data[l].indexOf('円 x ') != -1) {

          if (data[l - 3].indexOf('href') != -1) {
            var date = data[l - 3];
          } else if (data[l - 4].indexOf('href') != -1) {
            var date = data[l - 4];
          } else if (data[l - 5].indexOf('href') != -1) {
            var date = data[l - 5];
          } else if (data[l - 6].indexOf('href') != -1) {
            var date = data[l - 6];
          } else if (data[l - 7].indexOf('href') != -1) {
            var date = data[l - 7];
          } else if (data[l - 8].indexOf('href') != -1) {
            var date = data[l - 8];
          } else if (data[l - 9].indexOf('href') != -1) {
            var date = data[l - 9];
          } else if (data[l - 10].indexOf('href') != -1) {
            var date = data[l - 10];
          } else if (data[l - 11].indexOf('href') != -1) {
            var date = data[l - 11];
          } else if (data[l - 12].indexOf('href') != -1) {
            var date = data[l - 12];
          } else {
            var date = data[l - 13];
          }

          //console.log(date)
          var S_num = date.indexOf('\">');
          var E_num = date.indexOf('</a');

          var data1 = date.substring(S_num + 2, E_num);

          var S_num = date.indexOf('href=');
          var E_num = date.indexOf('style');

          var data2 = date.substring(S_num + 6, E_num - 18);


        }

      }


      //[3]データ


      var date2 = Msg[i][j].getDate();
      var from = Msg[i][j].getFrom();
      var subj = Msg[i][j].getSubject();
      /*var body = Msg[i][j].getBody().slice(0,100000);*/
      var perm = kensakukekka[i].getPermalink();

      OutputMsg.unshift([date2, data1, body, data2, perm])

    }


    //    OutputMsg[0] = ([subj, perm]).concat(OutputMsg[0])

    //[date,from,subj,body,perm]
  }

  for (var i = 0; i < OutputMsg.length; i++) {
    var cn = OutputMsg[i].length
    if (cn > maxc) { maxc = cn }
  }

  for (var i = 0; i < OutputMsg.length; i++) {
    OutputMsg[i].length = maxc
  }

  console.log(OutputMsg.length)

  if (Msg.length > 0) {
    var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('メール確認');
    mySheet.getRange(3, 1, OutputMsg.length, maxc).setValues(OutputMsg);
  }

  Browser.msgBox("完了")

}