Ref type is adding an apostrophe in my google sheet column

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?

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.


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


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.

1 Like

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.