Automate GTM’s Lookup Table Macro from a Spreadsheet


Click here for the updated version
that works with Google Tag Manager Version 2.
Upside down house

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

Gift BasketI 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.

3. Use a combination of Google Docs and Javascript!

Or, you can follow these easy steps  and let LunaMetrics do all the work for you with the help of Google Docs, the Developer Console, and a little Javascript magic!

Let's Get To Work!

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!

Interested in getting the most out of Google Tag Manager? Check out our Google Tag Manager Workshop which provides hands-on training!

Jon Meck is our Director of Marketing & Training, promoting our services and trainings to the world. He has a jack-of-all-trades background, working for companies large and small in social media, website design and maintenance, and analytics. He is an Excel enthusiast, he loves efficiency, and he is strong proponent of the “Work Smarter, Not Harder” mantra. Jon is also the author of two number puzzle books.

  • Les

    NIce work Jon. I have been struggling with finding the best way to import +250 URLs for a macro I want to run…. And well… I am not struggling anymore!

  • Cool trick man!

    Tnx for sharing

  • I tried to implement this but got confused from step 3. Could you do a video tutorial for implementing this? Perhaps include screenshots for not too advanced GTM users. You don’t have to publish this reply 🙂

  • Jen

    I tried using this, my values populated correctly, but then GTM would not allow me to save to the macro. Any ideas why this might be?

    • Hi Jen,

      You may have tried entering too many items? You could try doing it in parts to see if that helps. Check all the usual suspects, browser, etc..

      Hope that helps!

  • Thanks a bunch, Jon! Saved me hours of work today. Also, looks like there’s a hard limit of 250 rows per lookup table (at least in the old UI).

Contact Us.

Follow Us



We'll get back to you
in ONE business day.
Our Locations
THE FOUNDRY [map] LunaMetrics

24 S. 18th Street
Suite 100

Pittsburgh, PA 15203


4115 N. Ravenswood
Suite 101
Chicago, IL 60613


2100 Manchester Rd.
Building C, Suite 1750
Wheaton, IL 60187