Add NPS to Your Google Site

Intermediate

About this Tutorial

Create buttons on your Google Site to get an NPS, or Net Promoter Score directly to a Google Sheet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

ContentService SpreadsheetApp Utilities

Apps Script methods used

.createTextOutput() .getActiveSpreadsheet() .getLastRow() .getRange() .getSheetByName() .setValue() .formatDate()

Featured Formulas

AVERAGE()

Video Transcript

<div>00:00 Pretty simple, in this video we're going to add an NPS score uh taker or rater to your Google site.

Uh 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 and if you're a BetterSheets member and watching this on BetterSheets.co 00:17 you can get this exact sheet down below if uh if you're on BetterSheets.co if you're anywhere else, alright gotta go to BetterSheets to get it or uh Udemy as well, I might pop this into Master Spreadsheet Automation So, what we're going to do is we need a Google site, right? 00:33 We have a Google sheet, it's a brand new sheet but let's get a brand new site, site.new Slash 3 cause I have a bunch of accounts but this is a brand new Google sheet page, uh Google site, NPS score You can write whatever you want here, we're just going to add some buttons, we're going to go over to the 00:48 right side, we're going to add a button We're going to call this 1, we will get a link, so what an NPS score does is it will allow someone who is buying your product using your product. 01:00 to rate you 1 to 10, how likely are you to refer this product to someone else, to a friend.

So, you can insert these 10 buttons anywhere on your site into a section or on another page if you're like, hey go over to my NPS and rate me, let me know how I'm doing, right? 01:22 And the question is, how likely are you to refer this product to your friends and it is going to be a number . 01:30 between 1 and 10 and we will take in that number with this button, so this is going to be a button, we need a link, how do we get a link, well let's call this a score and then we also want a timestamp because the data that we're going to get in is going to say a number and we will be adding up that number 01:50 to get a total aggregate score or averaging that number , but over time what we want to do is increase that number , right, and say we want the end result on total to increase over time, meaning we don't want to take the aggregate of everything, we want to know what came in first, so that's why we do a 02:09 timestamp, so let's go to Apps Script and start coding right away, it's not going to be much, don't worry, and again, you can grab this sheet down below if you're on bettersheets.co, so the function we need right now is going to be a doGet, we need an event or some parameters here, we will just say the 02:29 letter E here, but you can also say P.

Event, if you want, uhm, but we're just going to use the letter E, one letter. 02:36 What do we want? We want the score. So when we get the URL, we are going to add to it a parameter that says score, and that will be E variable score equals E. parameter .score, and we can say the name of this, we can set it whatever we want, you'll see later that we will use the word score to get this, 02:59 uhm, and I'll show you how to do that. on the URL.

But what are we going to do with the score? 03:03 We're going to go to the spreadsheet , uh, that we have here, go to sheet one, actually we're going to call this scores, all uppercase, we're going to get the active spreadsheet , this entire file that we're in now, we're going to get sheet by name, get sheet by name, we're going to go to scores, we are 03:22 going to find, uhm, get range , we're going to find the last row, last row, we're going to put into to column one. 03:32 only one size, one by one, we are going to set the value, and this value will be the score.

But what is this last row? 03:42 Let's go find it. We will need all of these scores again, like the sheet scores, so actually I'm going to cut it and call it variable scores sheet equals, and it'll just be this score, So we'll call this scoresSheet. getRange setValue . 03:59 But we need that last row. So we'll say variable lastRow equals scoresSheet. getLastRow . Simple as that. We just get the last row, but we don't want to actually put it on the last row. 04:15 The issue is we're going to get the last row.

The last row means the last row that has data in it. 04:19 We don't want to rewrite over and over again. So we need to add plus one.

Get the last row, add one. 04:25 Make sure we're adding a row each time, so actually I will go here, I'm gonna delete all of these rows we're going to then do scoresSheet. appendRow and we're just gonna append a row at the end and then we're gonna put right under the last row, set the value score. 04:44 We also want to do one more thing, which is take all of this and we're gonna set one more value in the second column and that value will be the timestamp . 04:54 Well what is the timestamp ? Let's add it up here and call it variable timestamp equals.

I'm going to format it, but really you just need new date with a new then capital D date with parenthesis. 05:10 You really only need that, but it's gonna give you a timestamp and that timestamp 's gonna have all like milliseconds, everything. So we're gonna do utilities, utilities.formatDate and wrap this in parenthesis and as you see here we can choose the time zone . 05:29 Let's just GMT um, minus, actually just GMT, doesn't really matter. You can set any time zone you want. Minus six plus eight, whatever you want. 05:39 Format , this is gonna be the interesting part.

We can format this any way we want. We're gonna do the year, month, hyphen, the day, and also a space, an hour, colon, and then two Ms for minutes. 05:57 Okay, I'm going to do all that. Now, here's the important part we need We are going to name it and then deploy it. 06:04 Name it nbsgetter, click deploy, new deployment. We need a URL. Basically, we're allowing this spreadsheet to be open to the public but only through this URL and only gonna do exactly what we have selected it to do. 06:21 We're going to select the type web app.

