topic Re: Automatically Refresh Feature Layer from Google Sheets in ArcGIS Online Questions

Automatically Refresh Feature Layer from Google Sheets

WGIMaps — Fri, 18 Jun 2021 04:22:10 GMT

I have a feature service that was created from Google Sheets. The sheet I used to create the feature layer imports data via an external API and updates it every hour. I need the hosted feature service to update automatically along with it. Any solutions for this?

Re: Automatically Refresh Feature Layer from Google Sheets

jcarlson — Fri, 18 Jun 2021 13:56:31 GMT

If you search through older posts, you'll find this particular question is very popular. Here's a recent one:

https://community.esri.com/t5/arcgis-online-questions/google-sheets-not-dynamic-in-agol/m-p/1051453#M39702

To summarize that and similar posts: you can update your map layer at regular intervals, but you'll need to do some coding.

The map layer isn't "connected" to the Google Sheet, except that it stores information about the original source. But it's not aware of that sheet's contents at any given moment, or even if it still exists, it only holds the snapshot of the sheet from when it was added.

You can use python and the ArcGIS Python API together with an AGOL Notebook to get this sort of thing scheduled and running at regular intervals.

What's the ultimate source of the data in the Sheet, if I may ask? If it's just pulling from an API, it may be simpler to just eliminate the sheet altogether and have a Notebook that queries the API and populates a layer directly.

Re: Automatically Refresh Feature Layer from Google Sheets

WGIMaps — Mon, 21 Jun 2021 19:12:53 GMT

Josh,

Thanks for the info. I found the thread you referenced earlier and have been trying a few things here and there and have not had any luck. I am still pretty new with coding to I am still trying to figure it out.

Yes the data is coming in via API from a weather station. I have a dashboard set up to display the data but have yet figured out how to automate the overwrite process.

I am not yet familiar with Notebooks but have been reading up on it. I may give that a go. Only thing is I have put so much work into setting up the Google Sheets set up.

I am currently trying to use the Overwrite FS script to work and then just schedule a task to update every hour, but I am stuck in the Command prompt trying to get it to overwrite manually (see attached).

Thanks again for your help.

Re: Automatically Refresh Feature Layer from Google Sheets

WGIMaps — Mon, 21 Jun 2021 19:46:28 GMT

Not to mention using the scheduled tasks consumes credits. I need my data updated once an hour. 1.5 credits per update doesn't seem like much, but on a yearly basis it is just over $1300 just for the scheduled task if I am understanding the credit consumption correctly.

Re: Automatically Refresh Feature Layer from Google Sheets

jcarlson — Mon, 21 Jun 2021 19:53:04 GMT

It's 1.5 per hour per update. Meaning if your script only runs for 5 minutes to refresh your FS, you're looking at a bill of 0.125 credits per run, or 3 credits per day if the data updates every hour, even during the night.

That said, I have a massive multi-layer update notebook dealing with many thousands of features, and even that rarely crosses the 4-minute mark. If you're just updating a single layer, it may well be half that.

Re: Automatically Refresh Feature Layer from Google Sheets

jcarlson — Mon, 21 Jun 2021 20:03:21 GMT

Where is that script coming from? I've not used it before. I like to develop my own scripts and functions in Python, as it's a bit easier to understand what's actually happening "under the hood", and you can tailor the process to your particular situation.

Re: Automatically Refresh Feature Layer from Google Sheets

WGIMaps — Mon, 21 Jun 2021 20:34:45 GMT

Ahh good to know, thanks for the clarification Josh. I found this article on automation. This is where I got the aforementioned script and workflow.

https://learn.arcgis.com/en/projects/schedule-automated-near-real-time-data-updates/

Re: Automatically Refresh Feature Layer from Google Sheets

jcarlson — Mon, 21 Jun 2021 20:43:36 GMT

Thanks! Well, it's tricky with Google Docs. The link you have isn't really to the "file". When you add it to your content, what's happening behind the scenes is that the entire sheet is being read as a temporary text object and then imported from that.

If the Google Doc was a literal file, this could maybe work. Where's your Google Doc pulling this from? Are you doing any data manipulation there, or is it basically a copy?

Re: Automatically Refresh Feature Layer from Google Sheets

WGIMaps — Mon, 21 Jun 2021 20:48:11 GMT

My API is pulling data from a weather station cloud service I have installed on a property we consult for. The way the data comes over is pretty cumbersome so I created another sheet that imports the data in a workable format and that sheet is then published to my AGOL account as a feature service.