Read data from Google Sheet to server Google Tag Manager

Author
Stape
Published
Apr 23, 2022
Also available in

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

     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.

import google sheet variable

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.

google sheet reader to server gtm

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

set up google spreadsheet reader variable 

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

test google spreadsheet reader variable

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. 

object property extractor variable

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.

read data from google sheet to server google tag manager

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!

Need help with setting up server-side tagging?

All it takes is a few simple questions. Click Get A Quote, fill-up the form, and we will send you a quote.

Get a Quote
Tagged with:GTM

Host your GTM server at Stape