Image credits to Justine Ehrhard and Integromat

How building our own “Typeform to Google Sheets connector” improved our “non-developer” collaborators productivity

Empowering collaborators to analyze data from Typeform, without involving developers

Typeform is one of the online services we rely on at Unly, but they don’t always provide exactly what we need. For instance, we got stuck with the “export to CSV” feature which isn’t customizable enough to our taste and didn’t fit our needs.

So, we built our own solution by connecting Google Sheet to Typeform API!

Now, fetching Typeform Responses in sheets and keeping those sheets up-to-date is dead simple and can be done by any collaborator, without relying on developers!

The problem

When using the native “Export to CSV” feature through Typeform platform, you can’t customize whether you want the sheet’s header to use the “questions labels”, or the “refs”.

Typeform will export the couple “question: answer”, but doesn’t provide the ability to export “ref: answer”.

This has been an issue for us because some of our forms have coexisting french and english versions, and we need to combine data to cross-analyze and generate statistics. Since question labels are different between versions, it wasn’t possible to cross the data, that’s what refs are for.

Also, refs are useful in many other situations.
Anytime we had to connect Typeform to other tools, we experienced that we needed refs, because they were the only way to uniquely identify a question. In complex forms, we often end up with multiple identical question labels and tools like Data Studio can’t handle that.

Using tools like Zapier has similar limitations, because those limitations come from Typeform itself

The solution

We put this very simple example together so you can experience the complete workflow, from filling the form up to analyzing the results.

Everything starts with a form…

Start with filling the form, to see your responses added to the spreadsheet in real-time: https://fsf-sl.typeform.com/to/mJhgzI

Don’t hesitate to leave out a comment if you’re interested by an open source release of our connector!

Understanding how the Google Sheet works:

Now that you filled the form, you should see your answers in the sheet.

If you want to quickly understand how to configure the sheet, then check out the “Settings” and “API Documentation”. Most of it is explained there!

The Google Sheet data are configured to refresh upon page refresh. There is no auto-refresh configured here, because it doesn’t make sense for this demo.

Check out this tutorial if you want to know more about how the sheet works (auto-refresh, API fetching, etc.), and play around with it

How to consume any JSON API using Google Sheets, and keep it up-to-date automagically

Typeform Ref Editor

Typeform Ref Editor

If you wonder how we customised our “refs”, check out this tool, that’s what we use!

(until TypeForm releases a proper way of doing so)

Here are a few handy features provided by our connector

• No limit of responses fetched: Typeform API allows only 1000 responses to be fetched at once, but there is no such limit when using our connector!
• CSV-friendly: Typeform API isn’t CSV friendly, but our connector is! The outputData can be customised to fetch data as csv (for Google Sheet usage), or raw (for other usages), or both (for debug, usually).
• Unique “value” key: Typeform API use many different keys for storing the value, based on the type of the data, we only use one, which is much simpler to use!
• Authentication: Each form is secured by its own token. The goal is to avoid granting access to all forms from your TypeForm account at once. It is safer.
• Cloud provider agnostic: Written using the Serverless Framework, we use it on AWS but there isn’t much work to do to make it compatible with any other Cloud Provider. Very simple to deploy, if you’d like to deploy your own version on your own infrastructure. (once open-sourced)

Technically, our connector is nothing more than an AWS Lambda function that acts as a proxy between Google Sheets and Typeform API.

Pricing

Since it’s hosted on a Lambda, it really costs nothing due to the “pay for what you use” design (AKA “on-demand”).

For instance, considering 5 API call every 5 minutes (auto-refresh), we expect around 50k Lambda invocation over a month, which makes it a bill of… $0!

Limitations

• Timeout: The API request can’t run for more than 30 seconds due to AWS API Gateway timeout limit. It’s not really an issue (we experienced about 6 seconds latency when fetching around 1000 responses), but that’s something to be aware of when fetching forms with a many responses (> 10k).
• Google Sheets itself also has its own limit.

We hope you enjoyed this quick demo! Please let us know if you’d be interested in an open source release!

P.S: We may soon implement a way to merge responses from multiple forms together, when exporting them (use case: grouping responses from different forms sharing common refs together, when using localised forms for instance!)
Would that interest you as well? Let us know!