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

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.

Links

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

Instructions

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.

lookuptable-copy

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!

lookuptable-cells

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.

lookuptable-copycells

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…

lookuptable-console

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.

About

Jon Meck is our Technical Marketing Manager, 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?

    thx,

    • http://www.lunametrics.com Jon Meck

      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.

    Thanks

    • 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(…)”

    • http://www.lunametrics.com Jon Meck

      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!

      Thanks!

      • 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?

  • http://www.philippedenis.ca Philippe Denis

    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

    • http://www.lunametrics.com Jon Meck

      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?

    • http://www.lunametrics.com Jon Meck

      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?
    Thx
    Amir

    • http://www.lunametrics.com Jon Meck

      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?

        • http://www.lunametrics.com Jon Meck

          So start from the very beginning of the instructions.

          1. Open sheet – https://docs.google.com/spreadsheets/d/1-iZ9GxVgAUoaEDl2wlBDrGG0c9NIKUAKjrte_msI2H0/edit#gid=0
          2. Make copy.
          3. etc. etc.

          I updated the cell in H1 to reference “.gtm-vendor-template-simple-table-md” instead of “.gtm-vendor-template-simple-table”

          • Amir

            Bingo!
            Works like magic 🙂
            Thanks!

            BTW – the sheet you just shared is not open for viewing (need to request permission…)

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.