[alooPotato]

Our customers of [Streak](www.streak.com) use zapier heavily to connect to all their other tools so we have a ton of experience with it. We're currently building an alternative for our users natively into our app (we think we can make a more optimized experience for our app).

I think we've stumbled onto a pretty key product insight - we're basing our automation tool on a spreadsheet. Zapier is clearly optimized for non-technical users so doing any logic, even basic if statements, is really cumbersome. On the other end of the spectrum, pipedream and tools like it rely on your users knowing how to code. We think spreadsheets are the perfect balance, most users (at least ours) know how to do basic formulas and understand references.

Spreadsheets are awesome:

- development environment is completely set up for you, no tooling needed

- editor, runtime, debugger are all the same tool

- by default you see the output of all your computation, its a secondary action to see the code

- you can preview the intermediate data results at every step of your computation making debugging a dream

Here's how we're thinking of applying it to the Zapier use case:

- a series of zapier like triggers and actions

- each step is just a 2 column spreadsheet (property name and property value)

- the value can be something hardcoded, a reference to a value in a previous step, or a complicated formula

Anyone tried something like this before?

[robocat]

I had designed a similar system for consultant customisation of complicated business rules, although I failed to deliver it. I had intended to:

* Have one workbook per business function. One inputs sheet (variables in), one sheet for the formulas, one outputs sheet (variables out).

* Use a sort of test driven, self documenting approach. When developing with clients, it is easiest to ask them about specific examples, including simple and complicated ones. Consultants understand business rules when they configure a system, but it is very hard to come back to 5 years later when the business wants something tweaked. Test data inputs and expected outputs was to be included on other sheets, with documentation. And the system would validate the sheet formulas gave the correct outputs for the example inputs.

* Use custom formulas for our industry needs with a custom data type for some variable inputs - this is the crux of where I came unstuck with my design (it was kind of SQLish table inputs per input cell, visible in Excel when in design mode).

I didn’t actually finish the system, because I was trying to bidirectional integrate directly with Excel using COM, and I just couldn’t get that working because I overcomplicated it. This was a long time ago, and I couldn’t find a suitable non-excel engine to integrate with due to other team constraints. Note that spreadsheets are inherently side effect free functional programming at its finest IMHO: I wanted to avoid any imperative programming. However we ended up with an imperative Visual Basic like design instead which did work, but it takes extremely highly skilled consultants to use it, and is spectacularly inefficient.

[alooPotato]

Why did you decide to separate the input, formula and output into different sheets?

[robocat]

I just presumed splitting out the roles would make everything clearer and debugging easier. For the concept to work, the rules engine had to interact with the workbooks inputs and outputs, and that seemed like something that could have subtle mistakes and hard to debug by the consultants if everything were on a single complicated sheet.

Consultants are less familiar conceptually with functions, and this way I could get them to label variable inputs and label outputs. I expect consultants wouldn’t structure things unless given a format. The “formulas” sheet could be pretty wild and complicated: many consultants are more results focused than OCD organised. I didn’t want to have formulas etcetera mixed up with parsing the inputs and outputs. Also that way the spreadsheet can easily and unambiguously define the inputs and outputs for the engine to grab the names/definitions from.

I can think of other ways to do it, but it was just what seemed the most likely to work for multiple reasons.

One goal was to avoid recalculations by tracing the dependencies (yay spreadsheets) and using memoisation for the outputs, and versioning the results, and versioning the consultants’ formulas (i.e. the workbook was the unit of programming change, one reason to not put too much in each workbook). Auditing where values came from, and why values changed if formulas were modified, was one desire for the engine.

[kamalfariz]

Clay is basically Zapier applied to a spreadsheet UI https://www.clay.run/

You can set up each column to hit an integration. As a cell is populate either by hand or from the result of an integration, it triggers other cells to evaluate.

[alooPotato]

Cool - checking it out!