Relate AGOL feature layer to Google Spreadsheet CSV, display related records in pop-up?

[deleted-user-LNABNwA5CZcD]

I do water quality sampling for a non-profit and am currently working with four universities on a large project. We really need to map our data. I have 10 sites. For each site, I sample once per month for multiple parameters.

It seemed like the best way to structure this (mapping the sampling sites with data available in the popups) would be to have a feature layer of sampling sites and then to relate a table of data. Each sampling run would be a row (like a feature) and each parameter would be a column (like a field). The site-to-data relationship is a one-to-many.

The data is most easily held/shared in Google Spreadsheet. I have tried publishing it as a CSV and adding it as a AGOL feature layer. Then, using ArcGIS Pro, I tried creating a relationship to the data in the hosted CSV, but that did not seem to work. Maybe it's not possible?

End goal: I need to be able to click on a sampling site and in the pop-up have data from the most recent sampling run highlighted but also have past data available, be it in a table and/or chart but at the very least in a link.

I'm open to other approaches. I really need to manage the data in a spreadsheet outside of ArcGIS. Otherwise, I'm going to be transcribing data at least twice to get it into a spreadsheet and then into a separate attribute table.

Jake Skinner‌, it looked like you had answered a somewhat similar question a year ago, so I wanted to tag you. Allison Muise‌, I know you have the Mosquito Surveillance Web App, and that could almost do the trick, but I'm again having trouble with the hosted Google Spreadsheet. Plus, what you've done with the Mosquito Surveillance Web App is great and should be a standard, configurable option!

If it does not exist as a simple setup, ArcGIS Online really needs a simple setup for sampling in mind where you can click on a site and in a pop-up get past records and summary visuals (e.g., charts) of trends.

Thank you!

[JakeSkinner]

Hi Sam,

You could create a field in your feature service that will contain the link to your Google Spreadsheet.  When a user clicks on a feature, they can then click on this URL to take them to the sheet.

Another option would be to create a File Geodatabase that contain the sample site point feature class, the Google Spreadsheet imported as a table, and a relationship class between the feature class table.  When published the relationship should maintain.  When a feature is clicked the pop-up will show an option 'Show related records' which will open the table of related records.  The issue with this approach is keeping the table in the geodatabase up-to-date, as well as the hosted feature service.

Option two would be more streamlined if you have ArcGIS Server. You could publish the data to ArcGIS Server and then you would only have to update the table in the geodatabase, and not have to worry about updating the service.  This would occur automatically.

[AllisonMuise1 · Accepted solution]

Hi Sam,

I'm not sure which app in that suite you are looking at specifically, but the Mosquito Surveillance Charts app is a configuration of the Related Table Charts widget in Web AppBuilder, and the Mosquito Population Surveillance app is a configuration of the Crowdsource Polling web app template (primarily designed for data entry). Both apps are actually pointing to the same hosted feature layer.

I think that, these two apps, along with the links option mentioned by Jake, are your best bets for presenting related records. Both of these apps do require a geodatabase relationship, but I don't think that maintaining the updates needs to be too labor-intensive.

I would suggest that if you or someone you work with has some Python skills, it wouldn't be too hard to use a package like pygsheets along with the ArcGIS PI for Python to transfer data between your google sheet and a hosted layer. No re-publishing required and the script could be scheduled to run on an interval of your choosing making it very near to 'live'.

If Python isn't an option, there is a new 'Append Data' capability in AGOL that should allow you to easily append data from a CSV to an existing hosted feature layer. This does require someone to hit the buttons in AGOL on a regular basis, but would still be MUCH faster than manual data re-entry.

- Allison