No line of code was mishandled while writing this software

May 30, 2018 · Tools Webservices Workflows EDF CLO

Drag & drop programming has been an ever fleeing dream like... ever. Every other years, someone comes up with a revolutionary idea that should change everything... and every single time, it fails miserably.

A couple weeks ago at the Ncrafts Conference in Paris, Arnaud Porterie introduced me to the combination of Airtable and Zapier. And the only thing I can say is that Drag & drop programming has never been that close. It took me about 5 hours to get a good understanding of both tools and build a clone of the "Know Your Company" system without writing a single line of code.

Let me introduce the pieces first, and we‘ll see how it works together in a more complicated workflow after.

Airtable

Here's how Airtable is presented on Wikipedia:

Airtable is a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet. The fields in an Airtable table are similar to cells in a spreadsheet, but have types such as 'checkbox', 'phone number', and 'drop-down list', and can reference file attachments like images.

Users can create a database, set up column types, add records, link tables to one another, collaborate, sort records and publish views to external websites.

As you can see on the following showcase (provided by Airtable), you can create various "tables" or "tabs" ("Features", "Product Themes" & "Monthly Newsletters" here under) with strongly typed columns ("Feature" as text, "Launched" as checkbox...). You can make links between elements and compute a lot of elements.

Airtable Launch Calendar showcase

One very neat thing, with a few clicks, you can let Airtable generate a form to enter data into your database. You can then generate a public link for your form, and let other users fill it for you. Here is a form to enter bugs into a bugtracker database (another Showcase from Airtable):

Bugtracker Form

There are many more features, but I haven't been much further yet.

Zapier

Again from Wikipedia:

Zapier provides workflows to automate the use of web applications together. It is often described as a translator between web APIs.

If you know IFTTT, that's very similar. The idea is to automate online steps by using Webhooks. You build workflows like "When I receive a Tweet containing Keyword X, send me an email and archive it right away with the right labels".

Or like those showcases below:

  • Create trello card and google sheets row for new starred emails on gmail
  • Send Gmail emails when there is new activity in a Trello Card
  • ...

Popular Zaps

I order to do so, you simply create a chain of simple steps like those below:

Zap Steps

One of the killer arguments for Zapier, is the fact that you can created your own steps with JavaScript or Python code. But honestly, I haven't reached such a complexity that I needed that yet.

Know Your Company

What does it do?

Know your company is a very neat tool written by Basecamp that allows you to ask questions to all your employees on a regular basis and redistribute the answers to all as well. It helps you gain feedback over what is happening inside the company, and helps getting people to know each other. It can do more than that, but this is the core feature.

As a test, I wanted to recreate this simple workflow:

  1. Send a question per email to all my colleagues
  2. Everybody has 2 days to answer via an online form
  3. After 2 days, a digest email is created and sent to all my colleagues again
  4. Goto 1. with the next question...

This tool is pretty simple, yet it already requires a few not completely trivial integration points:

  • WebEndpoint for the fillup form
  • Storage of information
  • Sending Emails

The Workflow

The solution I created is pretty simple.

In Airtable:

  • I have two tables that store the questions and the answers
  • The questions have 3 possible states [Upcoming, open, closed] and an ID
  • The answers reference the questions and retain the answer itself and the provided name of the author

In Zapier:

  • One "Zap" works with a scheduler to send the next question at regular intervals.
  • One "Zap" is watching the "Answers" Table and is triggered each time a new row is created. It then fills a "digest" that Zapier persist for me.
  • One "Zap" works with another scheduler and sends out the digest per email.

So all in all, Airtable provides me with a form through which people can answer the questions. Through Zapier, I can send this form at regular intervals with different questions to my colleagues. When an answer is submitted, it is processed with zapier and added to the digest. At regular intervals, the digest is sent to all the colleagues again. And then another question is sent out with another form.

In addition to this, I created a way to submit and upvote questions.

Pieces of the solution

In a bit more details, here's how the "Process Response" is built:

Process Response

  1. When a new row is created in the "Answers" table of my Airtable base
  2. Set the answer's state as "Chewed"
  3. Continue only if the Opt-In checkmark is ticked
  4. Find the corresponding question in the "Questions" table
  5. Check if it is the current question (state = open)
  6. Append the answer to the digest
  7. Update the state of the question to "Digested"

No line of code.

That's it.

The problems sofar

Of course, it has not only been rainbows and unicorns. I found a few problems sofar.

1- The first has to do with generating dynamic forms. I want people to answer a question via a form. So I have to generate a generic form and write the question on it before sending it to the participants.

The best way to do this, would be to pass a question ID, and retrieve the text of the question before displaying it to the user. But this is not possible with AirTable.

In fact, the only way to pass dynamic information to a form is through URL parameters like this:

...mysuperform?prefill_question=mysuperduperquestion

This would fill the field "question" of the form with the value "mysuperduperquestion". This is unfortunately the only way I found to display the question to the user. Not super nice but it would work.

2- Second problem, AirTable can only display "editable fields" (the white boxes below) and cannot make them read-only. Using this URL-Parameter, I prefill a field which can then be edited by the user. Less nice. I created some logic in the backend that proofs that the question was not tampered with, and if so creates an admin warning.

Here's how it looks like:

Answer Form

Functional, but definitely not nice.

3- The last problem has to do with the query logic. Let's assume I search for all the "upcoming" questions from Zapier into AirTable. It would match from 1 to "n" rows. Zapier then takes the first of the list and hands it out to me. Unfortunately, nobody knows how this list is sorted and I kept on getting results I didn't expect. So I created another workflow based on question IDs and opened a support request on the Zapier side.

Conclusion

I am amazed at the simplicity yet power of this combination of tools. As an developer myself, I know I tend(ed) to look down on such solutions. But focusing on delivering value and holding the "minimum viable product" idea in mind, this is absolutely fantastic.

I would really recommend you to look into it. It's incredible what you can do in 2018...

Comments powered by Disqus