Refining UNIQUEID() expression

capture

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 :laughing:

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 ! :bowing_man:)

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 :smile:

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 ! :bowing_man:)

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)

5 Likes

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!

2 Likes

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

1 Like

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.

3 Likes

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()
)

:nerd_face:

2 Likes

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.

2 Likes

Thanks so much.

I have learned a lot of things today !

1 Like

That is why Appsheet is fun all the time.

3 Likes

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

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

:smiley:

11 Likes

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. :dizzy_face:

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

1 Like

Epic good sir
Cheers

5 Likes

There isn’t one.

1 Like

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

Brilliant ! :bowing_man:

2 Likes

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

3 Likes