If you've visited our site before, you'd have noticed that banner at the top of our page:

Or you might have noticed the inline banner linking to the #SGUnitedJobs virtual career fair site:

There was a time when we didn't have any of these, so what happened?

The Problem Domain

The header banner has been in-place for some time now, and it was created due to some needs:

1. We wanted a way to notify users about new features we've released
2. We wanted a way to notify users of scheduled downtimes because of systems that we're integrating with

More recently, to support whole-of-government efforts to reduce the impact on our workforce, the agency we're servicing - Workforce Singapore - decided to launch an #SGUnitedJobs virtual career fair. And there was a slight issue.

In the media release, the press were quoted the words "MyCareersFuture". This led to the general public landing on our site instead of the virtual career fair's site, resulting in a need to redirect users to the correct site.

This meant some updates to our landing page notification/updates elements:

1. The header banner needed to be clickable
2. An in-page element needed to be created

In addition to addressing users' problems, our solution also needed to accomodate use cases by our business elements, and they needed to be able to enable text-changes to be made without a new system deployment.

The Solution

We begin with the end.

Google sheets for us was a nice interface between business and technical elements. Business users could enter in information as it came along without interfering with the code-level development using a spreadsheet, with the spreadsheet being able to run custom-code to generate a JSON output that our system could consume.

"Wait, can Google sheets do that?" I hear you ask.

Setting up Google Sheets as a JSON API

Let's set up a simple Google Sheets that you can use as an API that's similar in nature to what we've done.

In MCF's backend, we consume data from Google Sheets, run validations on it before throwing it back out as a .json file which we push to our CDNs so that these can be consumed by our users without placing a load on our servers.

1. Create a new spreadsheet

Go to https://drive.google.com and create a new spreadsheet. Maybe create a table like:

PROTIP: The table above works like copypasta with Google Sheets if you copy it properly

2. Publish to the web

Go to the top navigation bar, and access File > Publish to the web. Confirm that Link is selected and select Sheet1. Change the type to Comma-separated values (.csv). Click the Publish button and say OK.

