チュートリアル 中級者 Gmail受信トレイの統計レポート

スクリプトは異なる情報を単一の変数で格納するためにJavaScriptオブジェクトを利用します。そして、ユーザープロパティをオブジェクトとして保存するために JSONエンコーディングを実行します。

activityReport()にトリガーされたfetchEmails_()ファンクションが１００通の新規メール上で動作します。

格納されたデータを取り出すためにユーザープロパティをデコードするためのコードが下のものです。そしてGmail内で新規メールを検索します。

  var variables = Utilities.jsonParse(UserProperties.getProperty("variables"));
       var conversations = GmailApp.search("in:anywhere after:"+variables.year+"/"+(variables.previousMonth)+"/31 before:"
                                      +new Date().getYear()+"/"+(variables.previousMonth+1)
                                      +"/31 -label:chats -label:spam", variables.range, 100);
       

sendReport_()ファンクションはfetchEmails_()ファンクションが契機になります。 前月のメールが全部読み込まれるとfetchEmails_()はsendReport_()を呼び出します。その後スクリプトはJvaScriptオブジェクトに格納されている情報からレポートを作成し チャートを作るためにChart Serviceを使います。

日々のメール受信数を表示するチャートを作るためのコードは下にあります。

 report+= "<img src=\'";
	  var dataTable = Charts.newDataTable();
	  dataTable.addColumn(Charts.ColumnType['STRING'], 'Date');
	  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Received');
	  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Sent');
	  for (var i = 0;  i < variables.dayOfEmailsReceived.length; i++){ //create the rows
		dataTable.addRow([i+1,variables.dayOfEmailsReceived[i],variables.dayOfEmailsSent[i]]);
	  }
	  dataTable.build();  
	  var chartDate = Charts.newAreaChart()
		 .setDataTable(dataTable)
		 .setTitle("Date")
		 .setDimensions(650, 400)
		 .build(); 
	  report+= "cid:Date\'/>"

このチャートをメールのボディに埋め込むためにsendEmail()メソッドに高度なパラメーターを使います。 すなわち”inlineImages”です。下のコードがメールボディにチャートを埋め込むもので、これでレポートを送信します。

 var inlineImages = {};
          inlineImages['Averageflow'] = chartAverageFlow;
          inlineImages['Date'] = chartDate;
          MailApp.sendEmail(variables.user, "Gmail Stats", report, {htmlBody:report, inlineImages: inlineImages});
      

function activityReport() {
  var status = UserProperties.getProperty("status");
  if (status != null) {
    status = Utilities.jsonParse(status);
    // If new month, send new report
    if (status.previousMonth != new Date(
      new Date().getFullYear(),
      new Date().getMonth() - 1,
      1).getMonth()) {
      init_();
      fetchEmails_();
      status.previousMonth =
        new Date(new Date().getFullYear(), new Date().getMonth() - 1, 1).getMonth();
      status.reportSentforPreviousMonth = "no";
      UserProperties.setProperty("status", Utilities.jsonStringify(status));
    }
    // If report not sent, continue to work on the report
    else if (status.reportSentforPreviousMonth == "no") {
      fetchEmails_();
    }
  }
  // If the script is triggered for the first time, init and work on the report
  else {
    init_();
    fetchEmails_();
  }
}

