I have Google Forms where users enter information, some of which contains leading zeros (e.g. zip codes, ID numbers).
However, when this data is saved to the Google Sheet, it doesn't treat it as plain text and strips the zero. The form fields are text fields with no validation.
I have tried to format the range of cells containing these values as plain text, but it seems Sheets does not set the formatting as plain text unless there is already text in a cell. So I can format a column as plain text, submit the form, and the leading zero will still be stripped.
I tried writing an apps script to deal with this but there's no good way for me to deploy it to the users who would need it.
How can I get around this without having to instruct users to prepend a single quote to their form entry? This is such a small thing, but I can't find a good simple solution to what it seems must be a common problem. If I can't get this to work I may have to drop Google Forms/Sheets altogether.