Convert Google Sheets into a REST API

Advanced

About this Tutorial

How to use Google Sheets as a database by turning it into a REST API with Google Apps Script. This makes Google Sheets a powerful tool for managing data online, allowing for easy data entry and retrieval without manual sharing or updating.

This allows you to add and get data from the sheet over the internet without sharing the sheet. It shows how to:

Write code in Google Sheets that listens for web requests.
Use doGet and doPost functions to handle these requests.
Insert data into the sheet and fetch a summary of this data through the web.
Deploy the script as a web app to get a URL for accessing the API.
Test the API with tools like Postman or embed it in a web form.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>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. 00:11 That means we can expose the data inside in a programmatic way, meaning we can uh 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. 00:28 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. 00:37 So this is really cool, really great example of how to get around that import range issue, um where you might be using import range to move data into another sheet, or you're trying to move data onto a web site and you're like, hey how do I embed this uh Google sheet as like a nice looking thing. 00:53 In fact, we can just grab the data outside, from inside of a sheet and put it outside of a sheet using the do, get, and do post Apps Script I'm going to show you in this video. 01:02 So in this video, we're going to convert our Google Sheets into a REST API and we're going to do a few things. 01:08 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. 01:16 So we can call this workouts and then we're going to add a summer workout.

So if we have some workouts we're like hey we did uhm we might want to track the number of pushups. 01:29 So we'll do 15 pushups and we can add these programmatically. We'll show you and then we'll have a summary here where we'll say pushups and we'll get a total equals sum and we'll just get workouts a colon a and we'll sum them all up and then be able to get that at any time. 01:53 So let's go up to Extensions, Apps Script and start writing from scratch.

Uh you don't need to know much and this is pretty simple to do um because I'm going to show you right away and if you're a BetterSheets member and you're watching this right now on BetterSheets you can get the uh Google Sheet down 02:06 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 doGit and then we're actually going to create another function called doPost and we need some event in here. 02:17 These are different events but we're going to call them e for event anyways.

We're going to do the same thing for both of them. 02:23 Alright. Now we need to get the some parameters uh and in the doGit we're creating a get post, um sorry, a get API call that's going to add to our workouts. 02:35 So we need to go to e uh let's say variable uh pushups equals actually let's call it count equals e. parameter .count. 02:55 And we're going to say count here, but we can say any text we want. We're going to, you're going to see how that is used in the URL later on. 03:04 And so we want to enter it into uhm the workouts.

So we're going to need to get, or variable workouts equals spreadsheet app dot get active spreadsheet dot get sheet by name. 03:20 And we're going to get workouts here. 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. 03:36 So we'll do workouts dot insert row before and we'll put a 1 here. That means it's before the first. Before the first row. 03:47 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 dot get range . 03:56 We're going to say column 1, uh, column 1. One row, or sorry, row 1, column 1, and only one row and one column. 04:05 So only one cell. And we're going to say set value is going to be our count. And what is it? 04:13 Maybe we add another parameter here, like a type of workout. So we say type equals e. parameter .type. We might add that to our URL. 04:25 I'll show you that as well in our URL.

And we're going to say, going to get exactly the same thing here. 04:32 But instead of the count, we're going to type in the type, and instead of the first column, the second part is the number two column. 04:42 So we need to change that to two column. And now we want to get some response, so we will say ah the doGet is just going to, actually, sorry this is doPost , and this is doGet , ah we're going to reverse those. 04:55 So the doPost needs to get a response that says, yep, we, it totally worked.

So we're going to say return, ah, contentService. 05:06 createTextOutput, and we're going to say, ah, just 200. That's the code that says, we're good. And that, ah, APIs need to return 200 code and say, hey, we are good. 05:18 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 ex, execute it as my, me, and we're going to change it to anyone can execute this if we're at any, ah, website, any page. 05:36 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, let's go to postman and check out how this works, because we've set it 05:59 up but we can change this count, we can change this type, uh but let's just double check that it works correctly, so we need to go to workspace, we need, we're just going to test a, we're just going to click new here and we're just testing http, so we're using a post, enter our url here, do not need  06:18 to change the url in this case, our key is going to be count, our value let's say 10, and we also want a type, and we'll say pushups. 06:28 Okay, 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, doesn't match the signature, insert rows before.

