How we use Google Sheets API to Manage Our Notification Banners
Tags: api, googlesheets, business, architecture
Published: 2020-04-24 10:52:39

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

![The header banner at the top of MyCareersFuture.SG](https://dev-to-uploads.s3.amazonaws.com/i/qcx5xadx81grglmeqyq3.jpg)

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

![The inline banner in the middle of our landing page at MyCareersFuture.SG](https://dev-to-uploads.s3.amazonaws.com/i/1te0zfbflm3tt2tlx54o.jpg)

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.

![An image of the sheet in Google Sheets that our business elements use to configure the banners](https://dev-to-uploads.s3.amazonaws.com/i/6d0eto3lh2ex41i32bpn.jpg)

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/21lio8b6veemafmiw56f.jpg)

*"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](https://drive.google.com) and create a new spreadsheet. Maybe create a table like:

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

> 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.

![(Are you sure you want to publish this selection?) Yasss, yass, yas](https://dev-to-uploads.s3.amazonaws.com/i/gqpjyegeawvuc67tjc9q.jpg)

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:

```js
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:

```sh
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:

```csv
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:

```js
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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify) 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.

![The final JSON output in the Output sheet](https://dev-to-uploads.s3.amazonaws.com/i/hi5u3ohyq35fstwk1gj4.jpg)

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:

```sh
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:

```sh
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!