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…
Update September 27th, 2020: Added support for ATOM feeds as well as RSS (the structure is different). The github repo is updated, and all it requires is a new column in the google sheet.
Not too much to this process…i just wanted to see if i could get RSS notifications from various sources into Google Chat without too much overhead. I assume at some point there will be a built-in bot for this provided by Google, but for now, all it takes is a bit of AppScript and about 5 minutes to set up.
Before we get started, note that…
I use Google Data Studio to create dashboards, and like many others, I want to know which dashboards are being used, by who, from where, on which devices. You know….usual Google Analytics website stuff.
It’s a good thing that Google provides the two main things to solve this.
Enterprise Analytics Architect; Google, Box and some Internet2 Advisory Boards & working groups. Higher Ed IT since 2002. @techupover and @usaussie on twitter