Build a simple Remote Job directory website using Google Sheets API

Ramakrishna Anand J

Posted in tutorials

If you're looking for a simple way to build a curated directory listing website like Product, job, travel,etc; then this tutorial will help you create one in a matter of minutes. Also, we are going to see - how to deploy the directory website to Netlify from Gridbox.

Before we get started let us look into the new futuristic approach of developing websites based on Jamstack.

What is Jamstack?

Jamstack is a new way of building websites and apps that delivers better performance, higher security, lower cost of scaling, and a better developer experience. It's a modern web development architecture based on client-side JavaScript, reusable APIs, and prebuilt Markup.

In this tutorial we're going to build a simple product directory website in a Jamstack way - Google Sheets API + Javascript (Tabletop.js) + Static HTML based on Bootstrap 4 (for layout).

Tabletop.js allows us to use Google Spreadsheets as a sort of data store, by taking the spreadsheet and making it easily accessible through JavaScript.

Let us dive into the tutorial:

Create a Google Spreadsheet

We’ll be using the demo we've made, with its source code and spreadsheet. The first thing we’ll need is a Google account to access the spreadsheet.

Open a new spreadsheet and input your own values in the columns just like ours. The first cell on each column is the reference that’ll be used later in our JavaScript, and the second cell is the actual content for the website.

We're going to create four different rows that will be used as a reference in Javascript.

  1. title - (Job Title)
  2. description - (Job Simple Description)
  3. category - (Category of the Job - For eg: Front-End, Design, etc...)
  4. link - (Job link or link to careers page)
  5. image - (Company Logo)

google-sheet-1.png

Next, we’ll publish the data to the web by clicking on File → Publish to the web in the menu bar.

A link will be provided, but it’s technically useless to us, so we can ignore it. The important thing is that the spreadsheet (and its data) is now publicly accessible so we can fetch it for our app.

That said, there is a link we need. Clicking the green “Share” button in the upper-right corner of the page will trigger a modal that provides a sharable link to the spreadsheet and lets us set permissions as well. Let’s grab that link and set the permissions so that anyone with the link can view the spreadsheet. That way, the data won’t inadvertently be edited by someone else.

Now is the time to initialize Tabletop using Javascript in our project. Let’s link up to their hosted minified file. Similarly, we could copy the raw minified code, drop it into our own script file and host it ourselves.

In this tutorial we're going to clone a fully functional pre-built directory website using Gridbox. However, you can make use of any IDE which you're comfortable with, to get started. (Just skip to full source code section for reference)

Let us clone the project

  1. If you've an account you can login to your account or you need to create one.
  2. Once you've logged in - click on this link
  3. Now you can see the preview of pre-built Job Directory Website using Google Sheets API and Tabletop.js
  4. Click on the clone project button to make a copy
  5. It will take you to the Gridbox designer

clone-project-1.gif

Let us modify the design

From the Gridbox designer, you're free to modify the existing layout this site by adding additional components or changing existing content, etc...

Note: Next to the menu, you will see an empty container with the following text - "This content will be loaded from Google Sheets via Javascript. Use the Code Editor to hack the code or modify your data." - Don't remove this container, because all your spreadsheet data will be loaded in this area as you've seen in the preview page.

Here is full source code:

HTML

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Curated Directory - Google Sheets API</title>
    <meta name="description" content="None">
    <meta name="author" content="">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk"
        crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.13.1/css/all.min.css">
    <link rel="stylesheet" href="assets/css/styles.css">
</head>

