Google Sheets - How to add calculations to a spreadsheet without breaking the Typeform integration

  • 9 March 2021
  • 3 replies
  • 658 views
Lasse
Rank
Userlevel 3
Badge +1

Hi there, 
Lasse from Typeform Support here. 

When integrating a Typeforms to a spreadsheet like Google, adding calculations to the tab where the form is integrated often break the integration. 

We recommend to always work on the data in another tab in the sheet. 

To showcase a way to do this, I have created the video below(6 min) where I use Google Sheets own =ARRAYFORMULA() and =IF() methods. 



In the video I also show a way to change data from True/False to a number (1 or 0) using the =IF() method. 

3 replies

john.desborough
Userlevel 7
Badge +6

@Lasse - thanks for this .. it does address a number of the questions that folks have posted here in the Community. 

There are also some additional things that Google Sheets allow you to do in addition to the ARRAYFORMULA and IF() functions - including vlookups - that are not always the easiest to uncover when you are starting to stumble through using Google Sheets. I have had to find a few ways to address some of the issues across multiple tabs to assemble data onto one tab (ie the MyData tab in your video) that include things like the following:

  • how to nest IF() statements within the ARRAYFORMULA 
  • how to use AND/OR logic statements in the ARRAYFORMULA - it is different that a ‘regular’ Google Sheet “if(A) and (B) THEN do C.. 
  • nesting vlookups into an IF statement to pull in data from a field on another tab 

Maybe these could be the ‘next tutorial’ video you can address for the crowd!!

thanks

 

des

 

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
Lasse
Rank
Userlevel 3
Badge +1

Hi @john.desborough 
Those are really great suggestion. 

I will definitely look into adding these suggestions into another tutorial. 

Thanks for the feedback

john.desborough
Userlevel 7
Badge +6

Hi @john.desborough 
Those are really great suggestion. 

I will definitely look into adding these suggestions into another tutorial. 

Thanks for the feedback

@Lasse - it took me a while to figure out and search Mr. Google to find some of the answers required to make them work.. 

the logic statement below is one of the bits i finally figured out at around 2am one morning, that combines all of the above issues (except for the vlookup… which happens in another part of the sheet lol)

=arrayformula((if(isblank(S2:S), " " ,if(S2:S<=17, 'results '!$A$2, if((S2:S>17) * (S2:S<=31),'results '!$A$3, IF((S2:S>32) * (S2:S<47),'results '!$A$4,IF((S2:S>=47),'results '!$A$5,"des error")))))))

 

des

 

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs