How To Create QR Codes in Google Sheets Using Formulas

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

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!

QR Codes In Google Sheets Formula

We can now scan this QR code with our phone camera or a QR reader to quickly access that data:

QR Codes in Google Sheets

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.

24 thoughts on “How To Create QR Codes in Google Sheets Using Formulas”

    1. 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?

  1. 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.

    1. that’s the formula, the author has mentioned it before,,

      =”https://api.qrserver.com/v1/create-qr-code/?size=150×150&data=”&ENCODEURL(A1)

  2. 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.

  3. 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!

      1. Hello, I’ having a problem using a named range instead of a single cell. Can you do this using a named range? Thanks!

  4. 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

    1. 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

  5. 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?

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

    1. 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

  7. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *