How to Add Javascript to Get Your Google Sheet Data to Loop on Your Website
Table of Contents:
- The Intro — Click Here for Article
- Setup Free Hosting and Domain — Click Here for Article
- Setup Free Database with Google Sheets — Click Here for Article
- Link Google Sheet Database to Website — Click Here for Article
- Add Javascript to Get Database Data to Loop
- Add Search Function to Website — Click Here for Article
- Add Non Php reliant Contact Form — Click Here for Article
Last week I covered how to import data from Google Sheets and integrate it into your website using a Javascript utility called Tabletop. This week I’ll cover how to automatically generate a grouping of data in a loop.
For example, let’s say you have a data group called whales. In this group you list various species of whales. Something like this:
Whales
Humpback
Narwhal
Beluga
You could either target an individual data block and retrieve the whale species from row two, three or four or you can automatically have the entire whales list generated.
Below is some data I setup. Notice in the first row I have the title, date, display date, body and img. On my website I want to pull in the title, date and img information.
This is what the website looks like. Keep in mind that I used some of my web development knowledge to style the look of this data into a somewhat more visual grid. That being said, each grid column displays all the data from the title, date, body and img columns in the Google sheet. You will only see three images because I’ve only specified three in the data.
This is only a rudimentary sample of what can be done; however, it is a representation of what is possible.
Let’s go into the details of how to write the code to generate the data.
The essential aspect to make your website generate the various data from all the title, date and img rows is the for loop. It looks like this:
for (var i = 0; i < array.length; i++) {
array[i]
}
I won’t go over in tremendous detail about the for loop, as there is an incredible wealth of knowledge on the internet about creating for loops; however, I’m basically establishing a variable which is set to 0, then setting the variable to be lesser than the length of the array, which is this case is the length of each data column. Then the block of information will increment until the length of the array is complete.
So this is how the code looks like:
Html code for reference
<body>
<div class=”post”>
</div>
</body>
Javascript
for (var i = 0; i < data.length; i++) {
$(‘.post’).append(
‘<div class=”article” style=”background-image: url( ‘ +
data[i].img + ‘.jpg)”>’ +
‘<div class=”text”>’ +
‘<h1>’ + data[i].title + ‘</h1>’+
data[i].body +
‘</div> ‘ + ‘<div class=”meta-info”>’ + ‘<img src =”’ + data[i].img + ‘.jpg”>’+
‘<div class=”date”>’ + data[i].date + ‘</div>’ +
‘</div>’ +
‘</div>’);
}
The Javascript breakdown:
I’m targeting the post class in my html and will append information from the spreadsheet.
I’m basically doing two things.
- I’m having javascript add divs and naming the divs so I can style them with css.
- Retrieving the title, date and img data from the Google Sheet.
To target the title, date and img data, you follow this pattern:
data[i].name-of-column-header
So each column of data will have a background image which is the image set from the img column.
‘<div class=”article” style=”background-image: url( ‘ + data[i].img + ‘.jpg)”>’
Each title will be placed in an h1 tag for styling purposes then followed by the body. Both of these are in a div called text for styling purposes.
‘<div class=”text”>’ + ‘<h1>’ + data[i].title + ‘</h1>’+ data[i].body + ‘</div> ‘
Lastly I created a div to display meta information like the date and an avatar.
‘<div class=”meta-info”>’ + ‘<img src =” ‘ + data[i].img + ‘.jpg”>’+ ‘<div class=”date”>’ + data[i].date + ‘</div>’ + ‘</div>’
That’s it. This is how the final javascript code looks.
function init() {
Tabletop.init( { key: ‘198HS_8FJbZhASXbJ7hfJV-iux563hd6gg7joNYJ6X9k’,
callback: showInfo,
simpleSheet: true } )
}
function showInfo(data, tabletop) {
console.log(data);
for (var i = 0; i < data.length; i++) {
$(‘.post’).append(
‘<div class=”article” style=”background-image: url( ‘ +
data[i].img + ‘.jpg)”>’ +
‘<div class=”text”>’ +
‘<h1>’ + data[i].title + ‘</h1>’+
data[i].body +
‘</div> ‘ + ‘<div class=”meta-info”>’ + ‘<img src =”’ + data[i].img + ‘.jpg”>’+
‘<div class=”date”>’ + data[i].date + ‘</div>’ +
‘</div>’ +
‘</div>’);
}
}
window.addEventListener(‘DOMContentLoaded’, init)
To understand the Tabletop javascript function, please check out last week’s article here.
This article is part of a series of articles I’m writing to help save you development costs for domain and hosting and save time without learning more complex CMS platforms like Wordpress. However part of every CMS platform is the ability to search and next week I will dive deep into that!
Get more insights like this on my blog, jaejohns.com/blog
