A locked sheet that still talks

00:00 — So one of the most advanced things we can do with Google Sheets is that we can create an API that essentially converts our Google Sheets into a database and we can use it as an API on the web. That means we can expose the data inside in a programmatic way, meaning we can access it through a URL that we'll create with a web app, and we can do it in a way where we protect the data inside of our sheet from anyone else. We don't have to share the sheet with anyone else, we don't even have to change the sheet from a share restricted to a share with anyone. We don't need to do that in order to use this. So this is really cool, really great example of how to get around that import range issue where you might be using import range to move data into another sheet, or you're trying to move data onto a website and you're like, "Hey, how do I embed this Google Sheet as like a nice looking thing?" In fact, we can just grab the data inside from inside of a sheet and put it outside of a sheet using the doGet and doPost Apps Script I'm going to show you in this video.

01:03 — So in this video, we're going to convert our Google Sheets into a REST API. We're going to do a few things. We're going to create a way for us to add workouts, and then we're going to see how much we've worked out. So we can call this WORKOUTS, and then we're going to add a summary page. So SUMMARY. So if we have some workouts where like, hey, we did... we might want to track just the number of pushups. So we'll do 15 pushups, and we can add these programmatically, and I will show you. And then we'll have a summary here where we'll say PUSHUPS, and we'll get a total equals SUM(WORKOUTS!A:A). And we'll sum them all up, and then we'll be able to get that at any time.

01:53 — So let me go up to Extensions > Apps Script and start writing from scratch. You don't need to know much, and this is pretty simple to do because I'm going to show you right away. And if you're a Better Sheets member and you're watching this right now on Better Sheets, you can get the Google Sheet down below this exact thing, the finished product. You can save all the time and get it down below if you want.

02:11 — So our function, we're going to write doGet, and then we're actually going to create another function called doPost. And we need some event in here. These are different events, but we're going to call them e for event anyways. They're going to do the same thing in both of them. All right. Now we need to get some parameters. And in the doGet... we're creating a... sorry, a GET API call that's going to add to our workouts. So... we need to go to... let's say var count = e.parameter.count. And we are going to say count here, but we can say any text we want. You're going to see how that is used in the URL later on.

03:04 — And so we want to enter it into the WORKOUTS. So we're going to need to get var workouts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WORKOUTS"). It's all caps. And we need to enter it into the first row. We actually want to, just in case we don't want to have to add rows here, we're going to insert a row every time we go there. So we'll do workouts.insertRowBefore(1). And we'll put a 1 here, that means it's before the first row. If we have a header there, we can say before the second row if we want. So we're going to insert a row, and then we're going to take workouts.getRange(1, 1, 1, 1).setValue(count).

04:12 — And what is it? Maybe we add another parameter here like a type of workout, so we say type = e.parameter.type. We might add that to our URL. I'll show you that as well in our URL. And we're going to say... get exactly the same thing here, but instead of the count, we're going to type in type. And instead of the first column, the second part is the number two column, so we need to change that to two column.

04:44 — And now we want to get some response. So we will say... the doGet is just going to... actually, sorry. This is doPost, and this is doGet. We're going to reverse those. So the doPost needs to get a response that says, "Yep, it totally worked." So we're going to say return ContentService.createTextOutput(200). That's the code that says we're good, and APIs need to return 200 code and say, "Hey, we are good."

05:19 — So how do we test this out? What we can do is first we're going to deploy. We're going to deploy this as a web app. We are going to change it to execute as me, and we're going to change it to anyone can execute this if we're at any website, any page, any URL can access this. We have to authorize it. We just have to authorize it once. Once we have all the stuff we need, it's going to deploy now, and it's going to give us back a URL. We need that URL. We copy that URL, click Done.

05:52 — Let's go to Postman and check out how this works. As we've set it up, but we can change this count, we can change this type. But let me just double check that it works correctly. So we need to go to Workspace. We're just going to test an API. We're going to click New here, and we're just testing HTTP. So we're using a POST. Enter our URL here. Do not need to change the URL in this case. Our key is going to be count, our value let me say 10, and we also want a type and we'll say pushups. Again, this is just going to be adding hopefully everything will be added there. We hit Send. Let's see what our response is.

