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.
What we’ll use:
First, setup your new Appscript 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.
Median received email for the last 3 months….
I wonder what that looks like for “unread” email in my inbox?
UPDATE JAN 18, 2021: Updated the process and repo code so that you only need to specify your GCP Project IDs, instead of needing to manually enter all your projects AND dataset IDs. So…it’s even easier now!
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…..
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 :-)
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.
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 could have followed Google’s documentation for a one-time CSV load approach: https://developers.google.com/apps-script/advanced/bigquery but….
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.
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…
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…
Enterprise Analytics Architect; Google, Box and some Internet2 Advisory Boards & working groups. Higher Ed IT since 2002. @techupover and @usaussie on twitter