What if you wanted to get prompted in Google Chat when you get an email from your boss, your wife, a particular application? Using AppScript, we can perform custom gmail searches, then based whether there’s a result, get a Google Chat notification. This will take all of 5 mins to set up.

Example Google Chat card-formatted notification

What we’ll use:

  • Go to script.google.com
  • Create a new project

Back in December, I started automatically collecting stats about my personal and work Gmail accounts every hour…of every day….of every week. It started as pure curiosity to see if the Gmail API was easy to work with in AppScript (it is), but turned into figuring out things like how much email I churn through, and when the busiest times of the day were.

Received email

Median received email for the last 3 months….

Pretty consistent across most hours of the day.

I wonder what that looks like for “unread” email in my inbox?

Collect table size and row counts from your BigQuery projects automatically, and store it all in BigQuery and/or Google Sheets.

tell me more…?

I wanted to see if it was possible to easily automate the process for tracking how my different BigQuery datasets and tables changed over time. At some point, I’ll also add in the ability to get email or Google Chat notifications when a threshold has been breached (table increased/decreased too much in between checks), but that’s for another day. …

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…

Code snippet from repo shared below

I realized lately that I want to mess around with visualizations in Google Data Studio, but I want it to be with data that I understand. That way, I can more easily tell if there’s something wrong or needing adjustment.

Another day, another AppScript experiment.

UPDATE: December 4th, 2020: The code in the repo now supports Google Shared Drives, thanks to the moveTo() method replacing the addFile() method.

— —

I wanted to load CSV files into BigQuery as a way of doing some quick aggregation & automation, without the heavier lifting of pubsub/cloud functions/data flow etc. I wanted to do a first pass on a lower-priority project and also test it on some personal data to experiment within the free tier.

I guess if you want a non-scalable one-time way of doing this…

Screenshot from Google’s documentation at https://developers.google.com/apps-script/advanced/bigquery

I could have followed Google’s documentation for a one-time CSV load approach: https://developers.google.com/apps-script/advanced/bigquery but….

Nah, let’s automate (a little)

I wanted to add a little automation to the…

As is typical lately, i got curious about whether i could get AppScript to play nice with a non-Google external REST API to pull in some information to Google Sheets. So…i started playing with the xMatters API (https://help.xmatters.com/xmapi/index.html#xmatters-rest-api)


Well….sometimes you want to pull information out for ad-hoc reporting, or long-term storage of certain info…rather than being dependent on a vendor’s retention schedule. Other times, you need to provide information outside the product itself to non-licensed users. Or maybe you need to display the data a little bit differently than it is shown in the platform itself. Or what about merging…

This one’s simple….. I was curious how much time I was spending in meetings…..how many people I usually meet with, who I meet with the most, and which meetings are the most frequent.

So…just like the quick appscript things I threw together for Google Meet, Chat, and Drive…I spent about 90 mins last night putting this together:

High level overview — AppScript code to query your google calendar and pull data into a Google Sheet, which can then be analyzed or visualized in Google Data Studio.

I was able to aggregate the last 7 years of my Google Calendar data…

Sample metrics from a dashboard that uses data from this script/process (Meetings, Total Duration, Total Attendees, Unique Attendees)

UPDATE: December 8th, 2020 — The code now supports moving the processed files into a Shared Drive as well as regular Google Drive, thanks to the moveTo() method replacing the now-deprecated addFile() method.

Google Meet (meet.google.com) recently added a feature where it will send the meeting creator/owner a CSV for meeting details, which includes:

— Attendee Name, Attendee Email, Attendance Duration, Time Joined,Time Exited

I wanted to aggregate this information for all my meetings though, so i could see how much time i’m spending, who I’m spending it with, which people attend my meetings most often etc. I mean, who…

  • UPDATE 9/20/20: Updated the code to combine folder/file info into a single results sheet AND made it work a LOT faster by changing the appendRow method. Now processes about 1500 files per 5 min run, where originally it only audited 600'ish.

Nick Young

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