Read data from Google Sheet to server Google Tag Manager

Server Google Tag Manager can accept data from multiple sources like websites, CRMs, webhooks, etc. With the latest integration of Firestore and sGTM, we now have even more opportunities for data sources. Stape created a variable that adds Google Sheet to this list - you can read values from cells or ranges in your sheet and use it as an input into Server GTM.

We already have a blog post that explains how to write data from sGTM to Google Sheet. This article will show how to read data from Google Sheet and use it inside server Google Tag Manager.

How does Spreadsheet Reader variable workCopy link to this section

There are three types of actions that this variable can do:

1. Read cell. Reads and returns data from one Google Sheet cell.
2. Read Range. Reads any field range within Google Sheet. Variable returns arrays of values.
3. Read Two Columns. Add a range that includes two columns. Variable returns an object that consists of these two columns. The first column is used as a name, and the second - correspondent value.

Spreadsheet Reader variable use casesCopy link to this section

The first use case that comes to my mind is enriching sGTM using a Google Sheet table. Let’s say there is a Google Sheet document with a user ID and user email. You can get a user ID in sGTM. With the help of Spreadsheet Reader, you can enrich data with user email by finding the corresponding user ID in Google Sheet. It will help enhance user parameters, for example, for Facebook conversion API, increasing the match quality score of Facebook conversion API events.

What do you need to use Spreadsheet Reader variable?Copy link to this section

     1. Server Google Tag Manager container

     2. Google Service Account

     3. Google Sheet Authentication

     4. Firebase account

How to set up Spreadsheet Reader variable?Copy link to this section

1. Download Spreadsheet reader variable from GitHub or add it from the sGTM Template Gallery -> Import Spreadsheet variable to server Google Tag Manager by clicking Templates -> New Variable Template -> Click three dots in the top right corner -> Click import -> Add template that you’ve recently downloaded -> Click Save.

2. Create a new variable -> Select action type; in this guide, I will show how Read Two Columns option works -> Add columns range. This method works only with two columns and returns an object that consists of these two columns. The first column will be used as a name, and the second column will be used as a correspondent value. 

This is how my Google Sheet looks.

I’ve added range A1:B4 to the Spreadsheet Reader variable. 

3. Add Spreadsheet URL -> Add API Refresh Token, Client ID, and Client Secret. 

4. Test Spreadsheet Reader variable. In my case, it looks like this: 

5. In sGTM, I know the product name and want to add productID using Google Sheet table. To do this, I will use the Object Property Extractor variable. In the Object, select your Spreadsheet reader variable that captures data from Google Sheet. In Property, I will choose the variable that records the product name. 

6. Test object property extractor. I’ve added to cart a product named Beanie, and the Object Property Extractor variable returned 1. It corresponds to the productID for this product according to Google Sheet.

Conclusion:Copy link to this section

The ability to read data from Google sheet to server Google Tag Manager gives an excellent opportunity to enrich sGTM data. In this guide, I've shown how you can use the Spreadsheet reader variable and enrich server Google Tag Manager with your data by reading it from a spreadsheet.

If you need help setting up server-side tracking, please don’t hesitate to contact us. We would be happy to assist you in getting this set up and ensure that your data is being properly collected and processed. Thanks for reading!