In this step-by-step guide, I’ll show you how to build a Lookup Table generator in Google Sheets, utilizing Apps Script and the Google Tag Manager API.
The purpose of the Lookup Table generator is to automate the often tedious task of adding many, many rows to a Lookup Table within the Google Tag Manager UI. There are other solutions for this, but none (as far as I know) that uses the Google Tag Manager API.
Also, using Google Sheets is a no-brainer, because of the similarities between the Lookup Table variable and a spreadsheet. Both are organized into columns and rows. Google Sheets has a wonderful Apps Script integration, so interacting with the GTM API has been made very simple.
This tool was inspired by the work I’ve been doing with Swappie, a company from Finland that refurbishes second-hand smartphones and has a marketplace that makes buying and selling these phones easy and safe. One of their use cases for web analytics is to collect product margin information into their Ecommerce reports, and this requires a daily updated data sheet where the product SKUs are mapped to their current product margins.
The Simmer Newsletter
Follow this link to subscribe to the Simmer Newsletter! Stay up-to-date with the latest content from Simo Ahava and the Simmer online course platform.
Fetch the sheet template
The First thing you’ll want to do is fetch the sheet template. Visit this URL:
https://docs.google.com/spreadsheets/d/1LDJ6NOtMMx_5vwRBdPAx9V0gTLnce-7aglQzSm-Ja7M/
Then, click the File menu and select Make a copy.
This makes a copy of the sheet in your drive, and this new sheet is what you’ll be working on.
Configure the sheet
Next, configure the sheet. For the Lookup Table generator work optimally, you’ll need the following things:
- You should create a new Workspace just for the Lookup Table output. That way you don’t have to worry about messing with the Default Workspace (which should rarely, if ever, be used).
- You need to create the Lookup Table variable in the Workspace.
Once you have these two in place, you need to collect the Account ID, Container ID, Workspace ID, and Lookup Table variable ID. The easiest way to do that is to browse to Variables in the GTM UI, and then right-click the Lookup Table variable, and copy its link address.
If you explore that URL, it will look something like this:
.../accounts/23019854/containers/8060344/workspaces/1000128/variables/730
The Account ID is the first number in that URL, i.e. 23019854.
The Container ID is the second number in that URL, i.e. 8060344.
The Workspace ID is the third number in that URL, i.e. 1000128.
The Variable ID is the fourth number in that URL, i.e. 730.
Add these to sheet in their appropriate places. Note! You can also type default as the Workspace ID, and it will automatically fetch your Default Workspace, assuming that’s the workspace you want to work in. Again, I recommend against this practice. It’s not how workspaces should be utilized.
Add the Apps Script code
Next, in the Google Sheet, launch the Script Editor. Click Tools and then choose Script Editor.
If this is your first time using the Script Editor, you might need to jump through a few steps, but eventually you should see this:
The next thing to do is to rename the project. Click the Untitled project text in the top left corner, and rename the project to e.g. Lookup Table Generator. Once you click OK, the project will be saved, and you can access its details through script.google.com.
Now, delete all the code in the editor, and replace it with the code copy-pasted from this gist.
Remember to click the Raw button to get the code in plain text format, ready to be copy-pasted into the script editor.
Code walkthrough
I’m not going to step-by-step you through the code, but I’ll briefly introduce what each method does.
| Method | Description |
|---|---|
getIds |
Parses the Google Sheet for the IDs (Account ID, Container ID, Workspace ID, Variable ID) the user has added to their appropriate places. |
getDefaultWorkspaceId |
If the user typed default as the workspace ID, or if the workspace ID they gave does not exist, the sheet falls back to the “Default Workspace”. This method fetches its workspace ID. |
getLookupTable |
This method uses the Google Tag Manager API to fetch the Lookup Table variable the Variable ID points to. |
sendData |
The data in the Google Sheet is collected, mapped to its correct API resource format, and sent to Google Tag Manager. The contents in the sheet are used to update (i.e. replace) the Lookup Table variable contents in the GTM container. |
populateSheet |
This method uses the IDs (from getIds) to fetch the current contents of the Lookup Table variable into the Google Sheet. |
Finalize Apps Script configuration
In addition to the code, you also need to enable API access in the Script Editor. Click Resources and select Advanced Google services...
In the overlay that opens, scroll down to Tagmanager, make sure it has v2 selected as the version, and then enable it by clicking the toggle at the end.
Click OK when done.
Test with the data fetch
To quickly test if the code works, select the populateSheet function from the appropriate menu in the Script Editor.
Then, press the Play button just to the left of the “Bug” icon in the Script Editor toolbar.
The script should now prompt to request authorization. Click Review Permissions.
Follow the sign-in prompts until you see the “This app isn’t verified” prompt. This screen means that Google hasn’t verified this app yet. It’s using APIs that can be dangerous in the wrong hands, which is why Google warns about this app.
Since we are building this for internal use only, you don’t have to worry about this prompt. You can click the Advanced link and then the Go to your project (unsafe) link.
Finally, click the Allow button at the bottom of the last screen in the flow.
At this point, the script will run. Once it’s complete, you can step back into your Google Sheet to see it populated with the Lookup Table variable contents.
Test with the data update
Let’s try updating the variable with data from the sheet.
Do whatever modifications you want with the sheet content first.
Note! Do not touch the Input and Output headers. Start modifying the sheet from the row after these.
Next, go back to Script Editor, and this time choose sendData from the list of functions to run. Click the Play button again.
If all works well, you should now be able to see the modified Lookup Table variable in the workspace in the Google Tag Manager UI.
Map the buttons in the sheet
Finally, to make the sheet a bit easier to use, let’s map the two buttons in the sheet to their corresponding functions.
Right-click the Fetch data button, click the little action menu in the top-right corner and choose Assign script.
Type populateSheet in the prompt that opens and click OK.
Next, do the same for the Update data button, but instead of populateSheet type sendData into the prompt before clicking OK.
Now, whenever someone clicks the Fetch data button, the script will populate the sheet with the variable details (assuming the user who clicks the button has authorized access to Google Tag Manager). Whenever someone clicks the Update data button, the script will update the Lookup Table variable in Google Tag Manager with the contents of the sheet.
If you want to edit the button again, you need to right-click it, as left-clicking it will simply run the script.
Summary
I hope this proof-of-concept was easy to follow. With these steps, you can create a Lookup Table Generator for internal use.
There are limitations to how much the script can be run while unverified. You’ll want to take a look at this documentation to understand what steps need to be taken if you want to get rid of the security warnings.
Basically, if the script is only intended for occasional use by the developer (you), or if the script is meant to be used only by users within your G Suite organization, you don’t have to verify the app.
For any other type of extended use, the app needs to be verified, and this is an intricate, often tedious process.
Google Sheets + Google Apps Script is one of the most powerful, easily accessible automation flows out there. It requires minimal understanding of the Google Cloud Platform, as almost everything is abstracted in the Apps Script sandbox.