Adding to Google Tag Manager

One of my colleagues at Adwise recently told me about By adding a small code snippet to your HTML, you get just in time preloading to decrease latency. It’s really easy to implement and free to use!

Of course we could manually add this code snipped to the page, but since we use Google Tag Manager for most of our clients we thought it would be even quicker and easier if we could just add the code snippet to a Custom HTML Tag in GTM. However, if we add the snippet and try to publish the container we get an error…

Tag Manager error

Luckily, there’s a blog post by Simo Ahava for almost every Google Tag Manager issue you can think of and this is no exception. About a year ago Simo wrote a blogpost about adding HTML Elements to the page programmatically and it introduces a workaround for the error message we got. Using his instructions, I rewrote the snippet so it can be added to the page through a Custom HTML Tag. It looks like this:

It can then be added to Google Tag Manager like this:

Add the rewritten snippet to a Custom HTML Tag.

And the resulting rendered HTML looks like this (you might have to click the image to zoom in a bit):

rendered HTML
The resulting HTML.

Pushing Google Sheets content to BigQuery

I’ve decided to dust off my blog. After several years of neglect, I thought it would be nice to try to do something useful with it again. For my little coding projects, I combine and use lots of code from others. I guess the least I can do is try to share some of my code and ideas in return.

But first I should probably warn you: I’m not an educated developer. Most of my coding skills are self-taught through websites such as CodeCademy, Udemy, and DataCamp, among others. So if you have any remarks, improvements or tips, please feel free to reach out.

How to push data from Google Sheets to Google BigQuery

The problem: At work, we have access to a Google Sheets spreadsheet with data that is refreshed daily (our agency Google Ads HI Score spreadsheet for those who are familiar with it). Unfortunately, we are not in control of the spreadsheet. So if we want to save each day’s content we must copy it to another sheet. However, if you do this daily, you quickly reach limits such as the maximum number of cells a spreadsheet can contain. That’s where Google BigQuery comes in.

The solution: With Google Apps Scripts we can access the BigQuery API to push data to BigQuery. We can also use Google Apps Scripts to access the content of Google Sheets. By combining these two it’s possible to push data from Google Sheets directly to Google BigQuery.

There are several examples to be found on how to push a CSV file to Big Query using Google Apps Scripts. There are also several blogs about downloading BigQuery results to a Google Sheets spreadsheet. We want to do the opposite though, and blogs and examples about pushing the content of a Google Sheets spreadsheet to Google BigQuery are more sparse. At least, I didn’t find any (free) useful examples. That’s why I wrote something that is based on two examples that sort-of do what I was looking for, but I adapted them to my needs.

I used these examples:

This script can be used as a Google Apps Script. The easiest way is to open the Google Sheet and go to the script editor through the menu bar.

In the script editor, you must enable the BigQuery API through the Advanced Google Services. You can then set a schedule for the Google Apps Script to fetch the content of the Spreadsheet and push it to Google BigQuery.

You can find the code below or check the repository at GitHub.