[IanCal]

> These tools are often ridiculed and their use by non-programmers for creating business tools is often frowned upon but they allow business users to quickly create flexible, makeshift solutions to their problems.

They do, which is both great and terrible. They're powerful and easy to get started with, but therein lies the danger.

When used to quickly whip something up they're great, but when those things grow or end up being relied upon they're no better than the hack that the CEOs kids friend who is 'good with computers' produces.

So, since they're both useful and dangerous, are there things we can do?

Perhaps a spreadsheet that allows some form of testing? Are there simple tests we could start to encourage people to use? When I've used spreadsheets for some financial things, I know that if I increase one cell, I expect another to increase (for example). I know certain combinations of inputs that should result in certain outputs.

Also, perhaps a clear path from spreadsheet -> application? Often intermediate values are displayed somewhere, so could a spreadsheet app lead someone to naming them all (typically they'll have a 'variable name' just to the left of them).

edit - I should really have read the article first, but I think a focus on making things testable is important.

[jamii]

That's definitely something we spend a lot of time thinking about. We have to be able to support easy, exploratory programming but also be able to nail stuff down if it ends up being used a lot.

We have a bunch of ideas queued up. One of the simplest is generating fake data as you write code so that you have a better chance of noticing edge cases. We are also planning to proactively hint about integrity constraints (types, unique/foreign keys etc) eg if a column only contains integers, show a button that fixes the type to integer. There is an optional typing system in the wings too.

There is also lots of similar research aimed at spreadsheets (eg http://eusesconsortium.org/wysiwyt.php) which we can borrow from.

[BjoernKW]

> but when those things grow or end up being relied upon they're no better than the hack that the CEOs kids friend who is 'good with computers' produces.

Ronald Reagan allegedly once said "Nothing lasts longer than a temporary government program.". The same applies to throw-away code and 'prototypes'.

> Perhaps a spreadsheet that allows some form of testing?

For complex, interrelated calculations this definitely makes a lot of sense. From my experience the most common problems with Excel sheets are collaborative editing and version control, though. I'm not sure if more recent versions of Excel and Office 365 in particular solve these issues. Google Spreadsheets sort of does (the UX is lacking, though) but then again hardly any company will even consider putting sensitive data in the cloud and rightfully so.

Even convincing users to use complementary tools that improve Excel-based processes might be difficult because due to prolonged, habitual (ab)use many users don't even see the problems that arise anymore ("Why change this? It's always been done this way."). Implementing such tools in a way they're accepted by users is hard. You can't just add Git or some acceptance testing framework to the flow and expect users to be happy with that. It'd have to be something that seems very intuitive and natural to use for the average Excel users.

A clear path from spreadsheet to application would be an interesting approach, too. Spreadsheets are a conglomeration of model, view and controller logic. If you could somehow separate those semi-automatically and generate a boilerplate application from that this might by a viable approach.