BUG: UNIQUEID() generated Exponential Values - issue has returned after previous fix.

AppSheet AppSheet Q&A
1

When a value is generated by UNIQUEID(), sometimes it will match a numeric exponential notation - e.g. "12345 e 12".

If the sheet column is set as “Automatic”, the sheet will recognize this value as exponential notation upon saving and translates it to “1.2345E+12”. Note: this translation does NOT happen when the value is copy/pasted into a sheet cell UNLESS the format is re-applied.

When AppSheet later retrieves the translated value, it not recognized as a valid key value.

NOW…I thought AppSheet had resolved this potential issue in some way so we don’t have to constantly update the sheet default format on each and every table column we add that stores a key inside each and every app.

Is that not true? Or did they and the problem is back again?

EDITED: Searching again I did find an older post about a fix for this:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/New-Bug-Encountered-UNIQUEID-changed-to-scientific-notation-by/m-p/362919

I haven’t seen this issue for years until today!

2 Likes
2

I don’t have this particular issue, but as far as I recall, this was never solved.
If you ask me, this may be a GSheet problem rather than an AppSheet one, and also I think they expect us to change the cell formats on sheets based on the data that’s going to be saved, so in this case changing the field to Text for PK/FK is a must

3 Likes
3

I also don’t believe this was ever fixed, and that it’s not an Appsheet issue, but a spreadsheet issue.

Formatting the full table to “plain text” from the get-go has been my SOP for a long time.

5 Likes
4

Good to know. I have always been concerned that doing so might impact other column types. I will probably apply this going forward

I was successful searching again for the post concerning the fix for this (I thought there was one). Here is the link to that article with a reply from Google staff:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/New-Bug-Encountered-UNIQUEID-changed-to-scientific-notation-by/m-p/362919

I’ll open this to AppSheet as a bug.

2 Likes
5

Oh yah, I remember that now. :grinning_face_with_smiling_eyes:

1 Like
6

I also recognize that I am somewhat contradicting myself in that older post where I suggested that we, the app developer change the sheet columns to a suitable type.

But, man it has been nice to not have to worry about doing that at all!!

7

As a general comment, this seems to work fine for english-focused apps but not for all languages.

In my case (spanish) it saves the data in US format instead of my local format when the cell is formated as Text even if both the sheet and AppSheet table config is setup correctly.

So I just make sure to format cells on the most-compatible way

3 Likes
8

Please upvote here, although this request not really collecting the interests from anyone.

https://www.googlecloudcommunity.com/gc/Feature-Ideas/UniqueID-expression-to-avoid-text-which-is-equivalent-with-the/idi-p/473838

1 Like
9

Hey @Koichi_Tsuji !

Thanks for drawing our attention to your post. I upvoted!!

I have also submitted something to AppSheet Support.

In light of the fact that this was already addressed previously, as noted in the link I provided above, I believe this problem should be treated as a regression bug getting immediate attention.

This issue is infrequent but it can cause major problems if it occurs under the right circumstances.

It is probably not feasible to go through all the sheets used in all the apps from the past couple years to ensure this issue doesn’t occur. So it seems the best fix at this point is for AppSheet to re-instate its solution to the UNIQUEID() function.

1 Like
10

Note that “fixing” this on the AppSheet side substantially reduces the number of possible unique IDs that can be generated, thereby increasing the risk of duplicates. I agree that this is a spreadsheet problem, not an AppSheet one.

2 Likes
11

For a little fun…

When considering the actual count of values that need removed, it seems like its substantial. But since we are only concerned about removing the results with the letter “e” in the second position from the right, the overall difference is actually small:

For an 8 character Hex key (produced by UNIQUEID()), the difference in the number of combinations is:

16 x 16 x 16 x 16 x 16 x 16 x 16 x 16  --> Total possible combinations = 4.294967e+9
versus
16 x 16 x 16 x 16 x 16 x 16 x _**15**_ x 16 --> Disallowing letter "e" = 4.026532e+9

Exponential Notation Pun fully intended!!

In other words... 4,294,967,000  versus 4,026,532,000

Because we lose precision with Exponential Notation, actual values are:

4,294,967,296 versus 4,026,531,840

Remember that we CAN re-use key values between tables, ao all the above means we can still have 4,026,531,840 happy co-existing rows in our table even if we were to ban the letter “e” from one of the key value positions.

Now for more serious stuff…

I agree this is not an AppSheet problem. Its a Google Sheet problem! And who was the idiot at Google Sheets that decided it was a good thing to automatically convert values into Scientific Exponential Notation? Especially considering 1/2 of 1% of all scientists in the world actually use the damn notation!! (I made that last part up).