<body>
    <nav class="navbar navbar-expand-lg navbar-light bg-gradient">
        <div class="container"><a href="#" class="navbar-brand"><img src="./assets/img/gb-92x92.png" id="iz1c8" class="logo" /></a>
            <ul class="navbar-nav ml-auto">
            </ul><a href="#" class="btn mt-2 mt-md-0 ml-lg-3 btn-light">Post a Job</a>
        </div>
    </nav>
    <section class="header-content py-3 bg-primary bg-gradient" id="i4zk">
        <div class="container">
            <div class="row">
                <div class="my-auto col-md-12">
                    <h1 class="display-4 text-center text-light pb-2">Find all Design Tools</h1>
                    <p class="lead text-light text-center pb-3">Browse Handpicked Best Design Tools for your next project</p>
                    <div class="form-group has-search w-75 mt-2 mb-2 mx-auto"><span class="fa fa-search form-control-feedback"></span><input type="text" id="search-box"
                            placeholder="Search by Job Title. Eg: Design" class="form-control" /></div>
                </div>
            </div>
        </div>
    </section>
    <div class="container py-4">
        <ul id="category-links" class="nav nav-pills nav-fill">
            <li class="nav-item"><a href="javascript:void(0);" data-category="all" class="filter-category nav-link active">All</a></li>
            <li class="nav-item"><a href="javascript:void(0);" data-category="full-stack" class="filter-category nav-link">Full Stack Development</a></li>
            <li class="nav-item"><a href="javascript:void(0);" data-category="front-end" class="filter-category nav-link">Front End</a></li>
            <li class="nav-item"><a href="javascript:void(0);" data-category="mobile" class="filter-category nav-link">Mobile</a></li>
            <li class="nav-item"><a href="javascript:void(0);" data-category="design" class="filter-category nav-link">Design</a></li>
        </ul>
    </div>
    <div id="data-feed" class="container py-5">
        <p class="text-center"> This content will be loaded from Google Sheets via Javascript. Use the Code Editor to hack the code or modify your data. </p>
    </div>
    <!--Bootstrap Scripts-->
    <footer class="py-3 bg-light footer">
        <div class="container">
            <div class="d-flex flex-column flex-sm-row align-items-center justify-content-center">
                <p class="mb-0 small text-muted">© 2020 Gridbox. All right reserved. </p>
            </div>
        </div>
    </footer>
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous">
    </script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous">
    </script>
    <!--- Tabletop.js--->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js"></script>
    <!---Where we're going to initialze tabletop and pull data--->
    <script src="assets/js/app.js"></script>
</body>

</html>

CSS

.avatar-lg img {
	height: 100px;
	width: auto;
}

.hide {
	display: none !important;
}

.show {
	display: block !important;
}

.has-search .form-control {
	padding-left: 2.375rem;
}

.has-search .form-control-feedback {
	position: absolute;
	z-index: 2;
	display: block;
	width: 2.375rem;
	height: 2.375rem;
	line-height: 2.375rem;
	text-align: center;
	pointer-events: none;
	color: #aaa;
}

.display-4.text-center {
	font-size: 47px;
}

.logo {
	width: 47px;
	height: 47px;
}

.badge-outline-dark {
	border: 1px solid #313131 !important;
	background: transparent;
	color: #313131;
}

.header-content.py-3.bg-primary {
	background-color: #007bff;
}

.navbar.navbar-expand-lg.navbar-light.bg-primary {
	background-image: -webkit-linear-gradient(90deg, rgba(0, 0, 0, 0) 8.73016%, rgba(0, 0, 0, 0) 8.73016%);
}

.bg-gradient {
	background-color: #007bff;
	background-image: -webkit-linear-gradient(90deg, rgba(0, 0, 0, 0) 9.52381%, rgba(0, 0, 0, 0) 9.52381%);
}

@media (max-width: 992px) {
	.display-4 {
		font-size: 33px;
	}
}

@media (max-width: 500px) {
	.header-content .display-4 {
		font-size: 23px !important;
		text-align: center;
	}

	.header-content .lead {
		font-size: 14px;
		text-align: center;
	}

	.category-card .card-title {
		font-size: 15px !important;
	}

	.feed-logo {
		height: 40px !important;
		width: auto;
	}
}

Here comes the interesting part --- Javascript

/*jshint esversion: 6 */

/* You can replace -->124L6LkF9NJ79aZCTYsArNJVDmnSZVUcc1RqXsVedrYU<-- this id in the following line from your Google Spreadhsheet id - you see in the browser address bar  */
var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/124L6LkF9NJ79aZCTYsArNJVDmnSZVUcc1RqXsVedrYU/pubhtml';
var feedParent = document.getElementById('data-feed');
var searchBox = document.getElementById('search-box');
var feedData;

function init() {
	feedParent.innerHTML = 'Loading...';
	Tabletop.init({
		key: publicSpreadsheetUrl,
		callback: showInfo,
		simpleSheet: true
	});
}

function doSearch(value) {
	searchValue = value.trim();
	searchValue = value.toLowerCase();
	if (searchValue.length > 0) {
		searchFeed = feedData.filter(function(feedData) {
			return (feedData.title.toLowerCase().includes(searchValue));
		});
		feedParent.innerHTML = "";
		populateData(searchFeed);
	} else {
		populateData(feedData);
	}

}

function showInfo(data, tabletop) {
	if (data) {
		feedData = data;
		feedParent.innerHTML = '';
		populateData(feedData);
	}
}

