Apr 16, 2022
Integrating Dynamic Databases in Static Webpages
If you’re anything like me, you have most likely at some point wanted to host your own simple website, and because you like to save money, have looked for free alternatives to mainstream hosting solutions. For me, that option was GitHub Pages, which I have been using since 2018 to host my landing page. Complete freedom and creative control, with only the cost of a domain name.
However, it can be nice at times to have something a little more interactive. So today, I’m going to walk you through how to setup a simple database with end user input, all on a free static webpage. You can view the finished project at anytime on my GitHub, github.com/tjsanzen/database-tutorial or view the finished project here. If you simply want to finished code, scroll to the bottom to view the files.
Our first step is to get all of our resources gathered up into one place. Start by heading over to GitHub and creating a new account if you haven’t already. Once you have a profile, go ahead and create a new repository. Allow the default wizard to take you through the setup, and name it whatever you’d like. I went with simply medium, but it’s completely up to you. From here, we’re going to create two new files, index.html and style.css. You can just leave them blank for now.
Once we have those setup, it is time to gather the rest of the resources. You may want to create a third file, named README.md in which you can store links or text reminders in. It is essentially a regular text file for your GitHub page, that allows you or visitors to quickly understand the purpose of your page.
Now, assuming you already have a Google account, we’re going to setup the database. Go to your Google Drive account, and create a new sheet, and for this example, name it static-database. For our example, we’ll be creating a simple restaurant review site, but you can adapt this to your ideas. In this case, we’ll create three rows, for the reviewer name, the restaurant they visited, and what they think.
With our “database” created, we need to find a way to take this data, and display it on our website. For this we’ll use a free solution, called API Spreadsheets. I’d like to note at this time, this project would not be possible without Astha Sharma who originally wrote an older article on pulling from a database using API Spreadsheets. All I did was modify it and add the function for end user input.
Sign up for a free API Spreadsheets account, which should be more than enough for most small use cases. They also have paid plans if you think your website will get more than 50 requests per day, or have more than 1500 lines of data. Once you’re logged in, select “Upload” from the top menu, and choose “Google Sheets.” You will be asked to link your Google account, and then given the option to copy your API URL. You’ll want to copy it, and then head back over to our index.html file.
Back in our index.html file, we’ll start by simply setting up your webpage. If you aren’t familiar with HTML, there are many free sites to learn on, I recommend W3 Schools. In our index.html file, start with this:
<!DOCTYPE html>
<html>
<head>
<title> Static Database Demo </title>
<link rel="stylesheet" href="style.css"> </head>
<body>
<div id="hero">
<h1>Restaurant Reviews</h1>
<h3>Leave a review on a restaurant you recently visited!</h3>
</div>
<div id="display">
<div id="loader">
<h4> Loading</h4>
</div>
<div id="allNames">
</div>
<div id="errorMessage">
<h2> Failed to get data. Please refresh </h2> </div>
</body>
</html>Once we’ve created our basic file, let’s add some JavaScript. We’ll add the following right before the </body> tag.
<script>
let allNamesElm = document.getElementById("allNames")
let loaderElm = document.getElementById("loader")
let errorMessageElm = document.getElementById("errorMessage") fuction setErrorDisplay(){
loaderElm.style.display = "none"
allNamesElm.style.display = "none"
errorMessageElm.style.display = "block"
}fetch("YOUR API URL").then(res=>{
if (res.status === 200){
res.json().then(data=>{
const yourData = data["data"]
for(let i = 0; i < yourData.length; i++){
let rowInfo = yourData[i] let rowInfoDiv = document.createElement("div")
rowInfoDiv.classList.add("name-row")
let rowName = document.createElement("h4")
let rowNameNode = document.createTextNode(rowInfo["name"])
rowName.appendChild(rowNameNode)
rowName.classList.add("name") rowInfo.Div.appendChild(rowName) allNamesElm.appendChild(rowInfoDiv) }
loaderElm.style.display = "none"
allNamesElm.style.display = "block"
errorMessageElm.style.display = "none" }).catch(err => {
setErrorDisplay()
}) }
else{
setErrorDisplay()
}
}).catch(err =>{
setErrorDisplay()
})</script>
This script will simply allow us to pull name row with the list of names, and display it on our website. You’ll simply modify this code, depending on your project and the size of your spreadsheet. For our example, we’ll add two more rows, “restaurant” and “review.”
let rowRestaurant = document.createElement("h4")
let rowRestaurantNode = document.createTextNode(rowInfo("restaurant"])
row.Restaurant.appendChild(rowRestaurantNode)
rowRestaurant.classList.add("restaurant")let rowReview = document.createElement("h4")
let rowReviewNode = document.createTextNode(rowInfo("review"])
row.Review.appendChild(rowReviewNode)
rowReview.classList.add("review")rowInfoDiv.appendChild(rowRestaurant)
rowInfoDiv.appendChild(rowReview)
Now if we commit these changes (GitHub lingo for save our file) and enable GitHub pages, once it finishes compiling we will be able to load our website and see nothing. That however is simply because our spreadsheet is still empty. If we add some reviews to our spreadsheet manually, suddenly it springs to life!
However, we still haven’t built a way for the end user to add a review. For this, the next site we’ll need to visit is Formspree.io, a free website that allows you to quickly generate a contact form that will send to an email. Sign up again for a free formspree account, and grab the code that it generates for you, because it doesn’t matter that much as we’ll be editing it. The only thing important to get is the form key it gives you.
We’ll then take this key and add it to our own custom code, as Formspree doesn’t allow customization on their free plan:
<div>
<h3>Leave A Review!</h3>
<form action="YOUR LINK HERE" method="POST">
<label>Your name:
<input type="text" name="name">
</label>
<label>Restaurant Name:
<input type="text" name="restaurant">
</label>
<label>Restaurant Review:
<textarea name="review"></textarea>
</label>
<button type="submit">Submit Review</button>
</form>
</div>Of course, customize this to match whatever sort of database you are trying to create. This code will be added right underneath the review loader we already created.
So now we have a database, we have a webpage, and we have a way of collecting new reviews! Done, right? Not quite. If we left this the way it is right now, you’d have to add every single review to the database manually, which is way to much work for me. If you want more control over who is able to add to your database however, this would be the way to go.
[WARNING: This next section is fairly complicated, and while at the time of writing, is free, may not be in the future]
Instead, I’m going to use one last tool, called Zapier Mailbox Parser. What this will allow us to do is simply send the emails from the form, directly to Zapier which will automatically add them to the Spreadsheet, thus updating the website.
Now this next portion is a tad bit complicated; What you’re going to need to do is head over to Mailboxes by Zapier and create an account, and walk through the setup on creating a new mailbox. It doesn’t matter what the email handle is, just something you can remember. Then, go over the the website we’ve created and submit a test review through the Formspree form which will go to your personal email. You’ll need to forward that email to the Zapier Mailbox, and then go over to Formspree, and switch the destination email to the Zapier Mailbox (which will force you to go into the Zapier Mailbox and confirm the email address which can be a bit of a pain to do).
Once you have that all sorted out, we can start parsing our emails. Open the email you forwarded from your primary account, and highlight and label the portions you want to save, per this example:
Once you’ve done that, you can close the email parser, and just go over to the Zapier main workpage. From here, we are going to create a new fuction to automatically add our email’s data to our spreadsheet database. Create a new “Zap” and choose your trigger as “New Email” and action as “Create a New Row in Google Spreadsheets.”
Inside of our Trigger, we’ll want our app to be Email Parser by Zapier and trigger event to be New Email
The account portion should autofill, but we simply want the Mailbox account you already created with Zapier. Our trigger will be the name of the mailbox we created, and you should see an email show up under test trigger.
Now, onto the spreadsheets portion. Under choose app and events, we’ll want to create a new row in our Google Spreadsheet:
The account will simply require you to link your Google Drive account so you can access the spreadsheet. Finally, we need to setup our action. Choose your Google Drive account (you can choose between connected school, personal, and work accounts if you’d like), the Database spreadsheet, Sheet 1, and then under the fields you’d like, just choose the “Parse Output [your row name]”. Finally go down to test action.
After you press test action, head back over to the GitHub website we’ve created, and you should see a new row with your test information. Finally, we can leave a new review and see if it appears after we refresh the page.
Now of course, this is not a very pretty looking website, and is fairly simple. However, with the basic idea now in your hands, it can be transformed into anything you want. Endless possibilities await!!
