Answered

Can you delete responses from an integrated google sheet without messing up the integration?

  • 23 June 2021
  • 7 replies
  • 466 views
A
Userlevel 1
Badge

Hi,

 

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

icon

Best answer by john.desborough 24 June 2021, 18:26

View original

7 replies

Mariana
Rank
Userlevel 7
Badge +5

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.

Smile – sunshine is good for your teeth.
john.desborough
Userlevel 7
Badge +6

@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

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

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)

john.desborough
Userlevel 7
Badge +6

@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

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

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!

john.desborough
Userlevel 7
Badge +6

@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

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

@john.desborough Four coffees is barely anything! :joy::rofl:

Life is a series of awkward moments separated by snacks.

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs