And for those who can, Appscript gives your spreadsheet super powers.
For those who don't know, you are not stuck with writing JS in the Appscript integrated web IDE that comes with Google sheets (though honestly it's not too bad itself).
Using clasp, you can develop your code locally in an IDE of your choice, in typescript and have a build step compile those to js, and have clasp push it to spreadsheet.
Once you have the tool chain set up the DX is quite nice.
1) Everything runs on the server, including triggers and even custom functions! This means every script call requires a roundtrip, every cell using a custom function requires a roundtrip on each change, and it feels much slower than the rest of the UI.
2) You can't put a change trigger on a cell or subset of cells, only on the whole sheet. So you have to manually check which cell the trigger happened on.
3) Reading and writing cell values is so slow (can be a second or more per read or write) that the semi-official guidance is to do all reads in a bunch, then all writes in a bunch. And it's still slow then.
4) A lot of functionality, like adding custom menus, silently doesn't work on mobile. If your client wants to use Sheets on mobile, get ready to use silly workarounds, like using checkboxes as buttons to trigger scripts and hoping the user doesn't delete them.
Overall I got the feeling that Google never tried to "self host" any functionality of core Sheets using Apps Script. If they tried, it'd be much faster and more complete.
This is true of MS Excel's scripting language (VBA) as well. Worksheets are objects with events; cells are objects without (VBA-accessible) events.
It may be an issue with scaling and efficiency.
Not only is scripting Google Sheets indeterminently and syrupy slow, it also imposes an arbitrary limit on how long your code can run, making a lot of applications not just inefficient but impossible. Running your code in google's cloud doesn't make spreadsheet api calls any faster, it just limits how long you can run, them BAM!
To get anything non-trivial done, you have to use getSheetValues and ranges to read and write big batches of values as 2d arrays.
https://developers.google.com/apps-script/reference/spreadsh...
It's easier to just download the entire spreadsheet csv or layers and bang on that from whatever language you want, instead of trying to use google hosted spreadsheet scripts.
I think that’s a consequence of the fact that multiple users can simultaneously edit a sheet. Yes, Google could special-case the “you are the single user of this sheet” case, but that’s extra work, and, I think, would be fairly complicated when handling edge cases where users frequently start and stop editing a sheet.
No, it's not. Built-in functions like SUM recalculate instantly, and custom formatting rules (e.g. "color green if above zero") get applied instantly, even when there are multiple users editing a sheet. Running custom functions and triggers on the server is just a decision they made.
Arbitrary AppScript isn’t guaranteed to be idempotent. You have to run it only once.
Can we make a programming language that will save developers from that? Maybe, but that would be very hard and that's not what Apps Script is trying to do. It already allows non-idempotence, trusting developers to write idempotent code when they need to. So it could run on the client just fine.
No because the datagrid in MS Access is too rigid and doesn't have the extensive slice-&-dice features of MS Excel. My first consulting gig was creating customized MS Access applications. Despite that experience, I use MS Excel today because I know that MS Access is too limiting.
>No because the datagrid in MS Access is too rigid and doesn't have the extensive slice-&-dice features of MS Excel.
i'm not saying it worked or worked well, but i'm pretty sure the point of Access in the office suite was so that you could access Access (get the clever marketing?) data from within Excel and then do all the excel things you were used to.
anyone know if that worked or didn't? DDE and all those other projects were always pursuing this as a dream
Access was pretty amazing on its own back in its day, ignoring its multi-user limitations. It glued together a relational database, visual query builder, GUI/Form Builder, and reporting. You could create forms with sub forms that linked tables together. Also had a datasheet view. All of this without touching VBA code, but VBA was there when you needed it.
Does it have atomic transactions yet? That's the main thing keeping me using small databases like Access even when a mere spreadsheet would do otherwise.
The maintainability of the resulting systems was not great, but they did the job and worse is better I guess..
Kind of an open source Google Forms/ Access where you could deploy front ends very quickly and have it hit a DB
Or try Airtable.
Here you go: https://visualdb.com/
And yes, not the greatest way to proceed once you know what you want, but a heck of a way to iterate quickly and identify the actual requirements.
100% agreed. Creating a spreadsheet is declarative programming, and Excel (and now Google Sheets) has made more developers than any other platform (probably by an order or two of magnitude).
I do not know a business that was not CRITICALLY dependent on Excel for actual business operations through the 90s and 00s...and the same is likely true today.
it works fine initially, but for long term it's usually more productive to have custom software.
If you multiplied the custom software development effort and cost to the multitude of places and use cases people in an organization are using spreadsheets I think you’d quickly find that it’s infeasible. And that’s not even taking into account how much value the adhoc-ability of spreadsheets adds to the equation. Most spreadsheets can be completely refactored or thrown away in a rather trivial manner. The sprint nature of software development screens out most things that could be spreadsheets. I’ll build it in an hour instead of waiting 2 weeks to get on the next sprint.
The software development process is too rigid for rapidly changing business needs. Having to spec out requirements and such is often an unknown and something you’re doing live in the moment when creating the spreadsheet itself.
There are plenty of tools for that. Airtable comes to mind, or if you want to use your own database try: https://visualdb.com/
Anything we made would have _never_ been as good a UI and would have stopped us from being able to publish new content. The other nice thing about them is you can scale the integration effort. You can download a sheet from GS as a CSV then upload that in an admin page and you can probably ship that feature today. A tiny bit more effort and you can add a 3rd dimension to the data by finding an XSLX library and exporting the page as an Excel workbook. Eventually you can do something with their API where you just click "reload data from the master sheet" and you can generate all the data you need to preview the changes on the frontend. That's what our Airtable integration looked like. Airtable was very "pull this in via API" native and had a better way of expressing higher dimensional data in cells which matched nicely with Postgres storing arrays in cells.
I worked in a computer lab in college, ca. 1989. One of my colleagues was in the mechanical engineering program, and had a bias generally for "solve the problem" over "elegant solution" or "appropriate tool" concerns. (I love the guy to this day, to be clear.)
When he first came to work at the lab, he was the only guy to have installed FORTRAN on his workstation. It didn't work well.
Then he discovered Lotus 1-2-3 and its macro language. He DELIGHTED in making the rest of horrified by creating all sorts of boundary-pushing utilities in Lotus macros. To be clear, he was at least 50% "doing a bit", and leaning into the "engineer only knows one tool" gag we'd all been riffing on. But he was still doing absurd stuff in Lotus that would've been better built in, say, Turbo Pascal or Turbo C.
I had no idea back then that this pattern would become so prevalent.
> especially so for people who can't code.
One day you are writing ‘sum(a2:a201)’ the next you do some conditional formatting and so the complexity builds up very slowly with your needs.
With sql, day one:
‘SELECT SUM(Column) AS Total FROM Table;‘
Way more complicated. Way more powerful too, but most people don’t need the power until much later.
And you have to work in the console which is an unfamiliar ui for many.
It’s not impossible, but very high overhead in comparison.
That's it. It's the math notation from high school, plus cell references, which can be inserted by clicking a cell.
I’ve worked in academics and industry around biologists, chemists, physicists, statisticians, bioinformaticians, and all varieties of engineers.
I’ve never seen “huge swaths” of anyone expected to learn R for anything outside of a few niche areas in statistics and bioinformatics.
What people are expected to know is how to use a spreadsheet. What people are often given is Microsoft Excel, and essentially nothing else. A lot of companies wouldn’t dream of letting random employees install or use R or Python.
It’s not ideal. But some battles can’t be won and aren’t worth fighting. Which is why people use Excel for so many things.
Years after we broke up, a new company she joined required her to take a VBA training course, and she texted me and told me I was right, VBA was easy and her biggest challenge was being patient while the other students struggled.
I spun up a local Grist instance in my org, using SAML with our org's email authentication. It's intuitive enough that I've replaced a few shared spreadsheets with it (now with rowwise permissions) and powerful enough that I've also replaced a few internal CRUD apps.
It's not in any way a spreadsheet, you can't use rows correctly and everything is stuck in columns. Not to mention the forms you use for input are not user friendly compared to a simple understandable coloured cell
Minor point, but I would say that people who can construct formulas, are indeed coding.
Nobody wants to explain to IT that they need to install Python on their machine, or drivers for sqlite, or - god forbid - get a proper database. Because that requires sign-off from several people, a proper justification, and so on.
There's Decker https://beyondloom.com/decker/
It is also a very bad co-authoring tool imo unless you have a very tight team and processes of how to use spreadsheets. I would not mind if I was using it alone. But spreadsheets encourage a certain naive "visual" approach to structuring data in them that can become an issue if you want to import the data to actually process it somewhere and your coworkers don't really understand this well.
Spreadsheets can be useful, however:
1. Speaking about excel in particular, localisation issues are an absolute nightmare if you happen to live in the wrong country (commas vs dots for decimal points) especially while importing CSV files (which is already unnecessarily complicated in excel). If somehow you don't notice these issues, you end up with wrong data without understanding there the error came from.
2. If your coworkers do not really understand very well how spreadsheets work, you quickly get to become really frustrated with issues coming up when you have to import a spreadsheet for actual processing. Datetimes with different forms, coloured boxes being meaningful, mixtures of text and numbers and whatnot.
Yes a big part of it is "people problem" rather than "technology problem" but imo spreadsheet technology (excel, google sheets etc) encourages a variety of practices that make it less reliable. If the technology gives you the freedom to mess up too easily, imo it is not just a people problem.
I have a couple of spreadsheets that I use on a weekly basis, and I found it easiest to build both of them from scratch, despite the tons of examples floating around for exactly my use cases, and despite it being my first time building anything with a spreadsheet in 10+ years.
On the bright side, after using these spreadsheets for a week, I lost all desire to write apps to do the same things. Google Sheets is a good-enough UI and solves sync across different computers and mobile.
i might have used it for burned CDs too, I can't remember. at work i had access to burning CDs at scale back then, I just don't remember when the blanks became cheap enough that nobody would notice my pilfering and and whether i still used my mac. i did burn disk backups to CDs
Give me a spreadsheet with a world cksss user interface and then you’ll have something!
They can emulate behaviour of databases; but the missing parts missing will haunt you. Spreadsheets are a jack of all trade, mastering nothing, haunting you with everything;.an amplifier for the Dunning–Kruger, where people are misguided about data-quality.
Spreadsheets are indeed a great tool, but the implementations we have today are bad, with too many booby traps, not enough safeguards, not even much comfort for those with higher demands.
Yeah a DB where any user can accidentally hit the spacebar and erase a formula, and never see any warning that their outputs are now horribly inaccurate.
What Problem?
Huge contrast with MS approach to this
In other words, one of the core use cases for a spreadsheet is that it empowers a broad swathe of users (broader than Tableau or PowerBI) to quickly extract insights from their data to fill immediate needs.
Or at least, that's a core use case if you can get your data into a spreadsheet without too much trouble.
The problem corporate IT/Dev folks face isn't that an idea started as a low-code tool, but rather that the low-code solution is often dumped on them with no budget or desire to improve it to be more reliable and maintainable.
At least until something fails... and usually in dramatic fashion that then wakes leadership up to the idea that maybe we should invest more into this critical business process. If the company didn't go under in the meantime.
I imagine that's because it's not really a technical problem, but an issue with how the whole organization (mis)handles complexity, and we collectively [1] still struggle to model/name a lot of those problems.
[1] Yeah yeah, I see you there in the back, you excited cyberneticist bubbling with enthusiasm to share... but I mean as a practical widespread matter.
As I see it the limitations of spreadsheets are what keep them from becoming the official way of doing things. At some point most of the stuff in the spreadsheets has data provenance in a properly managed IT system somewhere. If companies could get rid of this part of IT they would suddenly find themselves in a trillion dollar pit of lost money/inventory, As the spreadsheet mess spiraled out of control with no source of truth.
Other software that I use and write is version controlled and has tests to catch errors, mistakes, typos. Those tests regularly find and prevent problems! Likewise version control.
Could we get the same with spreadsheets? Seems difficult but not conceptually impossible, particularly with LLM assistance.
Also I remember Row Zero the demo on your home page was impressive. You guys were S3 engineers too, good to know your project took off. :)
You can create a super quick Python app with visualization and whatnot in about the same time as a spreadsheet. But then it's not online. And it's not best practice.
Web is just a fucking beast and then developers go in and add additional complexity.
It would probably be a much simpler web app if you made it just, like, a bunch of PHP scripts thrown in a folder. But they won't do that.
If someone shares a sheet with me, it’s for the intended purpose of sharing data and/or visualizations of that data.
I’ve always been a huge fan of spreadsheets, and the rare times I’ve encountered them being misused, it was a long time ago, and not near enough to make me an “anti-spreadsheet” person.
It sounds like these anti-spreadsheet people need to find a new place to work and/or new coworkers.
Either way people shouldn’t be anti-spreadsheets because some people misuse them. That doesn’t change the fact that they’re a great tool for tracking/sharing/visualizing data.
It happens all the time where I work. I don't want to be specific, but we have lots of examples here. In some cases people don't like the core software, so they work around it by tracking things on a spreadsheet. And sometimes that spreadsheet disappears (in one case, it was being kept on an XLSX on a USB thumb drive, but the thumb drive got corrupted and we lost some very important data.)
To say that it was a nightmare was an understatement. They were willing to dump vast sums of money to get something better, but they'd homebrewed so many human processes to deal with the spreadsheet that they struggled to adapt to a more conventional way of doing things.
With that said, it's still a great tool for the job because the different stakeholders can inspect it.
Lol. Go to literally any bank. They all have a legion of accountant,analyists that's sole job is to maintain their little fiefdom of spreadsheets that only they understand.
If most people knew just how held together by string,tape and gum the banking industry is there would be a run on the banks.
There is also always some 75+yo part time guy that maintains some sort of critical system. He always says, "I want to retire but they keep throwing more money at me"
Otherwise, I find them to be great deliverables.
Even though Excel is proprietary too, it's ubiquitous enough that people don't have to worry about it.
And then, if somebody makes a change in the database, a trigger will update the spreadsheet
Such a two-way binding makes it possible to continue relying on spreadsheets for UX, all the while the data is not locked in there and we can also have other processes handling the data (a web app, some cron job, etc)
Maybe market it as an API for excel or something
See here to understand what you are missing out by not using a database: https://visualdb.com/comparison/#integrity
though, much programming is poor and often can be accomplished better in a spreadsheet given the situation and use case...
I think even a complicated spreadsheet that can be directly edited and modified by the actual business stakeholders is preferred.
If the business stakeholders can edit said spreadsheet, they can code. Not well probably, but they can.
So, theoretically, they should be able to open a python script or whatever and hack away. A lot of calculations are actually much easier and straightforward in a real language as opposed to Excel.
But they won't, partially because developers would never let them.
Your 'trifles' are the biggest screw-ups from functionality to INFOSEC.
A spreadsheet gives you a DB, a quickly and easily customized UI, and iterative / easy-to-debug data processing all in a package that everyone in the working world already understands. AND with a freedom that allows the creator to do it however they want. AND it's fairly portable.
You can build incredible things in spreadsheets. I remain convinced that it's the most creative and powerful piece of software we have available, especially so for people who can't code.
With that power and freedom comes downsides, sure; and we can debate the merits of it being online, or whether this or that vendor is preferable; but my deep appreciation for spreadsheets remains undiminished by these mere trifles.
It's the best authoring tool we've ever devised.
EDIT TO ADD: the only other thing that seems to 'rhyme' with spreadsheets in the same way is: HyperCard. Flexible workbench that let you stitch together applications, data, UX, etc. RIP HyperCard, may you be never forgotten.