Hello and welcome. Pardon the alfalfa hair over here. It's been a fun day. I got to create Bitly in a Google sheet. Basically, I added click tracking to this Google sheet. Something that I've been trying to do forever, and I don't know why, but I just. Figured it out today and I coded it up. It's all about the code today.

So what's gonna happen is this video's gonna go out all of first two all better Sheets members, and then you're gonna be able to watch it on YouTube. If you are watching this on YouTube, you do not have access to the code. You have to be a better sheets member. If you're a better sheets member. The the link to the, to the sheet and the code is all in the des.

of this video. And so go ahead, make a copy for yourself. If you are watching on YouTube, feel free to follow along. Try to code it yourself if you wish, or become a Better Sheets member today and go grab the code right away. Here's what it does. Here's what click tracking does in your Google Sheet.

If you have a URL that you want to track, like Better sheets.co, you. Put it in this database tab as actual link. Just a totally random code here. I can use anything. I can use these numbers. And here's a number of clicks. I will show you how this works, but basically this is script.google.com, and then it's a.

Really long. It's not a link shortener, it's a click tracker. So this is a really long link, but I click it, I go to, I get to this redirecting page, and then it takes me to better sheets dot code. That redirecting page, I can edit and I'll show you how to edit it. And if we go back to click tracking in our data, in our database.

it says 15 clicks. I can reset this at any time. There, I reset it. So now I click again and we see the re, we'll see the redirecting. We can close that. One last one we used. You'll see redirecting anytime. Now it's really slow because I'm on, on my computer because it's it's using the, the bandwidth to upload this video as I, as I make it on loo.

let's go back to here, and it has one click. Now this URL that you see, I'm using a little bit of a trick here. I'm using the hyperlink formula to display one link. But use this get url, URL to click as a com custom function that I built to get our actual custom link. And what it does is, is that it's an actual Google Script web app.

I've published it as a web app here. I'll even show you the code here. And I've published this as a web app. And what happens is,  this get URL to click will add at the end the, the URL of the web app. It'll add this question mark. It'll say unique equals, and then it will add. , whatever unique code we want.

So if we want to change this to Better Sheets Co and just anything, it will still work because that's the unique thing. Now, now if I use the URL with the other code, that will stop working, but this one will continue working. So it's, it's, it's a very  not weird function, but it's, again, it's not like Bitly or anything.

We. We have to, let's see if it works. Now . There we go. So now we'll get to that script.google.com. Redirecting. And do we make it? There we go. We made it to better sheets.co. And now that should show up as two. See? And again, this will, if I copy this link. So copy link, address. And let's just create, let's do incognito and see this is the entire thing.

So, URL is the u is the web app url and we really just did a do get, which is like basically the Google Scripts get And we added this unique equals, and then we use this code and I'm gonna walk through the code for you. If you want, again, if you're not a Better Sheets member and you want access to the code, become a better sheets member.

Or just like, literally pause and copy down this script. If you want to put that much effort into it, feel free. But I'm gonna walk through how it works first. But first I'm gonna walk through some use cases. So this is a click tracker, right? This kind of click tracking. Relatively user friendly.

it's not tracking IP addresses bit. If you go to Bitly, they have like 20 pieces of information that they're tracking. User, like the person who clicks it, where, where they are, how many times it's used in other places, how many people make Bitlys for your link. This literally is just tracking your clicks.

So you can use this to compare links that you send for sales materials. , what's I, I wrote some down. So like, if you, if you're sending sales materials and you need to know, like within the, this week, I just need to know how many clicks I get, or if somebody clicked yes or no. That's a really good usage of this.

Instead of having to sign up for Bitly. Create this in, in a Google Stream for yourself, and then use it as many times as you want. Or make it part of like little teams, like give each one different dashboards or something. You can measure. Marketing campaigns, email marketing campaigns, social marketing campaigns, Facebook ads, and anywhere where, where you're using links.

I would, I would do a little bit more testing than I've done just to make sure it, it consistently works,  and you're not changing the URL all the time. . If you're like, oh, well I don't want random numbers here. I wanna this number, and then you change it halfway through the campaign, you're gonna lose the usage.

Like literally those links won't work anymore. . So some other use cases are like if you have, if you create a swipe file with a Google sheet, and then you're just like, how many people are actually using this? Like, which, which of these, you know, hundred pieces of information or, or websites that I use as, as I, that I gave out as like a inspiration which ones are actually used here.

