Generate QR Code with Google Chart API in Bulk from Excel Worksheet

This article is about generating QR Codes automatically in bulk from Google Chart API. If you know how it works, then you can skip all the descriptions in the article and go straight below and download the spreadsheet that can help you print QR Codes in bulk.

But, if you are interested to learn about how it was done, then please start reading from here. Before that, I think a lot of people want to have more introduction about some terms. So here they go:

What is QR Code?

QR Code is 2-dimensional or 2D barcode (unlike the traditional 1D barcode) that looks like the black pixilated image below:

Came from some innovative Japanese mind, who invented the code to make contents decoded at high speed. This 2D Barcode now readable by QR scanners, smartphones or mobile phones attached with cameras.

For iPhone (Semacode), Android Phones (QuickMark), Blackberry (ScanLife), and Symbian Phones or Nokia (UpCode) there are many competing apps that can read QR Codes from any printed media.

The utility of QR Code is simple, transfer a whole chunk of text or information from any printed media to your mobile phone (through QR reader) or computer (through QR scanner). So, this is the best way for you to make people go directly to your site from the printed QR Code or have people save your business card in their mobile phone in a snap.

What Does QR Codes Have to do with Web Marketing?

QR Codes are fastest way to transfer text and information from print to mobile phones. Therefore, it has good implications on increasing your site traffic from alternative media by referring the URL to your site. e.g. if you sell a specific product; by using mobile phone, the printed QR Code on your product can make the friends or colleagues of the buyer go to the page where he/she can also buy it.

For any type of contact request, a visitor can save your contact information from web, product, brochure, coupons, etc. and might convert later.

QR Code Bilboard Ad by Calvin Klein

Calvin Klein recently made an innovative adult advertisement on billboard with QR codes, without even showing the nude models. The title of the advertisement was “Get It Uncensored”. This triggered many people to go to their website and watch the uncensored video. Please read more about this news here.

Even though QR Code is thought to be a mobile marketing strategy, combined with the web technology, it can be a great strategy for web marketing.

What is Google Chart API?

According to the Google chart API site:

The Google Chart API lets you dynamically generate charts with a URL string. You can embed these charts on your web page, or download the image for local or offline use.

Interesting fact is that Google Chart API is also currently the most reliable and free source for generating 2D QR Codes.

How can you Generate QR Codes with Google Chart API?

If you look at the URL of the above image, you’ll see that there are many information avalable in the URL:

http://chart.apis.google.com/chart?cht=qr&chs=350x350&chl=MECARD:N:Asif%20Anwar;ORG:Blueliner%20Marketing,%20LLC.;TEL:008801712181761;EMAIL:anwar%40bluelinerbangla.com;ADR:55%20Broad%20Street,%2017th%20Floor,%20New%20York,%20NY%2010004,%20USA.;URL:http://asifanwar.tumblr.com;NOTE:Company%20site:%20http://www.bluelinerny.com;;

So, by just inputting the information in the right place in the right format, you also can generate the desired QR codes.

Interpreting the Google Chart API URL

From the URL, you will find several parameters that can be customized. Here is the list of the parameters and their description:

cht=qr

If you go to the Google Chat API site, then you can see that Google arranges many types of charts. So, by using cht=qr, you are telling Google that the chart type should be QR.

chs=350x350

This is where you mention what would be the chart size.

chl=MECARD

CHL is the Chart Label parameter. This actually states what type of content you have and based on that, your device will create certain action. You can have content as vCard (make an Phonebook Entry), URL (go to the referred site), Text (read the contents of the QR), Phone (call the referred number), SMS (Get info saved on your mobile), etc.

Usually, MECARD is used to tell that, the content is for Mobile vCard. According to ServletSuite.com, the definition of

MECARD is a text format lets you present address information for phonebook registration.

It is then followed by other heads with your custom values, like:

N: Full Name
ORG: Company Name
TEL: The Telephone Number
EMAIL: The Email Address
ADR: The Address
URL: The URL
NOTE: Any note or comment.

Each of the heads should be separated with semicolon. If you are using the Chart API for generating URL only, then the custom URL is the label. You don’t need separate label for that.

More information on Google Chat API Parameters are on: http://code.google.com/apis/chart/docs/gallery/qr_codes.html

