Google Apps Script

Tutorial: Creating Gmail Inbox Statistics Report

Editor's Note: Romain Vialard is a Google Apps Script Top Contributor. He has contributed interesting articles and blog posts about Apps Script. - Saurabh Gupta

October 2011

Goal

The tutorial shows how to setup gmail to send you an email at the beginning of each month showing statistics for the previous month, such as the number of emails you received, the top 5 to whom you sent email, the top 5 from whom you received email, charts on your daily usage.

Time to Complete

Approximately 10 minutes

Prerequisites

This tutorial will be easier to understand if you are already familiar with the following:

  • Gmail Services
  • Charts Services
  • JavaScript objects
If you're not, don't worry. We'll use them as we go along.

Overview

  1. Setting Up the Spreadsheet and Script
  2. Adding a Trigger
  3. How the Script Works
  4. Summary

Setting Up the Spreadsheet and Script

  1. Open a new spreadsheet in Google Docs.
  2. Save the spreadsheet by giving it the name Gmail Meter.
  3. Choose Tools > Script Gallery.
  4. On the Script Gallery dialog box, locate the the Gmail Meter script by typing in the search term "Gmail Meter"
  5. Click the Install button for Gmail Meter. You see a message that the script was successfully installed.
  6. Click Close.

Adding a trigger

To get your report by email, you need to set up a time-driven trigger.
To install a trigger:

  1. In the spreadsheet, choose Tools > Script Editor.
  2. In the script editor, open the Resources menu and click Current project's triggers... The Current Project's triggers dialog box opens.
  3. Click the link saying No triggers set up. Click here to add one now. A dialog box asking you to set up triggers opens.
  4. Ensure that the function activityReport and the event Time-driven are selected.
  5. Select Minutes timer > Every 5 minutes.
  6. Click Save. A pop-up box opens, asking for authorization to access the Gmail and Google Calendar services.
  7. Click Authorize.

Every 5 minutes, the script will read 100 of your conversations from last month. If you had only 100 conversations last month, you will receive your report by email in 5 minutes after installing the trigger. If you have 600 conversations, then the script will read 600 conversations in 30 minutes and send you a report.

You will receive a new report at the beginning of each month.

How the Script Works

To see the code, go to the Gmail Meter spreadsheet you saved in the previous section, and click Tools > Script editor.

The activityReport() function checks the date and the status of the report. If the report has already been sent for this month, it won't be sent again. If the month has changed, the function starts to generate a new report.

init_() is triggered by activityReport() at the beginning of each month to clear the data stored. The statistics script cannot process all of your emails in one pass, so it stores data from your Gmail inbox and uses a time-driven trigger to govern the processing. The trigger enables the script to process 100 conversations at a time with pauses between each 100. A user property is used to store the information from each batch (the number of emails sent and received, to whom,...). The init_() function clears the stored data at the beginning of each month and is triggered by activityReport().

The following code block defines the data to store as a script property.

var variables = {
    range: 0,
    nbrOfConversations: 0,
    nbrOfEmailsPerConversation: nbrOfEmailsPerConversation,
    // ...
};
// ...
ScriptProperties.setProperty("variables", Utilities.jsonStringify(variables));

The script uses a JavaScript object to store different information in a single variable. The script then performs a JSON encoding to save the object as a user property.

The fetchEmails_() function is triggered by activityReport() to work on 100 new conversations.

The following code block decodes the user property to retrieve the data stored and do a search in Gmail to get new conversations.

var variables = Utilities.jsonParse(ScriptProperties.getProperty("variables"));
if (!customReport) {
    var query = "after:" + variables.year + "/" + (variables.previousMonth + 1) + "/1";
    query += " before:" + variables.year + "/" + (variables.previousMonth + 1) + "/31";
}
else {
    var previousMonth = new Date(new Date().setMonth(new Date().getMonth() - 1));
    var query = "after:" + Utilities.formatDate(previousMonth, variables.userTimeZone, 'yyyy') +
        "/" + Utilities.formatDate(previousMonth, variables.userTimeZone, 'MM') + "/1";
    query += " before:" + Utilities.formatDate(new Date(), variables.userTimeZone, 'yyyy') +
        "/" + Utilities.formatDate(new Date(), variables.userTimeZone, 'MM') + "/1";
}
query += " in:anywhere -label:sms -label:call-log -label:chats -label:spam -filename:ics";
query += " -from:maestro.bounces.google.com -from:unified-notifications.bounces.google.com -from:docs.google.com";
query += " -from:group.calendar.google.com -from:apps-scripts-notifications@google.com";
query += " -from:sites.bounces.google.com -from:noreply -from:notify -from:notification";
// ...
var conversations = GmailApp.search(query, variables.range, BATCH_SIZE);

The sendReport_() function is triggered by fetchEmails_(). When every conversation from the previous month has been read, the fetchEmails_() function triggers the sendReport_() function. The script builds the report from the information stored in the JavaScript object and uses the Chart Service to create the charts.

The following code creates a chart showing the number of emails received every day:

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).toString(), variables.dayOfEmailsReceived[i], variables.dayOfEmailsSent[i]]);
}
dataTable.build();
var chartDate = Charts.newAreaChart().setDataTable(dataTable).setDimensions(650, 400).build();

To add this chart to the email body, we use an advanced parameter of the method sendEmail(): inlineImages.

The following code adds the charts to the email body and sends the report:

var inlineImages = {};
inlineImages['Averageflow'] = chartAverageFlow;
inlineImages['Date'] = chartDate;
inlineImages['DayOfWeek'] = chartDayOfWeek;
if (status.customReport) {
  inlineImages['Months'] = chartMonths;
}
if (variables.user.search(/gmail|googlemail/) == -1) {
  inlineImages['IntExt'] = chartIntExt;
}
inlineImages['Location'] = chartLocation;
inlineImages['Conversation'] = chartConversation;
inlineImages['WaitingTime'] = chartWaitingTime;
inlineImages['MessagesLength'] = chartMessagesLength;
if (variables.nbrOfAttachmentsReceived > 0) {
  inlineImages['AttachmentsTypes'] = chartAttachmentsTypes;
}
if (variables.user.search(/gmail|googlemail/) == -1 && variables.nbrOfAttachmentsReceived > 0) {
  inlineImages['IntExtAttachments'] = chartIntExtAttachments;
}

MailApp.sendEmail(variables.user, "Gmail Meter", report, {
  htmlBody: report,
  inlineImages: inlineImages
});

Summary

Congratulations! You've completed this tutorial. You should now be able to write scripts that store JavaScript objects as user properties for further reuse, create beautiful charts, and read your mailbox.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.