Build a Job Status Checker API in 15 Minutes

Advanced

About this Tutorial

The video tutorial demonstrates how to leverage Google Sheets and Google Apps Script to create a simple API for managing and checking the status of jobs or projects. This is particularly useful for project or product managers who need a straightforward system for tracking job statuses without exposing their entire Google Sheet to external users. The process involves:

Setting up a Google Sheet as a database: The sheet contains job IDs and their corresponding statuses. This setup acts as a backend where job statuses can be updated.
Creating an API with Google Apps Script: By writing a custom script, a doGet function is deployed as a web app. This function allows users to query the Google Sheet by job ID via a URL, returning the job's current status.
Deploying a web app: The script is deployed as a web application, which generates a unique URL. This URL acts as an endpoint for the API, where requests can be sent to check job statuses based on job IDs.
Front-end form for checking job status: A form is created using an online tool (like CARRD) where users can input a job ID. Upon submission, the form sends a request to the API, retrieves the job status from the Google Sheet, and displays it to the user.
Securing data visibility: This method ensures that only the relevant job status information is shared with external users, without revealing the entire content of the Google Sheet.
Integration with other Sheets: The API can also be used in conjunction with other Google Sheets using the IMPORTDATA function, allowing for the automated retrieval of job statuses into other spreadsheets without manual data entry or revealing the original data source.

This approach is valuable for project management, allowing for real-time status checks of various jobs with minimal setup and without the need for complex database systems or advanced coding knowledge.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IF() IFNA() ISBLANK()

Video Transcript

<div>00:00 So this is a really cool use of Apps Script and Google Sheets to essentially create a database And then we're going to create our own API where we're going to check the status of a job This is great if you're a prop managing projects if you're a product manager, and you want to Create essentially a place 00:18 on the web for someone to check certain jobs But you maybe give them an ID and you don't want them to see everything in the sheet So what you might be doing now is like using a couple of different Google Sheets and saying hey We'll we'll give you import range Or we'll give you some filter on this data 00:34 and trying to hide this data But this this way when we're gonna create an API and it's not as hard as you think because we're gonna Access Apps Script, and we're gonna create a do Get function here, which is built into Apps Script and gives us a lot of functionality, and we're gonna deploy a web app, 00:51 um it's really really quick, unbelievably easy, I'm gonna show you how to uh write this code and if you're watching this on bettersheets.co you can get this code down below, you can get this exact sheet. 01:02 But let me show you how it works first.

So we have two columns, a job and a status. We have IDs or we could have any text, any unique text here. 01:11 And this unique text is going to be read by our checker here, this is a uh card, I'm using C-A-R-R-D to create just a form online at an a URL uh and it's just a form that checks for this. 01:24 So we see job one over here, so let's look at for job one, check it, and what do we get in response? 01:30 So we got an error , it's actually because we're, we need to use incognito mode.

If we are the logged in user, it sort of errors out, but this is great for giving access to other people, so we're gonna use an incognito mode right now, we're gonna enter job, and we're gonna hit check, uh and what the response 01:49 should be is done, right? And what does it give us? Up here you can see done. So, we go back, let's check number 9, and the response should be in progress. 01:58 And so, let's see, does it do it? Yes, we get in progress. So now we can check jobs if we just know the ID. 02:06 We can also change this ID to something like, new job, or delta job, delta.

And if we enter delta, we just change the, we didn't change anything in the app, if we just change the sheet and the data itself, let's check for delta and see what we get in response. 02:24 We get in progress. See, it's checking the data live, and we've set up the API, we've set up the front end, and this is unbelievably easy. 02:31 We're just gonna do this in a few minutes. So let's get started. If you have never uhh used Apps Script before, this is a pretty advanced use of Apps Script . 02:40 Uh, and we need some data here. Uh, so follow along if you can.

If you haven't, take a look at, uh, spreadsheetautomation101 on bettersheets.co, or Master Spreadsheet Automation, which includes, uh, uh, spreadsheetautomation101 and it gets you up to speed on App Script , but this video just shows you  03:00 a really cool example of it. So we're gonna go up uh, extensions App Script , and that's here. I'm just gonna rewrite this here again for you as I would write it myself. 03:08 Uh, and then we're gonna delete it. But we're gonna do function , do get, Hit.

