Utilizing Google Sheet as a Website Database

Develop with E2

In this step-by-step tutorial we will be utilizing Google Sheets as a basic, back-end database for our website. In order to accomplish this, we will be learing how to:

  • Create a custom API using Google Apps Script which will allow us to fetch data from our Google Sheet
  • Use the javascript fetch method to retrieve & handle data from our API request
  • Display the data from our API request on our webpage using the forEach function

Requirements

  • A Google account
  • A text editor/website
  • Basic knowledge of javascript

Setup the Google Sheet

Go to Google Sheets and create a new spreadsheet. This is where your data will be stored.

Once the new spreadsheet is created, setup the first few rows of your data table (You can update these headers later as well):

id date title label ...
1 Date Title Label ...
2 Date Title Label ...
3 Date Title Label ...

Important: Table headers are utilized within the API request to retrieve specific data.

Create the Google Apps Script

In the window navigation menu of Google Sheet, find & click on Extensions > Apps Script. A new tab will open with Google Apps Script. Give the project a name (i.e. Database or Stored Data).

Replace the pre-set myFunction() {... section with the following code snippet:

function getSheetData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var jsonData = [];
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var obj = {};
    for (var j = 0; j < row.length; j++) {
      obj[sheet.getRange(1, j + 1).getValue()] = row[j];
    }
    obj["id"] = i;
    jsonData.push(obj);
  }
  return jsonData;
}

function doGet(e) {
  var jsonData = getSheetData();
  return ContentService.createTextOutput(JSON.stringify(jsonData)).setMimeType(ContentService.MimeType.JSON);
}

Remember to save the Apps Script project before progressing to the next steps.

Breaking Down the Code

function getSheetData() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    var jsonData = [];
    for (var i = 1; i < data.length; i++) {
      var row = data[i];
      var obj = {};
      for (var j = 0; j < row.length; j++) {
        obj[sheet.getRange(1, j + 1).getValue()] = row[j];
      }
      obj["id"] = i;
      jsonData.push(obj);
    }
    return jsonData;
  }

The getSheetData() function retrieves all the data from the active sheet in the spreadsheet, then converts it into an array of objects with each object representing a row of data from the sheet. It uses two (2) nested loops to comb through the data: the outer loop iterates through each row, and the inner loop iterates through each cell of the row.

The outer loop uses the index i to reference the current row and the inner loop uses the index j to reference the current cell of the current row. The sheet.getRange(1, j + 1).getValue() function is used to get the header value of the current column, which is used as the key of the object.

function doGet(e) {
    var jsonData = getSheetData();
    return ContentService.createTextOutput(JSON.stringify(jsonData)).setMimeType(ContentService.MimeType.JSON);
  }

The doGet(e) function receives the GET request, then calls the getSheetData() function. The function then uses the ContentService.createTextOutput() method to create a text output with the JSON data and converts it using JSON.stringify(). The setMimeType() method is used to set the MIME type of the response to "application/json", so that the browser knows to interpret the response as JSON.

Deploy Your API

Now your script is ready to publish. Select the Deploy button, then choose the option New Deployment from the drop-down menu.

Click the Select Type icon and choose Web app from the options.

In the form that appears, select the following options:

  • Description: This can be anything you want. Just make it descriptive.
  • Web app → Execute As: Me
  • Web app → Who has access: Anyone

Then, select Deploy.

Important: Copy and save the web app URL before moving on to the next step. This is your API URL. It will appear like this:

https://script.google.com/macros/s/SCRIPT_ID/exec

Performing Your API Request

Think of an API request like a postman. They take your letter to the intended recipient, and then they come back with a response from that recipient.

In simplest terms, an API request is the middleman that helps the client and the server communicate with each other.

If you are familiar with API requesting protocols, you can utilize your preferred method or you can utilize the below javascript fetch method to perform your API request using the API URL that you acquired from Google Apps Script.

fetch('YOUR_API_URL', {
    // Add additional configurations...
  })
  .then(response => response.json())
  .then(data => {
    // Add code to handle the API response...
  })
  .catch(error => {
    // Add code to handle the API error...
  });

Here is a simple example utilizing the javascript fetch method and forEach function that retrieves your data via the Google Apps Script API, then will display all data entries as list items on your webpage:

<ul id='list-items'></ul>
    
<script>
  fetch('YOUR_API_URL')
  .then(response => response.json())
  .then(data => {
    data.forEach(entry => {
      const listItem = document.createElement('li');
      listItem.className = '${entry.label}';
      listItem.innerHTML = '${entry.id}, ${entry.name}, ${entry.date}';
      document.getElementById('list-items').appendChild(listItem);
    });
  })
  .catch(error => console.error(error));
</script>

References

Hello World!

Comments

Post a Comment