function fetchEmails_() {
  var variables = Utilities.jsonParse(UserProperties.getProperty("variables"));
  var conversations =
    GmailApp.search(
      "in:anywhere after:" + variables.year + "/" + (variables.previousMonth) +
        "/31 before:" + new Date().getYear() + "/" +
        (variables.previousMonth + 1) +
        "/31 -label:sms -label:chats -label:spam",
      variables.range,
      100);
  variables.nbrOfConversations += conversations.length;
  for (i in conversations) {
    var youReplied = false;
    var youStartedTheConversation = false;
    var messages = conversations[i].getMessages();
    for (j in messages) {
      var date = messages[j].getDate();
      if (date.getMonth() == variables.previousMonth) {
        // ////////////////////////////////
        // Fetch sender of each emails
        // ////////////////////////////////
        var from = messages[j].getFrom();
        if (from.match(/</) != null) {
          from = from.match(/<([^>]*)/)[1];
        }
        var time = Utilities.formatDate(date, variables.userTimeZone, "H");
        var day = Utilities.formatDate(date, variables.userTimeZone, "d");
        if (from == variables.user) {
          if (j == 0) {
            youStartedTheConversation = true;
          }
          if (j > 0 && !youStartedTheConversation) {
            youReplied = true;
          }
          variables.nbrOfEmailsSent++;
          variables.timeOfEmailsSent[time]++;
          variables.dayOfEmailsSent[day]++;
          var to = messages[j].getTo();
          to = to.split(/,/);
          for (l = 0; l < to.length; l = l + 2) {
            if (to[l].match(/</) != null) {
              to[l] = to[l].match(/<([^>]*)/)[1];
            }
            for (k in variables.people) {
              if (to[l] == variables.people[k][0]) {
                variables.people[k][2]++;
                found = true;
              }
            }
            if (!found && to[l].length < 50) {
              variables.people.push([
                  to[l], 0, 1
              ]);
            }
          }
        } else {
          var found = false;
          for (k in variables.people) {
            if (from == variables.people[k][0]) {
              variables.people[k][1]++;
              found = true;
            }
          }
          if (!found && from.length < 50) {
            variables.people.push([
                from, 1, 0
            ]);
          }
          if (messages[j].getTo().search(variables.user) != -1) {
            variables.sentDirectlyToYou++;
          }
          variables.nbrOfEmailsReceived++;
          variables.timeOfEmailsReceived[time]++;
          variables.dayOfEmailsReceived[day]++;
        }
        if (messages[j].isInTrash()) {
          variables.nbrOfEmailsTrashed++;
        }
      }
    }
    if (youStartedTheConversation) {
      variables.nbrOfConversationsStartedByYou++;
    }
    if (youReplied) {
      variables.nbrOfConversationsYouveRepliedTo++;
    }
    if (conversations[i].hasStarredMessages()) {
      variables.nbrOfConversationsStarred++;
    }
    if (conversations[i].isImportant()) {
      variables.nbrOfConversationsMarkedAsImportant++;
    }
  }
  variables.range += 100;
  var error = true;
  while (error) {
    try {
      UserProperties.setProperty(
        "variables",
        Utilities.jsonStringify(variables));
      error = false;
    } catch (err) {
      variables.people.sort(sortArrayOfPeopleTo_);
      variables.people.pop();
      Utilities.sleep(2000);
    }
  }
  if (conversations.length < 100) {
    sendReport_(variables);
  }
}

function sendReport_(variables) {
  variables.people.sort(sortArrayOfPeopleFrom_);
  var report =
    "<h2 style=\"color:#cccccc; font-family:verdana,geneva,sans-serif;\">Gmail Stats - " +
      Utilities.formatDate(
        new Date(variables.previous),
        variables.userTimeZone,
        "MMMM") +
      "</h2><p>" +
      variables.nbrOfConversations +
      " conversations - " +
      variables.nbrOfEmailsSent +
      " emails sent - " +
      variables.nbrOfEmailsReceived +
      " emails received - " +
      variables.nbrOfEmailsTrashed +
      " emails trashed<br>" +
      variables.nbrOfConversationsMarkedAsImportant +
      " conversations marked as important - " +
      variables.nbrOfConversationsStarred +
      " conversations starred.</p><p>" +
      Math.round(variables.sentDirectlyToYou * 10000 /
        variables.nbrOfEmailsReceived) /
      100 +
      "% of those emails were sent directly to you. And you replied to " +
      Math.round(variables.nbrOfConversationsYouveRepliedTo *
        10000 /
        (variables.nbrOfConversations - variables.nbrOfConversationsStartedByYou)) /
      100 +
      "% of them.</p>" +
      "<table style=\"border-collapse: collapse;\"><tr><td style=\"border: 0px solid white;\">" +
      "<h3>Top 5 senders:</h3><ul>";
  var r = 0;
  var s = 0;
  while (s < 5) {
    if (variables.people[r][0].search(/notification|noreply|update/) == -1) {
      report +=
        "<li title=\"" + variables.people[r][1] + " emails\">" +
          variables.people[r][0] + "</li>";
      s++;
    }
    r++;
  }
  variables.people.sort(sortArrayOfPeopleTo_);
  report +=
    "</ul></td><td style=\"border: 0px solid white;\"><h3>Top 5 recipients:</h3><ul>";
  for (i = 0; i < 5; i++) {
    report +=
      "<li title=\"" + variables.people[i][2] + " emails\">" +
        variables.people[i][0] + "</li>";
  }
  report += "</ul></td></tr></table><br><img src=\'";

  var dataTable = Charts.newDataTable();
  dataTable.addColumn(Charts.ColumnType['STRING'], 'Time');
  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Received');
  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Sent');

  var time = '';
  for ( var i = 0; i < variables.timeOfEmailsReceived.length; i++) { // create
                                                                      // the
                                                                      // rows
    switch (i) {
      case 8:
        time = '8am';
        break;
      case 10:
        time = '10am';
        break;
      case 12:
        time = '12pm';
        break;
      case 14:
        time = '2pm';
        break;
      case 18:
        time = '6pm';
        break;
      case 20:
        time = '8pm';
        break;
      default:
        time = '';
        break;
    }
    dataTable.addRow([
        time, variables.timeOfEmailsReceived[i], variables.timeOfEmailsSent[i]
    ]);
  }
  dataTable.build();
  var chartAverageFlow =
    Charts.newAreaChart().setDataTable(dataTable).setTitle("Average flow").setDimensions(
      650,
      400).build();

  report += "cid:Averageflow\'/>" + "<img src=\'";

  var dataTable = Charts.newDataTable();
  dataTable.addColumn(Charts.ColumnType['STRING'], 'Date');
  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Received');
  dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Sent');

  for ( var i = 0; i < variables.dayOfEmailsReceived.length; i++) { // create
                                                                    // the rows
    dataTable.addRow([
        i + 1, variables.dayOfEmailsReceived[i], variables.dayOfEmailsSent[i]
    ]);
  }
  dataTable.build();
  var chartDate =
    Charts.newAreaChart().setDataTable(dataTable).setTitle("Date").setDimensions(
      650,
      400).build();

  report += "cid:Date\'/>";

  var inlineImages = {};
  inlineImages['Averageflow'] = chartAverageFlow;
  inlineImages['Date'] = chartDate;

  MailApp.sendEmail(variables.user, "Gmail Stats", report, {
      htmlBody : report,
      inlineImages : inlineImages
  });
  var status = Utilities.jsonParse(UserProperties.getProperty("status"));
  status.reportSentforPreviousMonth = "yes";
  UserProperties.setProperty("status", Utilities.jsonStringify(status));
}

