Make your Google spreadsheet available with a web application
https://github.com/fabrizio2210/been_to_it
The use case
In this project I wanted to populate a Google spreadsheet using a web application. The use case was a wedding invitation. Guests are invited via an unique URL, the displayed content is tailored on the guest and the answers registered in the spreadsheet.
Tailored how? Displaying the proper name, but also showing different invitation details (in Italy is common to have two batches of guests, one for the full day, the other just for the cake at the end of the day).
What answers? The attendance, if they had an allergy or food intolerance.
In the end, each row of the spreadsheet is a guest and the columns are the features.
The initial approach
At the beginning, I set up the web application as frontend of the Google Sheet spreadsheet with Redis cache in the middle. In other words, the web application was querying the Redis cache for guest data, if not present falling back to the spreadsheet by HTTP API. The HTTP API was also used to set the value entered by the user from the web input.
The use of the cache was just to have a lower latency and reduce the web calls to the Google API.
The (license) Problem
Everything was working fine on my computer. To make it work I had to generate a new credential token every week using the oAuth page. I thought I could have got rid of it by issuing a request for a proper license. But it turned out that, to have an everlasting license, I needed to go through the publishing process. The process is meant for developers who want to create a public application which will be used by single users. To be specific, a user needs to grant the permissions to read their own data on Google (e.g. own spreadsheets). In order to pass the publication process, I had to provide evidence about how I use the data and show how to use the app. Obviously, this process does not fit my needs because I just want to sync my own spreadsheet without making a public app.
Unfortunately, there was no other ways to have a permanent HTTP API access but ephemeral with the developer license.
If you cannot query, get queried
Changing the paradigm to overcome the license issue
Querying Google Sheet via HTTP API is no longer an option, but how can I read and write that data?
I found Google Apps Script as a solution. I can interact with Google API directly in the spreadsheet. I can even make external HTTP calls. Ta-da! Therefore, instead of querying the spreadsheet, I can push data to the web application and retrieve the changes. It is asynchronous, but the user will not notice that because they will refer to the cache.
So, the cache layer becomes essential because it hides the asynchrony by acting as a sort of database. It stores the data from the spreadsheet and contains the user input which is waiting to be consolidated in the spreadsheet.
The Apps Scripts script
The following is an example of script that I use to push the spreadsheet data to the web application. The same POST request also fetches the values to consolidate them in the spreadsheet.
To make sure the data is not tampered by any bad actors, a shared secret is used to authorize the HTTP call.
// "url" is the URL of the web application to sync.
// "secret" is an arbitrary password shared between the web application and
// this script to avoid bad actors uploading the data.
function syncCache(url, secret) {
var spreadsheet = SpreadsheetApp.getActiveSheet();
// My data isin the range A1:O100.
var values = spreadsheet.getRange("A1:O100").getValues();
// Make a POST request with a JSON payload.
var data = {
'values': JSON.stringify(values)
};
var options = {
'method' : 'post',
'contentType': 'application/json',
'headers': {
'Authorization': secret
},
'payload' : JSON.stringify(data)
};
var response = UrlFetchApp.fetch(url, options);
// Write the fetched data from the web app to the cells.
valuesToWrite = JSON.parse(response.getContentText());
for (const [cell, value] of Object.entries(valuesToWrite.cache)) {
console.log(`Writing ${cell}: ${value}`);
spreadsheet.getRange(cell).setValue(value);
}
}The script needs to be run regularly. Luckily, Apps Scripts provides time triggers, so you can set up your trigger to run every minute the sync.
Conclusion
In this article I focused on the data flow instead than the web architecture. If you are interested in the web architecture, you can give a peek at Photobook, which has a similar design.
Here, I wanted to highlight the solution for the license problem. Hopefully, this story might be an ah-ah moment for someone else in the same situation.
Best of luck with your development!