function populateData(data) {
	data.forEach(function(data) {
		var divCard = document.createElement('div');
		var tags = [];
		var tagsHTML = '';
		divCard.classList.add('card');
		divCard.classList.add('category-card');
		divCard.classList.add('mb-2');
		divCard.setAttribute('data-id', data.category);
		if (data.hasOwnProperty('tags')) {
			tags = data.tags.split(',');
			if (tags.length > 0) {
				tags.forEach(function(tags) {
					tagsHTML += `<span class="badge badge-pill badge-outline-dark mr-2">${ tags }</span>`;
				});
			}
		}
		var feedHTML = `<div class="card-body">
                <div class="row align-items-center">
                    <div class="col-2">
                        <a href="${ data.url }" target="_blank" class="avatar avatar-lg"><img
                                src="${data.image}" alt="..." class="img-fluid feed-logo rounded"></a></div>
                    <div class="col-7 ml-n2">
                        <h4 class="card-title mb-1 name"><a target="_blank" href="${ data.url }">${data.title}</a></h4>
                        <p class="card-text small text-muted mt-1 mb-1">${data.description}</p>
						<div class="tags-outer mt-2 mb-2">
							${tagsHTML}
 						</div>
                    </div>
                    <div class="col-3"><a href="${ data.url }" target="_blank" class="btn btn-outline-dark btn-sm float-right">Visit Website</a></div>
                </div>
            </div>`;
		divCard.innerHTML = feedHTML;
		feedParent.appendChild(divCard);
	});

}

searchBox.addEventListener('input', function(evt) {
	doSearch(this.value);
});

document.body.onclick = function(e) { //when the document body is clicked
	if (window.event) {
		e = event.srcElement; //assign the element clicked to e (IE 6-8)
	} else {
		e = e.target; //assign the element clicked to e
	}

	if (e.className && e.className.indexOf('filter-category') != -1) {
		//if the element has a class name, and that is 'someclass' then...
		var category = e.getAttribute('data-category');
		var allNavLinks = document.querySelectorAll('.filter-category');
		[].forEach.call(allNavLinks, el => {
			el.classList.remove('active');
			e.classList.add('active');
		});
		var otherCategories = document.querySelectorAll('.category-card');
		var selectedCategory = document.querySelectorAll('[data-id="' + category + '"]');
		if (category !== 'all') {
			[].forEach.call(otherCategories, el => {
				el.classList.remove('show');
				el.classList.add('hide');
			});
			[].forEach.call(selectedCategory, el => {
				el.classList.remove('hide');
				el.classList.add('show');
			});
		} else {
			[].forEach.call(selectedCategory, el => {
				el.classList.remove('show')
			});
			[].forEach.call(otherCategories, el => {
				el.classList.remove('hide')
			});
		}
	}
};

window.addEventListener('DOMContentLoaded', init);

Replace the publicSpreadsheetUrl variable in the code with the sharable link from the spreadsheet. See, told you we’d need that!

The showInfo function calls populateData() function where we’ll use the forEach() method to loop through each spreadsheet column while equating it with the corresponding ID.innerHTML method which, in turn, loads the spreadsheet’s data into the HTML tag through the ID.

This is a section of HTML showing the empty tags. This is a good way to go, but it could be abstracted further but creating the HTML elements directly from JavaScript.

<div id="data-feed" class="container py-5">
       <p class="text-center"> This content will be loaded from Google Sheets via Javascript. Use the Code Editor to hack the code or modify your data. </p>
</div>

The below function function will loop through JSON result and render the values into Bootstrap 4 card.

function populateData(data) {
	data.forEach(function(data) {
		var divCard = document.createElement('div');
		var tags = [];
		var tagsHTML = '';
		divCard.classList.add('card');
		divCard.classList.add('category-card');
		divCard.classList.add('mb-2');
		divCard.setAttribute('data-id', data.category);
		if (data.hasOwnProperty('tags')) {
			tags = data.tags.split(',');
			if (tags.length > 0) {
				tags.forEach(function(tags) {
					tagsHTML += `<span class="badge badge-pill badge-outline-dark mr-2">${ tags }</span>`;
				});
			}
		}
		var feedHTML = `<div class="card-body">
                <div class="row align-items-center">
                    <div class="col-2">
                        <a href="${ data.url }" target="_blank" class="avatar avatar-lg"><img
                                src="${data.image}" alt="..." class="img-fluid feed-logo rounded"></a></div>
                    <div class="col-7 ml-n2">
                        <h4 class="card-title mb-1 name"><a target="_blank" href="${ data.url }">${data.title}</a></h4>
                        <p class="card-text small text-muted mt-1 mb-1">${data.description}</p>
						<div class="tags-outer mt-2 mb-2">
							${tagsHTML}
 						</div>
                    </div>
                    <div class="col-3"><a href="${ data.url }" target="_blank" class="btn btn-outline-dark btn-sm float-right">Visit Website</a></div>
                </div>
            </div>`;
		divCard.innerHTML = feedHTML;
		feedParent.appendChild(divCard);
	});

}

