Automate GTM’s Lookup Table Macro from a Spreadsheet
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 some day, 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.
1. Copy the Spreadsheet to your Drive
Open up our handy-dandy Lookup Table tool here. 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
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!
About Jon Meck
Jon Meck is a Digital Analytics Engineer working with Google Analytics and Google Tag Manager. He is an Excel enthusiast and strong proponent of the "Work Smarter, Not Harder" mantra. He has a history of working with web technologies for companies large and small. Outside of work, Jon enjoys late nights working on pet projects, running races, and spending time with his family.