I work for a charity that offers free coding classes to children from low income families and we use type form to collect applicants for the classes. The class sizes are limited so I utilize the response limit so that the classes don’t get oversubscribed. Sometimes a parent will sign up their student two or three times for the same class (I think it’s a language barrier issue) which means I sometimes need to delete responses so that other students can fill those spots instead.
I am using a google sheet integration and although new responses are added to the sheet, when I delete a response, it is not deleted from the sheet. I would like to delete the response from the google sheet but when I do I get a warning message that I should not edit the data. I have had issues with the integrations turning off by themselves and I am wondering if this is why or whether it is ok to delete a response from a google sheet without it affecting the integration.
Thank you!
Andy
Best answer by john.desborough
@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
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
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.
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
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
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
Let me know how it goes.