If you want to shorten, not shorten, but track your email. When you're sending out email campaigns, you're sending out emails to particular people, just internally, you know, you're like, Hey, did that person ever get into that Google slide? One of the most interesting things that have been requested is sort of like, how do I know how many people have like accessed my Google sheet?

Well, now you have a link that's tracking how many times people click on that, so you don't have to actually do anything in the Google Sheet itself that you're trying to share and be like, how many people are opening. Just track the, the, how many people click on the link to get there. And this really does allow you to replace Bitly.

Again, it doesn't, it's not fully featured. It's not everything in Bitly, but it just click links tracks those click. Link. Link clicks. Oh my God. It's a tongue twister. Okay, so now I'm gonna go through the code and I'm gonna share with you exactly how it works. I'm gonna make a copy of the sheet after that and we're gonna, and I'll show you how to set it up.

So I think this is a really cool video where you get to see it working now you get to see how it works, and then I'm gonna make it in another Google Drive so that you. Don't, you'll totally know if this works. A hundred percent. If I, if I mess up, you'll see the mess ups. Every problem I go through that you'll go through, I'll go through.

So first let me explain how this works. You are gonna ha I'll, I'll go through the notes later because we're gonna walk through it. But basically there is a, your, for the web apps app itself and every link that we make with this get trackable, get URL to. . It's just taking that web app, it's going to that web app.

It's saying give me the unique code that, that as at the end. Okay. And it's saying, now go find that unique code on the DB tab. So we have our unique codes here. It's parsing that that equal unique equals, it's parsing that better. Sheets co. Here, I'll show you, show you again in the Copy link address.

I'll paste it here in a new tab right there. So this URL is, this is the, the all of this here is the web app, which I'll show you later how to get that when we set it up. But now we have a question mark, unique equals, and it says, better sheets come. Okay. So this code right here is saying just get me the database, get me all of the values.

This one is all of the unique values. This is all the real URLs, and it's literally listing those in an array of, of answers and what it does in this four loop. Okay? In a four loop, it says, find me the one that matches with this unique code. So, Go it, it grabs this column, unique code, and it says Better sheets, code matches this one, which is the first one.

And in array, in array talk, that's actually the zero one. So it says is, give me that zero. It says, find that zero one and now go get whatever is in the other. This.  this column, give me what is in the zero part of that column. Okay. And then redirect me, but it doesn't redirect me right away. So what it does is it does this HTML service dot create html output and it does this body and it, it says body, and then in the script it, it's actually a, we can take out this redirecting and it's, we can actually take out this whole body and.

creates an HTML output, and the only thing it does originally, or what it only really does is it says window.top location, hf, which is saying the, the, the HTML top that you're in. This, this Chrome browser. , go to the URL and it's getting the zero one. So it says go to better sheets.co. And that's it. So it's really redirecting me and then I added this body redirecting.

Just so that you have some script here, let me show you what it looks like if we take out this body part. Let me do, let me do this. Let me grab this button cause we're gonna put it back in. Once we, we do it, I'm gonna just copy it. I'm gonna delete it. Save. I'm just gonna hit publish Deploy as web app. I have to, I'll show you this again later.

Showing off without redirecting text update. Now we don't have to do anything to the web app url. All these links still work, and now what'll happen is you will not see the text that says redirecting. What you'll see is this. , just message that if it, if it takes some time here, it'll say a message like, this application was created by another user, not by Google.

It's just like letting people know this is not cool. So there it goes. See, the link still works, but it didn't have that message of redirecting. So we're just gonna add that right back in. So now all we do is add body redirecting and it's just text that says redirecting. You can say anything you want there.

If you wanna brand this, if you wanna put a quick message that says like, thanks. You wanna put an image? I don't a hundred percent know if that'll work, but let me now publish again and again. This is how you make changes. I might show this again later. Put back in text redirecting I might show this later, but I might not, is like, this is how you update it.

If you wanna add different elements to it, you literally just deploy his web app. Make sure you click new up here and then click update and all the links will still work because that web app URL is still the same. So now if we go and use one of these links, let's go to gum road.com. Now we should see an HTML thing that says here, text.

It just says redirecting. There it is. Redirecting. That's it. That's all it says. That's all it needs to say. Cause right away we're over at gum road dot. . Cool. So that is how to add click tracking and redirecting to your Google sheets. You get this code, you'll be able to copy this code if you want to, and then you'll have to create a DB tab and the settings tab so that we read it.

