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.
I spent some time with Apps Script a few weeks ago. It has some strange design decisions:
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.
> 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.
This is true of MS Excel's scripting language (VBA) as well. Worksheets are objects with events; cells are objects without (VBA-accessible) events.
But Google Sheets remote procedure calls are vastly slower than local OLE/COM dispatching. (And VBA/Excel presumably uses the optimized tighter COM interface binding instead of the slower high level COM IDispatch. Sure there's some overhead but it's nothing compared to Google Sheet's network overhead.)
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.
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.
> Everything runs on the server, including triggers
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.
> I think that’s a consequence of the fact that multiple users can simultaneously edit 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.
This reason doesn't make much sense to me. Let's say I write a non-idempotent custom function. It makes the spreadsheet behave weirdly: recalculating a cell twice leads to a different effect than recalculating it once. Does it matter whether the function runs on the server or the client? No, the spreadsheet will behave weirdly in either case, even with just one user.
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.
Or use the API to program in anything you want. We use Google Sheets for our accounting system, loading data via bank APIs and a cron-driven python script. We used to use Xero, but it couldn't handle the different tax regimes we operate in.
That looks like a great usecase. Would you be able to write about the architecture. A lot of us would love to be able to do things like this in Sheets, I'm personally trying to integrate a forecast estimate into Sheets
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.