Google Sheets converting UNIQUEID() ref keys ...

expressions
(Michael) #1

Google Sheets converting UNIQUEID() ref keys to scientific notation…

Has anyone experienced GS converting a parent ID to scientific notation when a new related row is inserted in a related child table?

Until now, I have always used UNIQUEID() to generate IDs for all my tables and until now, this has worked without issue. Now it seems that GS is converting any IDs containing “E” to scientific notation. The ID 323290E3 will be converted to something like 3.23E+08 when creating a child row related to 323290E3.

Has anyone run into this and found a reliable workaround? I think the following expression should workaround the issue: =TEXT(SUBSTITUTE(UNIQUEID(),“E”,“A”))

(Steven Coile) #2

Set the Google Sheet cell format to Plain text, preferably before storing any IDs.

(Michael) #3

+Steve Coile Yeah, I thought that should work but, it isn’t. I discovered this problem a few days ago and changed the column format then. This issue continued even after making the change to the column format from automatic to plain text. It seems that any IDs containing an ‘E’ followed by a number causes the conversion.

(Steven Coile) #4

Hmm… In my experience, the plain text format prevents the reformatting of apparent scientific notation, but there is no way to automatically correct reformatting that has already occurred.

(Michael) #5

+Steve Coile When I first discovered the issue, I manually corrected the foreign IDs then set the formatting for the column to plain text. I assumed this would prevent the issue from re-occurring but apparently not. With the column formatted as plain text, somehow, IDs were still being converted…