Automate GTM's Lookup Table Macro From A Spreadsheet
that works with Google Tag Manager Version 2.
Get it? It’s an upside-down house. She’s looking up at a table!
Earlier this year, Google Tag Manager added a great new feature called the Lookup Table Macro. It’s pretty simple to use. Populate the table with two columns of data. Feed a value into it using a macro and if it finds that value in the left column, it will return the value next to it. This useful macro isn’t meant to replace complicated Javascript switch statements or advanced regular expressions. But it can take a large list of data and save us lots of time!
There are a number of uses that spring to mind when working with this type of Macro, but most will depend on your individual site and how it’s laid out. If your site has a relatively small number of pages, consider using a Lookup Table to classify pages into categories using the page URL. Perhaps you have five different events and want to populate the value parameter with something different for each one. Or if you have a content site and want to pass in the month of a published article, you may need a table to convert numbers to the full month names, which is just one of the many examples in Simo Ahava’s blog post.
These tables are easy to set up for small batches but can get tedious if you need to add 20, 50, or even hundreds of rows. So what can you do to speed this process up?
1. Offer presents to the Google Tag Manager team in exchange for CSV import
I assume they like coffee and chocolates, though that might be a generalization. A nice gift basket of popular books could do the trick. Seriously though, it makes sense that this feature will be implemented someday, and hopefully sooner rather than later! Until then, read on….
2. Use browser extensions/macros to automate the process
If you’re a frequent user of iMacros or some other tool, this may be the option for you! Sometimes I find these useful, or sometimes the setup takes me longer than it would take just to get the project done. Phil Pearce has a comment here that may help you out.
3. Use a combination of Google Docs and Javascript!
Or, you can follow these easy steps and let us do all the work for you with the help of Google Docs, the Developer Console, and a little Javascript magic!
1. Copy the Spreadsheet to your Drive
Open up our handy-dandy Lookup Table tool – GTM Version 1 or GTM Version 2. The doc is protected, so you won’t be able to make any changes. Never fear, you can either download it as an Excel file or go to File, Make a Copy to save the spreadsheet to your own Google Drive.
2. Fill in the sheet
There are instructions on the sheet, but you can either type your table into the spreadsheet or copy and paste your information from another source (ex. Excel). You’ll notice as you do so that Column H will start filling in with green cells. This is a good thing!
3. Copy the goods
Once you’re finished inputting your data into the spreadsheet, you’ll need to select all of these fancy green cells and copy them.
4. Head into the Console
Now head over to GTM, where your shiny new lookup table is anxiously awaiting. Hit F12 to open the Developer’s Tools, and find the Console. This may vary by browser, but was tested and worked perfectly in Chrome, Firefox, and even IE!
5. Paste in the Cells
Now that you’re in the Console, just hit paste, then execute the Javascript you just pasted in! It may take a second for it all to finish, depending on your browser, how many rows you have, etc.
6. Double check your Data
As with any quick fix, you’ll want to double check to make sure it works properly!
That’s it! Give it a shot and let me know how it goes!
