Google Sheets and JSON: Easy Backend

Let’s build!! Haha, it’s very simple I promise.

Trust me, I am no backend master. When it comes to backend coding, I get all nervous and my hands get sweaty. But, there is a very simple backend setup that is, even for me, easy peasy japanesy!

I introduce to you the Google Sheets.

This article is split into 2 sections:

• Why Google Sheets?
• Let’s build!

Why Google Sheets?

Actually, why Google Sheets is pretty obvious. It’s free. It provides data editing GUI. It’s easy to use and edit. Using Google Sheets as JSON, in particular, is straightforward enough for people without backend experience to build. I guess it is more helpful to talk about when to not use Google Sheets as the backend.

Don’t use the Google Sheets as backend

• for anything serious
• for anything handling private data
• for anything handling data of large quantity

Imagine, you are in charge of editing and updating the Google Sheets data for a mobile application startup. And one day, your hand accidentally slips, erasing half of the data. Without knowing, you saved and closed the Google Sheets page. Then your colleague finds out an hour later that the entire mobile application is broken.

To put it in nutshell, the Google Sheets backend is vulnerable to human error. So don’t really build anything serious using the Google Sheets :)

Google Sheets as backend is great when you are keeping public data. For instance, my personal website about the Snooze Trio uses the Google Sheets as backend to visualize the official Instagram account statistics. The data are numbers that I was going to show the public anyway, so it is fine that the public can see the link to the JSON file even if I don’t directly show the data on the website. But if you are handling personal information or discreet data, you probably won’t want that.

The Google Sheets allows up to 2 million cells in a spreadsheet. It seems quite a lot. But if you are thinking about large data or data that grows fast, there are better options.

Let’s Build!

1. Google Sheets Setup

Go to Google Sheets now and create one. Then, fill it in with the data that you want to represent in your web application. Make sure to start filling in the data from the left most top corner (it makes retrieving data from the JSON file so much easier later)! Don’t hesitate to create several pages. In the case of the Snooze Trio page, I created three pages. One for weekly impressions and reaches, one for likes per post, and one for daily followers.

So far, easy peasy?

2. JSON Retrieval

Alright! Now let’s figure out how to get that Google Sheets data into the front-end. In order to do so, we need to summon The JSON. Below is the general url.

https://spreadsheets.google.com/feeds/cells/YOURGOOGLESHEETCODE/SHEETPAGENUMBER/public/full?alt=json

Update: Thanks to Bryce Sampson, here is an updated version of how retrieve the JSON. Please try this if the link above does not work!

https://spreadsheets.google.com/feeds/cells/YOURGOOGLESHEETCODE/SHEETPAGENUMBER/od6/public/values?alt=json

You can find your Google Sheet Code here! It’s the part underlined in red.

If you have multiple pages in your Google Sheets, make sure to add the page number. For instance, the “Weekly” page’s page number is 1. The “Posts” page’s page number is 2 and so on. Below is the url I used to retrieve data from the Weekly page.

https://spreadsheets.google.com/feeds/cells/1g4FBktkm7al3ZkDI8LuFXuztTqK4nY-eUYMLep6BRuw/1/public/full?alt=json

Now, let’s get the data through the url and see how it looks like on the console. Below is the script I included in the HTML of my personal website.

<script>
$(function(){
var sheetUrl = 'https://spreadsheets.google.com/feeds/cells/1g4FBktkm7al3ZkDI8LuFXuztTqK4nY-eUYMLep6BRuw/1/public/full?alt=json';
$.getJSON(sheetUrl, function(data){
  var entry = data.feed.entry;
  console.log(entry);
})
});
</script>

To see the console, ⌘+Shift+C.

What are those squigglies on the right? Is that my JSON? :D

Let’s open the array up and dissect this JSON!

3. Parsing the JSON

The JSON retrieves data from each cell starting from the top-left most and then pushes the data one by one into an array. Each cell has a key and a value. For instance, the very top-left most cell in my Weekly page contains text “Reach”. It is stored in the JSON with key “0”. The corresponding value contains more key-value pairs such as “category”, “content”, and “title”. Here, we only need to focus on the “content” key, as it is where the text “Reach” is stored with the key “$t”. Then the next cell “Impressions” is stored in JSON with key value “1”, next cell “Avg” with value “2”, and so on. Think of the cells’ key values as index numbers of the JSON array.

Now, if I were to parse the JSON, I need to figure out the patterns of the index numbers and contents. For instance, all the “Reach” data’s index numbers in the JSON array must be multiples of 4. The week ranges’ index numbers start from 3 and increases by 4. Then, let’s classify the data by categories and store them into distinct arrays! Below is the code I wrote in order to do so:

<script>
$(function(){
var sheetUrl = 'https://spreadsheets.google.com/feeds/cells/1g4FBktkm7al3ZkDI8LuFXuztTqK4nY-eUYMLep6BRuw/1/public/full?alt=json';
$.getJSON(sheetUrl, function(data){
  var entry = data.feed.entry;
  var weeks = []; // the leftmost column of the Google Sheets
  var reach = []; // second-left column
  var imp = []; // third-left column
  var avg = []; // last column
for (var i = 3; i < entry.length; i += 4){
    // entry[i].content.$t retrieves the content of each cell
    weeks.push(entry[i].content.$t);
    reach.push(entry[i+1].content.$t);
    imp.push(entry[i+2].content.$t);
    avg.push(entry[i+3].content.$t);
  }
  
  console.log(weeks);
  console.log(reach);
  console.log(imp);
  console.log(avg);
})
});
</script>

Seems like the JSON parsing went well. Now each array “weeks”, “reach”, “imp”, “avg” contains the organized data in corresponding order, ready to be used for some data representations!!!

4. Data representation using Chart.js

I’m a big fan of Chart.js. It supports 8 different types of graphs that are responsive (bar, line, area, pie charts etc.). And honestly speaking, I’m a big fan of its aesthetics. Below is the code I used to create a line graph based on the data in “reach” and “imp”.

var ctx = document.getElementById("weeklyChart").getContext('2d');
var weeklyChart = new Chart(ctx, {
          type: 'line',
          data: {
              labels: weeks,
              datasets: [{
                  label: 'Reach',
                  fill: false,
                  data: reach,
                  borderColor: '#F3A68E',
                  borderWidth: 3,
                  backgroundColor: '#F3A68E',
                  borderDash: [10,5],
              },{
                label: 'Impressions',
                fill: false,
                data: imp,
                borderColor: '#698C01',
                borderWidth: 3,
                backgroundColor: '#698C01',
                borderDash: [10,5],
              }
            ]
          },
          options: {
              scales: {
                  yAxes: [{
                      ticks: {
                          beginAtZero:true
                      }
                  }]
              },
              legend: {
                position: 'bottom',
                labels:{
                  boxWidth: 15,
                  fontSize: 15,
                },
              },
          }
      });

Note how classifying the dataset beforehand made creating the charts so much easier. Then, embed the chart created in the HTML body as below.

<canvas id=”weeklyChart” width=”600"></canvas>

And that’s it! It’s really it! Try this out and let me know how it went :) If there are any bugs or problems with this little tutorial please do not hesitate to comment! Have fun!!!