Article summary
A handful of times over the past year, I’ve found myself needing to provide a customer with a highly customizable but short-lived content management system (CMS). In each case, after the initial feature was built, there were expected to be deliberate but infrequent updates to the content. Due to this scaffold-esque nature, the solution needed to be low-cost, which ruled out developing fully custom software. Using a CMS vendor wasn’t appropriate either, due to the nature of the content and how it would change. I thought to myself, what other options are there?
Apps Script Plus Google Sheets
This reminded me of some work I’d done in the past to augment a large, data-heavy Google Sheet. I was able to add a fresh UI and advanced filtering via something called Google Apps Script. Apps Script is “a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products.”
It turns out that Apps Script, coupled with a Google Sheet, is also the perfect tool for the type of job I mentioned above. With this approach, you can build a simple, cheap, yet powerful CMS-like solution in practically no time at all. Here’s a brief description of how we set things up.
We created a Google Sheet with some meaningful headings and added rows of data. Next, we wrote a couple of functions in App Script that would enable us to construct a JSON object from the spreadsheet’s data. We also added a menu item that would allow us to conveniently export the JSON object with just a couple of clicks. When we were ready, we exported the JSON and dropped the file into the codebase. Anyone with access could continue to modify the Google Sheet. When ready, they could export the data again.
In the right circumstances, we’ve found this to be a really useful way to provide a great deal of value in a small amount of time. As with any solution, it’s not without its pros and cons.
Pros
Low Cost
Developing a custom CMS or integrating with a headless CMS vendor simply isn’t as economical as leveraging a spreadsheet. Ignoring the price of any off-the-shelf CMS solution, the cost of the spreadsheet approach is significantly less than developing custom software or integrating with a third-party CMS.
Collaboration & Sharing
Google Sheets supports real-time editing and collaboration for the entire team out of the box. Managing access is as simple as changing permissions to access the spreadsheet.
Short Learning Curve
If the user(s) are somewhat familiar with spreadsheet tools, this approach provides a low barrier to entry.
Easy to Model Data
The interface is literally a table, which makes modeling any tabular data straightforward.
Flexible Integration
There are many approaches to connecting the spreadsheet to your application. As described above, one option is to export data from the spreadsheet and drop it into your codebase. A more sophisticated approach might involve using the Google Drive API to fetch the latest data directly via script.
Supports Spreadsheet Functionality
All of the standard spreadsheet formulas like “SUM” or “COUNT” are at your disposal. As a bonus, you can even write custom formulas in Apps Script that you can use alongside standard Google Sheet formulas.
Cons
Versioning
Maintaining versions in Sheets can be challenging. Versioning is provided out of the box. But, the versions identified by Google likely won’t map directly to your application versions in a logical way. You can, however, manually name versions which may be sufficient depending on the use case.
Dynamic Content Support
It’s certainly possible for an application to fetch data from Google Sheets as needed. Approaching it this way introduces some concerns, though. How often should the application be getting the data from the spreadsheet? What happens if someone mistakenly modifies the values unknowingly or changes the structure?
To avoid some of this uncertainty, I prefer using sheets to produce static content that can be bundled with the application.
Complex Relationships
Attempting to model complex relationships in a spreadsheet can get tricky. Trying to capture those relationships may minimize the value of this spreadsheet approach to the point that a true CMS would provide greater value.
As consultants, we’re constantly looking for ways to provide the utmost value to our clients. That value frequently comes in the form of well-architected, custom applications or recommendations backed by years of experience. Sometimes, though, we can provide a great deal of value by simply identifying the perfect tool for the job.