Embed a Number in a Website from a Google Sheet

<div>00:00 Hello, welcome. So this started as a test, and now the test is complete. We can now embed a number from a Google sheet into a website. 00:11 You are seeing the result of this test right here at embed a number .card.co. I'm using card as the website builder. 00:18 It allows me to insert a little bit of html, JavaScript and a little bit CSS or a little styling. Those three things combined, I've written that all for you if you want to use this and wanna test it yourself. 00:30 And I'll show you the app script that I use to power this.

So let me just show you how this works. 00:35 First off. So on the le on the right here is a test. That's text that I just added, but this 99, this number is in this Google sheet here on the left in a one we can actually make that a little bit bigger. 00:50 So if I can just change that to say 44, 33. Now I just changed the sheet alone, and now on the right side on the website, I'm just gonna hit refresh. 01:03 Just hitting refresh will make that number go away. And now there's 33.

See, the number is refreshed, is going to be whatever is in the Google sheet. 01:13 Now this Google sheet is basically like a database, right? It's essentially at the end of an API call or in this case http request, get request. 01:27 And all of that you don't necessarily need to know. But I'll show you how this works. This works with literally two lines of app script here, and I'll walk through those. 01:37 And if we look at the embed, I've saved the embed in the sheet here. If you have access to the sheet, you're gonna have access to this code. 01:45 What? Right here.

Okay, so let's start on the left side where we have the code. Now that's probably the, gonna be the hardest part of this because in JavaScript you can just use this and copy paste it and that's it. 02:02 But you might wanna know actually how this works in JavaScript in app script . So it's a function do get. This is something that's pretty easy to implement because literally all you have to do is type, function , do get, do then a capital g e t that is all you have to do. 02:21 You don't have to install anything. You don't.

This is a trigger that is called a simple trigger inside of Google Sheets. 02:28 Just writing do get allows you to then deploy a web app. Now that's the, that's the big part, is you're gonna go up once you have this written, and I'll go through these two lines of texts soon enough. 02:42 Let's make it a little bit bigger. Once you have this text, you're going to hit up here deploy new deployment, and you're gonna go through the process of deploying. 02:52 You'll have to select the type web app and then hit deploy for the first time.

The first time you do it, you're going to have to enable,  You know, authorize authorize it. 03:02 What you're gonna want to do is execute as me and then who has access . You're gonna wanna change this to anyone. 03:08 I will try to show you some errors you might encounter as we go about this, but probably the first part of this let's go from the beginning. 03:16 Let's create a spreadsheet , and you can do that with sheet.new. I just have a one sale sheet. I'm just putting in a one. 03:25 And I'm, I named this sheet number with a capital N. That comes, i, that's important.

Later in our app script , we're just gonna write function . 03:36 Do literally do capital G get, now I'm writing it again, right under it, just so you can see how I wrote this step by step. 03:46 So I had these curly brackets, I have these parentheses and these curly brackets. And inside the curly brackets I'm gonna do we wanna do variable data equals, actually we'll add this later. 03:57 The most important part is spreadsheet app with a capital S and a capital a spreadsheet app dot get active spreadsheet . 04:06 We can use this auto cor auto fill , auto complete.

If you do not have auto complete here when you're typing this out, then you probably made some error , like maybe you did spread with a a not capitalized s or you did a sheet app. 04:22 That's easy to do. Now if I hit the dot, there is no auto complete.

So those are easy things to get wrong. 04:29 If this is the first time you're doing app script , if you says is the first time you're doing app script , this is a pretty advanced concept. 04:36 This do get go ahead and check out spreadsheet automation 1 0 1 over@bettersheets.co or have the whole course on Udemy as well. 04:43 Spreadsheet automation 1 0 1 by Better Sheets. So we have spreadsheet , app dot get active spreadsheet .

One thing we're gonna have to do, not on the pink part spreadsheet app, but everywhere else we're gonna have to add these parenthesis, nothing inside the parenthesis, just the parenthesis we need to add. 05:03 Then we're gonna do get sheet by name. And so in this case, we're actually going sort of deeper and deeper from the spreadsheet app as a whole across all of Google sheets to get active spreadsheet , which is the file. 05:13 Then we're gonna go inside it to get sheet by name. It is a capital B.

You can just start typing it and then select it from the auto complete. 05:21 If you don't select it, make sure that it's capital S, capital B, capital N. And then you must add the parenthesis. 05:30 Now, inside the parenthesis we're gonna put in quote numbers. That's the name of the sheet. So if you ever wanna change the name of this sheet, change the name here as well. 05:39 Gonna do get range . And here we're gonna do a one in quotes.

A one is the cell that you're trying to get the number from here to your website. 05:50 You're trying to embed in your website whatever number that is, wherever it is in your sheets, that's where what you need to do. 05:56 You gotta get The, the sheet name and then the range . Then we're gonna do get value.

This is probably the most easy or easiest to forget part because we think sheet by name get range . 06:10 If you haven't typed out app script before this Get value actually gets the value inside the cell, but the range just gets the cell and we think, okay, we got the cell, we got the number , we're on our way. 06:19 No, you're not. You have to get the value that's inside the cell here. That's inside the cell, inside the range . 06:27 That's inside the sheet, that's inside the sheet file, it's inside Google Drive, right? That's all of this here.

We're going from Google Drive to Google sheet to the sheet tab , the tab to the range , to the value inside of that cell. 06:39 That's stepping down, down, down into that. Now at the beginning, we've gotta do variable data equals, that's why I like to do that variable data later. 06:48 Now, where do we want that variable? Well, we want the, the do get. Whenever the H G T P request goes out, what do we want to get out of that? 06:57 We want to return, we always have to have a return.

And in this case, we want to return the data . 07:04 But in many times the data is going to be we don't necessarily know what it's going to be, but we wanna return a string . 07:13 There's strings, there's a array. There, there's different types of data or different types of information that we can send. We wanna do two string . 07:24 And again, when we type out data , cuz we have a variable data here.

When we hit a period, we have auto complete here, select two string again, add the parenthesis, but that's not everything. 07:39 In order to use the do get, we need to use this content service to create a text output. So we're gonna wrap this. 07:46 I'm actually gonna get do Command X and cut it. So we'll paste it later. We do content service with capital letters, just like spreadsheet app is a embedded sort of lot of functions that it gives us. 08:00 Content service is the same thing. Content service with a capital C, capital s dot.

Now we only have two options. 08:07 Now I said it has a lot of options, but this is the only two that we have for content service. 08:11 Just like spreadsheet app has this auto complete of all the things available. Content service is the same. So now we are gonna select create text output. 08:20 Inside of this create text output. We are going to paste what we deleted, which was data dot two string .

But you can also just type that out if you want to data dot two string and then add parentheses after the two strings. 08:33 So this closed parentheses is for the create text output, whereas the two string must have its own open and closed parenthesis as well. 08:41 That is it. That's the whole shebang. That's all of the app script you need to know. Once you type this out, you're only gonna need one. 08:50 You don't need multiple Do gets, I just wanted to type that out so you knew how it was built.

You're gonna save, hit, deploy, new deployment again, the first time you do this, when you hit web app and then configure I gotta select it again. 09:05 You gotta do execute as me you <laugh>, that means you, not me, me, you, you. And then who has access ? 09:12 This will be, it will automatically be selected as only myself. Change it to anyone and then hit deploy. If you make any errors and stuff, you can always redeploy. 09:22 We are just gonna grab this web app. Hit done.

Now, if I hit shaman shaman shift command and I'm gonna open an incognito window, I'm gonna just, the only thing I'm gonna do is paste that web app u URL that I was given by Google. 09:38 Hit enter and see what happens right here. 33, that is it. That is the whole thing. We've essentially created an API that takes a u URL input and gives you back some number . 09:52 And where that number is, is inside of this Google sheet.

So we can change this to 42, hit enter, go back to our here our, our script, or not our script, our our url. 10:06 Hit command R to refresh it and send that get request again. And here we go. We got 42. If we go to our page over here, we hit Command R. 10:16 We are refreshing getting that, that get request again, 42. It is refreshed. Every time we want to refresh the page, we can refresh it. 10:26 If we change the numbers, it'll be, it'll show up the next time you open that page.

All right, what's the next step is it's the website. 10:33 What's really cool about this is that you can create this, your url, you can embed it into a, on a website because all we're doing is going and getting that that number that this, this text response. 10:48 But how do we actually put it into a sheet? Now this is gonna be a little difficult for me to explain cuz it's not my suit. 10:54 I'm, I'm very good in Google Sheets outside of Google Sheets.

I'm not necessarily that great, but I have written a little bit of JavaScript here for you. 11:03 And essentially what we're doing is we're creating an H one at the top and we're giving the idea of number . 11:09 The script is gonna take the URL that we have that we deployed, which is actually we need to update that, I do believe. 11:17 There we go.

It's going to take the URL of the web app that we've created and it's gonna go fetch it. 11:24 And then that response, we're gonna get the text back and we're going to then take that text that we get back and insert into the document. 11:34 Wherever it says number this ID number , we're going to append the A span with that number in it. And that's what all of this is doing here. 11:44 Again, I'm, this is out of my element a little bit, but the, this JavaScript is working as long as you use it here as I'm doing. 11:53 You do have to wrap this in a script and then a slash script.

If you are familiar with JavaScript, this is going to be super easy for you. 12:01 And maybe the app script was a little bit difficult to know or didn't know exactly what to type. You can also style this with your own css. 12:10 I've just chosen to style with color white and a font size 50 pixels. That's it. So that's the CSS as much <laugh>, I didn't even use css, I just used styling here. 12:22 Used an H one in this particular case.

You might need it in multiple places and you might need multiple numbers, but that's not the part of this video that we're gonna go over. 12:35 So all I did is I'm using card I inserted a HTML embed and then I put the code right here as you see it here. 12:44 I took all of this and we can copy paste it, click done, click publish changes. And I'm just publishing this directly to a site on card. 12:58 And let's see, we're double checking. It's working 42 and here we go. 33.

So I'm gonna now show you a couple errors that you might run into if you're doing this. 13:09 First off, if you are getting, let's see, you might not have this Doge correctly spelled, you might not have the parenthesis, you might not have. 13:21 If you don't have the parenthesis, I don't even think it's gonna let you save. There it goes.

You got a syntax error , unexpected, unexpected token and it shows a red line here under the curly bracket. 13:32 But in actuality, the real problem is that you not with the curly brackets, that you don't have parentheses if you don't have the curly bracket and you hit command S see what happens. 13:42 Syntax error , unexpected token variable. It'll say the error is here at variable data , but in actuality you don't have the curly brackets. 13:51 So will move all of this inside the curly brackets. Hit save again there on deployment.

If you are deploying and you choose and you don't have access to anyone, you give it only to myself. 14:08 I'll show you what shows up here. We'll deploy this. We're gonna grab this web url, web app. I'm gonna hit shift command, end opening incognito window. 14:19 And I'm just gonna paste that URL right here. And we should get something like this, right? This is a sign in. 14:25 This is not what we want. We just want the text. You might get an error . But in our case, we, it asks us to sign in. 14:35 So we go back to deploying new deployment.

We're gonna change this to anyone.  Yeah, we make that me hit deploy again, updating the deployment copy, then do it again. 14:53 And we got the number . All right. Another issue is if you miss spell the name of the sheet in our here. 15:02 So if it's like numbers, let's say or we not capitalized, right? A hit command. S hit deploy new deployment, like gonna deploy this. 15:11 Like the first time we deploy it, we're like, okay, every everything's fine. You know, spoil alert, we know that the sheet name is wrong again, let's test it. 15:21 We get type error .

Cannot read properties of no. So it's saying get range . It's, it's telling us exactly where that error is. 15:28 Get range , let's go look at get range , get range . So again, the errors might not be exactly where they are, but just after or just before, just after where the real error is, which in this case, again, spoil alert. 15:40 Spoiler alert, we have the wrong sheet name, we need number, okay? So again, we fix that. We'll hit deploy new employment and we hit deploy. 15:54 Get our url, we try it again and everything's fine. <laugh>, right? Okay, done.

Now let's say we have done all of the app script , correct? 16:06 We've deployed it correctly. We have it on our website and we go back to our sheet and we delete that number . 16:12 What happens? Let's see, <laugh>, what happens? We're going to get nothing. <laugh>, absolutely nothing like this is one of those weirdest errors where you're like, ah, is this an error ? 16:24 I mean, I'm not getting anything. Should I get something? You might be googling for this google user content.com. This tells you it's like a redirect issue. 16:33 If you're just like searching for this.

I've been searching for this for days. I was like on this as a prom. 16:38 And really the actual issue is you don't have a number . It's, it's showing you exactly what there is. We'll put 8 74 there. 16:46 Again, change nothing else, but change that number . There we go. We have that text as a number 8 74. Great. All right, if, are there other errors? 16:57 There might be, you might have errors in the not the pronunciation. Oh my god. The capitalization of spreadsheet , app of content service. 17:07 If you have it correctly colored in here, then you know you're on the right track.

If it's not a pink, then you're on the wrong track again. 17:17 So if we just have a under, under case, no lowercase c you won't get that pink content service and you also won't get the auto complete. 17:27 We did, you might have active sheet here.

I would recommend active spreadsheet because we're not actually on the sheet itself when that get request is happening. 17:38 Active sheet is really used for when you're creating an app script and a script that runs when you're using the sheet itself. 17:48 You might also have an issue with, with data dot two string , you might forget to add the two stringing. You're like, Hey, this is a number , I just want the data done. 17:55 You might have a variable data , you might call this number and then this data wouldn't exist.

And you'll know if this variable is correct or not because it's grayed out. 18:05 If it's grayed out, it is wrong because it's not used anywhere. That's what this is telling you. This number is saying we're not using this. 18:12 If we change this data to number , you'll see now this variable number is darker. It's not gray, it's more black. 18:19 That is correct. So these two things can be literally anything you want. They just have to match .

So data and data for this sake, command S again, if you have some issues when, when saving, you might have some dot problems. 18:35 This is, this is auto wrapping, so don't think that you have to like add a, a new line here. We have a period I think. 18:50 Yeah. And then, yeah, this is all auto wrapping here. So this is all one line, line two, and this is line four here. 19:00 Cause I added a space there just to make it easier to read. Any other issues you have, comment down below or let me know. 19:08 Email me if you're a member. I'd happy to.

No, I hope this, I hope embedding a number is as cool for you as it is for me. 19:17 Bye.</div>