Hello, I'm gonna explain how to set up a Stripe web hook in a Google sheet and in Stripe. I'm gonna show you exactly how to set this up. I'm gonna share with you also the script and the code that goes along with the script. It's actually available on GitHub, so stay tuned and I'll share with you actually, No, you don't even have to stay tuned too much.

It's in the link is in the description below. To get this, it's available for the public a treat for those who are not better Sheets members. But if you are a better sheets member get everything that I put out as a script, as a template, everything all the sheets that I do, all the videos have If you're a paid member of better sheets, all the videos have a link to where you can get that sheet or script or whatever it is.

And in this case, in this video, We're gonna be doing something very interesting in Stripe where we're grabbing webhooks and why are we doing this? Because I'm building or I have built already a newsletter creator. It's a Google sheet that helps you create a curated newsletter. And I've been flummoxed before of how do I create stripe payments so that I could essentially create CK in a Google sheet?

This is one of the steps. I'm still working through this particular script. I'm still working on adding and making a little bit nicer and easier for the user. But I wanted to share this with you because it actually works. We're grabbing web hooks, which means we're creating a web hook as a web app in Google Script, which means that every time stripe fires some event you get a new customer, you get a payment you get a cancellation.

It sends that information to this web hook, and this web hook will put it into this sheet. So what that means is that I can monitor. I can get the email address of a new customer. I can find out if they paid within the last 30 days or within the past month. I can keep a list. If I filter this, I can keep a list of all those who have paid within the last month, those who have canceled those new customers who haven't paid yet.

I can keep track of those separate lists. And what's really cool about this is if I have a Google account. With my newsletter domain name and I have this data in a Google sheet. I can do a lot of stuff with it. I can export that to a CSV to send it to MailChimp or some other newsletter provider.

Yes, I can absolutely use CK and, but then I have to download the customer information. I have to get the email addresses if I have to download that and put it somewhere else. And this keeps it all within Google Sheets, and then I can do anything with it. What's really fun too, is I. Found this script.

Most of this script was found on a stack overflow. So this part at the top and then this part at the end when we insert the data that is all from Stack Overflow, this wonderful thing. But I ended up adding this switch case to this and I'll explain the code a little bit more. I will walk through the code now.

Then we'll walk through actually how to set up the web app and the web hook in Stripe. And then at the end, I'm gonna explain really more details about newsletter creator about why I'm doing this. But also I'll share with you some use cases that I've come up with of why would you use this as a web hook and stripe?

Because maybe you are keeping track of your payments. Maybe you're keeping track of your sales in Stripe, but you want to export. Data, or you wanna keep a log, which is what I'm doing, is keeping a log of all of that in a Google sheet so I can filter it in pretty simple ways. Again, Stripe has all of these sort of dashboards.

If you want, you can build your own dashboard in a Google sheet, but Stripe has some really nice dashboards. You might wanna stay over there. All right, so let's. Let's explain this code first. Again, the description. In the description below, you'll have access to the GitHub that's available for all the public.

I'm making that this available for completely everyone. Better Sheets members and all better sheets Members get this video first. If you wanna become a better sheets member, go to better sheets.co. And now let's start. To create a web hook and stripe, we need a link. We need a URL to send that data to.

And in order to do that, we have to create a web app and we need to deploy our code as a web app. To deploy our code, we need to have this function do post, and this creates an event, this E. And what does that do? Is it go grabs the J S O N creates it as an event, it then we'll get a timestamp here.

We get the last row of the sheet we're going to, I'm going to sheet log. You can change that name to whatever you want to name it as. What's the most interesting part of this code is that I have grabbed this event type, which is in the data package that is sent. This type is. Like the actual web hook name, and so I'll show you and Stripe exactly what I'm sending.

I'm sending these event types, but you can send every event type if you want, and then parse it and figure out what are you gonna do with that event. Sometimes you do nothing. Sometimes you wanna put it in the log. Sometimes you wanna. Tag it. And what I've added here is this switch. So based on whatever this event type is.

So back here in Web Hooks, these are four different types, customer created. And in my case I'm creating, like I said, a sub stack in Google Sheets. So what do I wanna, what do I care about? I care about if a create, if a customer's created, right? If they. Send me their email address. I care if they've paid when they paid, and I care about that.

The charge succeeded, right? If there's a charge that hasn't succeeded, I wanna deal with that in Stripe, not in Google Sheets. And then the last thing is I wanna know if they canceled, right? So using Stripe checkout we can send them a link, send them an invoice. In our sort of idea of a newsletter, all everybody signs up for a free newsletter, but then I can add a link that says, Hey, do you want to join a paid one?

I, and I would imagine the workflow would be like, everyone joins a free newsletter and people upgrade from there. There's not. Many people, and I wouldn't imagine many people will just want the paid, they'll want to get the free plus the paid. So that all works in this workflow. Outside of that workflow, I can't imagine other, there are edge cases, but we're going to deal with this first.

