Automatically overwrite google sheets feature layer

338
2
11-24-2021 01:05 PM
ArcProOne
New Contributor II

Hello,  I have a google sheet with latitude/longitude columns that I added to ArcGIS Online via New Item > Google Drive > Google Sheets.  I would like to have this update automatically, but it seems the only way to update it (sync it with the Google Sheets source) is through ArcGIS Online, clicking Update Data > Overwrite Entire Layer.  How can I emulate that "Overwrite Entire Layer" functionality with python?

ArcProOne_1-1637787576429.png

I've looked through the API documentation and various forum posts, especially the top reply in this post sounds very confident that there's a way to do it "quite easily!", but I can't seem to crack it.  I've tried:

 

item = gis.content.get(item_id)
item.update(data=google_sheets_url)  # returns True, but does nothing

 

where 'google_sheets_url' is the destination URL of the link on the AGOL item page:

ArcProOne_0-1637787374283.png

I've also tried the FeatureLayerCollection.manager.overwrite() method, but I doubt that's the way to do it.

I can imagine an ugly workaround where I schedule a download of the google sheet as a csv, and do item.update() or flc.manager.overwrite() using the file on my disk, or schedule some kind of truncate+append.  But both of those involve a lot of seemingly unnecessary stuff in the background; it really seems there should be an easy way to sync with Google Sheets.  I appreciate any help in advance!

0 Kudos
2 Replies
AbidHiraSaath
New Contributor III

I have tried reading csv using panda and am getting this issue.

 

AbidHiraSaath_0-1638026567943.png

 I appreciate any help in advance!

0 Kudos
ArcProOne
New Contributor II

Maybe you could write the pandas df to a temp csv file on disk, then use the file path as the argument for FeatureLayerCollection.manager.overwrite() and delete the temp file.  Then it would mirror this example: https://developers.arcgis.com/python/sample-notebooks/overwriting-feature-layers/

Could look like:

 

import tempfile
import os

g_csv = pd.read_csv(dwn_url)
with tempfile.TemporaryDirectory() as csvdir:
    local_csv = os.path.join(csvdir, 'pandas.csv')
    g_csv.to_csv(local_csv)
    layer_collection.manager.overwrite(local_csv)
    
    os.remove(local_csv)

 

See also: https://community.esri.com/t5/arcgis-api-for-python-questions/update-hosted-table-overwriting-it-wit...

P.S. you may want to blur your photo if those are real phone numbers, don't want another Squid Game on our hands.

0 Kudos