How to Power Testimonials with Google Forms and Sheets

<div>00:00 So in this tutorial, I'm going to walk you through the technical version, the technical stuff of how I power this testimonials page with a Google Form and a Google Sheet. 00:12 So kick back, relax, and check it out. So this site, I'm not going to go through the CSS or the JavaScript, but I might show that to you a little bit. 00:19 But for right now, I'm going to show you how this works on the Google Sheet side. You can go to bettersheets.co slash testimonials. 00:27 You will see literal testimonials or reviews, actually. These are reviews from AppSumo that I put up here.

These are real people. 00:34 You can also check them out at the AppSumo page. I just copied and pasted them here and said who they were from. 00:40 And then, I also allow you to add your own testimonials. So, I've sent this out to BetterSheets members. And and also anyone who visits this page can go and fill out this form. 00:53 So, let me show you that it works. Here's a testimonial. How do you like BetterSheets? I like it. Because, I made it. 01:02 My name, Andrew Kempfey. My role? Mmm, Wizard, maybe? Sheets Wizard? Where ? What company? BetterSheets? I'm gonna click send.

Great, I've sent it. 01:19 Where to? That is a Google form that goes through. It goes directly to this Google sheet, and here's testimonials, and here, let's go to the bottom. 01:26 Let's go to the top. There it is. It's right here. It's a, it's a, so it's inserting lines, right?

And right now, this test, quote unquote, testimonial from the owner of BetterSheets that I just filled out, is unapproved. 01:41 I added this, so what's going on is that every time that a form is filled out, it will add this approve and edit column, and they check boxes. 01:52 I'll show you that in a second. Now, if I don't, it's not I did, I can approve it.

If I like it, and I want to show it off, I can. 02:01 I can click approve, click approve, it will go over to this display tab, so if it's not approved, it is not on the display tab, see the made, it's not me, and if I click approve, and it's on the display tab, let's see, here it is, I made it. 02:21 If it's on the display tab, it is on the website, so I'll click refresh, let it load, and let's see, made, I like it because I made it, right? 02:33 So right away, it is there, so if the next person who sees this page wants to see this, you saw me just two minutes ago enter this, it's already on the page. 02:46 All I had to do was click approve, but let's say I want to edit this somehow, like, instead of I like it because I made it, I'll just say I made it. 02:57 What would happen in this sort of sentence when I say edit, I don't mean like edit from a bad review to a good review or a bad testimonial to a good testimonial. 03:06 I mean like some people say a lot of things and also they say a lot of things in a lot of different ways and sometimes you just want to edit down the stuff and so when I click edit here and again I'll show you how this works later it creates a copy be. 03:21 Exact copy with the same name, same role, same company, same exact text and I can edit it, delete it, and I can also uncheck approve of the other one or maybe I didn't at all yet and I can approve this one. 03:36 So now on the display tab it says I made it and now if we go to here right now I haven't refreshed it yet, but let me refresh this. 03:45 We will wait a moment for it to load, it does take a moment to load, and now I made it. 03:50 It is edit.

That is like magic. This page is being refreshed not refreshed, but updated every time I update that Google Sheet. 03:58 It is reading directly from that Google Sheet, so you must be wondering. How did I do that?

Okay, let's go to Apps Script , and I'll show you how. 04:09 First off, I did make a little custom here that if you want this sheet, you want this testimonial sheet get it, and then I'll give you the down below as a better sheets member, you get this sheet. 04:22 And in the custom menu , you will be able to create a form, and that will automatically create a form tied to your sheet. 04:29 Let's see. And it will also have all of this stuff in it.

So, from sort of reverse engineering this, what we're sending to the website where I have a little bit of JavaScript, I have a script, I have a script. I have a little bit of CSS and HTML to, like, make these nice looking things. 04:46 But the data from the Google Sheet is literally these three lines. I've created a function called doGet . I get the last row on the display sheet. 04:54 I get all of the data that's on there from the first row to the last row, and then I return it through this content service dot create text output. 05:02 And I format it as JSON.

And that's just so that the website, on the website side, I've created, I'm getting the data as JSON, I'm hoping, and then I'm parsing through that to get the different text, the different authors, different roles, the different companies, all that in the right proper area. 05:22 But just from the Google Sheet, to the website, it's this do get. But how do you actually implement this do get? 05:29 You go up here to deploy, new deployment, and you would select web app here. It's already selected for me.

