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!
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.
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.
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.
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) -> 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.
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.
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.
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.
All it takes is a few simple questions. Click Get A Quote, fill-up the form, and we will send you a quote.