And again, I'm working on this script, adding to it later, but here's where we got these event types we know what customer created charge succeeded and canceled, right? Okay, so we go back to our code, and this hook type is saying if there's customers created we want to tag it as new customer.

Okay. So that's the hook type. We want to get the customer ID and we want to get the customer email. That's the most important thing is we wanna know if somebody's a customer of ours, what is their email address? And the other things we get, we don't necessarily need to know their email address because it doesn't come in the data.

What does come in the data is the customer id, and so we wanna treat those other. Web hooks differently and we don't wanna get the email cuz we don't, we can't get it. We do wanna get the customer ID and match that to the ID that we got when the customer was created. So all of this switch case, we write it a switch.

And then in parentheses we just write what is the data that is gonna handle? And then in the case we go basically this is like a really convoluted, not a non convoluted, if else. So I started to originally write this in like an if this, then check this as if else if, and it was not working at all.

But when I used this switch, when I said this data is gonna be something. In this case, in when it's this, when the hook type, when the event type is equal to customer dot created, tag that as a new customer I want, and why did I do this? Why did I change that verbiage? I could have. Literally said like just tag it as customer dot created.

In Google Sheets we might be building this. We might be using this script For others, we might be writing this script, but someone in Google's SCR sheets is. Reading this data, and not every single person is gonna understand customer dot created, but they will understand the ver, the verbiage and the words new customer.

This is a new customer or a new user, a new subscriber. You can name it anything you want. And so I think changing that name and tagging it as such that as in this new phrase, is much nicer for users of our sheets if we are building for someone else. And it just makes it easier for me when I'm done with creating the script to grab this data, right?

A new customer, I can filter this, do anything I want with it. And here's what we get end up with. We get a date and time, we get this timestamp, we get this type, we get the data all here. I, and I'm again in the middle of really parsing through all this and figuring it out. So I really want to grab all that data and see what can I work with?

And it's all of this stuff, but this email, when it's a new customer, This email is buried here. Where is it? Email equals car gmail.com. And I want to parse that out. So how did I parse that out? I literally said event data dot object, which we can see is this part. See, this is named object.

Object equals all of this. Okay. And then I said email. That's it. Really simple way to grab that email and say we want to grab it. We don't have to do any re rejects regular expressions. We don't have to do any weird replace splitting anything with this script. This event, data.object.email makes it super easy to grab that data.

And what's really cool too, is all of this data is available to us now, country. Invoice settings anything here we can grab. Tax IDs, but right now we're just grabbing the email and we want this customer ID so that once we get all the customer IDs, we wanna say, have they paid in the last, 30 days or last month.

And again I'll be dealing with that all later. I just wanna walk through this and then we have to break, because if we don't break, it's gonna go through this whole thing. I'm really, we wanna make this a little faster and just like break. Go. Go on, move on. Then we insert the data into the sheet and I have five values here.

I have the timestamp, I have the type which I've switched with this switch case. I've made that into a nice readable thing. I have all the data here just in case I wanna look through this later and get more customer email. When there is a customer email, it puts it in. If you notice here some are blank.

It's because once we have payment, new subscriber, all these other things, the web book is not sending it. With this email, see, email equals no. I wasn't able to grab the email then but I can get this customer ID and always go back to this customer and figure out what their email is. Alright, next I'm gonna show you how to deploy.

So this is the new Google Sheet. Google Script ide. If you're using Legacy Editor, it's a little different. We just go up to deploy new deployment and. We wanna select our web app here. I've already done this before, so you might see something, you might see four different types here. This description is like a little information of oh, fixing bugs.

You might wanna say that. And now we can hit deploy. Now, this is important if you have not done this before. You get this web app and you need to copy this if you have done this. See, I've done this 15 other times. This is version 16. So I already have this web app, but I'm gonna copy this url, this web app url, because this is how we're gonna set up our web hook.

Okay? We copy that, copied it. Now our web hook, I'm gonna walk you through this. Let's go back. We need to move my face. In Stripe. If you are creating new Stripe, I'm not gonna walk through how to create a stripe. Just go to developers, web hooks, and this is what you'll see. I'm viewing only test data, so I only have this, I'm actually going to delete.

No I'm gonna delete this and start again. Delete web hook and I'll walk you through everything here. So here's all your end points. We want to create or add endpoint. This endpoint url. Is going to be the Deploy web app. So we take that Command V, this is the web app, and we're gonna do web app for Stripe into Google Sheets.

You name it, whatever you want. I haven't changed this version. So if you are struggling, if the different version, if the latest API ver if you have this latest API version, it's not working, might be because it. I'm using this current API version in 2017. Now you can select like all events, but for this particular web hook, I only want to again, get if a customer is created.

