Write data from server Google Tag Manager to Google Sheets

Author
Stape
Published
April 16, 2022
Also available in

Google Sheets is a cheap and easy-to-use platform to collect and manage data. Though there are a lot of fancy CRM systems, around 40% of markets and salespeople prefer recording website leads, sales, and campaign results in Google Sheets. 

Connecting a website with Google Sheets allows you to automatically track any user interaction,  or conversion inside the spreadsheet, making it easy for your sales and marketing team to operate and analyze data.

Previously the easiest and fastest way to send data from websites to Google Spreadsheets was using tools like Zapier. The biggest concern about using Zapier is price. The free plan includes up to 100 tasks (records) per month. 

Stape team created Google Sheets tag for server Google Tag Manager. This blog post will show you how to write data from server Google Tag Manager to a Google Sheets using the Google Sheet tag for sGTM. Let's get started!

How can you use the Google Sheets tagCopy link to this section

You can pull any data available in sGTM to Google Sheets. It can be event data, user data, website data, webhooks, etc. 

The first use case that comes to my mind is recording lead or sales data to the spreadsheet. You can add order information, traffic source, and UTM tags to the CRM. 

Google Sheets tag can also be helpful when debugging the sGTM setup. If you have some issues or doubts if the tag works correctly, you can send data to Google Spreadsheet to ensure all parameters were sent correctly. For example, let’s say you’ve implemented FB CAPI and think that sGTM does not send fbc parameters to FB, which affects conversion attribution to your FB campaigns. In this case, you can use the Google Sheets tag to add order IDs and fbc parameters to Google Sheets and see which orders were made after seeing a FB ad. 

How Google Sheets tag worksCopy link to this section

You need a Firestore account and Google Spreadsheet authentication to set up a Google Sheets tag. Stape users can set up Google Sheets authentication using tools in stape.io admin. 

Google Sheet tag uses Firestore to handle Google Spreadsheet Authentication. 

With the help of this tag, you can add a row or update a cell in the Google Sheets. 

What do you need to set up Google Sheets tagCopy link to this section

1. Google Service AccountCopy link to this section

1. Go to your stape.io account -> Open sGTM container -> Click Power-Ups -> Click Config Google service account.

google service account stape

2. Log in to the Google Cloud account (or create one) -> From the Navigation menu, click IAM & Admin -> Service Accounts -> Click Create service account. 

create google service account stape

3. Add account -> Click Next -> Select Roles. To set up the Google Spreadsheet tag, we need to use Firebase. Select Cloud Datastore User role for Firestore. If you want to integrate sGTM with BigQuery, select the BigQuery Data Editor role. On the third step, click no need to select anything; just click Done. 

set access google service account

4. Open Google Service Account you’ve recently created -> Open Keys tab -> Click Add Key -> Select JSON type in the popup -> Click create. A file will be downloaded to your computer. 

download key google service account

5. Open stape.io sGTM container -> Upload a Key to the Google Service Account power-up. 

upload google service account key to stape

2. Google Sheet AuthenticationCopy link to this section

1. Log in to your Google Cloud Account.

2. Select API & Services form the main menu -> Click Credentials -> Click Create Credentials.

create credentials Google Sheet Authentication

3. Select Create OAuth Client ID -> Click Configure Consent Screen.

create consent screen Google Sheet Authentication

4. Select Internal - if Google Sheet is located inside your google cloud organization. External - if it’s outside the Google Cloud organization.

5. Enable Google Sheets API using this link https://console.cloud.google.com/apis/library 

enable google sheet API

6. Click Add or Remove Scopes -> Select Google Sheets API. If you do not see Google Sheet API, then make sure you’ve enabled Google Sheet API. Add a Test user if needed. Click Save. 

select Google Sheet API

7. Once you are done with OAuth Consent Screen, go back to Credentials -> Click Create Credentials -> OAuth Client ID -> Select Web Application

select google web application 

8.  Add Authorized redirect URIs https://app.stape.io/integration/google/callback (if you use stape) -> Click Create.

Add Authorized redirect URIs 

9. You will see your Client ID and Client Secret on the next page. Make sure to copy these values.  

10. Go to your app.stape.io account -> Click Tools -> Select Google Auth Helper -> Add Client ID -> Click Start Authorization.

set google app verification stape

11. Select the Google Account -> On the second step, add Client ID, Client Secret, Auth Code will be generated for you -> Click Generate Refresh Token -> Copy refresh Token. 

3. Firebase accountCopy link to this section

1. Open Firebase -> click Create Project -> Select the same project you’ve used to create Google Service Account  -> click Continue -> Click Add Firebase on the third step.     

2. Click Build -> Firestore Database. 

create firebase database

3. Click Create Database -> select Start in production mode -> Click Next.

set up firebase database

4. Select Cloud Firestore location, it should match your sGTM server location. To check the sGTM server location, go to your stape.io account -> open the sGTM container -> check Server Location. To match the sGTM server location with Firebase, please check this link.   

Select Cloud Firestore location

5. Click Start a collection. Here you will need to set Firebase Path that lately will be used in Google Spreadsheet Tag. I’ve added Firebase Path to stape-demo/spreadsheet-auth -> Click Save.

set firebase path
set firebase path server GTM 

How to set up Google Sheets tagCopy link to this section

2. Send data to the server GTM container. The two most popular sending data to sGTM are Google Analytics 4 and Data Tag/Data Client.

3. Download Google Sheets tag from GitHub or add it from the sGTM template Gallery -> Open templates sections in the server Google Tag Manager container -> Click New.

4. Click three dots in the top right corner -> Click Import -> Select the Google Sheets tag template you’ve recently downloaded from GitHub -> Click save.

import Google Sheet tag sGTM

5. Create a new tag. Tag Type Sheets (the one you’ve recently uploaded to sGTM). This tag allows Adding a row or Updating cells -> Select Rows range -> Add Sheets URL -> Add API Refresh Token, Client ID, and Client Secret.

We’ve shown how to create API Refresh Token, Client ID, and Client Secret using stape earlier in this article. 

Add a trigger.

set Google Sheet tag sGTM

6. Add cell value that should be sent to Google Sheets. Add Firebase Path. I’ve shown you how to create it here

7. Test tag. You should see it triggered correctly in the server GTM debugger, and the row was added to the Google Sheets. 

test Google Sheet tag sGTM
test Google Sheet tag sGTM preview

Conclusion:Copy link to this section

By connecting Google Sheets to your website, you can manage marketing campaigns and track conversions quickly. It will provide complete insight into which channels generate qualified prospects and act on new conversions faster.

If you need help setting up server-side tracking, please don't hesitate to contact us. We're happy to help! In the meantime, be sure to check out our other blog posts for more tips and tricks on using server Google Tag Manager.

Tagged with:sGTM tag

Host your GTM server at Stape

By signing up you agree to Stape’s Terms of Use and Privacy Notice