Create Instant QR Codes

About this Tutorial

Learn how to use a free Google API to create QR codes in sheet to any URL. And we'll learn how to make them magically appear as you enter domains.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() IF() IMAGE() ISBLANK()

Video Transcript

<div> Hello, welcome. In this video we're gonna create instant QR codes, so a little bit of URL trickery. It's gonna get us a url, uh, QR code from a url. So right here, I've already created Better sheets.co. And then a little bit of Formula Creativity is gonna get us this instant QR code as I type in this. Url. So if I do free dot, better sheets.co, hit enter, boom. A QR code exists here. It is not just h I've not created this QR code in the past and shown it here. It's actually creating the QR code on the fly. So this QR code was not created before I made this video.

And you can go ahead, check out these cure codes, see if they work right now. Pause the video. Uh, check them out. Both of them are gonna. . So how do we do this? Um, we got a little trickery in the formula and we got a URL issue. So issue a thing to go through. So let's start at the url@thischart.google apis.com/ chart . This is a QR codes, uh, infographic. Uh, this is a.

A API that is doing a get request, and what we're doing is we're getting a get request and we're using image the image formula to show it. But the image formula is gonna go grab a URL online and then bring back whatever image it returns. And this QR code is gonna create an image immediately when it's hit with this get command. . One issue with this is that at the top of here it says, warning, this API is deprecated and at the bottom somewhere, it does say last updated 2016. So it is now February 5th, 2023 and it's still working.

I do not know if this will continue to work indefinitely, forever. Um, but it's been working so far. So, uh, here we can create a cure code on the fly with a url, get request as stated here. Um, and literally the root URL is. chart .google apis.com/ chart with a question mark at the end. And what we've done is we have created this URL with a c hs equals 100 x 100, which I will explain in a second. C h t equals qr, which I will explain in a second. And then C hhl equals, ah, what is this?

It ends the quote and then it has a, has an amper sand, and then we use a two to a, which is inside of a array formula . Inside of if is blank. So we did a little bit trickery there. I've explained in other videos how to do the array formula and if is blank to sort of show stuff as you go.

Um, we can focus this video on this u URL and image , but I do want to just share with you that this, you can grab this sheet and you would get this array formula , but how that works, it's probably gonna be in another video. look up array formula or if it's blank, I show that that sort of cool way to show some magical stuff happening. Um, but that's how we. , all of these to stack up basically without anything here. So if it's not blank, nothing's gonna show.

But when we put a URL inside of this A column, this B column is gonna come to life and it's gonna go get the url, which is a this API, and go come back with QR code, which is really cool. All right. Enough of that. Let me talk about those things. I said I would talk about c H T equals Q. Is the second thing I said and that just specifies that it's a QR code. That's all that does. The first one was ch equals 100 x 100, and this is an image size, so we can put any width and height here. And it's the size of the image , not the cure. Let me do that again in the correct order.

So CH hs equals the width X height, which is just for our purposes. I put 100 x 100.  and that gets us an image that's a hundred pixels by a hundred pixels. Uh, does not, uh, change the QR code in any way. It's just the image size that you're gonna show up. Um, then the c h t equals QR just literally just specifies that it's a QR code and you need that. We are conc uh, putting this together with after this. Question mark with an ampersand. And then we have chl, which is the last thing we'll get to. And CHL just get equals data . What that data is, is a url.

We could, in theory do this. Let me take all of this, create a new tab theory. We could do something like this where all in the quotes, we can do HTPs better sheets dot go. all in quotes, and that should give us a QR code. We can change this, you know, to 200 by 200 and similar QR code. Same thing. The image is just more pixels. Uh, but this should go to better sheets. Doco, I don't have my phone with me right now to check it. You can check every night. You can pause the video and check, um, if this is actually going to better sheets.co. Uh, but that's really all we need.

So what we're doing here is we're using Google Sheets power to. Just give us actually whatever's in the A column here with this A two to A, and we're using array formulas power to do this only once. And it shows up here, uh, as we go, as we add URLs. Let me talk about the URL though, that you can use. You can use any url. You can use a url that's maybe a link to a Google sheet, a link to a cell, link to a tab. The issue is that you cannot have more than 2000 k. It's 2000 maximum length. So it's like 2000 bites of information.

Um, you're not really gonna get close to that as far as I can imagine, unless you're like doing a lot of concatenations and you're getting very specific. For example, I would use QR codes a lot if I were doing like event registration or activity registration, and you wanted people to.  have their, they have their phone and they're walking around to maybe like iPad stands or something, or even printed out QR codes and like this person showed up here and they could do a QR code to a, um, to a Google form that's prefilled with whatever the, like that particular stand or that activity has some like code or even some name or some act like text. Now that text maybe it has then like,  QR codes of like, Hey, go to this link if you like it.

If you go to this link, you don't like this activity, or you did something, or, you know, there's lots of things you can add to, say a prefilled Google form , that it could be a lot of text. So I could imagine you could get close to that 2000 maximum length here. In those particular cases, if you're trying to track, you know, , you're pre-filling information, ratings, stuff like that. Where they are who, what date, what camp, you know, campaigns.

Maybe you throw a q I don't know if you can throw a QR code into like Google ads or Facebook ads, but if you can, maybe you wanna do some like UTM issues. UTM issues, UTM additions. Um, yeah, there's a lot of stuff you can add to a url, so I could imagine you could get to up to that two x maximum length. , but it does have a way around this. If you do have more than 2000 length, you can encode your data . You can send your data with a post, cuz I'm not gonna cover that in this video. That's about it.

That is the QR code insecure code where , uh, you can, you know, do it bare bones and you just have image equals this, uh, HTP chart .google apis.com/ chart . Question mark ch hs equals 100 x 100 ampersand cht equals qr. Uh, ampersand chl equals your domain that you want to create that a link to. Um, you can also delete that, uh, domain and add a two here. So now we're gonna get an error . . Oh, because it's a formula par error . We need to add an ampersand. We get a, okay, this is funny. We get a Q QR code because we're sending data somewhere, but this a two is nothing. So we can do HTPs.

Let's do better sheets.co and see it changes. Uh, so you know, it's a different QR code, so it actually probably went there. And now as we change this A two, we'll probably change this, uh, QR code. But we can also wrap it up. We can say if is blank. Uh, a two, A two comma. Now if we delete it now, we have no error . We have no QR code in here. And now if we do a uh, HTPs, uh, free dot, better sheets.co. Bam, we got it. That's very fast. . We have one. And then what we like, what I like to do is I like to wrap this with Array formula .

Array formula , uh, we just need to change a two to colon A and then change this a two to a two colon A. And now we have exactly as we have on sheet one we have here, so better sheets.co boom. We have a QR code. I like to make these much, much bigger so we can see them resize the rows, let's say 100. . There we go. Those are nice. We can center it if you wanna make it look nicer in some way. Maybe we wanna center all of these. Yeah. Make it really nice. Cool. Right. Instant QR codes, uh, in a really cool way. Uh, you can do programmatic URLs and then get a bunch of QR codes.

Um, Think you can not copy paste them. I don't know where you can use this, but yeah, you can add these to, um, anywhere you want. All right. Thanks for watching. Bye.</div>