But I'm gonna show you how to change those things in just a second. First, the first thing we're gonna do right now is I'm gonna open a incognito tab. I'm gonna show you how to do this. So we're just gonna write copy at the end. If you get a link, if you get this link, you'll get this link with copy at the end, which will bring you here, and it will just say, copy, oh, oh, I have to log in in a second.

Let me log in with another account

and should be able to log in.

It says, welcome. Ah, I need access. Good thing I did this. I will share this. All right, we're back in. We're now making a copy of it. Now. If you get the link, you'll get this. You can make a copy. We're gonna make a copy anytime now, waiting for Google. All right, so now you can get this, you can see this URL up here.

You can't copy this cuz this one won't be available to everyone. Meaning this is like a different, this is just copy of it. The original is available to all better Sheets members. I'm gonna call this my click tracker. Click tracker. Mr. Clicker tracker. All right. My clicker tracker is here. Now what I need to do is go to fi Nope, not file  Tools.

This is what you're gonna have to do in order to set this as a web app. I'm gonna go to script editor anytime. Now. It's probably a little slow because again, I'm recording this video as we speak as you watch this video. Alright, so we need to get the web app url. How do we get that? We go up here to publish.

We click deploy as web app and there we go and we click new and we deploy review permissions. I have to, you have to also do this because it needs some permissions.

I wish I had Jeopardy music play. I guess I don't because that would. . So it just needs to c edit, create, and delete your spreadsheet. Oh, that's, that's not so bad. Allow. This project is now deployed as a web, so this current web app url. Go ahead. Command A, command C. I'm gonna copy that whole U url. I'm gonna go to my clicker checker.

Go to settings and replace. One here in B one. Okay, when delete that and paste it. So now you can even see here it says cam fee approved.com. So I'm on the cam fee approved.com. Your domain, so this script is now mine, not the original one I was using. So if I go over to the db, I think it will already be here.

This get URL to click is already. And we already have a script, so let's double check. Let me reset all of these and let's double check that this is working, right? Because if it's not working, I have to go and fix something. . Oh, I'm so excited. Redirecting all, everything's working so far. Let's go see if it actually did it track the click?

Oh, no.

Okay, so I think I figured out, so when you copy it, you have to disable web app because it probably is copying my web app as if it's yours. So first off, when you go to publish, deploy as web app disabled web app yes. And then.

execute as me and anyone, and then click new and then web, and then deploy, update. And let's see if this'll work. Now. Now again, grab this. Any, we don't have to update this every time. We it should be the exact same. Let me double check. , maybe it's, yeah, it's the same. Yep, it's the same. So just pace that there.

Let's see if now it works. Once we disable it and then enable it again. Let's see if this works.

there it is. So that was the trick is when I originally copied it, it was still running the web app as my, my web app. So you will have to disable the web app and then enable it. So again, you're gonna go to, let me do that again so you can just double check and see that. You can see it is, we're gonna go to tools, gonna go to script editor.

And this will probably be much quicker for you because you're not also recording the video. You're gonna go publish Deploy as web app, and then you're gonna have to click disable web app update. And then , then deploy again and just keep clicking new when you update it so that it writes over again and you can have different project versions just in case you make changes.

Okay, so now this new clicker tracker is working in. I'm just gonna double check that. Another, like another url. URL definitely works on here. Again, we can make a lot of edits here. See one and one, and then if we make another click on this url. . There you go. So we'll close that. Go back. Fingers crossed it says two.

Yes. Okay, so if you are anywhere in your sheet, you can use this function to get a URL to click. Okay. So you just go here and you say, here in the db, all we have to do we is add like http.  s let's do youtube.com/better sheets and our unique code will do YouTube. That's it. Okay. Now all we have to do is get this url.

Okay? So this is really weird, so all we need to do is get this YouTube. Okay. So we can do that here. We can do equals, oops, get URL to click and we just need to do B six. We can also, if we know it, we can do it like that. And that's going to get us this script. We really, even if, if, you know, you can also create this u URL on your own cuz it's just going to.

The first part of the web app. So it's all of this in settings here. It's just this URL then in question mark and then unique equals whatever the code is. Okay? That is, that is the URL to get this special. So we can also put this in anywhere we want. So I, I can even avoid this, this. I'm not gonna delete the whole thing just in case you want it.

So we can say, okay, we need a URL here, link to YouTube. We can go equals get URL to click and just type in YouTube.

