Create a Tracking Number Fetcher with Google Sheets and Google Sites

Advanced

About this Tutorial

Build a tracking number google site with Google Sheets as a backend.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 We're creating a tracking form on a website that allows you to have a sheet like this with tracking numbers and statuses and someone is going to search for a tracking number you give them and they're going to get the status. 00:13 You can update this Google Sheet in real time and they can go and check the form on a website and find the status at any time they want. 00:21 So, you don't have to keep people up to date on the statuses every time they ask, they can just check it. 00:27 So, we'll see 987-654-321. We'll go to our sheet.

They'll be able to enter a tracking number on this Google site that's actually free. 00:38 Sites.google.com allows you to create free websites. We can track it. See status delivered and that status is here. So if we. 00:48 Check 456, blah, blah, for pending. Is it pending? Let's go check that tracking number track. And there, it says pending. 00:59 So anytime someone wants to find out where their item is, they can come here and try it out. So I'm going to show you how to create this step by step. 01:10 The steps we're going to do is we're going to create this sheet.

So we have a sheet with our numbers in it. 01:16 We're going to create a little bit of an API that is over here. All of this we're going to do in this video. 01:23 That's the main part of this video. And we're also going to create a Google site where if you're watching this on bettersheets.co, you can get this exact HTML and this is the embed for it. 01:34 If not, go become a member of bettersheets.co. right now. So that's the three steps we're going to do in this video. 01:41 We're going to create the sheet here. We have items, tracking number , and statuses.

The big issue here is to make sure your tracking number and your statuses are aligned, right? 01:52 That their tracking number is aligned with the status. That's all we need for the sheet. And now let's go create the API. 01:59 So that's up in extensions.go. We're going to get through all of this, but for right now, when you open your Apps Script , it's going to look like this. Function , my function . 02:16 We're going to change this my function to do get. This is a built-in function with a capital G, make sure. 02:24 And we're going to the URL.

Essentially, what we're doing is creating our own API for this specific sheet that anyone can access anywhere. 02:32 And I'm going to show you all the steps in this video. So, what we're going to end up returning is return contentService.createTextOutputStatus. Now, faulting, meaning if nothing is found, we want variable status equals trackingNumberNotFound. 03:01 Now this allows us to say, you're going to get something out of this any time we access this.

We will need to eventually in this video, I'll show you the steps. 03:13 Deploy this, this is the big button here, deploy this as a URL on a web app and get a URL, but for right now, let's do all of the coding we need. 03:24 So we need a trackingNumber because this URL is going to be, when you submit the form, the form itself over here is going to have this trackingNumber and is going to send it to the API and set to, hey, what's the status of this number ? 03:40 So we're going to get the trackingNumber from that URL by URL. parameter .trackingNumber.

Now we need to get the actual trackingNumber, SheetByName, and this is just tracking. This is just the tracking sheet itself. We want the statuses. Statuses equals tracking. getRange . This is C colon C, and this needs 04:17 getValues . What's going to happen here is we're going to get a an array of the C column. All of the things in the C column is going to be in a single array. 04:30 We also want the numbers. Variable numbers equals the same thing. Tracking. getRange , but B column getValues .

You change these two here, the B and C column, if you have different classification of your values . 04:50 Basically, we're just going to look through the numbers column, find a match , and then give the statuses back. So we have our tracking number not found, and now we create a for loop. 05:03 For i equals zero, i is less than numbers dot length, and and I++.

Now this for loop is, It's going to go through every single number here, these values , this array, and if tracking number , this number we get from the URL, is equal to numbers i, and we're going to get just the item in that array, then 05:33 we're going to do something. We're going to say variable status equals. Statuses, same spot in the array, so this i, there you go. 05:44 So this is all we need, this is all of the code we need, before we deploy. Deploy, new deployment. We're going to deploy as me, but anyone has access. 05:59 We'll hit deploy, and we're going to get a URL.

This is going to be a very important URL, where you can copy this. 06:04 Now what's going to happen is if we open an incognito window, and we paste this URL, and at the end we do, question mark, tracking number , with capital N, equals, let's go grab one of the tracking numbers, Let's see, 1, 4, 7. So see, this URL is just saying, hey, go to this URL, add the parameter tracking 06:31 number equals this number . We're going to hit enter, and it says in transit.

So this is our API now, that anyone can access with any tracking number , but we're going to clean it up. 06:42 We're going to create it at the back of a form. So we're going to create a form that someone can enter their number in, and and get this response from. 06:51 We're going to show it nicely. But that's the general idea of this URL, and this web app. Make sure we have this URL. 07:01 We're going to grab, actually, just all of this URL up to exec. We can always get it from deploy, manage deployments. 07:10 Here, this web app URL , copy that.

That's the URL for this, and we, this do get is really awesome, because now, we have an embed. 07:21 Now, this embed code, I've created it. It essentially is a form up here, just tracking, called tracking form. On submit, it's saying, fetch to the tracking status, and then here's this script that's written in JavaScript, that essentially says, go to this, uh, go to this URL. 07:37 Add this tracking number here, and then get the response, and add it to a little text here that says status. 07:45 That's it.

So, the only thing you need to change if you have this code, is right here, this URL, paste our new web app URL , right here on line 13. Again, if you want this code, go over to bettorsheets.co and get it. 08:01 Everyone watch it. Everyone watching this on bettorsheets.co can get this code. I'm going to copy this, because we're going to go create a, let's delete that. 08:09 This is a Google site. We can get to it by site.new.2, just create a brand new Google site. That's all we did.

We didn't do anything else to this, and all we're going to do is on the far right, Embed code and paste our embed code, so all of this has been written. 08:30 Again, this is just a form with a status that literally, text that says status, and a script that says go to this JavaScript, go to this URL, and get the response. 08:41 Pretty cool. Hit next. It's going to look like this. Hit insert. We can extend this all the way up. Publish .

Click publish . 08:52 You're going to have to publish this publicly, and then we're going to get a link, publish site link, copy link. 09:00 I'm going to open, I already have an incognito open. Incognito is going to be easier to do. Paste this, so this is just a bettersheets.sites.google.com slash bettersheets.co slash delivering happy. 09:16 Enter a tracking number , and if we enter a tracking number that is, like, doesn't exist, let's see what happens. Not found. 09:25 So let's move this out of the way a little bit.

Go find a number that actually exists in our sheet. 09:30 Seven, five, four, three. Enter that. Out for delivery. So now that is done. We have our Google site, we have our API created, and our sheet. 09:43 Everything is happy. If you are trying to do this somewhere else other than BetterSheets.co, we just need a form and a script that goes to this URL. 09:53 That's it. That's how to create a tracking number getter in Google Sheets with a front end on a website that anyone can access anywhere. 10:04 We just did that in about 10 minutes. Hopefully you can do that too. Enjoy.</div>