06:40 — We get some "does not match the signature insertRowsBefore". We have some kind of error. Maybe change all these variables to const. Let's do that first. WORKOUTS is fine. What is the error? Actually the error set is right here: "does not match the method signature SpreadsheetApp.Sheet.insertRowsBefore". Maybe it's going to give it to us in our executions. Let's see if there's an error here. Oh, I think this needs to be different: insertRowBefore. I think that's the issue. It was rows and should be insertRowBefore. We only need one.

07:33 — So in order to change this code, we need to deploy again. So that's the one issue is every time you change code you must deploy again, and you must get a new URL. So it's just going to deploy a new URL, copy that, let's paste it into here. We'll do count: 10, type: pushups. Send. And we should get a 200 code from this. Yep, perfect! And here... oh, we have a blank there because we hit that enter button before. So you can see at the very top we have our data.

08:11 — So now this is really cool, right? We are entering data into our sheet without having to share our sheet, without having to do anything else except for code this bit of code here, a few lines, nine lines of code essentially. But now how do we get the summary? How do we get this B1? Well, let's go to the function doGet. We're going to return, let's start at the end, we're going to return ContentService.createTextOutput(summary). And what's the summary? Well, we'll call it var summary = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SUMMARY").getRange("B1").getValue(). That's the most important part, we're getting the value of that summary cell, that cell in B1, and then all we're doing is returning it. So if anyone wants to check via API how many pushups have we done, we should be able to return this summary real quick.

09:34 — All right, let's deploy again, new deployment. Deploy. We're going to get a new URL here. We want to paste it into here. We can do a GET request, hit Send, and we should get not 200, but 25. So now we have a number 25, and we have it from right here. Perfect, right?

10:00 — So how can we use this? I want to show you now we've set up the code, we've coded it up, we are getting stuff from this sheet, we are adding stuff to this sheet, we are creating this REST API. Now how do we use it? Well, one case is if you have a new sheet, let's do a brand new sheet. I'm going to use a sheet on a completely different account. I'm going to use it on just a normal Gmail account. And we're going to do SUMMARY. If we have this URL and we just want the number, IMPORTDATA is going to be the key here. The URL is exactly this URL, we don't have to do anything else except for Allow access. We are allowing access, and here is our 25. So this is really cool. We are in a different account, we have this information on a sheet somewhere, and we're grabbing it from here, right? We can add to this, so again if we have an API... let's do POST, let me do count = 50, let me do 50 pushups today, type = pushups. We are going to Send. Go back here, and there's 50 pushups, and we now have 75, and our IMPORTDATA has updated immediately! Isn't that really cool?

11:20 — Okay, so how can we create a form online? Well, we can go to Carrd.co. It's an easy way to create a really easy form. We're going to create a new site. I want to show you from scratch how to do this. Blank canvas. There's nothing here yet, so all we're going to add is a form. We want... the type is Custom, we want to Send to URL. Again, we need to grab this URL. Our URL, don't need to edit anything here, just need to change the method to POST. And then in our field up here, we are going to create a label that says Pushups, type we can change this to a Number if we want, ID is going to be count. We can add a fixed value of type = pushups. Done.

12:25 — And now we are going to deploy this. We're going to say Workout Counter, workoutcounter.carrd.co. Let's see if it's available. It is available. We'll push it there. Publish it. This is very fast, just a couple minutes of doing this. This is now available online at workoutcounter.carrd.co. We're going to put in 44, submit. Let's see if something happens. What are we going to get? 200. And on our sheet we have our workouts: 44. We are now entering data into our Google Sheet from a form we've created online that is now completely open and available to the web. That's really cool. We can now get our total count, right? We can even return it in some places. We can say, "Hey, how many pushups have we done?"

13:16 — This is really cool. So we have turned our Google Sheet that is completely restricted into a REST API. We are creating new entries in here, we are getting a total out of here, and we're doing it with very limited code. We're using the functions doPost and doGet here that are built into Google Sheets Apps Script. We're creating an API that's available in the cloud all the time. We're creating a Carrd form to get that data in. This is really cool, and I'm really excited that you were able to watch this. I have some other examples. If you want to see other examples, I'll put them up on bettersheets.co, and in the course Master Spreadsheet Automation we'll also have some more examples of this kind of thing, which can turn a Google Sheet into an API. Really, really cool. Bye!