How To Create QR Codes in Google Sheets Using Formulas

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 in Google Sheets, we call the Google Charts API 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 Google Chart API request for QR Codes, which will create the QR code:

="https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl="&ENCODEURL(A1)

Notice how we specify we want to create a QR code with the parameter: “cht=qr”

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://chart.googleapis.com/chart?chs=250x250&cht=qr&chl="&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.

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

  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.

Leave a Reply

Your email address will not be published.