So I search for customer created, then I want to know if they have a subscription. Oops. I always do this. So go here. Sub subscription, customer subscription, created. I did all of this. Okay. Cu customer created, subscription created. I also wanna know if they've paid right payment and not check out, not invoice.

I want. Payment success.

Oh, this is what I want. Oh, charge, sorry, not payment. Charge succeeded. And then I also wanna know if they canceled right.

Canceled payout. Subscription schedule cancel. So this is what you can set up your web app as you wish, or your web click sorry as you wish and send only the events you want to send. Again, you can probably send all of them and just have some error handl and be like, some event happen, we don't care about it.

So we add endpoint point and we're in the testing data. And I wanna send a web hook, so we have this URL here. We're gonna send the web hook. Let's do customer created, cause that's the very first thing that'll happen and it'll tell you what happened here. And it says, test web hook error 500. If this happens to you, go over here and slide this across and you'll see this error and you can read it.

Where is it a server error occurred. Please wait a bit and try again. Okay, so let's go back to. Here and another place to see if it's run. But there's an error is if I go to, where is that? It's a little hard executions. There it is. It's a little hard to remember. And here's version 16, where this is a long time ago.

So it didn't happen. Let's just wait a little. Let's do exactly what they said. Let's wait a little, let's send test web hook again. And we scroll down to the bottom and it says, test web hook sent successfully. So if we go to our sheet, we can see new customer here, and it's this test email here. So you can see 7 0 6.

It is 7:06 AM It's 7:06 AM here. Great. This is a new one. So this web hook works perfectly if this keeps failing on you read through this response here and sometimes you can figure out what changes need to happen. Let's send another te So the next thing that'll happen is that a subscription schedule subs, customer subscription created.

So let's send that test hook and we should scroll down and test web hook sent successfully. We go to our sheet and it says, new paid subscriber. Great. Are they paid? Let's see. Let's actually send a charge. Succeeded. Send that test.

And then we go back Payment. Great. It works right? Someone paid us stripes, sent the web hook, and it gets into our Google sheet. This is really cool. Cause also we can see if they canceled, we can be like, Hey, should we send something to someone who canceled their payment? Not at all. So let's see. Test Web Hook 500 and probably gonna say, sorry, a server error could please wait a minute and try again.

So let's wait 22nd, or 5, 4, 3, 2, 1 sentence hook. Did we wait long enough? Yeah, there we go. And we go back to our sheet and we see canceled. Great. So now we know if we have a new customer, what their email address is. If they've paid, if they've canceled, we know everything in this sheet and we can filter it and everything.

So again, if you are looking for the code, check out the GitHub. I've made this available for public in GitHub. Made it copyable Pastable. Use this make it as for every use case you want, you can use Switch Case to take that hook type and put it into your sheet. I will explain this a little bit more right now.

Is that the sheet that you're getting? I've got the range. This number here is the column, last row plus one is the last, the final row, the next blank row. If you have an error when you first start putting data into this sheet, one reason you might is you have not. You don't have any data, you might have to put these headers in.

I had to type these headers in manually, and then once I had some data in here, I figured out, okay, the last row is this one. Put in information there. You can also clear this information and delete it or delete the rows. As you can see, I deleted a bunch of rows before. And then if you're, if you don't, if you have a bunch of rows, if you have a thousand rows and you don't see any data here, but everything is working perfectly, try scrolling down.

I like to delete all those rows because then I don't get into that trouble of having some dot somewhere or some weird data somewhere that I'm like, oh, I didn't know there was data in that sheet and that row. And now the last row is like 1001. The use case that I'm using for this is to create some kind of in a Google sheet.

So other use cases are for your own personal benefit of keeping the log of all of your striped payments, all your customers, if you might be already using some sort of Zapier, PLI some connection of saying, Hey, the stripe occurs here and I wanna put in a Google sheet over here. But this sort of skips all that step.

And you can don't have to use Zapier. You can create your own web hook and use that in from Stripe to get information from Stripe events in Stripe into a Google sheet. Let me know your use cases right in the description below. If you have a different use case and you're using this code. Thank you so much for watching.

If you are actually interested in. Creating a newsletter yourself. I do have a newsletter creator available for sale. Better sheets. Members do get all of the inner workings, and I am making videos about how all of this works and releasing all the codes separately. But everyone who buys this package of newsletter creator I'll also link to that in the description.

You get everything and you use it. You don't have to learn how it works, you just get to use it. So for 79 bucks, one time payment newsletter creator is available for your use. If you wanna know how it's made and you want to know all of the scripts and all of the little templates and all of the stuff that I put into this, then become a Better Sheets member today.

Better sheets.co. Thank you so much for watching. And again, check out GitHub. I've made this code available for free for everyone. Bye.