Overview:
Enter some text or a URL in a spreadsheet and a QR code will be automatically created – all with the help of a little formula that you’ll learn about in this tip of the week. This is a great way to create lots of QR codes very quickly and easily!
You will have two options: 1. Create your own; or 2. Use the provided templates. Note: For more information about what QR codes are and how to use them in the classroom, click here.
Option 1
Create your own spreadsheet
Let’s do one together for practice, and then you can adapt the instructions to create more of your own:
Getting Ready:
- Go to: drive.google.com
- Create a new spreadsheet.
- Label Column A “Text or URL.”
- Label Column B “QR Code.”
- Resize the columns and rows so they look like the screenshot below.
- Enter some text or URLs in column A.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Entering the formula:
Note – I figured out this formula by dissecting a goo.gl-generated QR code and then parsing it into a concatenation and image formula within the spreadsheet. I was extremely excited, and a little surprised, when it actually worked.
- In Cell B2, enter this formula (or copy and paste it.)
=image(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&A2)
Note – if the above formula doesn’t work, copy and paste it from this page.
- Press the Enter key on your keyboard.
- A QR code will appear!
- Click once on cell B2 to select the cell.
- Click the tiny square in the bottom-right corner of the cell and drag down to fill down the formula.
- Resize the rows and columns to make the QR codes larger or smaller.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Notes / Ideas:
- Use a form to populate a spreadsheet with URLs from students or other teachers; then add the formula to quickly create a QR code for each URL.
- Create a quick QR scavenger hunt. Just enter the clues in column A, and then print and “hide” the QR codes.
- Print the QR code list as a handout for class or for a presentation!
Option 2
Use a template
I have created two templates that already contain the formulas and are ready to use.
- Make sure you are logged in to your Google/Gmail account.
- Click one of the links below.
- Choose to “Make a copy.” This will move a copy of the spreadsheet into your Google Drive.
- Follow the on-screen prompts.
Replace the space character with a + character and the QR code will function and insert a space character perfectly.
If pulling data from other cells, use:
& “+” &
between the cell references.
For example,
= B3 & “+” & A3
Hi
Dear what about if i need to create QR for cell A1 and B1, A2 and B2 … like:
Name: ABDC
Age: 28
Address: gghatabb
Tel: 1234567
Any help plz.
Thank you.
Hi Ahmed,
I have good news and bad news! The bad news is that the new Google Sheets doesn’t support spaces or line breaks within the QR code formula. The good news is that there is a new Add-on that does!
First, enter a formula that will concatenate the desired info. (I’ll show you how here: http://tinyurl.com/ssconcat)
Then, use the QR Code Add-on to generate the QR code for the concatenated cells.
– Pull down the Add-ons menu to “Get Add-ons”.
– Search for the “QR Code Generator”
– Follow the prompts to enable or install it.
After it is enabled:
– Pull down the Add-ons menu to “QR Code Generator)
– Follow the on-screen prompts to generate your QR codes.
Good luck!
– Tammy
=Image(CONCATENATE(“http://www.freebarcodecreator.com/Generate?data=”, A1, “&type=18&size=300X150”))
This is awesome!! Tried out the template you provided and it actually works!
Here’s a question:
Is it possible to generate a QR Code that’s tied directly to a google spreadsheet and can pull info from a specific row?
If you have an old Google sheet, you could use a concatenate formula to pull the info in to one cell and then turn that cell into a QR code. In the new Google Sheets, however any spaces will break the code and it won’t work.
It seems when I paste the formula into my own spreadsheet, spaces break the code. But when I use your template, spaces are OK. Any idea why that is?? Thanks Tammy!
Jason,
The “new” Google spreadsheets have added some nice new features, but unfortunately for some things they don’t work as well as the old ones. Hopefully Google will bring back some of the old functionalities.
– Tammy
This is awesome!! Do you know if there is a way to control the size of the codes without having to resize them all individually at the end?
You can select multiple columns and rows (by clicking and dragging in the header area) and then resize them all at the same time.
Hello,
if anyone is looking for a quick and easy way how to generate QR codes directly from Google Spreadsheets check out my add-on.
https://chrome.google.com/webstore/detail/qr-code-generator/lofihghpipjlmpcnigcopahlpaopcoaa?hl=en&authuser=0
This is excellent! Thank you. Doesn’t work if there are spaces for me, though?
In the “old” Google sheets, it worked great with spaces. In the “new” version, spaces break the code for some reason. Hopefully, they’ll bring back that functionality.
I just tried this trick today and it looks like Google has deprecated this api. Have you seen any other ways of doing this without having to use a separate website to create the URL?
Hi Adam,
Interesting… It is still working for me if the cell contains a URL. However, if I enter text with a space, it breaks the code. Please let me know what your specific situation is and I’ll try to help.
– Tammy
Do you know how to copy ti QR code image to paste elsewhere (othe than snipping the image)?
Hi Kim,
It works for me to just right-click (or control-click on a Mac) it and choose “Copy.” Am actually surprised as I didn’t think it would work…
– Tammy
That’s awesome Tammy! You clever little thing you!:)
Actually works! Actually brilliant! Thanks!!!!