Easy Upload for Google Tag Manager’s Lookup Table Version 2


GTM Lookup Table Automation

In October 2014, the Google Tag Manager team announced a new version of their popular tool, complete with easier workflows, a brighter design, and many other wonderful features. Most things work in a familiar fashion, with a few name changes.

Macros are now called Variables, and the Lookup Table Variable works exactly as we would expect it to. Sadly, there is still no support for CSV upload, so there still exists a need for a tool that people can use to quickly copy and paste from Excel or Google Drive.

I created a clunky workaround for Version 1, and at the request of many, I’ve now created an updated version that works with the new interface. As the GTM team continues to improve the design and functionality of Version 2, this tool could possibly stop working, and could hopefully become unnecessary.

Update September 2016: The sheet was down for a few weeks after GTM launched their new interface, so if your copy of the spreadsheet stopped working, you’ll need to start over with the instructions below! Also, the smart people over at Seer Interactive have put together a different twist on automating this process with their own Lookup Table generator tool. Their tool involves exporting/importing JSON. While Lookup Tables in Google Tag Manager only support exact match types, the Seer tool will allow you to choose other match types and will output either a Custom JavaScript Variable or a Lookup Table based on your selections.


Lookup Table Tool – for GTM Version 2
Lookup Table Tool – for GTM Version 1 | Original Blog Post – for GTM Version 1


Let's Get To Work!

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 over to Google Tag Manager

Now head over to GTM, where your shiny new Lookup Table is anxiously awaiting. You want to fill in everything and get to the part as shown below. You don’t need to add any rows, this will do all the work for you. If you have any blank rows, you should remove those.

Lookup Table Google Tag Manager

5. Go into the Console

Hit F12 to open the Developer’s Tools, and find the JavaScript Console. This may vary by browser. In Chrome, you can also hit CTRL+SHIFT+J.

6. 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…


7. Double check your data

As with any quick fix, you’ll want to double check to make sure it works properly!

Lookup Table Automated Entry

And that’s it! Hopefully it works for you and saves you a little bit of time.


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.

  • A.

    Hi Jon,

    Many thanks for the post. Im not a developer. How would it be possible to update an existing macro? Can the code be added using the GTM API?


    • Hi A, This should work to ADD to an existing Macro. It won’t change a macro or update existing values, but should work properly to add additional rows. This is really just meant to be a quick workaround, I’m sure there will be better tools using the GTM API or the GTM interface as some point!

  • Tiaan Van Zyl

    Hi Jon,

    Cool article. Would it be possible to use lookup tables to send all debugging hits to a separate GA profile? Thus, if debugging is taking place, send all hits to a different UA property.


    • Jon Meck

      Absolutely – we used Lookup Tables for that very purpose. The Debug variable returns True/False if you are in Debug Mode or not, so you can just have a table with two UA #s and swap based on that variable.

      You can also use Hostname and swap the UA # if you’re on dev.example.com vs http://www.example.com.

      Good luck!

      • Tiaan Van Zyl

        That is simply awesome! I just don’t want all debugging hits to skew up some of the real-time data.

        Thank you!

  • Steven Johnson

    Looks like a great idea, but it doesn’t seem to work for me. Console throws the following error: “Uncaught TypeError: $(…).find is not a function(…)”

    • Hi Steven,

      Just tested and worked fine for me in Chrome! Give it another shot and make sure you follow the instructions completely! I’ll keep an eye out to see if others have similar issues!


      • Steven Johnson

        Crazy, it works today. Maybe it was just a momentary hiccup. In any event, thanks for this solution!

  • davidfricks

    Greatest trick ever! Thanks Jon!

  • Najja O.

    Thanks for posting, this is amazing! It saved me so much time, but I’m noticing the green cells stop populating after row 302. Is there anyway to have the formula copied all they to a thousand rows or more?

  • An hour just got added to my life.

  • IndyToddF

    I’m not having much luck renaming titles.

    1. I don’t see {{url hostname}} as variable to add. Is there another standard variable to use?
    2. When I add the code via console, it looks “right” – similar to yours. But when I go to save, the Input/output rows don’t appear.

    I tried several other ways the best I could (even mapping {{videoURL}} to no avail.

    Ref: http://www.lunametrics.com/blog/2015/05/11/updated-youtube-tracking-google-analytics-gtm/#google-tag-manager-installation

    • Hi, the sheet broke when GTM updated their interface a few weeks ago. Try starting over with a new copy of the sheet and see if that works!

  • Lindsey Buckle

    Great solution, thank you BUT… it’s not working for me 🙁
    I’m using this along with the YouTube URL to title lookup spreadsheet. A couple of my YouTube titles end up quite long with ” – YouTube” appended and additional quotation marks around both the URL, title and the line of code as a whole. This causes errors which I’ve fixed by cleaning up the code in the console. However, now I am getting the requisite number of rows added to my lookup table but they’re all blank. Any ideas why this might be happening?

    • Hi Lindsey – the sheet broke when GTM updated their interface a few weeks ago. Try starting over with a new copy of the sheet and see if that works!

  • Amir

    Hi Jon and all,

    I followed the instructions in detail.
    When I hit enter in the console, I see that it adds rows, but it doesn’t enter the values into them.
    So All I get is an empty lookup table.

    Any idea?

    • Hi Amir – the sheet broke when GTM updated their interface a few weeks ago. Try starting over with a new copy of the sheet and see if that works!

      • Amir

        Not sure what you mean by “new copy of the sheet”.
        I copied the sheet and started fresh. Even tried downloading and working offline in excel.
        The code I paste looks exactly (!) like in the photo of bullet 6 above, but still no luck…
        Any other idea?

  • You know that feeling when you stumble on a solution and you want to hug the one that came up with the answer? Yeah this. Thanks a million, mate.

  • Kolja Siegmund

    Tried it, and it’s a great solution, yet, for data sets containing more then 2,000 rows, my browsers started to struggle. So I fund another solution that works well for data sets up to 6,500 rows:

    PART 1:
    – Set up a new container
    – Include a simple lookup table (of two rows as placeholder)
    – Download the container as .json
    – Open json in editor and copy the relevant parts into excel:
    —> {“type”:”MAP”,”map”:[{ “type”:”TEMPLATE”, “key”: “key”,”value”:”
    —> “},{“type”:”TEMPLATE”,”key”:”value”,”value”:”
    —> “}]},
    – use concat function to build your file =concat(1ST CODE PART;LOOKUP VALUE;2ND CODE PART;RETURN VALUE;3RD CODE PART)
    – Replace placeholders in json file
    – delete comma after last copied line of code
    – change variable name and input variable / input value (1st part of json file)
    – now, safe as new json

    PART 2:
    – Go to your main container
    – Go to Admin
    – Go to upload container
    – Select most recent version
    – I prefer to select option “rename variables” ipv. “replace” next
    – select your json
    – let GTM do the rest of the magic

    Some remarks:
    – Run into trouble when ” where doubled after coping recoded lines into the file. So, I needed to replace all “” with “, },” with }” and “{ with {. The latter one actually changes code of input value, which should be fixed afterwords (adding an {
    – Upload files max sizes allowed is 1MB (about 6500 rows)
    – That might be the limit of all lookup tables
    – Your workspace size is limited, so you might be able to upload two or three of this files, until GTM tells you that your draft version is to large to be saved

    This works great to me for large data files and brings time down to 10 – 20 minutes instead of waiting the whole night or even longer ; )

  • pakile

    What is the maximum number of rows in a single GTM lookup table? (for instance, if mapping hostnames to GA property IDs)

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