Generating shorter unique ID for every user

:exploding_head:

@Darren_Murphy @ThinhDinh

Kind of did a bit of reading and came up with this

={"Code";ARRAYFORMULA(IF(A2:A="","",DEC2HEX(RANDBETWEEN(0, 9999999), 6)))}

Now this generates a 6 digit code

BUT

Its giving same value for all rows - which is bizzare !

And every refresh of the sheet or deletion or addition of row - all the value changes !

I feel some tweak on the above can make it static and unique - ?

Edit: Assume that what i am checking in the Column A is the presence of username in the userprofile
sheet

EDIT2 - Tried the ISBLANK - same behaviour - if i delete or add row then value changes

={"Code";ARRAYFORMULA(IF(ISBLANK(A2:A),"EMPTY",DEC2HEX(RANDBETWEEN(0, 9999999), 6)))}

-Shiv

For your use case I would steer clear of a spreadsheet solution and instead use the Glide computed columns approach that I offered in that other thread. The problem with the spreadsheet solution is that values will change if a row is ever deleted.

With my approach, the values are locked in once they are set. The only downside is that you have to “kick start” it by setting the first value manually.

@Darren_Murphy Yes i get that - i can probably use something like a serial number and then use the forumula you have shared. But this would be a numerical value.

I don’t want it to be a “Number only” and also It would be good if its random than a series for every new user added.

Also the solution you have mentioned also has to be at Sheet level right not Glide table ?

-Shiv

@Darren_Murphy You meant this one ?

Sorry, I was on my mobile just now, so a bit fiddly to provide a direct link.
This is the approach I’m referring to:

This is done purely in Glide - no spreadsheet formulas required. And the same general approach can be used to generate any variation of an alphanumeric code that you like (with the numeric part auto-incrementing). For example, you could have something like:

  • USER001
  • USER002
  • USER003
  • etc…

The “USER” part would be fixed, with the numbers auto-incrementing.

2 Likes

A bit of playing with the formating ( in spreadsheet ) and came up with this UniqueID

Its def not random and but its definitely Unique. Might not be the solution that i wanted or will use. But something easy to achieve for testing purpose on the sandbox.

I think its easy to guess what i have done there :wink:

-Shiv

1 Like

All that i have done is … Sheets > Select Date Column > Format > More formats > More date and time formats

Can reduce it to only YYYYHHMMSS to make it 10char but that would make it too obvious. So played around with jumbling the order of the Y M D H M S to get something not so obvious and usable.

But would continue to look out for better solution if available ( Who knows Glide might introduce a RAND option in the custom column in Glide tables as a Christmas present )

-Shiv

I think i am spending way too much time on this thing today which i could have spent in a better way :wink:

-Shiv

Hi,

I am looking for the generator random number for Barcode as well .

So you are using the formula in Google Sheet isn’t?
Can I know, is there any delayed ?

1/Does it need to be in the Google Sheet?

2/What type of information do you want to have in the barcode?

  1. Yes, so the last option is going to use the google sheet formula.

  2. Only unique 13 numbers .

I imagine if you have it sequentially then it can be an arrayformula, else you might have to find an external service or write some JS.

I see, so i’m going to use this formula.

image

So if you delete a row, then the numbers in all the rows below it will change. Does that matter?

1 Like

OOOOOOOOOO. Yes its a problem. ooo ooo yes yes its cannot be.

Take a look at my first option below:

I think that can work for your use case.

1 Like

Oo your case 2, same method with me doing now but using glide . i see

With the second case you have the same problem. If you delete a row, then all the numbers change. Also, with the second case the values will not be in the Google Sheet.

I think the first case is better for you :wink:

Exactly, thank you darren . let me try it first.