We have some kind of error , maybe change all these variables to const, let's do that first. 06:58 Workouts is fine. What is the error ? Actually the error is right here, doesn't match the method signature for sheet app dot sheet, insert rows , final quote, const. 07:12 code. Maybe it's going to give it to us in our execution. Let's see if there's an error here. Oh, I think this needs to be different. 07:25 Insert row before. I think that's the issue. It was rows and it should be insert row before. We only need one. 07:33 So in order to change this ah code we need to deploy again.

So that's the one issue is every time you change the code you must get deploy again and you must get a new URL. 07:43 So that's just going to deploy a new URL. Copy that. Let's paste it into here. Ah we don't, we're not, actually we shouldn't have done that. 07:53 We can do count. We'll do 10. We'll do type. Push-ups again. Send, and we should get a 200 code from this, if everything, yep. 08:05 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 because without Ow! Without having to share our sheet, without having to do anything else except code this bit of code here, a few lines, nine lines of code essentially. 08:26 But now how do we get the summary? How do we get this B1? Well, let's go to to uh the function doGet . 08:34 We're going to return, let's start at the end, we're going to return contentService. So similar to the top and we're going to create hexed output and we want to give the count. 08:48 Actually let's call it summary. And what's a summary?

Well, we'll call it a variable summary equals, we can do spreadsheetApp , we can copy this actually to make it faster. 08:59 Instead of workouts, it's summary. And we're not getting the whole sheet. We just need getRange . In this case, we only need one range , so we're saying b1. 09:09 We can actually just type in quotes b1 here, make it super easy and super easy to read, and then getValue . 09:15 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. 09:24 So if anyone wants to check via API how much, how many pushups are we doing, we should be able to return this summary really quick. 09:33 Alright. Let's deploy again. New deployment. Deploy. We're going to get a new URL here. We want to paste it into here. 09:46 Uh, we can do a get request. Hit send. And we should get not too many. 200 but 25. So now we have a number 25 and we have it from right here. 09:57 Perfect. Right? So how can we use this? I want to show you now we've set up the code.

We've coded up. 10:04 We are getting stuff from this sheet. We are adding stuff to this sheet. We are creating this URL. Now how do we use it? 10:11 Well, one case is if you have a new sheet. Let's do a brand new sheet. Actually, I'm going to use a sheet on a completely different uhm account. 10:22 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 . 10:34 Import data 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 . 10:42 We are allowing access. Allow access . And here's our 25. So this is really cool. We are in a different account. 10:49 We have this information on a sheet somewhere and we're grabbing it from here. Alright. We can add to this. So again, to if we have an API, I'll show you how to set this up as well. 10:59 Let's do post. Let's do count equals 50. Let's do 50 pushups today. Type pushups. We are going to send.

Go back here and there's 50 pushups. 11:12 Pushups and we now have 75 and our data , import data has updated immediately. Isn't that really cool? Okay, so how can we create a form online? 11:22 Well, we can go to card is an easy way to create a really easy form. Uh, we're going to create a new set. 11:28 I'm going 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. 11:37 Where is it? Form.

We want to, the type is custom, we want to send to URL, again we need to grab this uh URL. 11:46 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're going to create a label that says uhm uh pushups. 12:00 And we want a text, no actually we want, we can change this to a number if we want, char , uh, if we want. 12:12 Our ID is going to be count. We can add a fixed value of type equals pushups, done.

And now we are going to deploy this, we are going to say workout counter. 12:32 Let's publish this to a subdomain , workout counter, let's see if it's available. It is available, we'll push it there. Publish it, this is as, this is very fast, just a couple minutes of doing this, this is now available online, workout counter. 12:47 We are 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. 12:56 We are now entering data into our Google Sheet from a form we've created online that is now completely open and available to everyone. 13:04 To the web, that's really cool, we can now get our total count, right, we can even return in some places, we can say, hey, how many pushups have we done? 13:15 This is really cool.

So we have turned our Google Sheets that is now completely restricted , this is our normal Google Sheet, into a REST API. 13:24 We are creating new entries in here, we are getting a total out of here, and we are doing it with very limited code. 13:33 We are using the function doPost and doGet here. That are built into Google Sheets Apps Script , we are creating an API that is available in the cloud all the time, we are creating a card form to get that data in, this is really cool, and I am really excited that you are able to watch this and walk along 13:54 .

if you want to see other examples, I'll put them up on bettersheets.co, and in this course, uh in the course Master Spreadsheet Automation, we'll also have some more examples of this, uh kind of thing, where you can turn a Google Sheet into an API. 14:08 Really, really cool. Bye.</div>