Read data from Google Sheets 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. 

The Google Sheets Reader variable is a custom server Google Tag Manager variable created by Stape that allows reading data from Google Sheets. You can use it to pull values from single cells, full ranges, or two-column key-value pairs and enrich your server-side tracking setup.

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

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

1. 1.
   Read Cell retrieves the value from a single specified cell in a Google Sheet and returns it for use in server Google Tag Manager.
2. 2.
   Read Range retrieves data from a specified range of cells in a Google Sheet and returns it as an array of values.
3. 3.
   Read Two Columns reads a two-column range and returns an object where the first column contains keys (names) and the second contains their corresponding values.

The first use case that comes to mind is enriching sGTM data from Google Sheets. Let’s say there is a Google Sheets with User ID and user email. You can get a User ID in sGTM. With the help of Google Sheets 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 the Facebook Conversions API, increasing the match quality score of Facebook Conversions API events. 

If you're a Stape user, setting up the Google Sheets Reader variable is simple. Stape handles Google authentication internally, so you don’t need to upload any service account keys manually.

Here’s what you need:

1. 1.
   Server Google Tag Manager container. You need an active Server GTM container hosted on Stape. If you don’t have one, you can create it from the Stape dashboard.
2. 2.
   Google Sheets Connection. To authorize access to Google Sheets, go to the Connections tab in your Stape account and click Sign in with Google Sheets. Use the Google account that has access to the spreadsheet you want to read from. After authorization, this connection will be available in your server container.
3. 3.
   Google Sheets Reader variable setup. Add the variable template from the sGTM Template Gallery; or manually import it from Stape’s GitHub repo (Go to Templates → Variable Templates → New → Import → Select the .tpl file).
4. 4.
   Test your setup. Use Server GTM’s Preview mode to verify that the variable returns the expected data. The output will depend on the action type you've configured.

The variable will now be able to fetch and return spreadsheet values, which you can use in other variables, tags, or enrichments inside sGTM.

If you're not using Stape's built-in authentication, you'll need to set up manual access to your Google Sheets using your own credentials. Here's how:

1. 1.
   Enable Google Sheets API. Go to Google Cloud Console, open your project, and enable the Google Sheets API. This gives your service account permission to interact with spreadsheets.
2. 2.
   Create a Google Service Account. In your Google Cloud project, navigate to IAM & Admin → Service Accounts, then create a new service account. Once created, generate a Private key of JSON type - this file will be used later in Server GTM for authentication.
3. 3.
   Connect the Google Service Account to Stape using the power-up. In your Stape account, click on the Google Service Account power-up and upload the JSON key generated in the previous step.
4. 4.
   Share your spreadsheet with the service account. Open the Google Sheets you plan to use. Share it with the service account email (e.g., your-service-account@your-project.iam.gserviceaccount.com) and grant Viewer access. This is required for the variable to read data from the spreadsheet.
5. 5.
   Google Sheets Reader variable setup. Add the variable template from the sGTM Template Gallery; or manually import it from Stape’s GitHub repository (Go to Templates → Variable Templates → New → Import → Select the .tpl file). Create a new variable using this template and choose the desired action type (Read Cell, Read Range, or Read Two Columns). Provide the spreadsheet URL and range, then select Stape Google Sheets Connection as the authentication method. Enter the spreadsheet URL and data range. For Authentication, choose Own Google Credentials.
6. 6.
   Test your setup. Use server GTM’s Preview mode to verify that the variable returns the expected data. The output will depend on the action type you've configured.

1. Follow one of the guides in the section above (for Stape users or for non-Stape users).

2. In sGTM, create a new Google Sheets Reader variable. In this guide, we will show how the Read Two Columns option works. After selecting this option, add the 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 corresponding value.

This is how our spreadsheet looks.

3. Add spreadsheet URL and range.

We’ve added this range A1:B4 to the Google Sheets Reader. 

4. Test the Google Sheets Reader variable. In our case, it looks like this:  

5. In sGTM, we know the product name and want to add product ID based on data from a spreadsheet. To do this, we will use the Object Property Extractor variable. In the Object, select your Google Sheets Reader variable that captures data from Google Sheets. In Property, we will choose the variable that records the product name.

6. Test Object Property Extractor. We’ve added a product named Beanie to cart, and the Object Property Extractor variable returned 1. It corresponds to the product ID for this product according to Google Sheets.

Integrating Google Sheets with Server Google Tag Manager opens up powerful opportunities to enrich your data using external sources like product IDs, user attributes, or custom mappings.

In this guide, we walked through how to use the Spreadsheet Reader variable to pull data from a Google Sheet and enhance your sGTM setup.

If you need help setting up server-side tracking, feel free to reach out - we’re happy to assist and make sure your implementation runs smoothly. Thanks for reading!