Google Sheets, SpreadAPI, and an Extension

Google Sheets

At the risk of using a spreadsheet to do something a database should do, we hold all of our available professional development in one master sheet. This takes data from a Google Form and makes sure we're using a consistent format. For this project, I'm interested in the start date, the title, and the link to the corresponding calendar event.

Initially, we used Google's hidden JSON endpoint that's included with a published sheet. The problem with this approach is that each cell is returned in the response rather that each row. So, when I call the data, we get nearly 700 responses rather than the 40ish rows we have.

Yeah, that's not going to work.

This is where SpreadAPI steps in and helps out in a big way.

SpreadAPI

This is a little Apps Script library which allows your sheet to act as a JSON endpoint. It supports basic CRUD operations (GET, POST, PUT, DELETE) out of the box and even includes some user authentication baked in. To use SpreadAPI, you simply grab a minified version of the script form the author's repo and add it as a script file in your project.

Since it's a published API, we don't want just anyone posting or reading data from the sheet. Our master sheet has ~17 columns of data and we cannot limit which of those are available in the API without some extra processing that's outside the project scope at this point. Instead, we set up a User in the library settings which can only read access from the sessions list.

Each User has a name, key, and access params which can be defined. This means you can distribute different keys for different programs accesing the same data.

Here's an admin, who would have all permissions on all sheets:

User("admin", "SomeStr0nGP@55word", ALL)

compared with a user who can only GET from one sheet:

User("readOnly", "SomeR3@d0nly", { Sheet1: GET })

Now that we have users set up, we can start using the sheet as an endpoint.

The Extension

The extension is written in plain Javascript and grabs data from the sheet before populating the popup shown. I moved it from jQuery and refactored it to use async fetch calls.

Apps Script API endpoints are handled by a doPost function which only handles POST requests. The fetch calls are a little weird as a result. To get data, we fire off a POST request within fetch that has a payload which defines the query method to SpreadAPI.

const url = "yourWebAppUrl"
// This is interpreted by SpreadAPI
let payload = JSON.stringify({
    method: "GET",
    key: "yourPassword",
    sheet: "Sheet1",
    // other optional params
})

let fetchOpts = {
    method: "POST",
    headers: {
        "Content-Type": "application/x-www-form-urlencoded" // prevent CORS issues
    },
    payload : payload
}

// fetch returns a Promise asynchronously, so either use then() or async/await.

fetch(url, fetchOpts).then(
        resp => resp.json()
    ).then(
        data => console.log(JSON.parse(data));
        // do more stuff
    );

Once we have the data as a JSON object, we filter it to only display the next seven days of workshops for teachers.

A benefit of doing this in the background script is that it can also run in the background before a user clicks on the extension icon. It will poll the sheet for changes and then display a "NEW" badge if a session has been added.

We also do some optimization using chrome.storage.sync to keep a cached copy for display rather than polling the sheet at runtime. If new data is available, the storage object is overwritten in the background.

Challenges

Async work is challenging for me. A lot of my time refactoring was flow control, making sure data I needed was actually there when went to operate on it.

Specifically, I started storing a timestamp for the last time the sheet was updated. Moving from localStorage to chrome.storage.sync for caching made sense, but it was difficult because getting inforamation out of chrome.storage is asynchronous. I ended up adding an explicit function to get and resolve the Promise in the right order.

async someFunction() {
    function getTimeStored() {
        return new Promise((resolve, reject) => {
            try {
                chrome.storage.sync.get('last-updated', function (data) {
                    resolve(data['last-updated']);
                });
            } catch (ex) {
                reject(ex);
            }
        });
    }

    let timeStored = await getTimeStored()
}

It bent my brain a little bit, but it made the extension more helpful because we had a problem with repeat "NEW" notifications when teachers switched machines. This syncs their information across all computers they would log in on at any point.

The Result

The finished product is quite simple...a list of upcoming events in the next week, each linked to a calendar event with all the details. The goal was simplicity and utility. We're also able to cut down on emails sent out with the same information.

I think, for me, it gets back to putting information in front of people rather than always expecting users to go find information. It's much more like the RSS feed approach, where my reading is delivered consistently. When I want to read it, I know where to go.

In this case, we're helping take the load of remembering development opportunities off of our staff and waiting quietly until they're ready.

Comments