function sortArrayOfPeopleFrom_(a, b) {
  return ((a[1] > b[1]) ? -1 : ((a[1] < b[1]) ? 1 : 0));
}

function sortArrayOfPeopleTo_(a, b) {
  return ((a[2] > b[2]) ? -1 : ((a[2] < b[2]) ? 1 : 0));
}

function daysInMonth_(month, year) {
  return 32 - new Date(year, month, 32).getDate();
}

function init_() {
  var dayOfEmailsSent = [];
  var dayOfEmailsReceived = [];
  var timeOfEmailsSent = [];
  var timeOfEmailsReceived = [];
  // Find previous month...
  var previous =
    new Date(new Date().getFullYear(), new Date().getMonth() - 1, 1);
  var previousMonth = previous.getMonth();
  var year = previous.getYear();
  var lastDay = daysInMonth_(previousMonth, year);
  for (i = 0; i < lastDay + 1; i++) {
    dayOfEmailsSent[i] = 0;
    dayOfEmailsReceived[i] = 0;
  }
  for (i = 0; i < 24; i++) {
    timeOfEmailsSent[i] = 0;
    timeOfEmailsReceived[i] = 0;
  }
  var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  var user = Session.getActiveUser().getEmail();
  var variables = {
      range : 0,
      nbrOfConversations : 0,
      nbrOfConversationsYouveRepliedTo : 0,
      nbrOfConversationsStartedByYou : 0,
      nbrOfConversationsStarred : 0,
      nbrOfConversationsMarkedAsImportant : 0,
      nbrOfEmailsReceived : 0,
      nbrOfEmailsSent : 0,
      nbrOfEmailsTrashed : 0,
      sentDirectlyToYou : 0,
      people : [],
      dayOfEmailsSent : dayOfEmailsSent,
      dayOfEmailsReceived : dayOfEmailsReceived,
      timeOfEmailsSent : timeOfEmailsSent,
      timeOfEmailsReceived : timeOfEmailsReceived,
      previous : previous,
      previousMonth : previousMonth,
      year : year,
      lastDay : lastDay,
      userTimeZone : userTimeZone,
      user : user
  };
  UserProperties.setProperty("variables", Utilities.jsonStringify(variables));
  var status = {
      previousMonth : previousMonth,
      reportSentforPreviousMonth : "no"
  };
  UserProperties.setProperty("status", Utilities.jsonStringify(status));
}      

おつかれさまでした！このセクションのチュートリアルはこれで終了です。綺麗なチャートをもち、受信ボックスが読め、ユーザープロパティを持つJavaScripオブジェクトを格納できる スクリプトを作成できました。（これは今後再利用できます。）