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

tell me more…?

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…..

Image for post
Image for post
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…


Image for post
Image for post
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…

Image for post
Image for post
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…


Image for post
Image for post

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)

WHY?

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…


Image for post
Image for post

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…


Image for post
Image for post
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.

There are several 3rd party services to audit your Google Drive content….but I wanted a method that didn’t give full access to my files. …


Screenshot of a sample notification from the Box Status page as it appears in Google Chat.
Screenshot of a sample notification from the Box Status page as it appears in Google Chat.

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…


Image for post
Image for post

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.

  1. Add Google Analytics tracking codes to your Data Studio Dashboard
  2. Connect to Google Analytics data from Data studio with the prebuilt data connector

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