Refining UNIQUEID() expression

Swoopy · 02-21-2020 06:53 PM

This might be useful when you need “unique identifier” generated in sequential order. The alone AppSheet’s UNIQUEID() generates random 8-characters text which may not be suitable for sorting.

The following expressions generate the same unique identifiers as shown in this picture. It supports up to 9,999,999 rows which should be enough for spreadsheet files.

my original clumsy expression

CONCATENATE(
   IF(LEN(TEXT([_RowNumber]-1))=1, “000000”, 
      IF(LEN(TEXT([_RowNumber]-1))=2, “00000”, 
         IF(LEN(TEXT([_RowNumber]-1))=3, “0000”, 
            IF(LEN(TEXT([_RowNumber]-1))=4, “000”, 
               IF(LEN(TEXT([_RowNumber]-1))=5, “00”, 
                  IF(LEN(TEXT([_RowNumber]-1))=6, “0”, 
                     “”
                  )
               )
            )
         )
      )
   ),
   [_RowNumber]-1, “-”, UNIQUEID()
)

or

CONCATENATE(
   SWITCH(LEN(TEXT([_RowNumber]-1)),
      1, “000000”,
      2, “00000”,
      3, “0000”,
      4, “000”,
      5, “00”,
      6, “0”,
      “”
   ),
   [_RowNumber]-1, “-”, UNIQUEID()
)

or

CONCATENATE(
   IFS(
      LEN(TEXT([_RowNumber]-1)) = 1, “000000”,
      LEN(TEXT([_RowNumber]-1)) = 2, “00000”,
      LEN(TEXT([_RowNumber]-1)) = 3, “0000”,
      LEN(TEXT([_RowNumber]-1)) = 4, “000”,
      LEN(TEXT([_RowNumber]-1)) = 5, “00”,
      LEN(TEXT([_RowNumber]-1)) = 6, “0”
   ),
   [_RowNumber]-1, “-”, UNIQUEID()
)

(Thanks to MultiTech_Visions)

or (just THIS tiny expression ! )

CONCATENATE(
   RIGHT(("000000" & ([_RowNumber] - 1)), 7),
   "-",
   UNIQUEID()
)

(Thanks to Steve)

In case you need only the text in the front part, use this second expression instead,

(edited) my messy expression, again

CONCATENATE(
   IF(LEN(TEXT([_RowNumber]-1))=1, “000000”, 
      IF(LEN(TEXT([_RowNumber]-1))=2, “00000”, 
         IF(LEN(TEXT([_RowNumber]-1))=3, “0000”, 
            IF(LEN(TEXT([_RowNumber]-1))=4, “000”, 
               IF(LEN(TEXT([_RowNumber]-1))=5, “00”, 
                  IF(LEN(TEXT([_RowNumber]-1))=6, “0”, 
                     “”
                  )
               )
            )
         )
      )
   ),
   [_RowNumber]-1
)

or

CONCATENATE(
   SWITCH(LEN(TEXT([_RowNumber]-1)),
      1, “000000”,
      2, “00000”,
      3, “0000”,
      4, “000”,
      5, “00”,
      6, “0”,
      “”
   ),
   [_RowNumber]-1
)

or

CONCATENATE(
IFS(
  LEN(TEXT([_RowNumber]-1)) = 1, “000000”,
  LEN(TEXT([_RowNumber]-1)) = 2, “00000”,
  LEN(TEXT([_RowNumber]-1)) = 3, “0000”,
  LEN(TEXT([_RowNumber]-1)) = 4, “000”,
  LEN(TEXT([_RowNumber]-1)) = 5, “00”,
  LEN(TEXT([_RowNumber]-1)) = 6, “0”
),
[_RowNumber]-1
)

(Thanks to MultiTech_Visions)

or (just THIS tiny expression ! )

RIGHT(("000000" & ([_RowNumber] - 1)), 7)

(Thanks to Steve)

However, the second expression may (rarely in some case) return repeating text. So, it should not be used as unique identifier.

(See more details given by GreenFlux, thanks)

MultiTech

I slimmed down your nested IF() expressions using SWITCH():

CONCATENATE(
SWITCH(LEN(TEXT([_RowNumber]-1)),
	1, “000000”,
	2, “00000”,
	3, “0000”,
	4, “000”,
	5, “00”,
	6, “0”,
	“”),
[_RowNumber]-1, “-”, UNIQUEID()
)

