How to Build a Custom Web App With Spreadsheets — Part 1: Build a Form & Display the Data
Planning the music for an event or party can be stressful. Especially if you’re the DJ & everyone attending wants their song to be played.
This is part one of a three part series about how to build a custom web app with spreadsheets.
- Part One: Learn how to build a form and display the data
- Part Two: Create a login page where the user can update & delete
- Part Three: Deploying the web app
For part one, we will go through how to make a form for guests to submit their song request and how that data can instantly be displayed. Ideally you can use this for any project you would like!
We have a YouTube tutorial to follow along if you are a visual learner.
How the end product will look:
The Form
Full Code:
<body> Code:
<body><div id="form">
<form id="myForm">
<h1><label>SONG FORM</label></h1>
<div class="writeInInputs">
<h3><label>Song Name</label></h3>
<input name="Song" placeholder="Enter the Name of the Song"/>
<h3><label>Artist</label></h3>
<input name="Artist" placeholder="Enter the Name of the Artist"/>
<h3><label>Link<label/></h3>
<input name="Link" placeholder="Enter the Link of the Song">
</div>
</form><div id="buttonBreak">
<button onclick="SubForm()"> Submit </button>
</div>
<a href="display.html"> Check out the queue </a> </div>
</body>
Part One: Inputs
In this form, all of our inputs are going to be text you write into for Song Name, Artist, & Link. They are all in the div <writeInInputs> because they will be styled the same way.
To get a simple text field for something like a song name, use the following code and change the name attribute (“Song”) to whatever you want to name your input.
As a general rule of thumb, you should let each input have its own unique name attribute.
Placeholder is the text you in your input column before typing in the value.
<input name="Song" placeholder="Enter the Name of the Song"/>We follow the name format for Artist & Link which look like this.
<input name="Artist" placeholder="Enter the Name of the Artist"/>
<input name="Link" placeholder="Enter the Link of the Song"/>Part Two: Button
After you’re done with all the inputs, we’ll need a button to submit.
The submit button will be used for submitting all the inputs. This will run the function that we’ve made for saving our data, which I’ll go more into next.
<button onclick="SubForm()">Submit</button>Part Three: Styling
For these 3 values, Song Name, Artist, & Link I put them in their own div and gave it a class “writeInInputs” since they will look similar.
.writeInInputs{
padding-top:10px;
}
.writeInInputs input{
width:75%;
border-top: 1px;
border-left: none;
border-right:none;
padding:15px;
text-align: left;
}
.writeInInputs h3{
text-align:left;
padding-left:12%;
}We also added style to the entire body, input tags, and form as seen below
@import url('https://fonts.googleapis.com/css2?family=Roboto&display=swap');
body{
text-align: center;
background-color: #FA8BFF;
background-image: linear-gradient(45deg, #FA8BFF 0%, #2BD2FF 52%, #2BFF88 90%);
font-family: 'Roboto', sans-serif;
}
input{
border-top: 1px;
border-left: none;
border-right:none;
}
#form{
background-color: white;
margin: 50px 350px 100px 350px;
padding: 40px 0 100px 0;
border-radius: 10px;
width: auto;
height: auto;
}For the button, we put it in a div that was given the id #buttonBreak to be able to give it some padding around the button itself.
button{
background-color: #FA8BFF;
background-image: linear-gradient(90deg, #FA8BFF 0%, #2BD2FF 52%, #2BFF88 90%);
border:none;
color: white;
padding:20px 0 20px 0;
border-radius: 20px;
width:75%;
height:75%;
text-align: center;
font-size:25px;
}
button:hover{
background-color: #FA8BFF;
background-image: linear-gradient(45deg, #FA8BFF 0%, #2BD2FF 52%, #2BFF88 90%);
}
#buttonBreak{
padding:75px;
}Part Four: Prepare to Save Using API Spreadsheets
In this code, we will be saving our data to Google Sheets using an online API tool called API Spreadsheets.
First, create a new Google Sheet and make the header names equal to the name attributes in your code.
Then, go to API Spreadsheets and create an account. Next, upload your Google Sheet and get your API URL.
Save this URL for the next part of our code.
Part Five: Configuring JavaScript
Finally, we have the part of our code at the top that uses Javascript to submit data from the form. We will be using AJAX for this.
First, we need to include the FULL jQuery library in our HTML so that we can use AJAX. That’s what the following code will do.
<script
src="https://code.jquery.com/jquery-3.4.1.js"
integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU="
crossorigin="anonymous"> </script>
<script>Then, we have the Subform() function, which is how we’ll submit the data to our Google Sheet. This is where we’ll change the url attribute to the API URL we just created.
function SubForm (){
$.ajax({
url:'https://api.apispreadsheets.com/data/12633/',
type:'post',
data:$("#myForm").serializeArray(),
success: function(){
alert("Form Submitted Successfully!")
},
error: function(){
alert("Error: Form Not Submitted")
}
});
}You can also customize the success and error messages that pop up after the user submits the code, which are currently “Form Submitted Successfully!” or “Error: Form Not Submitted”.
And there you go! You now have a basic web form set up!
The Queue
We are going to use the data from our HTML form that we just made and display it on a Queue.
Part One: Go back to API Spreadsheets and click on the “Read” tab and Javascript Code Sample since we will only be reading our spreadsheet
We will be copying and pasting this Javascript code sample in our code.
You don’t have to do that now. We will show you when to do it below. Just keep the tab open.
Now let’s create our HTML File.
Full Code:
Part Two: Setting up the <head> & <body> in our HTML file
In the <body>, we are going to include 3 div tags that will be crucial in:
- Displaying our data from Google Sheets, <div id= “allSongs”>
- Showing a loader while we are getting the data, <div id= “loader”>
- Showing an error message if something bad happens, <div id= “errorMessage”>
<div id="hero">
<h1>DJ Song Queue</h1>
</div>
<div id="display">
<div id="loader">
<h4> Loading</h4>
</div>
<div id="allSongs">
</div>
<div id="errorMessage">
<h2> Failed to get data. Please refresh </h2>
</div>
</div>
<a href="form.html" target="_blank" style="text-decoration: none; color:white;"> Fill out another song request </a>
<footer> <h4> Powered by <a href="https://www.apispreadsheets.com" target="_blank" style="text-decoration: none; color:white; text-align: center;"> API Spreadsheets </a> </h4>
</footer>I also included a Header/Hero in the <div id= “hero”> to display at the top of the page and a <footer> to display at the bottom.
Part Three: Copy the Javascript code from API Spreadsheets
Place it at right before the end of your </body> tag in between <script> </script> tags.
<script>
fetch("https://api.apispreadsheets.com/data/12730/").then(res=>{
if (res.status === 200){
// SUCCESS
res.json().then(data=>{
const yourData = data
}).catch(err => console.log(err))
}
else{
// ERROR</script>
Part Four: Styling for Crucial Divs
Write CSS Code for our 3 crucial <div> elements
In <style> we will be styling our 3 crucial elements.
We will set <div id= “allSongs”> and <div id= “errorMessage”> to NOT display initially.
This is because when the website initially loads, we will be fetching our data from Google Sheets and won’t have anything to display.
We also won’t know till the fetch is complete whether to show the error or not.
#allSongs {
display: none;
}
#errorMessage {
display: none;
color: red;
}We will also add the following CSS for the <div id= “loader”>. This will give it the appearance and make it spin.
We want the loader to display when the website initially loads till the fetch is complete. So we won’t be setting anything for its display property.
#loader {
border: 16px solid #f3f3f3; /* Light grey */
border-top: 16px solid #3498db; /* Blue */
border-radius: 50%;
width: 50px;
height: 50px;
animation: spin 2s linear infinite;
margin: auto;
margin-top: 40px;
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}Part Five: Get our 3 crucial elements & Set up an Error function
let allSongsElm = document.getElementById("allSongs")
let loaderElm = document.getElementById("loader")
let errorMessageElm = document.getElementById("errorMessage") function setErrorDisplay(){
loaderElm.style.display = "none"
allSongsElm.style.display = "none"
errorMessageElm.style.display = "block"
}
The first part of our Javascript is going to be getting our 3 crucial elements using Javascript’s document.getElementById function and passing it the id of our element.
We also set up a function for if there is an error. This function sets the display of the loader and allSongs to be none (aka not display). And turns on the display for the errorMessage.
We will be using the function whenever there is an error in fetching our data.
Part Six: Fetch the Data
We start off by fetching the data. If you copied and pasted your API Spreadsheets code correctly, the URL of your file should be automatically on there.
This URL needs to match your file and you can always get it from your dashboards
If your fetch is successful, then the data from your Google Sheets will be in an array in the returned object with a key of “data”. See my returned object below.
{"data": [
{
"Song": "Despacito",
"Artist": "Luis Fonsi",
"Link": "https://www.youtube.com/watch?v=kJQP7kiw5Fk"
},
{
"Song": "The Way",
"Artist": "Ariana Grande",
"Link": "https://www.youtube.com/watch?v=_sV0S8qWSy0"
}
}Each element in the array will correspond to the row in the Google Sheet. The array element will be an object with the key being your Column Name and its value for the row.
We get this array by accessing the “data” key from the returned object.
const yourData = data["data"]for rowInfo in yourData
Now we loop over yourData and get the object in each element. We will call this rowInfo. This will be our {“Song”: …, “Artist”: …, “Link”:…} object for each row.
for (let i=0; i<yourData.length>; i++) {
let rowInfo = yourData[i]First we will create a container <div> that will hold our elements for name, written and audio. We will call this rowInfoDiv. We will give it a class called song-row so we can style it later.
let rowInfoDiv = document.createElement("div")
rowInfoDiv.classList.add("song-row"Then we will create elements for each of our 3 values.
Let’s create the element for Song. We will make this an <h2> and call it rowSong
let rowSong = document.createElement("h2")Then we will create a text node that contains the Song text from our rowInfo object. We will call this rowSongNode.
let rowSongNode = document.createTextNode(rowInfo["Song"])We add this text node to our h2 element and give it a class called “Song” so we can style it later.
rowName.appendChild(rowSongNode)
rowName.classList.add("Song")We follow the same similar process to create a <h4> element called rowArtist for the Artist text. Instead of “h2” in our createElement method, we specify “h4” and give it a different class: “Artist”.
let rowArtist = document.createElement("h4")
let rowArtistNode = document.createTextNode(rowInfo["Artist"])
rowWritten.appendChild(rowArtistNode)
rowWritten.classList.add("Artist")Finally, we create our <a> element and call it rowLink. We specify its src to be rowInfo[“Link”] which is the URL someone inputs on the form.
We also set the appropriate values for its attributes so it can display properly. And give it a class called “Link” to style it later.
We are also going to setAttribute to “href” and one for “target:_blank” so our link will be clickable and open up in another tab.
let rowLink = document.createElement("a") rowLink.setAttribute("href", rowInfo["Link"]) rowLink.setAttribute("target","_blank") let rowLinkNode = document.createTextNode(rowInfo["Link"]) rowLink.appendChild(rowLinkNode)
rowLink.classList.add("Link")
Now we have our three components for a given row in the Google Sheet: rowSong, rowArtist and rowLink.
We add these to our container <div> which we called rowInfoDiv.
rowInfoDiv.appendChild(rowSong)
rowInfoDiv.appendChild(rowArtist)
rowInfoDiv.appendChild(rowLink)And finally before each iteration of the loop is over, we add rowInfoDiv to our crucial div <div id= “allSongs”> which we obtained at the start of the code and called allSongsElm.
allSongsElm.appendChild(rowInfoDiv)Part Seven: For Loop Over & Errors
After the for loop ends, our allSongs div will contain as many rowInfoDiv elements as the rows in our Google Sheet.
Now we want to display this div and hide our loader and errorMessage.
loaderElm.style.display = "none"
allSongsElm.style.display = "block"
errorMessageElm.style.display = "none"There are multiple errors that can happen. We can deal with them in the catch blocks.
We also deal with an error if the API return code is not 200. This means that there was a problem getting the data.
We created a function before that shows the errorMessage and hides the loader and allSongs.
We call this function setErrorDisplay() whenever we encounter any error in our code.
Part Eight: Styling the entire display
@import url('https://fonts.googleapis.com/css2?family=Roboto&display=swap'); body{
background-color: purple;
height: 100%;
font-family: 'Roboto', sans-serif;
} .song-row{
background-color: white;
border-radius: 10px;
padding: 10px;
margin: 20px;
width: 75%;
}.Song{
display: inline-block;
padding-right: 10px;
} .Artist{
display: inline-block;
padding-right: 10px;
} #hero{
color:white;
} footer{
color:white;
}
In the style tag I gave my background a color of purple, added a new font, and gave borders & paddings to each of our elements.
Test
Test your form and open the queue at the same time to see the data display instantly.
In Part 2 we will be creating a login page for the DJ so they can delete & update the queue on their end.
Closing Remarks
If you want a custom app or project built using spreadsheets, you can check out our consulting service here: https://www.lovespreadsheets.com!
