Hi @apstrainingacademy. What a great initiative!
Regarding your question, any changes you make to the form or sheet can break the integration. I'd advise you do not delete it. Maybe hiding the line where the duplicated response is would work?
P.S.: We do offer discounts for NGOs! If you qualify for that and haven't requested your discount, just head over here for more information and how to request it.
Hope this helps
Let me know how it goes.
@apstrainingacademy - great initiative, from another user!
Just a thought to consider relative to the Google Sheets issue:
- for all the integrations that i have to GS, I create a second tab to pull the data from the primary ‘data dump’ tab - using the arrayformula() function that filters the data pulled into the next tab
- this allows me to change column names to something more usable for reporting and to do calculated fields and other transforms and lookups to other tabs
- this also allows me me to purge data from the second tab without worrying about error messages from data landing on the main integration page with Typeform.
While that may not work for you, it does let me do what i need to do when i need to process data periodically.
and before my second coffee, so it may not make sense lol
des
Thank you both!
Quick question on your point John.
I have been using a QUERY formula to pull in the original data into a second sheet but as you probably know you cannot delete data that is being pulled in by a query as it simply repopulates into the next row (to my knowledge anyway) hence why I have been deleting it from the integrated sheet.
Question: Does this arrayformula allow you to delete data from the second sheet while it still remains in the original or do you have to do multiple array formulas around the original data you want “delete”?
e.g. if I want to exclude A3….array(A1:A2) and array(A4:A5)
@apstrainingacademy - you can embed if() statements into your arrayformula statements which allow you to “filter” which records you want to bring across. here’s a description of what i have done:
- on the main form (typeform dump) i added one column outside the range of typeform called “pulled”
- on the report tab (second tab in mine) i use the arrayformula to check IF there is a “ “ (blank) don’t do anything but if there is a value in A2:A (range) then do something else
- arrayformula(if(isblank(A2:A),"", something else...)
- the ‘something else’ includes checking the ‘pulled’ field on the first tab to see if i have already pulled the data into the report tab for processing (i use ‘yes’ but you could use a 1/0 indicator as well)
- this allows me to check to see if it’s a new record or one that i have already pulled across into the report tab and allows me to just grab the new data
- i also capture the row number of the record on the main tab as a field element for the report tab (more in this in a minute)
- once i do whatever i want to do in my reporting process ie if you want to use the form to collect the results of a survey in a class/webinar and want to clear out the data,
- i have a macro/trigger that i can fire that uses the row number field on the report tab (along with the ‘token’ field -which i pull into the report tab) to go check for row number and token to match and to set the ‘pulled’ field to yes
- this ‘preserves’ my audit record of the typeform entry/dump page
- once the records on the dump tab are marked as pulled and i am done my processing, i can remove all the data from the second reporting tab - i can do it manually or fire a macro
- the arrayformula functions will look to add any new rows automatically but because of the filter including the pulled field, i won’t get any of the already processed records “back” into the reporting tab
hope that makes sense
des
Yes that makes sense! Thank you for taking the time to explain all that! I am quite sure this will solve the issues I’ve been having! Thanks again!
@apstrainingacademy - good! i was worried that 4 coffees wasn’t enough to make it sound rational. lol.. let us know if it works for you!
des
@john.desborough Four coffees is barely anything! 