This uh function is a built-in trigger , so we don't have to do anything. 03:17 We just need to make sure we get this correct that it's due get with capital G. We have the curly brackets as well. 03:22 Uh, and we need some input. So what this URL is doing, we're gonna, we're gonna deploy to a URL. Thank very much. This URL is we're gonna have some piece of data that is attached to that URL. 03:34 And that, in this case we're gonna call it e. We can call it anything we want. This, this is a variable we can call. 03:40 We can call this data or job.

But that might be a little confusing. So, I'm just gonna call it e. 03:46 This is very typical in App Script for event or shorthand for event. What we're gonna need first is a variable called job and we're gonna get e. parameter .job. 04:00 Why job here? Because we're gonna add to this URL, um, we're gonna add to the URL that we get, you'll see the API, uh, uh, uh, um, question mark job equals and then we're gonna have this check. 04:14 You'll see this later on.

Uh, what we need to do is we need to look for all of these statuses and say, hey, based on this parameter that we get, this variable, we need to go and get the status for it. 04:30 So we're gonna do const statuses. This is, we're just calling it statuses because it's all the statuses in one array, spreadsheet app dot get active spreadsheet . 04:40 Add once, if you're typing this yourself, uh add that you have to add these parentheses. Get sheet by name, we can actually put this on a new line. 04:48 And if we want, we're gonna do get sheet by name.

The name of the sheet we have here is statuses. 05:00 You can name it whatever you want. We're gonna call it statuses. is. And this allows us to get just that sheet. 05:07 We're gonna now get the last row of that sheet. Because we need to know how many things are in here. 05:15 Equals. Statuses. Which is this Peace. Variable right here. Get. Last row. That's all we need. Let's follow along here. Now we're gonna grab all the jobs. 05:32 We're gonna do exactly the same thing we did here with statuses but we're gonna get the jobs.

Thanks for And these are the job IDs or job numbers here. 05:41 And we're gonna add a little bit more but we're gonna do a spreadsheet app. Get active spreadsheet . We can use the uhh autocomplete here to do that. 05:51 Get sheet. Eat by name. We're using statuses. Same. We're also going to get the range and the range is going to start at column one. 06:08 Uhh sorry row one, column one, and it's going to go however many rows we have. So last row is our variable for last row. 06:17 And then we only want one column.

But what we need to make sure is in this array we get the values . 06:26 Now we need all of the statuses. So this actually allows us the statuses, allows us to delete all of this, add statuses. 06:34 And there. That might be a little bit easier to read that we're grabbing the statuses page. We're getting the range , the first uh, the first column up until the very last row and we're getting all the values . 06:48 We can copy this and paste it and use it for status array. We can call this job.

I'll array or jobs, whatever we want. 06:58 And instead of getting the first column, we get the second column. So now we're getting both pieces of data . We're getting whichever column we want, which is the first column to look at what's the ID. 07:07 Then we're getting the data we want, which is the status as array. Okay. Now, we're going to create a little for loop. 07:15 We're going to create i equals zero. We're going to do i is less than jobs.length. Because we want to check through all of the jobs. 07:23 i++ because we want to iterate one time.

And each time we iterate we're going to say if jobs in brackets i, which is the way to indicate each line individually. 07:36 And if that's equal to job, meaning the parameter we got in the URL, well what do we want to do? 07:42 We want to say hey, give us a status. So, we do variable output equals statuses array i. So it's the exact same row as the one that's matching that job. 07:55 And now we go down to the bottom and we say return. And normal Oh no.

Basically in a function that we're writing in the app script without the do get if we're just creating a function we're just gonna say return output. 08:06 But we're using do get and we're gonna create uh basically an output text output to the web so we can't just return the output we need to do. 08:17 This uh a little bit something different which is content service.

Content service dot create text output and then put output in there. 08:29 So again normally a normal function in Google Sheets that stays in Google Sheets we wouldn't have to do this content service create text output but we have to do that now because we want it to go out to the web. 08:42 Okay I'm gonna delete all of this and we'll see if we made any errors here because we're gonna try this. 08:48 out we're gonna deploy new deployment we're going to select myself to run it anyone can run it uh hit deploy we're gonna get a unique URL from this now now we have set up essentially an API that .

