Answered

how to get array formulas to work in google sheets

  • 26 November 2020
  • 6 replies
  • 432 views
john.desborough
Userlevel 7
Badge +6

Kill this post if you would like but I thought it might be one to start with in the Community in terms of discussion: thought i would post here instead of directly to support to see if this is the type thing that could be discussed here in the community.. 

(the whole workflow process is geared to capturing the data into a google sheet, using Document Studio add-on to merge the data into a google doc template and then push a pdf out by mail to the respondent) 

I am trying to add a calculated column (or series of columns) into the Google sheet and have the field updated with each entry of a new row of data from Typeform BUT it is not working for me despite looking at about 2 hours of google support comments 

what seems to happen is that if i put in a calculated field using the ARRAYFORMULA method to add the calculated value automatically to the field in the new row, the google sheet does not accept any new Typeform results into the table. I have tried removing protection on the table to make it work but even that seems not to work - almost as if the new row data will not write to the sheet due to the fact that the array formula is present in the cell… the records are stored ‘up in typeform’ but are not being pushed down into the google sheet. 

the only workaround that i have found is to create a second tab on the google sheet and use the array formulas and calculated fields to display them there as rows are added to the main tab by Typeform and then use the Document Studio to report off this additional tab. 

is there anything else that folks could suggest or have i managed to find the easiest way to do this.. 

 

icon

Best answer by Gabriel 27 November 2020, 15:51

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

6 replies

Nordin
Rank
Userlevel 5
Badge +3

Hello John, 

I think you have found a good way to do it. Tinkering with the data sheet where we send the results might stop the integration on some cases as you saw. :weary:

Taking that into account, I can confirm that the safest approach is the one you followed by leaving the original data sheet untouched and making all the editions, configs and other customizations in a second sheet. Additionally, I know some users use Lookups to “mirror” the data but I don’t have a video tutorial or anything like that to share now... 

What I don’t know though, is if it’s the easiest. :smile:  It would be interesting to see what the community has to say about this. Maybe we have some G. Sheets guru who can help us out. Definitely a nice topic to have open!

May the form be with you!
Gabriel
Rank
Badge +5

Always good to ask questions @john.desborough - isn’t that what the community is all about? Mind you we are not equipped to give these types of questions a tone of love, but we can try our best to get the right person on the case if possible. @Nordin is as good as it gets though. 

"If you think you are too small to make a difference, try sleeping with a mosquito".
john.desborough
Userlevel 7
Badge +6

@Nordin - thanks for the reply/insight.. it’s not always easy to find the ‘best workaround’ but sometimes Occam’s Razor leads to the most expeditious result.  The video/tutorial about how to create the conference passes was the root of my attempt to make it all work, but there are some of the additional feature/steps that were required to make the workflow to the output report being able to be delivered in the email of the respondent. And it was a whole lot simpler to use q1, q2 etc as the column headers to link to the Document Studio workflow than the full ‘question as header’ text.. 

 

@Gabriel - Thanks.. the shape of a community forum will morph over time as more enquiring minds get involved and engaged on pushing the envelope of the product as well.. appreciate what you folks are doing to make this possible

 

des

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

Will get there, @john.desborough, I promise. 

Would you be able to share that video tutorial? Our video guru @suzieq is eager to get feedback on these so anything you can share with us will be helpful., 

"If you think you are too small to make a difference, try sleeping with a mosquito".
john.desborough
Userlevel 7
Badge +6

@Gabriel @suzieq 

 

here is the starting point help link, from whence I started my odyssey: https://help.typeform.com/hc/en-us/articles/360029617031-Create-professional-looking-event-badges-by-merging-typeform-answers-in-a-PDF

it’s in part 2, with the Document Studio integration, that there are some ‘tips and findings’ that could be added in the Google Sheet portion (ie use another tab for the report out calculated fields etc. and the arrayformula stuff).

 

this would really assist folks who are interested in getting the data from the Typeform ‘survey’ and presenting a report back to the user - say like a maturity model matrix report - and would suggest a separate help article that uses the same principles as the conference badge report to extend out to this type of reporting.. 

 

just my 4th coffee speaking hypothetically of course.. 

 

des

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

this is a really cool idea @john.desborough We’re looking to create more help center videos that are use case based so this could be a good option for a new video! 

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs