Make a Custom Website Using Data from Google Sheets

Astha Sharma on 2021-02-11

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 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.

Create a 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 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 {“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.

Reach out to us at info@lovespreadsheets.com if you have any questions!

Check out this article on how to deploy your site so it can be shared and have a URL.

Share