Automatically collect stats about your Gmail account every hour

I wanted to get this written before 2021 starts, for people who want to start collecting for a full calendar year…..

Example breakdown from stats collected using this method

If you want to easily collect stats about your gmail account, this will show you how (using the code shared below) with just a little Appscript, and a Google sheet. If you’re not a developer, or code scares you…don’t worry… you only need to change one URL to point to your own sheet, and that’s it. Everything else is ready for you to copy-paste and run. It’s only 75 lines of code to begin with anyway :-)

Note: This will work for personal/consumer gmail accounts, as well as organizational accounts.

First….Prep your Google Sheet…

  • Create a new google sheet (http://sheets.new)
  • Create a new tab, so you have a total of 2 tabs
  • Optional: Name your tabs something like “Columns” and “Dimensions”, but you can totally leave them as Sheet1 and Sheet2.

Next…Prepare your appscript project…

  • Open a new tab and go to http://script.google.com and create a new project
  • Name the project something like “gmail stats”
  • Open the existing Code.gs file (or create a new one)
  • Copy the code from the Code.gs file in this git repo
  • Go back to your google sheet and copy the URL.
  • Update the variables at the top of the Code.gs file to point to your own Google Sheet, and tab names.
  • Optional: If you are using an organizational account (like for school or work) then you can change the queries that populate the “from my domain” stats to use something other than gmail.com. Look at line 34 and 35 of the Code.gs file and just change the references for gmail.com to your own domain.

If you are on the “old” appscript IDE, you may need to add the Gmail Library. If you’re on the “new” interface, then skip to the next section to run it for the first time.

The new IDE automatically does this for you…but just in case….click “resources” then toggle the Gmail Api to “on” (as pictured below):

Use the Resources menu to add the Gmail API manually in the legacy IDE
Toggle the gmail api to on

Then…Grant permission for your code to access your account..

This is necessary to allow your own appscript project to have permission to access your gmail (to run queries) and your google drive (to write the results to a sheet). Note…if you give someone else access to your appscript project, you need to trust them :-)

Click the function drop down, and choose “set_sheet_headers”

This will prompt you to permit the script/project to run on your account. It will ask you to grant permissions to Google Drive and Gmail, and look something like this:

Click “Advanced”

Click “Go to Gmail Stats” (unsafe) .. or whatever your project name is.

Click “Allow” …

This will start the process of running the sheet_headers code.

Then…Check your Google Sheet for success!

Open your google sheet, and you should see a header row in both sheets. If so…you’re now ready to run the gmail stats function.

Successful stats written to your google sheet

Getting gmail stats for the first time…

Click the function drop-down and choose “getGmailStats” then click to run it.

Wait a few seconds, then check your google sheet. You should have data in both sheets. One of them with several rows (one per dimension), and the other with a single row with multiple columns.

Finally…Setting it to run automatically…

Now that it ran once, you can set a trigger to run every hour. Click the triggers icon in the legacy/old interface (or left menu option if in the new interface).

Create a new trigger to run every hour:

Create a trigger to run the getGmailStats function every hour every day.

That’s it!

You can now sit back and watch the stats roll in.

Optional/Note: If you changed your trigger to run on a different schedule, just make sure you alter the code and sheet columns to reference a different timeframe. IE: change the queries that say “newer_than:1h” to something like “newer_than:24h” for a 24 hour period instead…and then change the columns in the sheet to use 24h instead of an hour.

BONUS POINTS….Create a dashboard!

Use Google Data Studio to create a dashboard to show off your new shiny metrics.

quick dashboard using the data collected on my own gmail account for the last week or so

That’s really it now :-)

Happy New Year!

Enterprise Analytics Architect; Google, Box and some Internet2 Advisory Boards & working groups. Higher Ed IT since 2002. @techupover and @usaussie on twitter

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store