How to assign a unique ID to a google form input?

Question

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.

Answer 1

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:

1. Create an additional column in the spreadsheet linked to your form, let's call it: "unique ID"
2. Row number 1 should be populated with column titles automatically

3. 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.

4. Make sure you never delete that row, even if you clear up all the results from the form
5. 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.