A link should be provided to you. Test it out by pasting it into the address bar of your page. A .csv file should be downloaded. Opening it up should reveal (if you've entered in the information as-if from above):

ID,Name,Username,Email
1,Joseph,joseph,j@seph.com
2,Matthias,matthias,m@tthias.com
3,Goh,goh,g@h.com

3. Making it JSON

We'll be inserting the JSON as values in the empty JSON Output column you've copied above using the Script Editor. In the header navigation menu, go to Tools* > **Script editor. A new Code.gs should be waiting for you.

Overwrite the generated code and paste in the following code:

function onEdit(e){
  const editedRow = e.range.getRow();
  const activeSheet = SpreadsheetApp.getActiveSheet()
  const sheetName = activeSheet.getName();
  switch (sheetName) {
    case "Sheet1":
      const jsonData = createUserJSON(activeSheet, editedRow);
      const cell = activeSheet.getRange(editedRow, 5);
      cell.setValue(jsonData);
  } 
}

function createUserJSON(sheet, editedRow) { 
  const userJSON = {
    id: sheet.getRange(editedRow, 1).getValue(),
    name: sheet.getRange(editedRow, 2).getValue(),
    username: sheet.getRange(editedRow, 3).getValue(),
    email: sheet.getRange(editedRow, 4).getValue(),
  }
  return JSON.stringify(userJSON);
}

Google Script is essentially JavaScript. Also, if you've changed your sheet name, you might want to replace the "Sheet1" in the switch-case branch to the name of your sheet.

The above code is triggered on an edit to the sheet. It checks whether the edited sheet is named Sheet1 and if it is, generates a JSON string and pastes it into the 5th column of the row being edited. Save it by going to File > Save. The name shouldn't matter.

Go back to your sheets and edit one of the existing values. On removing your focus from that cell, you should see a JSON value appear in the 5th column.

4. Consuming the JSON

Retrieve the link from the Publish to the web stage. We're going to use that to retrieve our JSON outputs. Your original link should look like:

https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=csv

Confirm you've done an edit so that the JSON appears and then do a curl to see what it looks like now. In your terminal:

curl 'https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=csv'

You should receive something similar to the following as your response:

ID,Name,Username,Email,JSON Output
1,Joseph,joseph,j@seph.com,"{""id"":1,""name"":""Joseph"",""username"":""joseph"",""password"":""j@seph.com""}"
2,Matthias,matthias,m@tthias.com,
3,Goh,goh,g@h.com,

We're almost there!

5. Tying It All Up

Now that we can generate the JSON, go ahead and update the other rows so that all of them have a value in the JSON Output column.

In this step, we'll link all the JSON Output values into a new sheet which we can use as the data source in a service.

Add a New Sheet by clicking the plus (+) symbol at the bottom left of the existing spreadsheet. Name it Output. We'll be writing the aggregated JSON Output to row 1 column 1 of this sheet.

Go to Tools > Script editor once again and modify the script there so that it looks like:

function onEdit(e){
  const editedRow = e.range.getRow();
  const activeSheet = SpreadsheetApp.getActiveSheet()
  const sheetName = activeSheet.getName();
  switch (sheetName) {
    case "Sheet1":
      const jsonData = createUserJSON(activeSheet, editedRow);
      const cell = activeSheet.getRange(editedRow, 5);
      cell.setValue(jsonData);
  }

  // > diff starts here
  const jsonOutputs = activeSheet
    .getRange("E2:E")
    .getValues()
    .filter((val) => val[0].length !== 0);
  const aggergatedUsers = [];
  jsonOutputs.forEach(function(value) {
    const user = value[0];
    aggergatedUsers.push(JSON.parse(user));
  });
  const outputSheet = SpreadsheetApp.getActive().getSheetByName('Output');
  const outputCell = outputSheet.getRange(1, 1);
  outputCell.setValue(JSON.stringify(aggergatedUsers, null, 2));
  // / diff ends here
}

function createUserJSON(sheet, editedRow) { 
  const userJSON = {
    id: sheet.getRange(editedRow, 1).getValue(),
    name: sheet.getRange(editedRow, 2).getValue(),
    username: sheet.getRange(editedRow, 3).getValue(),
    email: sheet.getRange(editedRow, 4).getValue(),
  }
  return JSON.stringify(userJSON);
}

What we just added basically retrieves the value of all rows in column E which in the sample sheet we created corresponds to the column JSON Output. It then transforms the retrieved values into an array object before converting it back into a string using JSON.stringify. The null, 2 arguments in the JSON.stringify call basically indicates to format the JSON in a human readable way (see JSON.stringify documentation on MDN if you're interested in what exactly it does)

After you've made the change, save the GoogleScripts project, head back to your sheet and go ahead make a modification to one of the columns to trigger an output to the Output sheet in your spreadsheet.

Go to File > Publish to the web once again, this time selecting the Output sheet and selecting Tab-separated values (.tsv) as the type. In the Published content and settings section, ensure that Output is also being published and that the checkbox with Automatically republish when changes are made is also checked.

Copy the provided link which look like (sensitive values are masked with XXX)

https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=tsv

To modify this such that it will always return just the first row and column, append a &range=A1 so that the final link looks like:

https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=tsv&range=A1

Let's test this out by running a curl with it which would be what your Request module would be doing if it calls this URL:

curl -vv 'https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=tsv&range=A1'

To test that it's valid JSON, we can pipe it to a tool called jq that can help us validate it:

curl -vv 'https://docs.google.com/spreadsheets/d/e/2PACX-XXX/pub?gid=YYY&single=true&output=tsv&range=A1' | jq '.'

We're done. Aren't we?

6. What if something goes wrong?

As with all code, things can and will go wrong during development, but the awesome news is that Google provides us with a page where we can view errors in our script and this can be found on the Code.gs page through the file navigation menu via View > Executions which should open the script's Google Apps Script dashboard.

Note that only calls to console.error go through to this dashboard.

Looks like we're done for real!

If you like what you just read, don't forget to leave some reactions/comments so we know this has been interesting for you- and do consider following us for more insights into tools we use and our development processes.

Cheers and till next time!