Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Leading Zeroes Removed When Writing String Data to Google Sheets

JakeS
Alteryx Alumni (Retired)
Created

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:

 

LeadingZero.png

 

Environment

 

  • Product - Alteryx Designer
    • All Versions
  • 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:

 

LeadingZero1.png

 

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

 

No ratings