Cheers!

Swoopy

Wow ! There are lots of functions for me to check out.

MultiTech

See also:

CONCATENATE(
IFS(
  LEN(TEXT([_RowNumber]-1)) = 1, “000000”,
  LEN(TEXT([_RowNumber]-1)) = 2, “00000”,
  LEN(TEXT([_RowNumber]-1)) = 3, “0000”,
  LEN(TEXT([_RowNumber]-1)) = 4, “000”,
  LEN(TEXT([_RowNumber]-1)) = 5, “00”,
  LEN(TEXT([_RowNumber]-1)) = 6, “0”
),
[_RowNumber]-1, “-”, UNIQUEID()
)

Swoopy

Thanks so much.

I have learned a lot of things today !

Koichi_Tsuji

That is why Appsheet is fun all the time.

Steve

I slimmed down your SWITCH() expressions using RIGHT():

CONCATENATE(
  RIGHT(("000000" & ([_RowNumber] - 1)), 7),
  "-",
  UNIQUEID()
)

Swoopy

Brilliant ! this is even slimmer. Less processing, so clever.

It is about 11:30 PM here. I have no choice but wake up to place this in INITIAL VALUE.

(My old school function for this job is PADL(([_RowNumber] - 1), 7, “0”) but cannot find any similar function in AppSheet)

Steve

There isn’t one.

Swoopy

Your RIGHT trick is better and is the best. Only simple RIGHT expression without any nesting is enough for this !

Brilliant !

MultiTech

Epic good sir

Suvrutt_Gurjar

Wow! Overall an interesting discussion thread with great expressions by all with icing on the cake provided by @Steve

GreenFlux

To expand on this, for anyone else considering using only the number portion:

Keep in mind that the _RowNumber for a given record will change if anyone ever sorts the Google Sheet directly.
And if a row is deleted, the resulting _RowNumber will get reused on another row. If this is a primary key and you have child records involved, those children will get linked to the new row (if not deleted with the original parent).

I think the first expression using this as a prefix to UNIQUEID() is a good solution for creating sequential keys though. But if rows are deleted and a _RowNumber gets reused, then it will sort on the UNIQUEID() portion, which is random text. So the ordering would be close, but not guaranteed sequential unless rows are never deleted or sorted.

Swoopy

Thank you for giving more details of my first post “rarely in some case”. If you do maintenance your spreadsheet table very often, things would not be going good with this solution.

Phong_Lam1

Hi all, what expression should I use If I want to have 2 letters in It at the front so I can Identified the table ?

Somethings like this: 2 letters00001-UniqueID, 2 letters00002-UniqueID

Thanks

SkrOYC

CONCATENATE(
  RIGHT(("LI0000" & ([_RowNumber] - 1)), 7),
  "-",
  UNIQUEID()
)

Although using [_RowNumber] doesn’t seem right for some reason.
I would do:

CONCATENATE(
  RIGHT(("LI0000" & (COUNT(TABLENAME[IDCOLUMN]) + 1)), 7),
  "-",
  UNIQUEID()
)

Or, since I’m a big-expressions nerd:

CONCATENATE(
  RIGHT(
    CONCATENATE(
      "LI0000",
      COUNT(TABLENAME[IDCOLUMN]) + 1
    ),
    7
  ),
  "-",
  UNIQUEID()
)

Phong_Lam1

I tried the expression, It does not show the letters… .Just one I at the front

SkrOYC

Change the 7 for an 8

Phong_Lam1

I Just test again. This time 7 work! Thanks @SkrOYC

SkrOYC

I’m sorry, that won’t work, my bad.
Do this instead:

CONCATENATE(
  "LI",
  RIGHT(
    CONCATENATE(
      "0000",
      COUNT(TABLENAME[IDCOLUMN]) + 1
    ),
    5
  ),
  "-",
  UNIQUEID()
)

Ralph13

Can Appsheet auto generate the UniqueID without having the 8 characters of random characters?

I want like this "0000001, 0000002, ..."

Instead of like this "0000005-23614715, 0000006-b712fb66, ..."

Steve

Serial Numbers, If You Must - Google Cloud Community