Create a Live Tracking Form with Google Sheets and Google Sites

0: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. 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. So. You don't have to keep people up to date on the statuses. Every time they ask, they can just check it. So we'll see. 9, 8, 7, 6, 5, 4, 3, 2, 1. We'll go to our sheet. They'll be able to enter a tracking number on this Google site that's actually free. sites.google.com allows you to create free websites. We can track it, see status delivered, and that status is here. So if we check. Create a Tracking Number Fetcher with Google Sheets and Google Sites How to Use Google Sheets as a Website Database Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet Track Anything! In a Google Sheet

0:45 4, 5, 6, blah, blah. For pending. Is it pending? Let's go check that tracking number . Track. And there it says pending. So anytime someone wants to find out where their item is, they can come here and try it out. So I'm gonna show you how to create this step by step. The steps we're gonna do is we're gonna create the sheet so we have a sheet. With our numbers in it, we're gonna create a little bit of an API that is over here. All of this we're going to do in this video. That's the main part of this video. And we're also gonna create a Google site where if you're watching this on Better sheets.co, you can get this exact HTML and this is the embed for it. If not, go become a member of Better sheets.co right now. Create a Tracking Number Fetcher with Google Sheets and Google Sites How To Create a Stop Watch in Google Sheets Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet Track Anything! In a Google Sheet

1:31 So that's the three steps we're gonna do in this video. We're gonna create the sheet here. We have a items tracking number and statuses. The big issue here is to make sure your tracking number and your statuses are aligned, right? 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. So that's up in extensions app script . We're gonna get through all of this, but for right now, when you open your. App script , it's going to look like this function , my function . We're going to change this, my function to do get. This is a built-in function with a capital G. Make sure and we're going to get a URL. Essentially what we're doing is creating our own API for this specific sheet that anyone can access anywhere, and I'm gonna show you all the steps in this video. Create a Tracking Number Fetcher with Google Sheets and Google Sites GPT in SHEETS Access Free API's with Google Sheets 43 Minutes of Google Sites and Google Sheets working together

2:19 So what we're gonna end up returning is return content service. Create text output status. Now we want a status that is defaulting, meaning if nothing is found, we want variable status equals tracking number not found. Now, this allows us to say. You're gonna get something out of this. Anytime we access this, we will need to eventually, in this video, I'll show you the steps. Deploy this. This is the big button here. Deploy this as a URL and a web app and get a URL. But for right now, let's do all of the coding we need. So we need a tracking number because this URL is going to Create a Tracking Number Fetcher with Google Sheets and Google Sites 43 Minutes of Google Sites and Google Sheets working together

3:04 be when you submit the form. The form itself over here is going to have this tracking number and is gonna send it to the API and say, Hey, what's the status of this number ? So we are gonna get the tracking number from that URL by URL parameter DO tracking number . Now we need to get the actual tracking numbers, so we're gonna say spreadsheet , app dot, get active spreadsheet . Get sheet by name and this is just tracking. This is just the tracking sheet itself. We want the statuses. Statuses equals tracking. Do get range . This is C and C and this needs get values. What's gonna happen here is we're going to get an array. Of the C column, all of the things in the C column is gonna be in a single array. Create a Tracking Number Fetcher with Google Sheets and Google Sites Getting Started Coding in Apps Script 43 Minutes of Google Sites and Google Sheets working together

3:51 We also want the numbers. Variable numbers equals the same thing. Tracking do get range , but B column get values . You change these two here, the B and C column. If you have different location of your values , basically we're just gonna 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. For I equals zero I is less than numbers length and I plus plus. Now this for Loop is gonna go through every single number here. These values , this array, and if tracking number , this number we get from the URL. Create a Tracking Number Fetcher with Google Sheets and Google Sites Figure Out Frequency of Numbers 43 Minutes of Google Sites and Google Sheets working together

4:36 Is equal to numbers I, and we're gonna get just the item in that array. Then we're gonna do something. We're gonna say variable status equals statuses. Same spot in the array. So this I, there you go. So this is all we need. This is all of the code we need before we deploy. Deploy new deployment. We're gonna deploy asme, but anyone has access, we'll hit deploy and we're going to get a URL from this. This is gonna be a very important URL. We can copy this. Now what's gonna happen is if we open an Inc 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. Create a Tracking Number Fetcher with Google Sheets and Google Sites 43 Minutes of Google Sites and Google Sheets working together

5:22 Let's see, 1, 4, 7. So see this URL is just saying, Hey, go to this URL, add the parameter tracking number equals this number . We're gonna hit enter, and it says in transit. So this is our API now that anyone can access with any tracking number , but we're gonna clean it up. We're gonna create it at the back of a form. So we're gonna create a form that someone can enter their number in and get this response from. We're gonna show it nicely, but that's the general idea of this URL and this web app. Make sure we have this URL. We are gonna grab actually just all of this URL up to exec. We can always get it from deploy managed deployments here. This web app URL , copy that, that's the URL for this and we this do get is really awesome 'cause now. Create a Tracking Number Fetcher with Google Sheets and Google Sites 43 Minutes of Google Sites and Google Sheets working together

6:09 We have an embed. Now, this embed code I've created, it essentially is a form up here just tracking, called tracking form on submit, it's saying fetch 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, add this tracking number here, and then get the response and add it to a little text here that says status. 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 better sheets.co and get it. Everyone watching this on Better sheets.co can get this code. I'm gonna copy this because we're gonna go create a. Let's delete that. This is a Google site. Create a Tracking Number Fetcher with Google Sheets and Google Sites When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet Track Anything! In a Google Sheet

6:54 We can get to it by site.new two slash two. Just create a brand new Google site. That's all we did. We didn't do anything else to this. And all we're gonna do is on the far right click embed, change it to embed code and paste our embed code. So all of this has been written. 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. Pretty cool. Hit next. It's gonna look like this. Hit insert. We can extend this all the way up. Hit publish . Click publish . You're gonna have to publish this publicly. And then we're gonna get a link. Publish site link. Copy link. I'm gonna open. I already have an incognito. Better Email Form on Google Sites How to Use Google Sheets as a Website Database 43 Minutes of Google Sites and Google Sheets working together

7:40 Open incognito is gonna be easier to do. Paste this, so this is just at better sheets. sites.google.com/better sheets.co/delivering happiness. Enter our tracking number and if we enter our tracking number that is like, doesn't exist, let's see what happens. Not found. So let's move this outta the way a little bit. Go find a number that actually exists in our sheet. 7, 5, 4, 3. Enter that out for delivery. So now that is done. We have our Google site, we have our API created and our sheet. Everything is happy if you are trying to do this somewhere else other than better sheets. At co, we just need a form and a script that goes to the CRL. That's it. That's how to create a tracking number . Create a Tracking Number Fetcher with Google Sheets and Google Sites How to Validate Email Addresses for FREE in 17 Minutes Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet Track Anything! In a Google Sheet

8:26 Get her in Google Sheets with a front end on a website that anyone can access anywhere. We just did that in about 10 minutes. Hopefully you can do that too. Enjoy. Convert Google Sheets into a REST API How to Use Google Sheets as a Website Database Advanced Coding in Google Sheets for Programmers OnlySheets