And I'm just going to click deploy. 05:40 And literally it will set when you use the function do get, it creates. Well, I'll copy this and I'll show you. 05:49 It does all of this for us. We don't have to do anything. We literally just have to use the function do get with a capital G and it will return this stuff. 05:56 Watch. If I go to a incognito window and I paste just that web app URL . That I got that that Google deployed for me. 06:05 I click enter and this is the data from that sheet. So it's in the JSON format structure.

It's all of the text, the authors, the roles, the companies, all of that different stuff from the testimonials all all structured here. 06:22 There's a you can probably see it actually, and I will copy this and I will go to JSON beautifier, which I like to use. 06:30 I will paste it and now you see it's structured data . Computers can read this very well. The first it's an array of testimonials. 06:39 Here's the first testimonial.

The person who wrote it, the role they have in their company and the company they have. 06:45 So in my case, I put AppSumo as their customer of AppSumo because they left a review. But as people start filling this in, you'll see they'll say other things like what their role is at the company that they work at. 07:01 The other things in this app script that you'll find is an on open function with which automatically creates that custom menu . 07:10 And you can then click that button and you'll copy the sheet to your Google Drive.

Once you click this button, create form, what does that do? 07:19 It'll create a Google Sheet form. It'll have a how do you like it? Your thing? What's your name? Your role? 07:30 Your company? It'll create that form for you to send to users. How does it create? It creates also, if you go to settings, put your name or your site name or your app name here in B1 on settings. 07:41 It will include that in the form.

If, however, you do not do that, if you're like, oh, you jumped the gun and you actually have better seats here, go to the testimonials tab, unlink, delete this, and try again. 07:55 Literally, you can just make another one. Delete it, unlink it, then delete this tab. You'll still have that form somewhere in your Google Drive, but it should be fine. 08:04 But then you'll create a new form that's linked to this sheet, and it'll be called testimonials. It will always rename it testimonials. 08:11 That's in this app script here. We can find it here.

Set name, it sets the name to testimonials. What it also does is, it, creates the approve and the edit columns. 08:29 Then on, Okay, so here's a tricky thing. It also creates a trigger so that whenever that form is edited, there is an edit to the form, it will insert checkbox . 08:43 In the appropriate row. So we saw those approve and edit checkboxes already created once someone enters a new row. Answers your Google form . 08:55 I would double check this a few times off on your own.

We also have on edit here, and this function is using the normal, built in on edit function of Google Sheets and Google Apps Script , and it says whenever someone's on the testimonial sheet. 09:13 No, I think I have to fix that. No, no, I don't need to fix that. No sheet is supposed to be testimonials. 09:19 Yeah, that is an error . Get sheet by name. If it's, testimonials, and the column is seven, and the row is greater than one, it will insert a row, and it will copy paste. 09:31 So this is the edit here. Oh, yeah, I see a little error here.

This is supposed to be is equal to testimonials. 09:38 Okay, so now it'll work. It worked before, but it didn't take into consideration this testimonial. Page, meaning if you edit the seventh column on any of the other ones, it would have done this as well. 09:51 But now it won't. It fixes that bug. So now it'll only happen on the testimonial sheet. So yeah, on this on edit is pretty cool because it's going to copy the line that you're on, copy it and insert it right below it and paste it. 10:07 And then it sets the value. Yeah, setting the values .

And so now you have a copy of each one. Why I do that is because if you're working in a team, if you have more than one person doing this, dealing with testimonials, reviews, whatnot, and you edit, a testimonial, sometimes you'll want the original 10:29 in other places. You'll want to, like, copy and paste the original so that, like, you have it as, like, here's the full version or you just want, like, a record of it. 10:42 And all of the ones that are the original will have a time.

The ones that are the edits will not have a time stamp, so that's one indicator that you've edited, edited it and I think it's better to keep that data around just so I'm actually gonna prove it just so that, again, for record , if 11:00 you're working with other people, they might want to read the original one, you might want to do that.

Do that for market research, like, if you take testimonials and you're like, how do people describe what we do, what is our positioning are we missing some segment of the population and how they 11:15 talk you'll, you don't want to edit it for that market research purposes. Yeah, so go ahead, take a look at this testimonials. 11:25 Sheet. I'll share it again. If you're a better sheets member down below, copy the sheet, check it out. Check out this app script . 11:30 If you want, we had a lot of things going on. We have an a do get web function .

We have on open function to create the menu. 11:42 We have a form being created. If you click that menu. Item and create a testimonial form. Then we also have it create a trigger . 11:49 We had to create this trigger so that on form submission, it would add the checkbox . We have that checkbox function and we have an on edit function for the edit. 12:00 So when you click that edit checkbox , it will create another row. Let's double. Check this that it's working perfect. It is working. 12:08 And now if I edit the G column on another sheet, it won't work, which is good. All right.

