I need to enumerate rows in a google sheet to use as simple unique ids. When I add a new row, I want it to get assigned the next number that hasn't been used. The problem is that I need to be able to delete a row, and not have any of the ids change. So if I had rows 1 through 5 enumerated, and I deleted row 3, I would expect to have this:
DATA ID
A 1
B 2
D 4
E 5
I can easily make a function that enumerates numbers for each row, but I don't know how to make that number immutable once created the first time. Also, if I were to delete the last row (ID 5 above) and then add another row, I don't know how to ensure that the new row's id would be 6 instead of a new 5. Thank you.
In my case all I want is to be sure to have unique numbers that won't repeat even if rows or data get added or subtracted. So I just used this formula. In my case I only had a few hundred rows, but you could increase the "1000" to whatever power of ten is going to be bigger than your total number of rows:
=int((now() - DATE(2021, 7, 19)) * 86400) * 1000 + row()
As @MattKing pointed out, this will recalculate if you do something like insert a column before this function, or even just reopen the spreadsheet. So you can tweak the cell to be self-referential. It's not really recursive, but you do have to turn on "Iterative Calculation" for this to work. Go to:
File -> Speadsheet Settings -> Calculation -> set "Iterative Calculation" to "On". Then to be safe, you can set the "Max number of iterations" to 1 since this isn't actually recursive. Then use this cell formula but change both occurrences of the cell referenced in the VALUE() function (AL11 in the example below) to whatever cell this is in:
=IF(VALUE(AL11) > 0, AL11, INT((NOW() - DATE(2021, 7, 19)) * 86400) * 1000 + ROW())