Field Type from Google Sheets Not Respected

[DanaAllen]

Hi - 

I'm trying to use a Google Sheet as the source for a serial chart within an Information Dashboard. 

The trouble is that two of my columns come across as 'double' fields (as they should - they are formatted at decimals in the Google Sheet). 

Two of my columns which contain similar data (chemical concentrations) come across as 'string' or text fields. Again, they are formatted in Sheets as decimal numbers. 

I have tried importing without column headers (so only numbers in the sheet). I've tried stripping formatting from the original file (Excel sheet), saving as a CSV with appropriate formatting, then copy/pasting to Sheets with appropriate formatting, pre-formatting each column in Sheets then pasting in the data as 'values only' to strip any residual equations. 

The only thing that has sort of worked is when I add in 'dummy data' to the many blank rows in the two columns that continually come in as 'string' fields. This doesn't completely solve the issue - they import as 'integer' fields instead of 'decimal' despite having decimals (albeit just the same value copy/pasted to fill in the gaps). 

I'm at a loss. I need to preserve these gaps in the data, but if AGO can't understand that gaps don't equal cell formatting, then this 'integration' is fairly useless. Anyone able to help? Example Excel sheet attached (the Google Sheet will be based on this). 

[jcarlson]

It's important to remember that when you're adding a Google Sheet item, it's really just converting it to text and back. If you watch the network traffic when you add a Google Sheet, you'll see this:

https://www.googleapis.com/drive/v3/files?access_token=<some-token>&pageSize=10&q=(mimeType="text/csv")

 So it's reading as "text/csv".

Once that happens, AGOL is running the analyze() function, the same as when you add a CSV file directly.

In my experience, converting a text-based data file like a spreadsheet to a format with specific data types is always a headache, esepecially when it comes to handling null values, or "gaps". A text-based format has no way to differentiate between an empty string, '', and a true NULL value. And despite how consistently you enter your data, a spreadsheet just doesn't have true types the way a database does.

When you add the dataset originally, there should be a way for you to indicate what kind of fields you have:

You could also create the layer first and append to it using the CSV.

There are more reliable ways to do this if you're comfortable with a bit of python, too.

[Dana_FSC]

Hi Josh,

Thanks for this information.  Makes sense even though it would seem like AGOL ought to either respect the formatting across the board or specifically give an option during import of Sheets to designate field type (it doesn’t). 
So - my goal is to create a hosted feature layer that my employees can paste data into from our field equipment logs. Currently I have to upload a new CSV every time to get the info dashboard chart to update with new data. If I create a CSV with my data on the cloud and import that, designating field types specifically as I go, then data gets appended to that layer, would that work?

 I’m not great with Python (don’t really use it at all) but can learn or possibly outsource to accomplish this function. 

thanks again. 

[jcarlson · Accepted solution]

Unfortunately, the formatting doesn't come out with the raw text. There wouldn't be a way to differentiate it from the actual data. You can check out the RFC definition of the CSV file format, as that's really what's at the root of this limitation. Though it does seem odd that the Google Sheets object won't give you the field type options on the initial import... That might be worth a bug report.

header1,header2,header3
data1,data2,data3
data1,data2,data3
...

I would create the CSV separately, yes, the go through the field designation, etc. Then any time you need to append to the layer, you just need to re-export the CSV and upload it, and the fields will already be in place.