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

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

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, lets 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.

You can apply unique ID numbers using an arrayformula next to the form data. In row 1 of the first rightmost empty column you can use something like

=arrayformula(if(row(A1:A)=1,UNIQUE ID,if(len(A1:A)>0,98+row(A1:A),iferror(1/0))).

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

Leave a Reply

Your email address will not be published. Required fields are marked *