Let's see that link. And now go to that link and it should redirect us to our YouTube youtube.com/better sheets. There it is. So if we go back to our database, see it says one. Okay. So all we need to do now is know this unique code that we typed in. It could be anything. You could also, if you just need random numbers, can do, do that round up.

Get a random set of numbers.  and then shift command V and now that'll work. So our YouTube, our, our unique code YouTube won't work anymore. But now this will work. So we can do again equals get your URL to click anywhere we want. We just get that. Or we can say B six works the same. We're just getting this, these numbers and there's our script.

So again, our script will take us to. YouTube page and it will add one here. So two. So this is how we can then use anywhere in our sheets, we can create. Now we have a database, so we can say link to YouTube. We don't have to show this. Okay. We just need this URL to click. Okay. So we want to, we wanted to say, we wanna make a nice, cool button here.

Let's say we want to make it look like this and make a URL here. So we go hyperlink and we get that. Get URL to click and put in YouTube. And then what's the label we want here? We want it to say U2 two, wink. Right?  and now that's a link to our script. So let's take all of that. Let's go to view, let's get rid of our grid lines.

Let's make this centered, I guess, and make it like a button, maybe like a big green button. Green button. Yeah. Maybe red button. A big red button with text. White. , let's change that to like impact or something. Or aerial black is good. 20. There you go. And actually, let's make this even like look a little but more buttony.

Let's put our borders, thicken those bottom. Yeah. Bottom and side. Make it a little buttoning. And now we have a link here. YouTube link. We can even wonder if it's gonna let us merge all of these together. . Let's merge those sales. There we go. It's one, one giant cell to rule them all. And anywhere we go here, boom.

Click that link. YouTube link. And now, whoops. We have an error. Oh my God. Pan error. Do. Oh, . No.  because we got an error, because we don't, we're not using YouTube anymore. We're using this. So if I change this back to YouTube, boo boo, boo boo. Now it'll work. So I showed you one, one error. You're gonna have, if you change your, your unique codes, you're gonna have errors.

It's gonna look like an error that you can't, it won't know where to go . It just, it won't find it. Right? So we have our.  got to see an error and now we have three here. So we can also use this like if we have this link right, we can, we can make a little dashboard. It's like, oh number of clicks and we can go equals

And we just point it over here at this and now, . Actually, I want to do this. I wanna say three people clicked the link. Make this

center it up.

So now every time we click it, let's just open in a new tab in the background. . Look at that. That number just keeps going up every time somebody clicks that button. And so now we can create little cool dashboards of like, if we go back to our newsletter mall, if you ever, if you haven't checked it out, go check out virtual new newsletter, virtual mall.com.

Check out newsletter virtual mall.com. If I had this click tracking before I could have put every link behind a click track. And known which newsletters actually get more clicks than others. So I can figure out, oh, which storefronts are working better? Which ones should I sell for more amount of money the next time if I sold storefronts?

And I think it's a really fun thing to add to our sheets, especially for even internal use, just to know like, Hey, if we send out links in our emails you can use this outside of Google Sheets as well. This is really cool. This is just like a really basic click. We can use outside of Google Sheets, but Google Sheets is definitely going to power this and I'm really excited to see what use cases you have.

If you're using this, if you run into any issues, email me. If you are watching on YouTube, then just find my email somehow, I guess. Become a Better Sheets member. As of right now, as of making this video, better Sheets is available, but with AppSumo, so go check out Better sheets.co. There's a link there to buy it on AppSu, you get lifetime access right now.

Which means like as, as we improve, as we get more and more videos, all templates and stuff that I make as part of Butter Sheets are available. All of them are available to better sheets members. They are able to go and copy the the pay, the, the original template and the original code. If you are not able to pay, then go ahead and try to recreate this yourself with, with, by watching this video.

You're more than welcome to. But I think it's a fun implementation and a really, really cool way to really power our sheets in a whole new and different way. Track those things, do ab testing in a Google sheet with links really, really fun things. I think you're gonna come up with some really cool ideas.

Again, let me know those ideas. If you're watching on YouTube and you use this and you put. And you use it in a really cool way. Go ahead, share this link. If you are not a part of our Facebook group I have a public Facebook group called I Love Google Sheets. Go share your use of this in there. I'd really like to hear your cool uses of your click tracker inside of there.

So facebook.com. Slash Group slash I love Google Sheets. If you love Google Sheets too, I think you're gonna love that group and share all cool stuff. So thanks so much for watching. I hope you enjoyed adding a click tracker to your Google sheet. Spite.