1

I have a spreadsheet that gets its data from a Google form. I use a formula I found on a forum to generate a unique ID and assign it to every entry avoiding repetition.

=arrayformula(if(len(B2:B);""&text(row(B2:B)row(B2)+2;"000")&RIGHT(VALUE(B2:B);3);iferror(1/0)))

The problem I have is that this ID is not permanent and changes every time I add or delete a row in between the first and last entry.

Could anyone help me? I want to avoid the ID to change every time I modify or delete a repeated entry.

Improve this question
3
  • Hi and welcome to WebApps. You say The problem I have is that this ID is not permanent and changes every time I add or delete a row in between the first and last entry. Would you explain why you add or delete rows between the first and last entry? Would you also explain whether this formula is linked directly to the Form Response sheet, or to a copy/paste version of that sheet (maybe copy/paste triggered by onFormSubmit)? Would you also provide some examples of the data in Column B?
    – Tedinoz
    Commented Feb 9, 2022 at 23:08
  • I'm surprised that your formula works. For example, RIGHT(VALUE(B2:B);3): VALUE converts a string into a number but RIGHT returns a substring from a string. So Right(Value) is a contradiction; and row(B2:B)row(B2)+2 I can only assume that's a type because the inclusion of row(b2) generates an error. Was this your original source Unique ID Column?
    – Tedinoz
    Commented Feb 9, 2022 at 23:28
  • 1
    As Tedinoz has alluded to, there are a host of factors involved here, factors without which it would be virtually impossible to suggest an effective solution. If you'd like, share a link to the spreadsheet (or to a copy of it). That will be the most efficient means for the volunteer contributors here to figure out both what you have as a starting point and what solutions may work given that information.
    – Erik Tyler
    Commented Feb 9, 2022 at 23:50

1 Answer 1

Reset to default
1

The problem I have is that this ID is not permanent and changes every time I add or delete a row in between the first and last entry.

The reason that you have this problem is that the Unique ID is generated by a dynamic formula. Inserting or deleting a row will, by definition, cause the formula to recalculate, and can modify all the existing IDs.

The solution is not to use a formula to determine the ID, but to write the ID into the spreadsheet. There is an example of the code to do this in Unique ID Column.

In your case, you have chose to build your unique ID based on a row number. This is fraught with problems because, as you know, row numbers can change as rows are inserted or deleted. So the risk that an ID is not unique is substantial. I'd recommend looking at using some other attribute of your data, or simply keeping running record of numbers and assigning the next number (the last number plus one) as (part of) the unique ID.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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