Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop 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