This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
@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.
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.
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).