Google Sheets is a very popular online spreadsheet application that anyone with a Google account can use totally free. It is very intuitive and even people with no prior experience to Spreadsheet applications will find it easy to use.
Web Content Management Systems typically use the user friendly interface for the end user to manage a website content. The website manager does not have to depend on the developer for creation of new pages, posts, tags or the like. This added abstraction allows non developers to manage their websites easily. This is the reason why Content Management Systems like WordPress, Joomla and Drupal have become so popular.
Typically, Web Content Management Systems (CMS) are of two types. One using the traditional approach uses a relational database like MySQL for the backend storage. A more recent trend has seen database less CMS like Hugo. These store the data in plain text files. For the end user or the website manager, there is no difference as the user friendly interface is visible to him/her regardless of the type of CMS he/she uses.
When not to use a traditional CMS
For a rather complicated website, the use of a full-fledged content management system makes sense. There is a lot of content to be managed. There are plugins available to extend functionality. For single page websites or simpler ones with a few pages, using a traditional CMS is not a very smart thing to do.
The following points are important to consider –
- The header that a CMS loads is usually more feature rich – meaning it has support for a lot more types of features that one may require. However, the included scripts are loaded by the CMS itself – even if you don’t use it. This adds to unnecessary bandwidth usage.
- A traditional RDBMS will be an additional overhead that allows database storage at the cost of speed. For a single page website, maintaining a fully functional database on the server for serving requests for each page visit is a waste of resources. A headless or static CMS that does not use a database will be faster and use far less resources.
The advantage of using Google Sheets as backend
Google sheets is easily accessible and easy to use, even for those who have no development experience. The following advantages are worth noticing –
- Easy to use, no learning curve needed
- Accessible and platform independent. All one needs is a browser and a Google account.
- Version control is a part of the product offering. If there is an error, you can revert to a history version easily.
- Ability to share the sheets with those you want to with role based rights. This is analogous to sharing access credentials to a full CMS.
How to use Google Sheets as a data source
This is fairly simple. You have multiple options to fetch information from Google Sheets. The Google Sheets API will give you access to all the data you need to view and manipulate.
Step 1: You can get a JSON response with the entire spreadsheet data. Publish the spreadsheet on the Web first.
Step 2: Now check out this URL –
Open the Google Sheets and copy the sheet code and sheet number.
For example, my sheet is here
My sheet code is
1clakCW5TxaIAEhr7qujT5x3j3Sf7uGRiL-a4zjou4-I and my sheet number is 1.
Now feed them into the above URL and you will find a JSON response.
Alternative Step 2: The other option is to use third party APIs that actually filter out the unnecessary information (metadata) from the previous JSON response in point ii and provides the content of the sheet directly in simple JSON response.
‘Tabletop js’ is a good example that ‘gives spreadsheets legs’.
How to integrate Google Sheets as backend
Step 1: First create a static website using whatever tools you have at your disposal. A simple text editor should be fine. I am using Bootstrap for my basic styling requirements, so that it looks professional and familiar.
Step 3: Initiate a script block and store the path of the spreadsheet in a variable.
Follow up with a
init() function with a key, callback and set simpleSheet to true.
Create a new function
showInfo and pass data and tabletop as arguments. This is interesting because tabletop (after
init()) automatically holds the data of the sheet you assigned in the sheet variable in Step 2.
Step 4: Create a basic card based structure that will repeat for the number of rows there are with details.
<div class="col-md-4"> <h4 style="text-align:center;"></h4> <img class="img-thumbnail rounded mx-auto d-block" style="height:200px;" alt="" src=""</img> <p style="text-align: justify; text-justify: inter-word;"></p> <p><b>Status:</b></p> <p><b>Built Date: </b></p> <p><b>Destroyed Date: </b></p> <p><b>Destroyed by: </b></p> <p><a class="btn btn-outline-info href="#">View details</a></p> </div>
Step 5: Now go back to the sheet and note down the column headers. This is how it looks now.
Note: Tabletop requires you to follow a simple guideline for naming column headers. The headers should be the first row and each of the header names should start with a small letter.
Accessing the data is simple. For instance if your header cell is named ‘name’, getting all names from the sheet will require you to iterate i over data[i].name.
Therefore for my headers in the sheet, the respective data fields are –
So the Step 4 can be re-written as –
<div class="col-md-4"> <h4 style="text-align:center;">'+data[i].name+'</h4> <img class="img-thumbnail rounded mx-auto d-block" style="height:200px;" alt="'+data[i].name+'" src="'+data[i].imagesrc+'"</img> <p style="text-align: justify; text-justify: inter-word;">' + data[i].summary + '</p> <p><b>Status:</b> '+ data[i].status + '</p> <p><b>Built Date: </b> '+ data[i].builtdate + '</p> <p><b>Destroyed Date: </b> '+ data[i].destroyeddate + '</p> <p><b>Destroyed by: </b> '+ data[i].destroyedby + '</p> <p><a class="btn btn-outline-info" target="_blank" href="'+data[i].details_link+'">View details</a></p> </div>
You can also debug the issues by simply enabling the Console of your browser’s Developer Tools. The JSON response with the data object and its contents will be output there by default. If there is a parsing error, you can make sure the object returns the contents correctly.
You can share the sheet with edit access to only those who you trust and your site will be up and running without depending on any other heavy server/database resource.