Let us add search functionality to the directory in order to find items based on title.

gsheet-search-filter.gif

In the HTML we've added search box, the following section of HTML showing the search box.

 <div class="form-group has-search w-75 mt-2 mb-2 mx-auto"><span class="fa fa-search form-control-feedback"></span>
        <input type="text" id="search-box" placeholder="Search by Job Title. Eg: Design" class="form-control" />
 </div>

Listing to search-box input event

searchBox.addEventListener('input', function(evt) {
	doSearch(this.value);
});

doSearch() function will filter the JSON array generated by Spreadsheet by the matching search input - keyword with title.

function doSearch(value) {
	searchValue = value.trim();
	searchValue = value.toLowerCase();
	if (searchValue.length > 0) {
		searchFeed = feedData.filter(function(feedData) {
			return (feedData.title.toLowerCase().includes(searchValue));
		});
		feedParent.innerHTML = "";
		populateData(searchFeed);
	} else {
		populateData(feedData);
	}

}

Adding Content Filter Menu

Now let us create a filter menu - For eg: if you've more categories of data then we can add filter function to filter the array based on the category for better user experience.

gsheet-filter.gif

Here is the HTML snippet for filter menu

 <div class="container py-4">
    <ul id="category-links" class="nav nav-pills nav-fill">
        <li class="nav-item"><a href="javascript:void(0);" data-category="all" class="filter-category nav-link active">All</a></li>
        <li class="nav-item"><a href="javascript:void(0);" data-category="full-stack" class="filter-category nav-link">Full Stack Development</a></li>
        <li class="nav-item"><a href="javascript:void(0);" data-category="front-end" class="filter-category nav-link">Front End</a></li>
        <li class="nav-item"><a href="javascript:void(0);" data-category="mobile" class="filter-category nav-link">Mobile</a></li>
        <li class="nav-item"><a href="javascript:void(0);" data-category="design" class="filter-category nav-link">Design</a></li>
    </ul>
</div>

In the above snippet - you can see data-category attribute with some value -

We're going to fetch this attribute value using on click function and filter the spreadsheet JSON array result.

Here is the snippet

document.body.onclick = function(e) { //when the document body is clicked
	if (window.event) {
		e = event.srcElement; //assign the element clicked to e (IE 6-8)
	} else {
		e = e.target; //assign the element clicked to e
	}

	if (e.className && e.className.indexOf('filter-category') != -1) {
		//if the element has a class name, and that is 'someclass' then...
		var category = e.getAttribute('data-category');
		var allNavLinks = document.querySelectorAll('.filter-category');
		[].forEach.call(allNavLinks, el => {
			el.classList.remove('active');
			e.classList.add('active');
		});
		var otherCategories = document.querySelectorAll('.category-card');
		var selectedCategory = document.querySelectorAll('[data-id="' + category + '"]');
		if (category !== 'all') {
			[].forEach.call(otherCategories, el => {
				el.classList.remove('show');
				el.classList.add('hide');
			});
			[].forEach.call(selectedCategory, el => {
				el.classList.remove('hide');
				el.classList.add('show');
			});
		} else {
			[].forEach.call(selectedCategory, el => {
				el.classList.remove('show')
			});
			[].forEach.call(otherCategories, el => {
				el.classList.remove('hide')
			});
		}
	}
};

selectedCategory will return all the matched results which needs to shown in the page and rest of the content needs to hidden. This can achieved by adding .show and hide classes in CSS.

Here is the final demo url:

https://www.gridbox.io/app/preview/b874e4b7-2652-48bb-b912-836e7b5f11b1

Deploying to Netlify

Once your project is ready, you can always click on publish button on the left bar and click on the publish button to deploy the project to Netlify

If you haven't connected with Netlify already then you need to do the following

  1. Go to Deployment Settings (https://www.gridbox.io/app/account/deployment-settings)
  2. Click on the Authorize Netlify button
  3. Login to Netlify Account if you've one or create a new account in Netlify .
  4. Authorize Gridbox
  5. Now you're connected.

Publishing a Project to Netlify

deploy-directory-1.gif

  1. Open the Project you've created.
  2. From the left toolbar - Click on Netlify Icon.
  3. Click on the Publish to Netlify
  4. That's it your Project will be deployed to Netlify - with auto generated url.

Adding Custom Domain with Netlify

https://docs.netlify.com/domains-https/custom-domains/

Ready to build your next Bootstrap, HTML or Jamstack Project with Gridbox?

Check on this link (https://www.gridbox.io) and spread the word :)