Lessons learned connecting BigQuery to data sources in Google Sheets. APIs and tools change over time. Future-proof your integrations to protect against unintended consequences.

Google BigQuery and Google Drive logos
Google BigQuery and Google Drive logos

The ability to connect BigQuery to Google Sheets as a data source may be one of the most powerful, yet underutilized features for quickly and easily integrating small data into your enterprise data warehouse. For example, Google Forms is a quick and dirty way to securely obtain information from your users and supports storage in Google Sheets — which in turn can be exposed directly to BigQuery in realtime and without the overhead of ETL.

I had been successfully querying Drive data in BigQuery both at home and at work for quite some time — until it STOPPED WORKING…..

Identify the Problem

For…


Using a Raspberry Pi to interact with your Google Cloud Platform projects without having to expose your user credentials.

GCP and Raspberry Pi Logos
GCP and Raspberry Pi Logos

Install the Google Cloud SDK on a Raspberry Pi to access and interact with your Google Cloud Platform projects via a Service Account. In this example, we’ll create a Service Account with access to load speedtest result data into Google BigQuery.

Create the Service Account

Google Cloud official documentation for creating service accounts.

Using the Cloud Console, we’ll create a service account with access to load data into BigQuery for our particular project.

First — Navigate to the Cloud Console Service Account menu using the link below or by selecting IAM & AdminService Accounts.

https://console.cloud.google.com/apis/credentials/serviceaccountkey


Line graph of Min, Max, and Mean temperature over time
Line graph of Min, Max, and Mean temperature over time

I’ve been working on a few home automation projects using historical weather data from the Big Query NOAA Public Weather Dataset to schedule automations, estimate energy usage, or predict when I’ll get my next home heating oil delivery. To take things a step further, I need to know the average mean temperature on a given date.

To do this, we’ll take the last 20 years of NOAA weather data for a given weather station and calculate the mean mean ;) (average temperature by day using the mean temperature values per day). …


Google Sheets, Big Query, and Public Data Sets — Calculating Degree Days and K-Factor.

Car dashboard speedometer with focus on 99MPG fuel economy
Car dashboard speedometer with focus on 99MPG fuel economy

Have you ever wondered how home heating fuel companies know when to make a delivery? Even if you don’t get propane or home heating oil deliveries, you can still estimate your fuel usage over time by using the same formula used by energy companies by leveraging public weather datasets in BigQuery and your past heating bills.

Much like tracking fuel economy (measured by miles per gallon) in your vehicle, energy companies use a formula that relies on historical weather data (measured in degree days) and a…


Google Maps measuring the distance between 2 points with a weather icon.
Google Maps measuring the distance between 2 points with a weather icon.

Use BigQuery Public Datasets, Geography Functions, Wildcard Tables, and ARRAY_AGG & UNNEST to locate and query local historical weather data near any address.

While working on a smart home data project, I found myself searching for public weather data. It turns out that Google BigQuery has a ton of weather data in the NOAA GSOD (Global Surface Summary of the Day Weather Data) Public Datasets — comprised of daily weather data from approximately 30,000 distinct weather stations worldwide.

While it would have been easy enough to filter by country and state, then weed through the list until I found something…


Windows applications stacked
Windows applications stacked

With the sudden shift to remote learning in the spring due to COVID-19, Students and Educators were left scrambling to find ways to salvage the remainder of the school year. One of the biggest challenges was connecting students with the software tools installed on school computers — including specialized software that is often too expensive to distribute to students or too resource intensive to run on home computers.

Many schools quickly adopted expensive SaaS (Software as a Service) solutions that loaded some specialized software into cloud computing environments that Students and Teachers could access from home. Others cobbled together wonky…

Jake Holmquist

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