Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Number Formats using Google Sheets Input tool

7 - Meteor

I'm using the Developer login credentials option to read in the data from a google sheet. I'm noticing that the numeric values of the cells are not coming in as desired. 


For instance, one cell value is .745 -- However -- it is formatted to display as $0.7 in google sheets.  Alteryx is pulling in the value "$.07" as a text field. 


I don't have edit rights for this sheet, so I can't change anything.  Is there a way to pull the .745 **value** instead of the formatted text?



Alteryx Community Team
Alteryx Community Team

@gtaramasso Alteryx will only read what is displayed in the sheet. This works the same for Excel as well. You would have to change the format of the cells from Currency to text or some other format that displays .745 in the Google Sheets in order for Alteryx to read it that way.

7 - Meteor
7 - Meteor

No, Excel does not work like that. See attached XLSX example. The file has the same value, .745, formatted 5 different ways. Regardless of formatting, Alteryx brings in .745. 


Google sheets, on the other hand, brings in the value as it is formatted. As I mentioned in my original post, I don't have the option to change how the google sheet is formatted and I need it to bring in the value of the cell --not the formatted text.



6 - Meteoroid

Unfortunately, Alteryx and Google Sheets have issues aligning data types.  As you note, data types are dynamic in Excel and in Google Sheets within a column, but Alteryx forces a column to a given data type across the entire column.  This is probably something that could be solved with improvements to the Google Sheets Input tool.  (For the record, the Google Sheets Output tool has a similar issue, where if you want to treat a numeric-string ("0409") as a string, you have to expressly format the column in Google Sheets with the format style "0000".


I think this is where challenges arise.  If it finds things that are stringy, it'll import that column as a string.  Fortunately, we're all powerful beings within Alteryx, so we can fix this.


When bringing in formated numbers from Google sheets, I'll parse the numeric values out using a RegEx configured to Replace.  I look for the following values and "replace" them with nothing: $ , ( ) %




Now, you notice there are parenthesis that I'm removing, which would represent negative values.  Before I replace them using a RegEx tool, I "Detect Negatives" looking for both an open and close parenthesis.  If I find them, I return a result of -1.  If I don't, I return a result of 1.  After the parse and after the change to a numeric type, I'll multiply my result (which is an absolute value if it came as a parenthesis, by the "Detect Negatives" of either -1 or 1.  My math teachers would be proud.



Once I've removed all the stringy stuff, I can use a Select tool to force the result into a Double (or other decimal friendly data type).


Problem (hopefully) solved.


reformatting google sheets inputs.png