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)
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!
Wow ! There are lots of functions for me to check out.
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()
)
Thanks so much.
I have learned a lot of things today !
That is why Appsheet is fun all the time.
I slimmed down your SWITCH() expressions using RIGHT():
CONCATENATE(
RIGHT(("000000" & ([_RowNumber] - 1)), 7),
"-",
UNIQUEID()
)
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)
There isnโt one.
Your RIGHT trick is better and is the best. Only simple RIGHT expression without any nesting is enough for this !
Brilliant !
Epic good sir
Wow! Overall an interesting discussion thread with great expressions by all with icing on the cake provided by @Steve
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.
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.
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
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()
)
I tried the expression, It does not show the lettersโฆ .Just one I at the front
Change the 7 for an 8
I Just test again. This time 7 work! Thanks @SkrOYC
Iโm sorry, that wonโt work, my bad.
Do this instead:
CONCATENATE(
"LI",
RIGHT(
CONCATENATE(
"0000",
COUNT(TABLENAME[IDCOLUMN]) + 1
),
5
),
"-",
UNIQUEID()
)
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, ..."