In this tutorial, you’ll learn how to create QR Codes in Google Sheets using formulas. And how to transfer them to Google Docs and Google Slides using Apps Script.
A QR code is a type of matrix barcode.
Barcodes and QR codes are optical labels that are machine-readable and link to information about items or products.
They’re ubiquitous today. You see them everywhere, from restaurant menus to packaging labels.
In this post, we’ll look at how to create fully-functional QR codes in Google Sheets.
QR Codes In Google Sheets
To create QR codes, we call a QR code API (Application Programming Interface) to generate a QR code. Then we display those QR codes in Google Sheets with the IMAGE function.
Let’s see an example.
Assuming we have some information in cell A1, the first step is to encode the data for the API with the ENCODEURL function:
=ENCODEURL(A1)The encoding ensures the data can be used in the query string of the URL when we call the API. For example, we can’t have spaces in URLs, so the ENCODEURL function will replace spaces with a special code (in this case %20).
Anyway, provided you have the ENCODEURL function present, it will take care of that for you.
Next, we prepend the QR Code Generator API request for QR Codes, which will create the QR code:
="https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL(A1)If you click on the URL generated by this formula, it will open the QR code in a new tab of your browser. So now we need to display the QR code in the Sheet.
To do this, we wrap the formula with the IMAGE function:
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL(A1))Boom!
There it is! Our QR code in a Google Sheet!
We can now scan this QR code with our phone camera or a QR reader to quickly access that data:
QR Codes Template
Click here to open a view-only copy >>
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings.
In this case, right-click the link to open it in an Incognito window to view it.
See Also
Create Barcodes In Google Sheets In 2 Easy Steps
My friend, Amit Agarwal, has written a tutorial on how to create dynamic QR codes for collecting UPI payments, using custom functions in Google Sheets.
Is it possible to see how many scans the QR codes receive with the chart api?
Ola! Digamos que eu queira fazer uma planilha de estoque. Eu cadastro um qr code para cada item. e cada vez que escaneio um produto cadastrado, através de uma função procv eu eu consiga trazer todas informações deste mesmo item. da pra fazer no sheets?
Thanks for sharing, this is a great article. I was planning on utilizing this in my UTM tracking worksheet where we occasionally use QR codes in offline settings – posters, product literature, etc. The only issue I’m having is getting the QR codes out of the Google Sheet because I am unable to copy and past the image of the code. Any thoughts? I’m sure I could do a screen grab, but wasn’t sure if there was a more elegant way to do it.
that’s the formula, the author has mentioned it before,,
=”https://api.qrserver.com/v1/create-qr-code/?size=150×150&data=”&ENCODEURL(A1)
This feature would be much more useful if it were accessible in Google docs. For a printed Google docs pamphlet, instruction book or just any old book , to be able to utilise a hyperlink by scanning a generated QR code on the page would be great.
Have you tried inserting the sheet into your doc?
This is a fantastic little formula and clear instructions on how to use it. You saved my project when the QR cod add-on choked after 200 lines and I needed 1000 QR codes for school photos. Thank you!
You’re welcome! That’s great to hear. Cheers!
Hello, I’ having a problem using a named range instead of a single cell. Can you do this using a named range? Thanks!
What a useful thing to be able to do right within Google Sheets without any add-ons. Thank you for explaining this!
Amazing way to easily create code Ben
Any ideas for download qr code with high quality?
Great tool,
How can I bulk save all these QR Codes? I am really struggling to be able to save them as a jpg etc
Hi Sean,
You can publish the Sheet to the web, then download or “Save as” the webpage to your desktop. Within the download, you’ll have a folder of all the QR images. See an example in the new video added to this post.
Cheers,
Ben
Thanks, however, when I scan the QR code with my phone, it just takes me to a google search listing all about QR codes? How do you link scanning to that record?
its not work on january 2025
please help
I’ve updated the article with a new API 🙂
This function was working perfectly and now the image no longer displays in the spreadsheet (as of 1/1/24 approximately): = image(“https://chart.googleapis.com/chart?chs=300×300&cht=qr&chl=”&ENCODEURL(A1))
The URL link works fine
Is there some new security feature that prevents the image feature from calling the url ?
I’ve updated the article with a new working version! 🙂
Ben,
This no longer works. Have you found a suitable alternative? It looks like google shut down the https://chart.googleapis.com service in 2024.
Thanks, Todd. I’ve published a new YouTube video on this very topic and updated this article.
Cheers,
Ben
This is really great. Is there a way to download the image of the QR code that is generated?
Hi Emily,
Couple of methods to try:
1) From the menu: File > Share > Publish to web, then open the new webpage, then save the open webpage to your desktop and you’ll have a folder containing all the images, including the QR ones.
OR
2) File > Download > Web page, then click on the HTML file to open in browser, then download as above or right-click to save images.
Alternatively, you could use a screen snip tool to grab the image.
Hope this helps!
Ben
Ben,
I created a Google Form that linked to a Google Sheet, then used the chart.googleapis.com/chart to generate QR Codes, but did NOT want to use a screen capture for the QR Codes and had no way to download the them as images.
I have been searching and searching for a good 4 hours for a solution. Then, I found your page and it gave me the tools to PUBLISH (don’t forget to UNPUBLISH once done) the page and right-mouse-click the images and BAM! Just what I needed.
I also like the download the page option for ALL images to be downloaded (however you gotta name them all).
Thank you, thank you, thank you!
Woohoo! You’re welcome. Great to hear.
With Google breaking how =IMAGE() works and requiring an extra click to enable, is there a way around that now?
(if you copy an existing sheet with the =IMAGE() formula you should get a warning when you open that new sheet
“Warning: Some formulas are trying to send and receive data from external parties.”
That warning must be clicked to allow the image to be visible, which breaks automated workflows. Any suggestions?
Hey Ben this is awesome, I was wondering how long can these QR code last?
Hello Ben,
I’m new to using QR Codes and formulas you were using above – I know a few excel formulas to be dangerous. But what I would like to learn more of- is if you can teach me how to map a QR Code to a data form into Google Sheets. And how the form looks on a report. What I’m trying to do is utilize a QR Code to sign up all players and volunteers interested in playing in a 4 Player-Scramble Golf Tournament, by having individuals sign up to play or volunteer by entering the following: Volunteer or Golfers name (First) (Last), phone (###.###.####), email (@address.com), Team Captain (x) or Team Player (x) “If Team Player”- enter Captains Last Name (Last Name), or Single Player (x) without a team, Volunteer (x), Area of interest (<<>>) etc.. Will the form be laid out like a spreadsheet w/ Columns and Rows would be ideal. Thank you for sharing.. John Denver