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…..
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):
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.
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:
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.
That’s really it now :-)
Repo Code shared here:
Happy New Year!