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.
2. Log in to the Google Cloud account (or create one) -> From the Navigation menu, click IAM & Admin -> Service Accounts -> Click Create service account.
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.
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.
5. Open stape.io sGTM container -> Upload a Key to the Google Service Account power-up.
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.
3. Select Create OAuth Client ID -> Click Configure Consent Screen.
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
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.
7. Once you are done with OAuth Consent Screen, go back to Credentials -> Click Create Credentials -> OAuth Client ID -> Select Web Application.
8. Add Authorized redirect URIs
https://app.stape.io/integration/google/callback if you use Stape Global and https://app.eu.stape.io/integration/google/callback if you use Stape EU -> Click Create.
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.
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.
3. Click Create Database -> select Start in production mode -> Click Next.
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.
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.
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.
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.
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.
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.
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.
Relevant posts
How to Set Up GA4 Tracking Using Google Tag Manager Server-Side Container
In this article, I will describe the advantages of using GA4 and GTM server tagging. You can use it as a step-by-step guide on how to send events to GA4 via the GTM server container. As for eCommerce/enhanced eCommerce, I will cover it in another article.
Updated Apr 6, 2022How to set up Snapchat Conversion API using server Google Tag Manager
Want to learn how to set up the Snapchat Conversion API using server Google Tag Manager? Look no further! In this blog post, I will talk about everything you need to know.
Apr 9, 2022How to connect Google Service Account to stape
Learn how to create a Google Service Account and connect it to your stape container in this step-by-step tutorial. Doing so will allow you to integrate server Google Tag Manager with BigQuery and Firestore.