Make a Custom Website Using Data from Google Sheets
Learn how to make a quick custom website using HTML, CSS, and JavaScript using data from a Google Sheet.
Perfect for beginners to web development or experienced devs looking to make a website without a backend!
The End Product
Final Website
My website is about How to Pronounce Indian names — a cause close to my heart as an Indian-American.
Below is a sample of how it looks. You can view it live here, as well.
Code
Here is the full code for the two files that will create the website: index.html & index.css.
Don’t worry! We will be going through and explaining all the code to you.
index.html
index.css
Now let’s get into the code!
Start by Creating Your Spreadsheet
Create a spreadsheet on Google Sheets that contains the data you want to show up on your website.
As I mentioned above, my website is about how to pronounce Indian names and I want to display 3 things: the name, how it is spelled out phonetically and the actual pronunciation.
So in my spreadsheet, I have the following column names:
- name — the name to pronounce
- written — name spelled out phonetically
- audio — url of the audio file that contains the pronunciation of the name
Connect your Google Sheet on APISpreadsheets.com
Now, we need a way to get our data from Google Sheets onto our website.
To do this, we are going to be using our tool API Spreadsheets to convert the Google Sheet to a REST API.
It’s okay if you don’t understand REST APIs fully. All you need to know is they let you read or write data using a standard protocol available through Javascript
The nice thing about using Google Sheets with API Spreadsheets is that I can keep adding values onto my Google Sheet and the website will update automatically with the new data!
Make an account if you don’t have one already (it’s free for up to 3 files!) and choose the file you want.
Once you have uploaded the file, checkout the quick tour to get a deeper understanding of all the features.
For this tutorial, we will only be reading our spreadsheet so click on the “Read” tab and Javascript Code Sample
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 & CSS Files.
Open Up a Text Editor of Your Choice
I will be using Sublime Text but you can use whatever you are comfortable with, such as Atom or Notepad.
Create HTML file
Create and set up an HTML page, index.html, with the <head> & <body>.
In the <body>, we are going to include 3 div tags that will be crucial in:
- Displaying our data from Google Sheets, <div id= “allNames”>
- Showing a loader while we are getting the data, <div id= “loader”>
- Showing an error message if something bad happens, <div id= “errorMessage”>
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
Copy the Javascript code from API Spreadsheets
Place it at right before the end of your </body> tag in between <script> </script> tags.
This code lets us fetch the data from our Google Sheets using an API.
Create CSS File
Create a CSS file and name it index.css. Save it in the same folder as your HTML file.
Import this CSS file into your index.html file by including the following in the <head>
<link rel="stylesheet" href="index.css" />Write CSS Code for our 3 crucial <div> elements
In the index.css file we will be styling our 3 crucial elements.
We will set <div id= “allNames”> 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.
#allNames {
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); }
}
The FULL Javascript Code
We are going to be adding A LOT more to the fetch code. The final code in the <script> tags will be as follows.
Again, we will be breaking it down block by block.
Get our 3 crucial elements & Set up an Error function
We start off by 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 allNames 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.
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": [
{
"name": "astha",
"written": "ah-sa-tha",
"audio": "https://audio-url.com"
},
{
"name": "alok",
"written": "ah-low-k",
"audio": "https://audio-url.com"
},
{
"name": "adhaar",
"written": "ah-dah-ar",
"audio": "https://audio-url.com"
},
{
"name": "neelam",
"written": "knee-lum",
"audio": "https://audio-url.com"
}
}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 it’s 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 {“name”: …, “written”: …, “audio”:…} 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 name-row so we can style it later
let rowInfoDiv = document.createElement("div")
rowInfoDiv.classList.add("name-row")Then we will create elements for each of our 3 values
Let’s create the element for name. We will make this an <h4> and call it rowName.
let rowName = document.createElement("h4")Then we will create a text node that contains the name text from our rowInfo object. We will call this rowNameNode
let rowNameNode = document.createTextNode(rowInfo["name"])We add this text node to our h4 element and give it a class called “name” so we can style it later
rowName.appendChild(rowNameNode) rowName.classList.add("name")We follow a similar process to create a <p> element called rowWritten for the written text. Instead of “h4” in our createElement method, we specify “p” and give it a different class: “written”
let rowWritten = document.createElement("p") let rowWrittenNode = document.createTextNode(rowInfo["written"]) rowWritten.appendChild(rowWrittenNode) rowWritten.classList.add("written")Finally, we create our <audio> element and call it rowAudio. We specify its src to be rowInfo[“audio”] which is the URL of the audio file.
We also set the appropriate values for its attributes so it can display properly. And give it a class called “audio” to style it later
let rowAudio = document.createElement("audio")
rowAudio.src = rowInfo["audio"]
rowAudio.id = "audio-player" rowAudio.controls = "controls" rowAudio.type = "audio/mpeg"rowAudio.classList.add("audio")
Now we have our three components for a given row in the Google Sheet: rowName, rowWritten and rowAudio.
We add these to our container <div> which we called rowInfoDiv
rowInfoDiv.appendChild(rowName) rowInfoDiv.appendChild(rowWritten) rowInfoDiv.appendChild(rowAudio)And finally before each iteration of the loop is over, we add rowInfoDiv to our crucial div <div id= “allNames”> which we obtained at the start of the code and called allNamesElm.
allNamesElm.appendChild(rowInfoDiv)For Loop Over
After the for loop ends, our allNames 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" allNamesElm.style.display = "block" errorMessageElm.style.display = "none"Dealing with All the Errors
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 allNames.
We call this function setErrorDisplay() whenever we encounter any error in our code.
Run this file
- Copy all the other code from the index.css file at the beginning. This contains the styling for all our elements
- Save both your index.html and index.css files.
- Open your index.html file in any browser of your choice and everything should work and look like the initial picture
The API link in this tutorial is live so you will be able to see and hear the data!
Closing Remarks
There are a lot of concepts here that we did not get into too much detail. W3Schools is a great resource to delve further into topics that may confuse you.
If you want a custom app or project built using spreadsheets, you can check out our consulting service here: https://www.lovespreadsheets.com!
Check out this article on how to deploy your site so it can be shared and have a URL.
