Typeform Tips & Tricks – May 13, 2024

Here’s a little hack that I have used to ‘consolidate’ data from two separate tabs/Sheets into the same data set in Google Sheets.

Scenario: you did a survey last year using a form that you have now closed. You duplicate that survey to reuse the structure with a couple of content tweaks. You want to have both sets of data in one tab, with the responses to the new version being added as rows to the end of the original set of data.

You can connect the new survey to the existing Google Sheet for the prior survey but it will add all the responses to this version into that tab. Now you have response data on two separate tabs and you want to consolidate the ‘same set of columns from each’ into one data set.

Here’s what you do in Google Sheets – it might be a bit of a hack but I have used it for a number of clients and for my own data sets:

• navigate to the ‘old tab’ and go to the bottom of the data set
• on the first empty row you can ‘pull’ all the data into the old tab, from the new tab, using the arrayformula() function - which you would enter into the first empty row with the appropriate ‘lookups’ to the new tab to pull the data
  • I use the arrayformula function a lot in report generation and there are a couple of Typeform Community webinars where I show this in operation
• when you put the necessary formula in each cell of the blank row, it will pull in each new record on the new tab - giving you a complete data set on your original tab name - in case you use it in any other downstream processes or integrations.

I’ve shown a bit of an example in the image below: - zoom into the image and you will see the arrayformula() function in the A6 cell. In the example, the rows above the highlighted line were copied into the tab - simulating the old form entries, Then i copied in the line of arrayformula()’s to pull in the data from another tab.

Everything you see below the highlight is pulled from the ‘new tab’ in your scenario.

Now you have a consolidated data set and as each record is added to the ‘new tab’ for the new version of the typeform, it will be added to this data set.

Note – I have not done extensive testing of scenarios where you wanted to include a third tab feeding into this one, using a new arrayformula() line. I have tried a couple of scenarios where I used two closed forms and added the third successfully but if the second form in my example above is still actively receiving submissions and trying to add them into the data set, it fails.

For now, I’m quite comfortable recommending this with one closed form and a live second version pulling data into the same tab in this fashion.

Hope that helps, in case you ever run into this scenario.

Cheers

des

You can find this and other bits over on the website​