Leading Zeroes Removed When Writing String Data to Google Sheets
When writing string data to Google Sheets that contain leading zeros, the leading zeros are removed after being written to Google:
Environment
- Product - Alteryx Designer
- Product - Google Sheets
Diagnosis
Google automatically formats values when they are written to a cell. The same behavior occurs when manually entering data into the sheet directly. When looking at the web traffic, one can see that the following is sent to Google from Alteryx:
<batch:operation type="update"/>
<id>https://spreadsheets.google.com/feeds/cells/1YW6I6543216gsgsgsgsgD24j-hHA6ydcA/od6/private/full/R4C1</id>
<gs:cell row="4" col="1" inputValue="00004"/>
The correct value "00004" appears as the InputValue. Google then returns the following:
gs:cell [ row=3 col=2 inputValue=4 numericValue=4.0 ]
Google has interpreted "00004" as a number and automatically converted it to "4."
Solution
You can workaround this by placing an apostrophe in front of the value within Alteryx prior to writing the data:
This forces Google to interpret the data as a string and include the leading zeroes. The following is a sample formula that adjusts all rows within a field to include a leading apostrophe:
"'" + [Field1]
Additional Resources