Operations Dashboard: How to add a dynamic Google Sheet/CSV layer

[MarthaRodgers1]

Hi, I have a google sheet which is updated periodically and I want to add that layer into my Operations Dashboard.  

I have tried these steps:

In Google Sheets:

Use Publish to the Web as a CSV.  

I then copied that link and inside my AGOL Web Map I used "Add Layer from Web" and used that link.

It adds the layer fine and I am able to set the refresh on that layer.  

Everything refreshes fine in the Web Map.

However, when I try to add that layer to my Operations Dashboard, the Layer is Not Found.

I understand it has to be a hosted Feature Layer, but how do I make it a Hosted Feature Layer that is still updated periodically from the Google sheets?

Any help is appreciated!

Thank you!

[EricShreve]

Martha, 

You could try using the ArcGIS API for Python to update the hosted feature service by setting the python script on a windows task scheduler.

1. Publish the CSV as a hosted feature service in AGOL.
2. Create a script similar to below:

3. Then download the script and add it to a web server were you can automate it through the Windows Task Scheduler.

   # coding: utf-8
   
   # In[88]:
   
   
   # Import libraries
   from arcgis.gis import GIS
   import urllib.request, csv
   from arcgis import features
   import pandas as pd
   import os
   
   
   # In[89]:
   
   
   # Connect to the GIS
   gis = GIS("https://www.arcgis.com", "YOUR AGOL USERNAME HERE", " YOUR AGOL PASSWORD HERE")
   print("Logged in as " + str(gis.properties.user.username))
   
   
   # ## Overwrite the feature layer
   # Let us overwrite the feature layer using the new csv file we just created. To overwrite, we will use the `overwrite()` method.
   
   # In[90]:
   
   
   #item id of the feature layer in AGOL Organization
   Engines_featureLayer_item = gis.content.get('Enter Feature Layer ID Here')
   
   
   # In[91]:
   
   
   from arcgis.features import FeatureLayerCollection
   Engines_flayer_collection = FeatureLayerCollection.fromitem(Engines_featureLayer_item)
   
   
   # ### Access the overwritten feature layer
   # Let us query the feature layer and verify the number of features has increased to `51`.
   
   # In[92]:
   
   
   #call the overwrite() method which can be accessed using the manager property
   Engines_flayer_collection.manager.overwrite('DFFM_Engines.csv (file location)')
   
   
   # In[93]:
   
   
   Engines_incidents_flayer = Engines_featureLayer_item.layers[0] #there is only 1 layer
   Engines_incidents_flayer.query(return_count_only=True) #get the total number of features
   
   
   # In[96]:
   
   
   dirPath = "(file location where 'DFFM_Engines.csv' is located"
   fileList = os.listdir(dirPath)
   for fileName in fileList:
    os.remove(dirPath+"/"+fileName)
    print("file deleted")
   ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Let me know if that makes sense.

Thanks,

Eric Shreve

[MarthaRodgers1]

Hi Eric, actually inside the Web Map it updates fine with the newest data from the Google Sheets/CSV.  But I can't even select that layer to put into my Operations Dashboard.

I guess I can just "embed" the Google Sheet into the OPs Dashboard, but it's not as slick looking as using the List option.

Thanks,

Martha

[EricShreve]

Martha,

Yes, the layer will update fine on the map but will be recognize on the ops dashboard because that layer type is not supported. The work around would be to create the update the feature through python.

[MarthaRodgers1]

Hi Eric, oh ok, thank you!  I understand now.  

I wish it was a little more straight forward, but I'll try the Python option when I get a chance. 

Thanks again!

-Martha

[HollyTorpey1]

Hi Martha,

I ran into the same issue when I wanted a dashboard I was building to reference a Google-sheet-based layer I had added to a web map via "add layer from web." 

I found my way around it by creating a hosted feature layer from my Google sheet using Add Item /From Cloud Drive from the My Content tab (instructions here). The resulting layer is not dynamic, but as long as the schema stays the same, updating it is very quick (click Update Layer on its item Overview page, select Overwrite Entire Layer, and then select your Google account from the list). It's not quite as wonderful as a dynamic link but it will get your data from your Google sheet to your dashboard.

This method probably wouldn't be effective if the data in your Google sheet changes continuously, but in our case I only need to update it a couple times a week, and doing so only takes a minute. I added all the cloud layers that need to be updated to an "Update" category I so they're easy to find and update all at once.

Hope that helps! Forgive me if I'm telling you something you already know.

Holly