scratchpad of ideas for making Google Sheets formulas much nicer

google sheets better formulas.md

Google Sheets better formulas idea

Basically, I passionately hate the cramped awkward language that Google Sheets uses for formulas. Wouldn't it be super cool if there were a much nicer language you could work with that compiled back into Google Sheets? It'd be like CoffeeScript, but for Google Sheets, and with a browser extension so you could do it all seamlessly in Google Sheets.

So first off, there's some basic obvious stuff you'd want to do:

• Pretty printing: split across multiple lines, add indentation, add color coding
• Cleaner logical operators: replace AND(x,y) with x && y, replace IF(a,b,c) with something like if (a) { b } else { c }, etc.
• Better function names: kill the ALL CAPS regime, and give things clearer names (for example, why the hell does FIND() mean case-sensitive search and SEARCH() means case-insensitive search?? how are you supposed to remember which is which??)

Beyond that basic cleanup, there's some further really complicated (but really convenient) stuff you could do:

• Declare variables: if we allow our language to use a hidden sheet for internal memory, we could do really fancy stuff that normal formulas don't support.

Here's how I'm thinking this works. You'll probably have two hidden sheets. You'll have an index sheet and a data sheet. The data sheet will just be row after row of pure shenanigans; whatever needs to be stored. The index sheet will allow the formulas to find stuff in the data sheet. The only trick is figuring out the best way to store this stuff such that our formulas can still operate quickly. And to that... I'm not really sure. The variable declaration stuff might not be the best laid plan.

• Use text names for columns instead of letters: in 99% of spreadsheets, the first row consists of labels for what's in each column. Given that we have pretty names, it makes no sense to burden ourselves with remembering silly letters for each column.

Technical implementation

Uhh, so this is the hard part. I don't really know much about compiler design, and that's pretty much exactly what I'm planning to build here. My plan right now is to educate myself on compiler design, maybe take a class via OpenCourseWare or something.

I do know that this broad project will have two basic pieces: the compiler and the extension. The compiler will be the pure standalone logic that takes my custom language as input and spits out Google Sheets formula as output. The extension will be the part that connects the compiler to the existing Google Sheets library. I dunno bout compilers, but browser extensions and JavaScript I'm half decent at, so I have a few ideas.

One of the most crucial pieces I'll need is an abstraction layer for interacting with the Google Sheets DOM. I can't trust that Google Sheets won't completely redo their UI next week, so I need all the logic for interacting with the sheet to be contained within one simple file that I can quickly revise whenever Google breaks it. It's probably gonna break a lot, because I have to interact with their actual JavaScript (the entire spreadsheet grid is just a big canvas element so there's not much I can do directly with it).

Converting from formula into pretty language

Another thing I'll have to deal with is the fact that, once my language starts implementing more advanced shenanigans, it might be difficult to convert it back from a formula to my language for editing. Perhaps the solution will be, for every human-editable sheet, there's a hidden ghost version of that sheet. The cells in the ghost version and the normal version match up one-to-one. For any formula written with my language, the pretty version will be stored in the ghost sheet, while the raw formula will go in the human-visible sheet (where it has to be). The browser extension will seamlessly retrieve the pretty formula from the ghost sheet whenever the user tries to edit it from the normal sheet.

All that said, perhaps that's stuff that should wait until the project has matured more. At the very beginning, my main focus will be on simple stuff, like cleaner logical operators and displaying the language in a pretty printed format. For that kind of simple stuff, converting between pretty language and formula language should be lossless, so it won't be an issue.