And we, yeah, we're sending Jason to a website. 12:19 So I should probably go over that, that JavaScript that's going on. So let's look at inspect this. See if we can find the JavaScript going on here. 12:34 Actually, probably better to show you in VS code. So in VS code, here's the testimonials page. We're taking this URL. 12:45 URL, which is literally the web app URL that I showed you that is just a bunch of data . And we're doing a fetch URL. 12:51 So if you're used to Apps Script and you know URL fetch dot URL. Sorry.

Yeah, fetch URL in Apps Script . 13:01 This is the JavaScript version, fetch and URL. What we're doing is we're doing a fetch URL. Is taking that response. 13:07 We're reducing it down to figure out how many there are. We're then formatting them in like columns to be nicer. 13:15 Like it's a masonry grid so that they don't have big gaping holes. If there's a big one, there's like a long text and a short text next to it. 13:23 It's going to be very lot of white. We'll be right back.

Space so I tried to fix that by sort of just doing columns here for each item. 13:32 We're taking the text, the author, the role, the company. We're getting all of that.

We're putting it into a card. 13:38 We're styling the card with this background white with a bit of shadow with some border with some rounded corners and we. 13:46 Piece together the HTML of the text, the author, the role at company, and I can edit all of this if I want to on the website side. 13:54 I don't need to edit the data at all and we're putting into a grid where we're saying put the first third in the grid to the left second third in the grid to the middle and. 14:06 And a column, sorry, a column in the left middle and right.

And I think if, I think there's also, if it's a smaller size, if it's like mobile, it's only one column should be. 14:21 Yeah, great column one. And then on a larger size, it's three columns. That's the point there. That's it. That's the JavaScript. 14:30 Some HTML, little bit of HTML, bunch of JavaScript, little CSS. And we are grabbing that data from the Google sheet because of that do get. 14:40 So let me remind you here from the beginning of the video or early on the video, it's literally this do get function . 14:46 Deploy and it's this web app URL that we're getting here.

And what this is doing is just again grabbing the data that's in display display sheet, setting it as JSON and sending it out to the world. 15:02 Whoever goes to that URL will grab that JSON, which is really cool. In a few ways, right? This doesn't sound very private and it's not very secure in data . 15:15 It's not very good data security, but in case of having a public website like this, it's okay.

It's okay to have this data sort of at this URL level, and if anybody finds this URL, they can grab that data . 15:28 They can't edit the sheet because if they want to edit the sheet and add some testimonials that are like bad and they want it to go on their website, they can't because I have to approve them. 15:37 I have that act, that human action. I have to take that checkbox to get it onto display tab. And this you web app URL . 15:47 Only works on the display tab because I've said get sheet by name here.

So they cannot edit that URL to get something else in the sheet. 15:58 For instance, I've noticed people will use like import range in a spreadsheet to grab data from another spreadsheet . But that's what we're going to need to say, what is the what is the range ? 16:12 And if you edit that range and you guess correctly the other tabs, you can grab information from that other sheet when you're like, oh, I want to just send this one tab. 16:19 Not everything. But if somebody guesses what the other tab names are or edits at range , you are scared.

Your data 's out there in this. 16:26 In this case, our data is very much secure because it's only grabbing this data here and it's deployed on Google servers. 16:34 So we don't have to host this at all. It's not necessarily HIPAA compliant. I had a question earlier about like only sheets being HIPAA compliant and I don't think it is. 16:47 It's not HIPAA compliant because my Google sheets might not be HIPAA compliant. If your Google Workspace is HIPAA compliant and your Google sheets are, then yes it is. 16:58 But in my case, this is probably not very much.

This is not very good HIPAA compliance here.

Feel free to email me, ask me any questions, if this is a little too, this is definitely an advanced video, I understand that. 17:14 And if you're very much wanting to learn a lot of the pieces of this, I go over that in spreadsheet automation 101, the entire three hour course will get you up to speed on writing app script . 17:25 It will also get you, e-mailing, I don't do any e-mail in this one, but it'll also get you accessing APIs, I don't actually access any APIs in this, but it'll get you very familiar and very confident in app script I do not yet go over the do get in app script spreadsheet automation 101, I  17:42 do, however, do go over the on open and the on edit functions there, so those two, if you're looking for more information about that, and more piecemeal, like stepping blocks and want to learn that, go check out the spreadsheet automation 101, the course is available for free for all Betasheets members 17:57 , so grab that at betasheets.co slash courses, spreadsheet automation 101, bye.</div>