Hey there stranger!

Sign up to get access.

Build a Book Recommender with AI in Google Sheets

About this Tutorial

Create an Email form that sends a few books to AI and gets back a book recommendation. Build the backend in Google Sheets Apps Script while using Carrd to easily make an online form.

Video Transcript

00:00 Hey, so let's build the book recommender. We're gonna get an email form from online. We're gonna create that email form.
00:06 We're gonna get an email and then we're gonna send that email to someone and say, hey, here's some books you I recommend for you, there might input some books so we have some input and we saw, we have a book we wanna recommend for books. So let's start in Apps Script. Four members of Better Sheets, I
00:15 have this script already for you. Get it at Sheets, resources in the video called Upgrade, Google Sheets, open AI prompt to j- G-P-4, I already have this written for you. So let's get that AI prompt here. I'm gonna rename it AI, function AI. Uh, and we're gonna send a prompt and get a reply back. We're
00:41 gonna set the API key to uh, uh, API key. And we'll go grab a API key to test this. So we got an API key from opening AI here and we're gonna run a prompt. What's that prompt that we're gonna run? Uh, let's just add it here to the prompt uh, to the function. I'm gonna do variable prompt. Equals. Based
00:21 on this list of books, please uh, suggest one more book to read and tell me quickly how uh, why I should. Read that book based on the books I input. I list here. Uhh. And then we're gonna do plus books and we're gonna get uh, books here. So we'll insert a list of books in text and we're gonna get a book
00:27 recommendation out of it. So let's get that book recommendation. Books input. So I'm gonna create a card C-A-R-D. It's truly just a really easy and fast way to create a little HTML page. We're gonna create a form here. Let's see where it's form. Thank you. We're gonna say custom uh send to URL. We'll
00:28 create an API for this. We're gonna fill in the API later. We need a post and we want actually two things. . We want email, text, text yeah. Actually we can say email here if we want ID email. We're gonna create another form here and we're gonna call this books and we're gonna add the ID books. And so
02:14 when we send this uh, this form, we're gonna get an email, we're gonna get a list of books and where do we get that? Let's create a doo post here. So function, doo post. And we're gonna get uh some uh URL here. I'll actually Thank you. An input from URL. An variable email is equal to e.parameter parameter.email.
00:33 That is literally what we put here as the ID. So whatever ID you put in, you can get it. Books equals e.parameter.books. I think we did books. Yes, books not just book. And now we want to get uhh, we want to return just 200. Thank for watching. I'll you next uh, but actually instead of 200 it's going
00:38 to be contentservice.createtextoutput200. That is just a reply we want to send back. But we want to email someone. Mmm, and so. So let's get actually a variable, uh, recommendation equals AI books. And so we're going to insert basically, take whatever books we get, put that in the recommendation and 
00:54 get an output down here. Return JSON. The choice is whatever the return is of that. But we want to save this to, uh, the sheet. So we're going to do spreadsheet app dot get active spreadsheet. Yep, dot get sheet by name and we're going to call this database. Shoot by name database. First we're going 
01:12 to insert a row after one and then we're going to take that same database and we're going to dot get range. Uhh column 1, no sorry, row 2, column 1. Uhh. One cell wide and one cell big set value. And we're going to say, I think our first thing we're going to, yeah, email. We're going to get the input
01:30 and then we want the book that they recommended. Okay. So we're going to actually copy this all the way again, paste it twice and instead of the first column, it's the second column and this is books. That's what we get up here books and then we're going to get the recommendation here and we're going
03:00 to print that out into the cell. Thank you. So how do we actually email the person while we're going to create another function called email? Uhh. I'm sure we're just going to call it email and we have the email and we have recommendation. Did we spell this correctly? There we go. Recommendation. And
01:47 what's that function? We'll create it here function email. We have email and recommendation. We're going to do mailapp.send email. We're going to send an email. A 2 is going to be email. The subject is going to be book recommendation. And our body is going to be the recommendation. For right now we're
02:02 just going to send that super simple. And to use this do post we need to deploy new deployment. Let's see if this is all going to work. We're going to select our type web app where you're going to run as me. Uh as me and anyone can use it deploy authorize access. We need to authorize the use of all of
05:32 these functions. It'll say which function they're using. Spreadsheet app, mail app, all that kind of stuff. URL, you can always get it again. Uh over here in our form, actually we'll call this book. Recommendar. Next I'm gonna rename this books I've read and liked. Uh. Over here sent to URL, put that
02:25 URL in here. Do not do anything else to it. We don't have to. It's gonna form its own um stuff from the post. It's gonna create a little parameter there called email called books and then it's gonna add those parameters. Click done. We're gonna save this as a book book recommendations. Get. Where you
02:33 can get book recommendations. We're gonna save this as a book record mender dot car.co. See if that's available. It's checking. It's available. Yes. That's publish it. There's a speedrun. See how if we can do this. All right. I'm gonna use my own email address. I'm going to finish this. I've read a project.
07:14 Hail Mary. and hitch hikers guide to the galaxy. So based on those, give me some recommendation. We're gonna hit submit. I think we're gonna hit submit. Let's see if it's gonna work. exception. Do you want to mention it? Okay uh so line five we gotta do a little bit of fix here.
02:39 Insert rows. I think I need to do insert row. That's a little uh. That's a little error here. New deployment. Let's try it again. Deploy. We have to get a new URL.
02:41 Every time we deploy a new web app we're gonna get a new URL. Go back over here. Click okay. We're gonna insert it into this URL. The new one click. Click Publish. Publish changes. And the next thing I'm actually gonna do is I'm gonna open this site is uh incognito. Cause I think there's sometimes there's
05:11 a weird thing that happens. Okay. Email. Again. Android better. She's like go. So let's use the same submission. Let's see if there's any error. Again error. Type error email is not a function. Okay. Let's go and look at that. Email function email. Hmm. What we could try to do is put this line here. 
03:27 Instead of using this function, we'll just use mail app. Let's see if that works. Deploy new deployment. Again we go through the same process again. Deploy as anyone we might might have a problem with this email but we can there's other ways to fix it if we don't. Uh if we do have a problem with it okay.
04:54 Let's go back and fix our URL. Done. Publish. Publish changes. We are doing you can fast-quick run here through these errors. Alright. Email. Same. Email. Same. Recommendation. Now we got a 200 code. That is plus a plus awesome. We have... No recommendation. Uh oh. Actually I would suggest reading the
07:15 Martian by anywhere. Okay. So this is the books and then the recommendation and it should email us. Let's check our email. We got a book recommendation in our email. I would suggest you read the Martian. This book would be a great choice for you based on your enjoyment. Of course, I would recommend the
03:13 Martian too. Umm, some of mine's element science fiction in humor. Yeah. Uhh. Perfect. We got it in our email. So now let's go fix that little error where we have uhh. We want to save these emails, save the inputs and save the book recommendation to our database. Uhh. Let's do it all again. Hopefully
03:16 new deployment deployed to anyone. Get that URL. Fix it up in our site. All we have to do is put it in the URL. Done. Hit publish changes and we're going to have a book commender saving to our database, emailing. Let's see what we get. So actually I'm going to try the Bible and project. Hail Mary. Let's
03:40 see if that works. We got a 200. Let's see if we have book recommendation. I'd recommend dune. Get your recommendation based on those two. The Bible and project kind of my very religious and religion power, all this stuff. Yeah, great recommendation. We have it. Do we have it in our database. Yes, we
03:52 do. And so now we have a created a book recommender in what? 11 minutes here. Really cool. Here's the code for you. Uh, if you are a Google, I don't know if you're a Google sheet user and you're trying to do this.
04:00 We did it. . You are a better sheet member. You can grab the URL of the sheet down below. Copy the sheet to your Google sheet drive.