Ref type is adding an apostrophe in my google sheet column

[Mike_T]

User selects a name from a reference list, the form returns the IDKey to the google sheet. If the ID key is a number an apostrophe is being added to the beginning of the ID Key. I’m trying to reference this value with a google sheet formula but it doesn’t recognize it when there is a leading apostrophe. Any ideas?

[GreenFlux · Accepted solution]

This might have something to do with the column’s formatting in Google Sheets (not an AppSheet setting).

Try selecting the column in Google Sheets and setting Format>Number to Plain Text.

[Mike_T]

That worked like a charm!!! Thank you for your quick and most helpful reply!

[GreenFlux]

Glad to help! I had a similar problem recently with Google Sheets ‘helping’ by automatically converting a string to a number in scientific notation and breaking Ref’s.

@Aleksi, @Steve, since this can break Ref’s in an app, should this be reported as a bug? I think AppSheet should be setting/forcing the formatting of values in a key column to prevent this automatic behavior from the Google Sheets side. Setting the column as key should lock the formatting.

[Jonathan_S]

I agree, I also had this issue where an app was converting lot numbers and best before dates into scrambled numbers that made no sense. set to plain text took a while to figure out and works perfect.

[Horgi]

I have the same problem but it is something that AppSheet is doing. I set the column to Number>Plain Text in Google Sheets but when I use the data in my app it changes the data back to '5 instead of 5. I am trying to use the data to add a total up for a percentage. Thanks, David