How to Generate QR Codes in Bulk from Excel Worksheet?

Well, this is what this article is about. In excel worksheet or any other sort of database, you might have information or record like this:

A2. Name: Asif Anwar
B2. Company: Blueliner Marketing, LLC.
C2. Phone Number: 008801712181761
D2. Email: ɯoɔ˙ןıɐɯb@ʇuɐʇןnsuoɔǝpɐɹʇ (upside-down text)
E2. Address: 55 Broad Street, 17th Floor, New York, NY 10004, USA.
F2. Website: http://asifanwar.tumblr.com
G2. Note: Company site: http://www.bluelinerny.com

[A2 to G2 are cell reference]

In terms of QR Code generation, these information is best for website/URL reference or business card transfer from print to mobile. And with all these information, you get this URL again:

Now, if you look at the URL that generates the QR Codes, you will find a pattern. e.g.:

http://chart.apis.google.com/chart?cht=qr&chs=[THE-CHART-SIZE-YOU-WANT]&chl=MECARD:N:[FIRST-NAME]%20[LAST-NAME];ORG:[COMPANY%20NAME];TEL:[YOUR-TELEPHONE-NUMBER];EMAIL:[YOUR-EMAIL-ADDRESS];ADR:[YOUR-ADDRESS];URL:[YOUR-URL];NOTE:[YOUR-NOTE]; ;
In other analogy with cell reference:http://chart.apis.google.com/chart?cht=qr&chs=[THE-CHART-SIZE-YOU-WANT]&chl=MECARD:N:[A2];ORG:[B2];TEL:[C2];EMAIL:[D2];ADR:[E2];URL:[F2];NOTE:[G2]; ;

So, it is possible the join these information from excel worsheet to generate the URL and finally generate the QR Codes. But, there is one problem, the information you have, has spaces and @ sign that are not allowed in the URL. So, you have to replace them with “%20” and “%40” respectively.

In Excel Worksheet, you can use the following formulas to replace the spaces and @ signs and create a new text that can be easily joined:

H2. =SUBSTITUTE(TRIM(A2),” “,”%20”)
I2. =SUBSTITUTE(TRIM(B2),” “,”%20”)
J2. =SUBSTITUTE(TRIM(C2),” “,”%20”)
K2. =SUBSTITUTE(TRIM(D2),”@”,”%40”)
L2. =SUBSTITUTE(TRIM(E2),” “,”%20”)
M2. =SUBSTITUTE(TRIM(F2),” “,”%20”)
N2. =SUBSTITUTE(TRIM(G2),” “,”%20”)

[Assuming that you are placing the replaced texts in single row that starts after G column]

Since you have all the spaces and @ replaced, you now can join the texts with =CONCATENATE() formula. e.g.

=CONCATENATE(“http://chart.apis.google.com/chart?cht=qr&chs=350x350&chl=MECARD:N:”,H2,”;ORG:”,I2,”;TEL:”,J2,”;EMAIL:”,K2,”;ADR:”,L2,”;URL:”,M2,”;NOTE:”,N2,”;;”)

The strategy for generating the URLs for QR Codes using Google Chart API may vary in case if you are using separate form of database. Once you have created the URLs, you can easily embed them in the site or print them by visiting the file on browser.

Now I can go a long way to discuss on how you can embed them. For your convenience, I have done all the trouble in the embeded spreadsheet below. You can download and use it to generate QR Codes and also embed in in a printable format.

Download this Spreadsheet

The Embed Code in the spreadsheet some times come with double (2) double quotation marks in HTML when you try to paste the embed codes in other HTML Editors. Please find and replace double (2) double quotation marks with single double quotation marks, before finally using them for print. You might also see that there are starting and ending double quotation marks in the embed codes, delete them as well to make it work for you. Feel free to change the codes into the design you like best.

If, I have saved your time, feel free to comment below, like, and share the article with your friends. And if you feel it’s too geeky for you, then consider this Gig for $5 in Fiverr: I will create Custom Designed Qr Code Badges from CSV Database for Events for $5

Some of my other articles on QR Codes:

QR Code Marketing and Advertising Ideas and Strategies for Internet Marketing

How to Turn Your Billboard into an Interactive Marketing Campaign and Measure ROI

19 October 2010 ·