A Spreadsheet as a Deployment Target
A friend recently asked for a small program to correlate data from a couple of online services. Rather than shipping a command-line tool, I put the code inside a Google Spreadsheet. The exercise was a useful reminder that spreadsheets, often dismissed by professional programmers, are a genuinely practical deployment platform for simple tools aimed at non-programmers.
My friend is skilled with audio-visual tech but isn't someone who lives on the command line, and he runs Windows. Like many computer users, though, he uses Google Sheets routinely. The tabular metaphor is a simple, effective way to handle structured data, and users can see results immediately as they work. Google Sheets also makes sharing a common data store easy. A script hosted in a sheet can expose a custom menu, letting the user run the code and inspect the output without installing or updating any software. If I get hit by a bus, the sheet can be shared with someone else who can take over the scripts, which are written in JavaScript, a widely known language.
Authentication Is the Real Bottleneck
The core logic of the script was trivial: compare the list of members on a Slack channel against members registered on Patreon or on a separate hand-maintained list, then generate lists of people to add or remove from Slack. Pulling emails and computing set differences is easy. The hard part was convincing the remote services that the script was allowed to fetch the data.
Both services use OAuth, but the standard doesn't make integration plug-and-play even with Google's libraries. I ended up sidestepping authentication where I could. Slack lets you create an app, grant it the needed authorizations, and receive a simple access token directly on its website. Placing that token in the script is generally poor security practice, but in this case the script lives in the same spreadsheet as the data it downloads, and that data isn't terribly sensitive.
The Patreon data was both more awkward to authenticate and more sensitive. So I took a different route: the Patreon web app allows the user to download data as a CSV file. I simply asked my friend to do that and import the file into the spreadsheet. There is a real opportunity here for Google to simplify the whole authentication flow—ideally a developer could call a fetch method on a remote service and let the infrastructure handle the authorization dance.
Organization and Data Flow
I don't program with spreadsheets much in my usual work, but I applied my standard instincts. First: download data to a local store with minimal manipulation. My Slack download script only selects the fields I want and dumps them into one page, clearing and replacing the page on each refresh. The Patreon page assumes a simple upload of the CSV file. A third page holds the hand-maintained list of exceptions. All three are pure data sheets: single tables, headings in row 1, no formulae.
The comparison script reads from these three data sheets, does the application logic, and writes the two result lists to a separate output sheet. This mirrors how I'd use separate text files in a command-line app. The user can see the raw downloaded data, I can run and test the application logic without re-downloading each time, and there's a clear one-way flow of data between sheets and code.
Performance Traps and API Limits
My first run of the Slack download script was depressingly slow, adding rows at a rate of about one per second for a thousand or so rows. The culprit was Sheet.appendRow. Switching to defining a range—which can be the whole sheet—and using Range.setValues instead made the additions effectively instantaneous. Nothing in the documentation or anywhere else online hinted at this alternative, which is a real barrier to wider adoption of the platform.
The Patreon API exposed another design flaw. It provides a resource listing all supporters of a campaign with their Patreon ID and name, but to cross-check against the Slack list I also needed their email addresses. That required a separate GET request per supporter, for several hundred people. API designers take note: if you provide ID-based lookup for a resource, support fetching data for multiple IDs in a single call.
Separating Logic from Cell References
Spreadsheet scripting often encourages thinking in terms of cells like B22 or ranges like A2:E412. I prefer to work with plain JavaScript data structures so I can use collection pipeline operators. A small helper function extracts a sheet's data into an array of objects with headers as keys, which keeps the application logic clean and testable.
The development environment itself is understandably crude—a text editor reached from a spreadsheet menu. It's acceptable for a short overnight stay, but nothing more. For something bigger, I'd investigate editing the script via Emacs's Tramp mode or syncing local files with Google Drive to keep source in a git repository. For this task, about 150 lines of code, it wasn't worth the effort.
Hosting a simple application on a Google spreadsheet is an appealing deployment option for a range of straightforward tasks. It lets users run code without installing anything, enter data in a familiar environment, and share everything with colleagues. It's not a platform that gets much discussion, but it's worth keeping in mind for any job that might otherwise be a shell script—when your users aren't comfortable with console windows and text files.
