Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script to automatically get stats (count of threads, age in days of oldest message) on Gmail inbox
// Google Apps Script to automatically get stats within a Gmail inbox
// Stats logged:
// - current date & time
// - username / email address
// - # of message threads in inbox
// - Age in days of oldest message
// 1. Visit https://script.google.com and create a new project
// 2. Name the project
// 3. Copy and paste this gist
// 4. Create a new Google Spreadsheet or reuse one
function inboxStats() {
// get email address of user's inbox
var userName = Session.getEffectiveUser().getEmail();
// 5. Create a new Google Spreadsheet or reuse one and copy the URL
// 6. Paste the URL of the gsheet below
gsheetURL = 'gsheet-URL-goes-here'
var dataSs = SpreadsheetApp.openByUrl(gsheetURL);
// 5. Set the sheet name on the gsheet, copy and paste it below
var sheetName = 'inboxStatsLogs'
var sheet = dataSs.getSheetByName(sheetName);
var now = new Date();
var oldest = now;
var pageSize = 50;
var start = 0;
var threads;
do {
threads = GmailApp.getInboxThreads(start, pageSize);
threads.forEach(function(thread) {
oldest = thread.getLastMessageDate() < oldest ? thread.getLastMessageDate() : oldest;
});
start += pageSize;
Utilities.sleep(1000);
} while(threads.length > 0);
// calculate age of oldest messag in days
var ageOfOldest = dateDiffInDays(oldest, now);
// get all threads in inbox as an array in order to count
var threadsCount = GmailApp.getInboxThreads();
// Add a row of the spreadsheet's first sheet and include the following data:
// current date & time | username / email address | # of message threads in inbox | Age in days of oldest message
var logSheet = dataSs.getSheets()[0];
logSheet.appendRow([now, userName,threadsCount.length,ageOfOldest]);
};
// Test
// 7. Test the script: Run > Run function > inboxStats
// Scheduling
// 8. Create triggers for the frequency you would like such as hourly or start & end of each day
// Utilities
// compare dates only, ignore time of day
function dateDiffInDays(d1, d2) {
return Math.round((datetimeToDate(d2) - datetimeToDate(d1)) / (1000 * 60 * 60 * 24));
}
// Create a date from a date-tiem
function datetimeToDate(d) {
return new Date(d.getYear(), d.getMonth(), d.getDate());
}
// Thanks
// Mark Wilson for the date of oldest message technique
// https://github.com/mddub/google-apps-scripts/blob/master/log_oldest_email_age.gs
@RandyVogel

This comment has been minimized.

RandyVogel commented Apr 10, 2018

Rows 20 and 24 don't need terminal semicolons?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment