Create a Vote Button from Google Sites to Google Sheets

Intermediate

About this Tutorial

Connect your published Google Site to Google Sheets with Apps Script and a Button. Great to use for Voting, upvoting, NPS, or anything you want feedback on, at the click of a button from your Google site to a Google Sheet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 So in this video, I'm gonna show you how to connect Google Sites buttons into Google Sheets So you can do like upvoting or voting on different topics or like you can do an NPS score or net promoter score If you want to assign different buttons different values , you can ask for feedback, thumbs up, thumbs 00:18 down, meh You can create all sorts of buttons and I'm going to show you how to create a button here on your Google site I'm gonna actually publish this for real and we're going to get information every time someone clicks on it on something, we're gonna get that information, a count, a rotating count 00:34 , an upwardly, it's gonna count the number of times people click on it.

So, let's say we're doing an NPS score or rather a thumbs up or thumbs down So we want thumbs up and thumbs down. 00:47 Obviously we're gonna start with zero ah as the starter and we're going to have to create our Google site and the connection between Google Sites and our Google Sheet is going to be a web app that we deploy. 01:00 And I'm gonna show you exactly how to do that for free here in Google Sheets. So, right now we're gonna create that app script here and we're gonna do site.newslash3 as well. 01:10 We're gonna create a brand new Google site.

This is totally free sites you can create and we're gonna say, won't you, okay, I don't know, won't you ah or do you like this? 01:27 Do you like this? We're going to, on the right side over here. Scroll down to button. We're gonna say thumbs up. 01:34 And we're gonna put a little emoji here. Thumbs up. We need a link here. So let's see what that link is going to be. 01:42 We're going to create a function in our web app and we're gonna call it thumbs up.

We're gonna say do get and here we're going to say spreadsheetapp .getactive Active spreadsheet dot get sheet by Nate actually we need parentheses there. 02:00 Sorry get sheet by name. We have ah just one sheet so far. So it's going to be sheet one. And we're going to get range . 02:11 We're going to get the range of the count where we're gonna put at it. Ah that is going to be a two. 02:18 And we're gonna get the value that's there. Get value. We're gonna assign this a variable count equals.

Now we're going to take this entire thing we just did and paste it and say SpreadsheetApp .GetActiveSpreadsheet.GetSheetApp. 02:30 SheetByName.Sheet1. GetRange .A2. So the range of A2. Instead of get value we're going to set value. And what value are we going to set? 02:40 It's going to be count plus one. We're just gonna add one to that count. Okay. Now this is the tricky part. 02:47 We're gonna go up to. Nah I'm I'm saying that ironically cause it's just a couple clicks. We're gonna click deploy. 02:52 We're gonna click the new deployment.

And here we will select our type as a web app. we need to allow anyone that has access to this URL to access it. 03:05 So we're gonna click anyone. But we will want to execute it as ourselves. And we're gonna deploy. When we deploy Google is going to give us back a URL. 03:16 And this may take a moment now because I'm recording this. If I'm not recording this it's like sending data back and forth real quick. 03:22 All right. It's asking us to authorize it. We will authorize it. And we just need, I wanted to do this in real time. 03:31 We just need that URL back.

Just give us that URL, come on. Here we go. We got this web app URL . 03:38 So if you notice here we have a deployment ID and a web app. We only need the web app URL . 03:44 We can copy that, click done. Go back to our site. And the link of the button is going to be this. 03:52 This here. I just remembered we have one thing to do actually before we insert. Actually it's gonna be the same link anyways. 03:59 We want to, return something here. So this is editing the uh the sheet.

But we need to return something to the uh basically the link that's coming in. 04:12 The sending of the link. We need to send back hey it's okay which is going to be a 200 code in uh sort of computer speak. 04:19 So we're going to say return and then we're going to use content content service dot create text output on all we need is This is just saying, hey, everything worked. 04:31 Worked totally fine. If there's an error , it will give us, it'll do something else. But we just want to return to the URL that's giving us the data . 04:38 Hey, it's okay. So we save that.

In order to update our deployment, go up to deployment, manage deployments. You could do a new deployment and you'll get a new web app URL . 04:49 But we can also just edit this and call it a new version. Let's say updated with return 200. And everything else stays the same. 04:59 click Deploy. We should get the exactly the same URL. Ah, here. The web app URL . Not the library, but the web app URL . 05:07 Should be the same. Let's double check it. Yeah, it is the same. Okay. So, we're going to insert that. Do you like this? 05:16 Thumbs up. Let's give this a little bit more room.

