How to assign a unique ID to a google form input?
google-forms, google-sheets
Solution
I was also banging my head at this and finally found a solution.
I compose a 6-digit number that gets generated automatically for every row and is composed of:
3 digits of the row number - that gives the uniqueness (you can use more if you expect more than 998 responses), concatenated with
3 digits of the timestamp converted to a number - that prevents guessing the number
Follow these instructions:
- Create an additional column in the spreadsheet linked to your form, let's call it: "unique ID"
- Row number 1 should be populated with column titles automatically
In row number 2, under column "Unique ID", add the following formula:
`=arrayformula( if( len(A2:A), "" & text(row(A2:A) - row(A2) + 2, "000") & RIGHT(VALUE(A2:A), 3), iferror(1/0) ) )`
Note: An array formula applies automatically to the entire column.
- Make sure you never delete that row, even if you clear up all the results from the form
- Once a new submission is populated, its "Unique ID" will appear automatically
Formula explanation:
- Column A should normally hold the timestamp. If the timestamp is not empty, then this gives the row number: `row(A2:A) - row(A2) + 2`
- Using `text` I trim it to a 3-digit number.
- Then I concatenate it with the timestamp converted to a number using `VALUE` and trim it to the three right-most digits using `RIGHT`
Voila! A number that is both unique and hard-to-guess (as the submitter has no access to the timestamp).
If you would like more confidence, obviously you could use more digits for each of the parts.
Problem
Google Forms - I have set up a google form and I want to assign a unique id each of the completed incoming form inputs. My intention is to use the unique ID as an input for another google form I have created which I will use to link the two completed forms. Is there another easier way to do this? I'm not a programmer but I have programming resources available to me if needed.