43 Minutes of Google Sites and Google Sheets working together

0:00 Here's a compilation of things you can do with Google Sheets and Google sites, getting Google sheets into Google sites, integrating Google sites with Google Sheets. Let's just get into it. 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. 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.

0:46 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. 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 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.

1:33 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.

2:19 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. 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.

3:06 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 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.

4:39 These values, this array, and if tracking number, this number we get from the URL. 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.

5:29 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.

6:16 get is really awesome 'cause now. 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.

7:02 Let's delete that. This is a Google site. 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.

8:32 Copy link. I'm gonna open. I already have an incognito. 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.

9:18 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. 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. Pretty simple. In this video, we're going to add an NPS score taker or rater to your Google site. It's going to use a little bit of Apps Script, but I will show you, I will write it here live, so you will see it is not that much.