Have you ever needed a quick and easy way to get data but didn’t want to hassle with getting a full blown database setup? Well I was pleased to recently discover that you can very quickly and easily get access to and display data stored in a google sheet on your website. What makes this really easy and thus appealing is that you do not need to gain access to this data via the Google Sheets API, rather you can get a JSON representation with a simple URL. Today we will look at this in detail and discuss the various considerations that should be made when using such a technique.
Google Sheets Setup
Before anything else you need to have an account with Google so that you can use their Sheets App. Assuming that you have a Google account the next thing to do is to open a new Google Sheet and put some data in it that we will eventually be displaying on our example website. Here is what I’m going to use as prototype data for this article.
It is important that after creating your google sheet, you publish it to the web. If you do not publish it to the web you will not be able to get access to it later from your website. In order to publish it, do the following:
- From the open spreadsheet click the File -> Publish to the web… menu options which will bring up the following window.
- Be sure that the “Entire Document” and “Web page” values are set in the window and click the green “Publish” button. Once you have completed this setup you are now able to retrieve a JSON representation of the sheet which is discussed in the next section. It’s important to note that you do not have to also share the document in addition to publishing it. It should work just fine if you’ve only published the document.
Accessing the Sheets Data
Now that the Google Sheet is populated and published to the web you can retrieve a JSON representation of the data by using a simple URL. What is amazing about this is that it can be done without having to connect through the Google Sheets API, all you need is to formulate the right URL and the data is returned. The key is formulating the correct URL and the pattern to use is as follows:
https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/od6/public/values?alt=jsonNote that you would need to replace SPREADSHEET_ID with the ID associated with your specific spreadsheet. Here is an example of how to determine what value you should use for your SPREADSHEET_ID. Open up the Google Sheet in your browser. Looking at the address in the browser address bar for the sheet, your SPREADSHEET_ID can be found where the bold underlined string is in the following spreadsheet URL example
https://docs.google.com/spreadsheets/d/17i4-KiwZQiYiIHUl6mb0pkV9FnQNFnqr4HM-4vt9MP4/edit#gid=0Once you have formulated your URL you can past it into any web browser and you should see the JSON for your specific sheet displayed.
At this point you should be able to use the URL to retrieve the corresponding JSON using whatever language you prefer. Here is a quick example using a simple HTML document with JQuery and some very basic CSS to get the contents of the Google Sheet to display on the web.
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta http-equiv="X-UA-Compatible" content="ie=edge" />
<title>Sheets Blog</title>
</head>
<body>
<div class="title">Product Inventory</div>
<table class="inventory-list">
<tr class="inventory-header">
<th class="id-column">ID</th>
<th class="price-column">Price</th>
<th class="name-column">Name</th>
<th class="quantity-column">Quantity</th>
<th class="value-column">Value</th>
</tr>
</table>
<div class="total-text">Inventory Value: <span id="total"></span></div>
<script
src="https://code.jquery.com/jquery-3.4.1.min.js"
integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="
crossorigin="anonymous"
></script>
<script>
$(document).ready(function () {
getCurrentInvetory()
})
function getCurrentInvetory() {
var spreadsheetID = "17i4-KiwZQiYiIHUl6mb0pkV9FnQNFnqr4HM-4vt9MP4"
var url =
"https://spreadsheets.google.com/feeds/list/" +
spreadsheetID +
"/od6/public/values?alt=json"
console.log(url)
$.getJSON(url, function (data) {
var entry = data.feed.entry
var length = entry.length
$(".inventory-item").remove()
$(entry).each(function (index, element) {
if (index === length - 1) {
$("#total").text(this.gsx$value.$t)
} else {
var inventoryItem = $('<tr class="inventory-item">').appendTo(
".inventory-list"
)
inventoryItem.append(
$(document.createElement("td"))
.text(this.gsx$id.$t)
.addClass("product-id")
)
inventoryItem.append(
$(document.createElement("td"))
.text(this.gsx$price.$t)
.addClass("product-price")
)
inventoryItem.append(
$(document.createElement("td"))
.text(this.gsx$name.$t)
.addClass("product-name")
)
inventoryItem.append(
$(document.createElement("td"))
.text(this.gsx$quantity.$t)
.addClass("product-quantity")
)
inventoryItem.append(
$(document.createElement("td"))
.text(this.gsx$value.$t)
.addClass("product-value")
)
}
})
})
setTimeout(getCurrentInvetory, 300)
}
</script>
<style>
.title {
text-align: center;
margin: 2rem;
font-size: 2rem;
}
.inventory-list {
margin: auto;
}
th {
text-align: left;
}
td,
th {
padding-right: 3rem;
}
.product-id,
.product-quantity {
text-align: center;
}
.product-price,
.product-value,
.price-column,
.value-column {
text-align: right;
}
.total-text {
margin-top: 2rem;
text-align: center;
font-size: 1.5rem;
}
</style>
</body>
</html>The following screenshot shows the results of the above HTML/JQUERY/CSS rendering the data from the spreadsheet. If you change a value in the sheet it should quickly update the corresponding display of that value on the website without having to refresh. This shows that the true values from the Google Sheet are being displayed on the website.
When should/shouldn’t you use this approach?
There are some obvious applications for this technique. For example any time that you want to pull data from a data source and just simply display it. This is great for prototyping and/or small scale websites. You could integrate this with Google Forms and display the results of the data you collect from that form on a static page. This technique is obviously limited to scenarios where you only want to pull data. If you want to update the values from the front-end you’ll need a more robust solution that allows for you to submit a form with a GET/POST HTTP request to a back-end that would then process those values and store them in a database. But that can take a lot more work to setup which is the beauty of using this technique. I hope that this has been helpful and would love to hear your thoughts in the comments below.