We will execute as ourselves, as me, and it will be available to anyone. 06:27 So anyone that has this URL can enter some script. We will, I'll show you how that works. But they will only be able to do that. 06:35 That's a really cool thing about this do get and allowing this web app URL to be out there. People can't enter your sheet. 06:42 They can't edit or delete your sheet at all but they can do whatever you've prescribed for them to do. We will authorize access. 06:51 The access they will have is that they can enter a new line.

And we will allow, which is pretty simple stuff, just to be able to enter lines. 07:01 Set a date, done. We will copy this web app URL once we get it. And we will now put a link here in our Google site. 07:11 Again, the name of the button is just 1 because we just want the score of 1. But we have a link that we will use for the entire, all 10 numbers. 07:18 But at the end we will do question mark score equals 1. So that's all we need to get the data of the score 1 into this button. 07:29 So we will insert that there, we will, duplicate it, we will call it 2.

At the end we will write 2, update. 07:38 Let's put this next to, there. And let's test this, right? Let's see how this is going, um, and is it going to work, right? 07:47 Let's publish our sheet to NPS. Uh, let's see who can, who can view it. We want the draft restricted , but we want the published site to be anyone public, right? 08:01 We want to do it. Direct people here, uh, to score us. Or maybe we're adding this already to our sheet, uh, or to our site. 08:08 Alright, let's click publish . Okay, it is published.

We can view it, but in order to execute these things we need to do it on a incognito tab, cause we can't, uh, to do it. 08:28 Let's delete all of that and just get the bare URL. There we go. We have an incognito tab. We have one. 08:36 It's gonna maybe give us an error . Yeah, we need an error . Oh, we need to fix this. So back in our code, I think we used append row. 08:45 Um, yeah, we don't need to actually do this. We can do insert row after last row. Let's do that instead of the append row. 09:05 And we need to deploy this.

We can keep the same URL if we click on manage deployments. We go to our untitled one, which is the only one we have. 09:14 Click edit and we select the new version. We can add a new description. Uh, insert row . Click deploy. We will keep the same URL. 09:26 Let's go. Actually, we need to do something else too here. So, one thing I just totally forgot to do is, Return contentService. 09:35 And we're just going to return a code that says 200. Hey, it worked. ContentService.createTextOutput 200. This will show up to the user. 09:43 So, let's actually not just say 200.

Let's say uhm you, thank you. You scored, you entered a score of, and then add a plus and the score. 10:02 Whatever they entered. So, let's see if that's going to work. Manage deployments. Again, we will make sure it is deployed. 10:09 We will edit up here. Version. Select new version. And do added return. Deploy. Again, should be the same URL. So, we don't have to actually change our site. 10:22 We can go to our site in an incognito window. Let's click on the number 1. Let's see what happens. You entered a score of 1. 10:34 Did we? Let's go check our sheet. And there we go.

We have a score of 1 and we have the timestamp . 10:42 We have the exact timestamp that it was selected. So, what's really cool now is we can obviously duplicate this as many times we want. 10:53 Edit this to 10. Edit the score to 10. Update. Now let's duplicate it again. Right, and you can see where we're going with this. 11:04 We're going to have 10 buttons here. How likely are you to uh refer this to a friend? How likely are you to refer our product, let's just call it better sheets, for better sheets to a friend? 11:27 How likely are you? Let's vote.

All right, we can take a break. Decrease this a bit. Uh, we still need to add a few more here. 11:39 Just trying to format it so it all fits here on one line. Let's make sure we have them all. Again, this is a little tedious, but it's allowing us to create these buttons really fast without having to create more and more code. 11:53 We just edit the score here and update, duplicate , I'm going and edit this 5. Edit the score here 5. We can also change it to the word 5 if we want. 12:09 I don't know if we really want to do that. But edit this, edit the score 6.

We are almost done with all of these, right? 12:18 We need to make this smaller. Duplicate again. Oh my god, we're so close. 7 and update this to 7 update. 12:33 One more and let's keep that 9 and 10. I don't know why we want to do that but that'd be funny. 12:42 There we go. So again, publish and this will be new, uh available. Let's view publish site. We can delete all of this stuff and again we just need to use the incognito tab just because of our script because we're logged in. 13:01 Let's click 9. Double check that it's all working. You entered a score of 9. Perfect.

Let's do 10. You saw enter score of 10. 13:13 Let's see is it on the Google sheet? It is on the Google sheet. Now what's really cool is we can insert a row above and be like here's our average score. 13:21 Average A3 colon A. We have a score of 6 now. Let's do some more. Let me click 9 a couple more times. 13:33 And we're just looking at this that it's entered a score of 9. Entered a score of 10. Great. Let's go back to our sheet. 13:43 Score of 7. Average score of 7.8 right? So it's just averaging this and now we have a live score. A NPS score. 13:50 Net promoter score.

Ah and it's getting it from Google sites. So we can put that Google site block into anywhere we want. 13:57 We can have a link to it. We can add it to our Google Sites. Really cool to have this NPS score. 14:02 And pretty darn easy right? We had a little bit of code. Again if you're a BetterSheets member you can grab this code down below. 14:08 It'll be down below if you're watching this anywhere else. Go become a BetterSheets member today. Bye.</div>