It is manual intensive to try to keep the sheets formatted to avoid this problem. I just ran into it today trying to fix some raw data by manually copying into a new sheet, fix and then copy back. Existing key values were converted. Luckily it’s never very many that need to be corrected.

And considering that UNIQUEID() is just a special purpose function controlled by AppSheet, it just makes sense to help prevent the issue in the first place and put back the code that disallowed the problematic key values.

On a side note, I did submit to AppSheet support with the Link from AppSheet staff about resolving this. Support asked me for the app and steps to re-create! Huh??? Somehow I don’t think a support person is going to generate 10,000 rows into a sample app until the Exponential Notation problem happens!!!

3 Likes
12

So that’s about 260 million possible values that we’d lose.

I’m far from an expert with combinatorics (if that’s even the right term), but I’m seeing a different way to calculate it. I calculated it the other way, by counting up the number of combinations that would get us into trouble. Basically, any number, then an e, then any other number. So that’d be 10 possible values across 7 positions, multiplied by 6 different positions that an e could be in (i.e. not beginning or last position). 10^7 * 6 = 60 million. Did I do it wrong?

2 Likes
13

Btw, if we are on this, why not UNIQUEID("UUID")? I’m serious, that’s my de facto for keys since like 10 apps ago, wrapped in LOWER()

3 Likes
14

FWIW, here’s my results from directly typing values into Sheets columns formatted as Plain Text and as Automatic.

dbaum_0-1675396175835.png
dbaum_0-1675396175835.png221×234 3.59 KB

2 Likes
15

I was thinking about this, that it was not just the 7th digit, the 6th could also generate problems

2 Likes
16

Yes. But its close!

First as pointed out by @dbaum below, there are 2 positions where the “e” can create the exponential notation bit. I didn’t realize and is good to know.

Second, it’s not 10 possible values at each position, its 16. The keys are Hex - so 1,2,3,4,5,6,7,8,9,a,b,c,d,e,f are the possible values for each position.

To calculate values where “e” IS always in a position (static), you simply calculate the combination for all the other positions. In this case 16^7 ==>> 268,435,456

Since we now know there are 2 positions where “e” causes a problem, the number of lost combinations is double - one or the other but not both. The calculation is 2*16^7 ==> 536,870,912

The number of allowed combinations WITHOUT “e” is now ==>> 16^6 * 15^2 ==>> 3,774,873,600

I still think that is PLENTY of row key values for a single table. Do you not?

HOWEVER, just so everyone is aware, there is a performance concern if the pool of possible keys shrinks too small.

I am fairly certain AppSheet tests a generated key against the table to make sure it doesn’t already exist. If it does then, the value is not accepted and a new value is generated. If the pool of possible keys shrinks small enough, the app may need to regenerate a value several times before finding a good one. That can lead to performance problems and gets worse as the table row size continues to grow.

17

[quote=“WillowMobileSys”]
First as pointed out by @dbaum below, there are 2 positions where the “e” can create the exponential notation bit. I didn’t realize and is good to know.

Second, it’s not 10 possible values at each position, its 16. The keys are Hex - so 1,2,3,4,5,6,7,8,9,a,b,c,d,e,f are the possible values for each position.
[/quote]> I still think that is PLENTY of row key values for a single table. Do you not?

  1. dbaum’s test looks…odd. A very small sample size, doesn’t really make any sense, doesn’t match my previous experience. Not convincing of anything. I’m sticking with there being 6 positions of ‘e’ that can cause issue.

  2. Yes, the number of possible values is 16, but the number of possible values in keys that we would remove, is only the 10 integers.

Yes, I think it’s still enough values, even is the amount removed is your larger 260 million value.

1 Like
18

More than a test, it was just showing that “e” could cause problems on either the 6th or 7th position, so “12345e99” could cause them

1 Like
19

Btw, my 2$ for the math class.
@WillowMobileSys is correct when it comes to total amount of combinations.

16 posible values with 8 characters is 16⁸, so in this case, knowing that an e causes problems on either the 6th or 7th position, it would be 15²x16⁶=3.774.873.600

20

I can’t say I know for sure but I’ve never noticed it with the “e” in other positions.

Oh yeah, I see what you mean. It would be a lot fewer actual problem combinations. But I read that AppSheet was just testing if the “e” appeared in the position, then they skipped it. I was basing my calculation on the probable (easy) implementation. It would be complicated to try to skip ONLY the actual problematic values.

1 Like