Let's do there. And we're going to, I think we need to publish it. 05:27 And we're going to call this What do you think? Like this. And hit publish . And actually we need to update the parameters . 05:38 Publish settings, I think. Um. No, I think it's share with others. Yeah, we want this to be anyone with the link . 05:47 Anyone. It's a published site to the public. There we go. Oh, nope. Actually, restrict the edit. Sorry, the published site is the one. 05:56 Don't let anyone edit it. But let everyone have access to the So we will now view published site.

And we can actually go right here and do an incognito tab. 06:13 There we go. Do you like this? And so now we have a totally public site that is uhm available to Thumbs Up. 06:24 Let's check if this works. We're gonna click thumbs up. It's going to open another tab here and it should say 200. 06:31 That 200 is exactly the message we sent. So we can add some text here if we want. Let's go back to our sheet and see Thumbs Up 2. 06:39 Okay, let's go and click Thumbs Up again and see if that number is up. It updates to 3.

So every time I click this Thumbs Up, I'm getting that count is going up. 06:49 So this is really cool, right? We can create a button on a Google site really quick within less than 10 minutes. 06:56 It's just actually a few minutes. But let's see, can Can we do it so that we can get the same message? 07:01 But attached to it, different information, Thumbs Up and Thumbs Down. Let's see, and give us a return a different thing. 07:10 Because what's going to happen is, let's close all of these.

If we try to do a little bit too much in this doGet , it can only take in one thing and return another thing. 07:21 But we need to know what is the difference between two parameters . Like we need to give it the parameters Up or Down. 07:28 And so, we're going to get variable. Vote equals e.params.vote. Now what we're going to do is we're going to add a little bit of text to this URL, which allows us basically, I'll show you here. 07:48 So we will duplicate this and call it Thumbs Down. Let's do an emoji there.

At the end of this, we're going to do an emoji. 08:01 We're question mark, vote equals down. And on the other one, on the up, we're going to use the exact same URL, except our vote is up. 08:15 Okay? So when someone clicks that button, they'll send the URL, the same URL we were doing with this count here. 08:22 But now we have some extra information. We know what the vote is. And we get it by using e.params.vote. And so now this vote is either going to be up or down. 08:33 So based on that information, if vote is equal to up, then we want to do one thing.

And if vote is equal to down, we want to do another. 08:47 So now we can execute two different things based on what that information is. So we're going to cut that, um, what we were doing before. 08:57 Actually, we're going to take all of this count. And now we're going to execute the up the same exact way. 09:04 We're not going to change anything. We're going to copy it, and we're going to edit a few things. We're going to edit this A2 to B2, which is, remember, our B2 here. 09:16 And we're going to take the B2 range and set the value to count up, or sorry, down.

The count is going to go up, but it's the number of downs we have. 09:23 Uh, we also want to say, what, what did you vote for? You voted and now we hit play. Plus vote. 09:33 So that same variable that we used to say, which one did you do? We're going to tell you back, hey, you voted up or you voted down. 09:39 So we're going to save this. Again, we need to deploy. So we're going to make sure it's saved. Make sure, this takes a little bit of time sometimes. 09:47 Deploy. Manage deployment. So exactly the same URL here.

We're going to get a new version updated with up and down params. 09:58 We're going to click deploy. Again, the URL is not going to change. In this way, if we did a new deployment, the URL would have changed. 10:05 We can actually double check that this is, yeah. We can just look at the last few letters. GQ. And this is the same here. 10:15 GQ, yeah. Okay. So let's update this. Let's make sure it is published. Yep. We want to publish it. And now, let's look at a live version. 10:32 I think, copy, publish , type, link. Let's see. Will it update? Okay, it finally updated. Great.

It just took a little bit, a moment. 10:44 Let's make sure we're getting that message. Type error , we need to read the properties a little bit differently. So I think I actually messed this up. 10:53 It should be parameter . I think let's try that. Let's deploy again, manage deployments. We are updating version, 3 to new updated with parameter . 11:06 Let's deploy as well. And the good thing about not changing the URL is we don't have to update the publish site. 11:12 This URL again is the exact same. So our publish site should now work.

Let's just view publish site and let's click thumbs up and see if we get the same error . 11:25 Oh, I think it's Just because I didn't open it in an incognito window, that's the only issue. Okay, same. Sometimes with Google Apps Script you have to execute these things as if it wasn't you. 11:40 Uh, let's see. Fingers crossed this works. You voted up and click down. Let's see, did we vote down? You voted down and let's check our sheet as well and we have a vote down. 11:54 So this is really awesome.

Again, use an incognito window if you get that error message that it's like ah, Apps Script is messing up. 12:01 But the mess up that we did fix was making sure, let's click done here, that we're using e. parameter not e.params.vote. 12:09 So that was the only issue there. I hope this was really exciting for me. Uh, one of my first tries at Google Sites and it's relatively easy knowing that you can create this doGet inside of Google Sheets and Apps Script .</div>