topic Re: Operations Dashboard: How to add a dynamic Google Sheet/CSV layer in ArcGIS Dashboards Questions

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

MarthaRodgers1 — Fri, 13 Sep 2019 17:03:04 GMT

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!

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

EricShreve — Sun, 12 Dec 2021 03:54:37 GMT

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.

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

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

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

MarthaRodgers1 — Fri, 13 Sep 2019 21:12:56 GMT

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

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

EricShreve — Fri, 13 Sep 2019 21:20:12 GMT

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.

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

MarthaRodgers1 — Sat, 14 Sep 2019 18:29:57 GMT

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

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

HollyTorpey1 — Mon, 16 Sep 2019 19:11:20 GMT

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