1

I have a google form to capture order information, When the user captured all order information, it pushes to a google sheet so we can keep an order history of who ordered what, when and how much.

I have about 120 rows in my google sheet, but from row 121 on wards I seem to be getting random duplicates.

I have asked the users of the sheet if they are re-capturing orders that have already been captured, but they say they have not.

Looking at the time entries of the duplicates, it does not duplicate immediately 07/05/2019 16:41:32 and then below it is the exact SAME entry, but time stamped at 09/05/2019 08:45:41

Why would this happen?, how would this happen?, how do I stop it from happening?

I am aware I can just sift through the google sheet and remove duplicates, but I have connected this google sheet with a telegram bot that every hour scans the sheet and forwards the latest row to a group so that sales know when orders went out to customers, so the duplication is filtering through to my telegram group as well sending duplicate information to that group and adding some confusion so I need to sort this out.

2
0

I have been searching for an answer for this exact problem for a while. I am using form for the same reason and this can be very confusing or at least annoying when there are one or more duplicates.

If you couldnt find any solution to this problem I recommend you to use a duplicate showing conditional formating formula and every time it captures a duplicate entry delete the row completely.

I know it does not solve the issue but it may help you a bit.

0

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

If you dont have any app script or triggers running then i am sorry to say that the Timestamp clearly shows its a manual submitted duplicate user entry - the google form wont submit an entry one fine morning 2 days later all by itself without user intervention especially if you have no code pre filling form values or a script that copy pastes entier rows after data is submitted- this is the most common absent minded human error you will see form a person who is not doing their job right wherein they have entered the info on the 7th & then absent mindedly entered the very same data on the 9th. We have had the same issue at our office before. The form was meant to ensure that the users had limited access to the google sheets as we had people who unintentionally kept deleting rows collumns & unlocked formulas on our shared sheets - but then they found a new way of messing up by making duplicate entrys especially when the have forgotten they have entered certain job numbers already.

This happens if there is no alternate tracking, coordination & reconcillation mechanisim especially when you entrust someone with critical data & cross your fingers hoping they wont mess up your hard work -belive me they will - but it is our job to make it difficult for them to mess up by using the available checks & measures & at the same time it is also our job to cross check their work to avoid double the job for ourselves.

The simplest way to solve this problem you have is - if they are entering customer order information via the app they should also have a manual cross refrence sheet in their hands with that very same data written down, they can just cross out the manual data sheet for the customer data they have already entered that way they wont make the mistake of making a duplicate entry of the very same data two days later.

If you need to know who is entering the duplicate data just incase the orders are not linked to any specific agent simply change the form settings to capture email addresses for each users form submission & you will have your culpurit.

first thing dont work on the actual form responses data sheet get the data to a new sheet and then work on it (see more below on why not to do this & what to do instead)

To make sure there really isnt any technical error with your sheet, which i doubt, make a copy of the google sheet & test out the form from the test sheet with form submissions with test data for a couple of days that way you can be sure that the sheet & the form is working fine

If this human error still cant be avoided just get rid of duplicates and use the corrected duplicate free data.

On your new data sheet - Highlight duplicates & delete.

You can do this by

A) conditional formatting > highlight duplicates > delete duplicates

b) pivot table > collate data> filter & ignore duplicates data > use filtered usable data

c) google app script > search & delete duplicates cells or rows etc

You can either do this manually if you dont have a lot of data to colate

Working directly on the actual google forms responses sheet is not advisable as google forms will overrite any preexisiting data and replace it with the form responses answers - be it text formulas or even cell formatting - theres a simple fix for this - do not work directly on that sheet - just bring that data to a new sheet & then your codes & formulas will work just fine be it scripting code or simple vlookup formulas to colatlate your data.

Heres a simple workaround solution:

I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.

With this solution i will show you - you get two benifits

1) you can retain any cell formatting you want - this method allows you to do that conveniently

2) and most importantly you can use formulas safely without the fear of the formulas being deleted on each new form response entry - as you are aware google forms creates a new entry on the next available row irrespective if you already prefilled formulas in any other collumn or rows on that response sheet - thsi is especially usefull if your form collects numerical data that needs to be summed up etc. & needs to be used for pviots & charts in real time

the solution is simple - on a corresponding new sheet just use "=arrayformula" & refrence the whole form responses answer sheet data range.

What you get on your new sheet is the exact replication of your form responses data sheet that will autoupdate with each new form response & with the added benift of using unlimited formatting & unlimited formulas without the hassel of losing the same by google forms overwriting rows & deleting formatting & deleting formulas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.