How to update AGOL Feature Layer's Schema tied to a Google Spreadsheet?

collective_e

I added a Hosted Feature Layer in AGOL (a layer with points) via a Google Spreadsheet that is located in a Google Drive account. This setup is vital, as it allows my team and I to collaborate in the sheet while also reflect changes in the Feature Layer (and ultimately the map it is loaded onto).

Changes made to the spreadsheet are MANUALLY AND PARTIALLY pushed onto the Feature Layer. It's (1) a manual process via "Overwrite Entire Feature Layer" option under the layer's Overview>Update Data menu option. It's a PARTIAL process as it ONLY recognizes changes to rows (add and or delete features) and NOT COLUMNS (AKA the schema) which include: (1) adding a new column, (2) deleting an existing column, (3) renaming a column.

I am shocked no one else has asked this question. I even expressed this to the ESRI support team in multiple instances and they DO NOT seem to have an inhouse service set up (double shocked). They did log this as a bug but I have not heard anything about it (BUG-000117463). 

I am asking the community for help, is it possible to update a hosted feature layer's schema WITHOUT manually adding each new column within the Data tab or by adding an entire new feature layer (with changes to the schema on the google spreadsheet)?. Ultimately, I would like AGOL to automatically update these changes in the feature layer in a scheduled matter. I believe this is possible via an ArcGIS Notebook. However, I tried that option and it is also not working.

Thank you

ChristopherCounsell

Hi,

You should be able to link a layer to a google sheet and have the attribute table updated when new columns are added. See below for a guide on how others do it:

https://community.esri.com/t5/education-blog/map-a-google-sheet/ba-p/1313820#

https://storymaps.arcgis.com/stories/2164b43b0a5947a594ed0752d6d32f34

However I'm not sure if this method requires the Google sheet to be shared publicly, or if it would prompt a second authentication (google) on loading the Google sheet layer via url.

ArcGIS for Excel can do this better, if the user adding fields is the layer owner or administrator.

Otherwise the 'google sheet' method is similar to updating an existing layer with a CSV. It needs the same schema.

You can consider:

• Overwriting the layer using ArcGIS Pro. Export the sheet to CSV, process e.g. table to xy, then overwrite. This should replace the layer with added fields.
• ArcGIS Notebooks. Python can access google sheet, check table against layer, update schema using ArcGIS python API, then load the table. Lots of options here. Can schedule to run daily for a nominal credit cost.

The python method will have a lot of variation depending on your goals. Here's a script I found (but have not looked at):

https://www.arcgis.com/home/item.html?id=9128874e003042cd9b71ca6f5aa69ef2

Personally I would manually add the fields and try to govern the Google sheets so that the schema isn't changing frequently. You could add 'extra' spare fields at the start. Schema changes make your map and any web apps difficult to update as you need to reconfigure pop-ups etc. you could also move the data entry to ArcGIS online. It depends on your workflow and many sheets / schema changes you need to accommodate. Python is great but you'll still need users to not mess up a spreadsheet - it's very difficult sometimes for a CSV to go to a database if users are not formatting tables correctly. E.g. here's an article I contributed to for some common formatting errors:

https://support.esri.com/en-us/knowledge-base/problem-generic-errors-from-uploading-and-published-csv-000024821

Cheers

Chris