it exists on the cloud and it's this URL that allows us to uh uh access it so how do we test this we're  09:13 gonna go to postman and we're going to test this here in in future. And uh postman we have our URL this is our web app URL and we want to test with a key which we've created the key here if you haven't noticed yet and maybe I didn't say it but job JOB this is the parameter we want to add to our URL. 09:35 So that's going to be our key.

We're going to write in job and what's the value let's do one this is the very first one we should get a status done now we can send that and we're should get a request a return back done perfect it's working absolutely perfectly now let's create a front end we're gonna 09:53 do that in card so in card all I have to do I'm using a blank thing here I'm gonna create a brand new one you see it here I'm gonna create a brand new one called a form we're going to select the type custom we want to send to URL because our API is just a URL paste in exactly that same web app if you 10:16 lose it you can go back to deploy managed deployments this I think is the second It's just going to be a list of all of the deployments. 10:24 It's going to be a list of last one the latest one here if you lose it there it is we're going to change the method to get and then over on the fields we want a enter job ID The 10. 10:40 text uh the type is going to be text ID again same parameter we've added here in our code JOB job that's going to add that job equals to the URL we're going to change the submit to You enter so you see which one of these is different we can actually delete this one and this is the one we are now checking 11:03 we hit done and all we have to do is save this publish it we're publishing it to a unique uh card sub domain you are more than welcome to page. 11:12 try it out yourself there or if you have your own website you can get it there so we have now updated our website there it is and again we are going to enter to let's say enter and let's see what But we get, we get done, perfect. 11:27 And if that, status has changed if number two is like hey umm review let's go back and see what that might look like now hit enter two wait for a moment ooh the you have the pay, so wait we're waiting and it is now review so now this is great we can create this API uh so quickly right less than fifteen 11:56 minutes this has been twelve minutes we've done this we now have an API that works it gets this data from the sheet what else how else can we use this well if we create a brand new sheet let's go sheet.new and we're like hey I want that data in the sheet I'm I don't want to use import range I don't want 12:12 to have any uh trace back to this sheet right Right?

If you use import range you have to um allow access you need to show the URL of the sheet I don't want any trace of it but I don't want to have to do this front end kind of thing well what can we do we can do equals import data and this allows us to 12:34 add a URL here and we're gonna do and b1 and we're gonna say if is blank b1 if it's Thanks for watching!

actually blank don't do anything but if it's not blank if there's something there then we're gonna add this exact this this URL we're gonna go to it we're gonna import that data we're gonna have to 12:58 add a question mark jobe equals and we're gonna look for the answer to that so right now it is blank Thank you. I'm gonna put in one it says loading there now I first this is similar to import uh range I do have to allow access but this is really cool because the URL that I'm using is not necessarily 13:19 connected to that sheet no one's gonna.

I'm gonna be able to know this script URL is connected to that sheet and now we have a checker right here so we can change this to two let's see that a load and it is in review so again we have a front end possible if we want to with card and using uh. 13:36 any kind of form and buttons here but over here on the left side we can put this into a goo sheet and create our own front end if we want to and we can hit delete and it shows nothing if we have forty five let's see it probably gives some kind of error . 13:52 an a so if we can always add on if and a around it and there we go or some message it might want some message not applicable might don't say instead of an error it's like not available. 14:08 available.

or not found. let's do that. now if we do four should show up. there you go. done. and we're done with our status job checker. 14:19 uhh this is really exciting. I really love this kind of stuff. umm we're using gucci tina coma completely new in different way. 14:26 I hope you enjoyed it.

if you are a better sheets member and watching this on better sheets then you can get down below if you're not watching this on better sheets. 14:31 uhh become a member and get the sheet or uhh this is probably going to be added on Udemy as well. 14:37 uhh but yeah if you're a better sheets member go and grab the sheet down below. you get this code written for you. 14:42 umm and the card is super simple. umm it is just a basic card with a form. there.</div>