Search GitHub Jobs using a Spreadsheet

The GitHub Jobs API allows you to search and view jobs. You can look up jobs by roles, functions, and locations, and retrieve a list of the job listings including the title, company name, location, description, how to apply, and more.

In this tutorial, you will learn how to create an app using the GitHub API to retrieve the listings in a spreadsheet. Then, publish it as a web app that you can share with other people.

You can use the Find jobs on GitHub template to get started right away or follow this step-by-step guide to build it from scratch.

What you’ll need

• A dashdash account. You’ll find more information at the end of the guide on how to get early access to the platform.

Connecting the API

We’ll start by setting up the API request. We’ll be using the following parameters to perform our search:

• description: A search term, such as “java” or “backend engineer”.
• location: A city name, zip code, or other location search term.
• full_time: If you want to limit results to full-time positions, set this parameter to “true”. Otherwise, you can leave it blank.
• page: Used for pagination as the API returns 50 listings at a time. By default, it’s set to “0” which retrieves the first page of results. You can set it to “1” for the second page, “2” for the third, and so on.

All the parameters are optional and you can use any combination of them. We’ll list them down in column A.

A1: url
A2: description
A3: location
A4: full-time
A5: page

For “url”, we’ll copy and paste the API URL.

B1: https://jobs.github.com/positions.json

The API request is structured in the following way.

[API URL]?[first parameter]=[value]&[second parameter]=[value]& …etc.

That way, it corresponds to a request that looks like this.

https://jobs.github.com/positions.json?description=python&location=new+york

We’ll be using the GET function, which is the HTTP web request to retrieve data from the API, and structure the API call by adding the parameters and referencing their corresponding cells in which we’ll be adding the values.

D1: Get jobs
E1: =GET(B1&”?description=”&B2&”&location=”&B3&”&full_time=”&B4&”&page=”&B5)

By executing E1, you will get a #SIZE! error because the API is trying to retrieve all the roles on GitHub Jobs as no search parameters are defined. We can execute our first request by defining the values to the parameters in column B. For example, if we enter “Full Stack” in B2 and “California” in B3, we’ll get a list of relevant roles from GitHub.

B2: Full Stack
B3: California

By clicking on the {data} object, we can view the list of retrieved roles. In the next section, we’ll discuss how we can have them automatically displayed in the spreadsheet with every search.

Displaying the Jobs

dashdash offers three functions that allow you to display JSON data in table form easily; INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA.

• INSERT_DATA: Displays the data in a table and keeps adding any new data to the same table in new rows with every function execution.
• OVERWRITE_DATA: Overwrites the table data with every new function execution.
• UPDATE_DATA: Takes an extra key parameter, which is usually a unique identifier (e.g., url). It checks the defined key to check if the inserted rows are unique (don’t match the key) or not. If a row is unique, it’s inserted normally in the table, acting like INSERT_DATA. If a row is not unique, it doesn’t insert it in a new row and only updates the other data points it contains if they had any changes.

We can use INSERT_DATA to display the retrieved job listings. All we have to do is define the data object (E1) and where we want to display the table (A8:8).

D2: Fill table
E2: =INSERT_DATA(E1,A8:8)

When using INSERT_DATA, new job listings will be added to the table with every new search. If we would like to completely overwrite the table with each search, we can use OVERWRITE_DATA.

E2: =OVERWRITE_DATA(E1,A8:8)

Controlling the Function Execution

Whenever any changes are made to any of the values of the parameters, the GET function automatically executes, and new results will be retrieved. However, if we want to control the function’s execution, we can use a button to do so.

To add a button, we need to select the cell where we want to add it (e.g. B6). Then, click on the action elements section, select “button”, and give it a label. We’ll give it the label “SEARCH”.

To link the button to the function, we need to use an IF function. The IF condition defines that if the button is pressed, the function is executed. Otherwise, we’ll show the text “Press the button”.

E1: =IF(B6=”SEARCH”,GET(B1&”?description=”&B2&”&location=”&B3&”&full_time=”&B4&”&page=”&B5),”Press the button”)

You might notice that E2 will return an error. That’s because until you press the button, E1 won’t be a data object anymore. We can use the IFERROR function to hide the error.

E2: =IFERROR(OVERWRITE_DATA(E1,A8:8),””)

Publishing as an App

Once the spreadsheet is ready, we can go ahead and publish it as a web app.

All we have to do is define the cells where users can input the parameters values. To do that, we need to select the input cells B2:B5, click on the action elements section and select “Input field”.

To publish the app, click on the “Publishing Settings” icon in the upper left corner next to “Preview.” Then, hit the “Publish” button.

The app is now published, and the link is copied to the clipboard. We can also copy it by clicking on the “Copy link to clipboard” icon that has appeared next to the publishing one. We can view the published app version of the dashboard by pasting the link in the address bar.

The published app behaves in the same way as the spreadsheet. That way, you can share it with your other people.

Make the Most out of dashdash!

To take your app to the next level, you can leverage dashdash’s automation functions like the REFRESH function to automate the retrieval of new job listings. You can also combine it with other integrations like Slack and Gmail to receive alerts of new jobs by Slack or email.

Get Early Access!

To get a dashdash account and start building your apps, you can reach out to me via:

• Email: hady@dashdash.com
• Twitter: @HadyElHady93
• Calendly: Book a call so I can help you set up your dashboard and get started.