New Bug Encountered: UNIQUEID() changed to scientific notation by Google Sheets' auto-formatting

GreenFlux
Participant V

A few weeks ago I had an app with just one record that was behaving differently than the others. It kept losing references to child records. When I viewed the Google Sheet, the UNIQUEID() value had been auto-formatted in scientific notation, because it just happened to meet the format of [number] E [number], like 421e24.
2X_1_1a5829ff5d1a2b369ed40a0dc930779aabed1e77.png

I manually changed that one ID, and updated references in child tables. I figured the chances another key meeting that format were low enough that it wouldnโ€™t be an issue. Then it happened again today.

This could be corrected by appending the UNIQUEID() with an &โ€œxโ€, or some other character. Or you could turn off the default auto-formatting option in the Google Sheet. However, both these fixes require the developer to deploy the work-around in every app.

Since the default Google Sheet settings will interpret AppSheetโ€™s UNIQUEID() improperly, I recommend that this be considered a bug. It will affect many new users who wonโ€™t think to deploy either of the fixes mentioned above.

Please consider updating the UNIQUEID() function to always end in a letter, or some other method of avoiding text strings that can be interpreted as scientific notation-- suddenly breaking the app due to the value UNIQUEID() returned.

Solved Solved
2 21 2,064
1 ACCEPTED SOLUTION

A fix for UNIQUEID has been put in place where any values it produces that look like scientific notation will be regenerated. One other case was also found and fixed where values could have leading zeros (such as 00001111) and Google Sheets or Excel would remove the leading zeros in those cases. That case has also been fixed. This fix should roll out early next week.

View solution in original post

21 REPLIES 21

Hayden
Participant III

Great points! Iโ€™ll relay this issue to our engineers, Iโ€™d also encourage you to repost the above in the Feature Requests / Bug Report sections of the community.

Iโ€™d also encourage you to repost the above in the Feature Requests / Bug Report sections of the community.

Actually, I thought I had posted to the Bug Report section, because thatโ€™s where I started the draft. Turns out, thatโ€™s another bug!
https://community.appsheet.com/t/tags-on-the-forum-pages-introducing-this-forum/17155/10

Thanks for pointing this out, @Kirk_Masden!

Solution from @Steve

Of course, I think the task that this expression performs (that is, placing a letter from the alphabet before what is generated by uniqueid() ) should be a default feature of the expression โ€“ we shouldnโ€™t have to learn this workaround.

Yes I also saw that itโ€™s deleting the Bug Label automatically.

I have received the bug report. I am investigating how best to fix this problem.

A fix for UNIQUEID has been put in place where any values it produces that look like scientific notation will be regenerated. One other case was also found and fixed where values could have leading zeros (such as 00001111) and Google Sheets or Excel would remove the leading zeros in those cases. That case has also been fixed. This fix should roll out early next week.

Great! Thanks!!

Thanks @hugheshilton!

Unfortunately, Iโ€™m just finding that I have this issue in my data that was being collected prior to the fix being put in place in December. How should I go about finding correcting those records that were affected?
a) It is easy enough to FIND โ€œE+โ€ in the data. But how to I find where leading zeros were dropped?
b) Once Iโ€™ve found the โ€œE+โ€ scientific notation, how to I correct the data to ensure that my cross-reference (foreign-key) records in other tabs in my spreadsheet?
c) Once Iโ€™ve found where my IDs have missing zeros, is there a way to know how many missing zeros should be added back?

A UNIQUEID() value is eight characters long, so add enough leading zeros so the value is that long.

@hugheshilton It looks like this bug is back.

2X_b_b0024f69298a6efa5d4be0bbde134a620d824406.png

This table was generated using the โ€˜Start with an ideaโ€™ app generator, if it makes a difference.

Hmm if the table was generated by the Start with an Idea tool, then it probably is an unrelated bug. I donโ€™t think the data the tool generates goes through the same code path as the actual UNIQUEID function although I could be wrong. Probably @tony would know.

I never bothered to remove the following formula:

mid(โ€œabcdefghijklmnopqrstuvwxyzโ€, randbetween(1, 26), 1) & uniqueid()

Perhaps that was a good thing โ€“ just as insurance. As long as thereโ€™s a letter at the beginning, the error @GreenFlux pointed our wonโ€™t occur.

tony1
Participant V

Hi. @hugheshilton is correct. This is because those unique ids are being generated from Spec, which uses a different method for creating the unique ids. The output of UNIQUEID() should be fixed.

Iโ€™m coming into this a little late.

I had noticed quite a while back the issue of scientific notated values in the ID columns. I fixed it by setting the data type of the column to โ€œGeneral Textโ€ in the sheet. This is analogous to defined data types in a database.

Wouldnโ€™t it be a best practice to simply set the data types of the columns in the sheets instead of coding around it?

Am I missing something?

Yes!

Coding around it will ensure the value is stored correctly across all sheets, in other columns where the key is stored as a Ref. I preferred this approach because you only have to apply it in one place.

Whereas, adjusting the column setting would be required in the main sheet and every Ref column, and would require making the same adjustments on every new sheet or Ref thatโ€™s added along the way.

Neither approach should be needed now, though. I guess this bug is only in the โ€˜Start with an ideaโ€™ app generator.

My understanding is the workaround WAS implemented in the UNIQUEID() function. Thatโ€™s why its no longer an issue there.

I do understand why the workaround was made. Itโ€™s just typically considered bad practice to implement code to PREVENT issues caused outside of the code. Except in circumstances when the problem source cannot be fixed/changed.

Iโ€™ll spare you the experiences and explanation.

Thatโ€™s correct. The bug only exists in the โ€˜Start from an ideaโ€™ generator now.

I wasnโ€™t suggesting the work-around is still needed. I was just stating that the temporary fix was easier to implement in a single place using the modified formula, rather than modifying every sheet involved. In any case, itโ€™s not required now.

I do see what you were getting at, but this was a temporary work-around to a known bug, not a design approach to be used permanently.

I used to use the info in this link Manually generate UNIQUEID() key values - AppSheet Help (google.com) to generate unique key values to the manually introduced data in google sheets that later on feed an app in Appsheet. I've just found many of these manually generated IDs are causing trouble. The worse thing is you don't realize it when you generate them (or after removing formulas with copy-paste values) because the issue arises when the app itself references this IDs in other tables... I would like to know if there is any alternative to current "=DEC2HEX(RANDBETWEEN(0,4294967295),8))" formula to avoid this issues whenever generating uniqueids this way.

A quick solution for your equation is to wrap it in a "substitute"

=SUBSTITUTE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"E","A")

that should help

Top Labels in this Space