Automating AGOL Dashboard Updates from Onedrive shared Excel File, possible?

[JonJones1]

One important thing to note is I don't have the Enterprise version of Arcgis, only AGOL. 

I have an Excel file in OneDrive containing sales data for different bakeries, which is updated regularly. I'm using ArcGIS Pro desktop and have upgraded my ArcGIS Online (AGOL) subscription to the GIS Professional Standard level. My goal is to automatically update an AGOL dashboard widget, likely a pie chart, with the latest sales data whenever the Excel file is updated.

I've created a map in ArcGIS Pro with points for each bakery location, linked by a unique bakery ID that's also in the Excel file. While I can link the Excel data to ArcGIS Desktop using "relates," I encounter an error when trying to publish the map to AGOL, stating the Excel table needs to be part of the geodatabase. Upgrading to GIS Professional Standard hasn't solved the issue. This did allow me to make relationship classes, which I thought would help but basically I end up with a snapshot of the Excel when I created the relationship class that I couldn't get to update/refresh the data, or at least I couldn't figure it out, when I published the map to AGOL but might be missing something.

How can I ensure that updates to the Excel file reflect in an AGOL dashboard automatically using just the tools available to me in AGOL or Arcgis Desktop since I don't have Arcgis Enterprise?  Possible? 

[JeffSilberberg]

Maybe the new Data Pipelines feature will help you --

https://www.esri.com/arcgis-blog/products/arcgis-online/announcements/introducing-data-pipelines-bet...

Or maybe just a data expression -- 

https://www.youtube.com/watch?v=rowW2PistiQ

[JonJones1]

Thank you sir! This at least might point me in the right direction. 

[timcneil]

Hi @JonJones1 , 

It's hard to say exactly what the best workflow would be for you without looking at your dataset. 

I see you mention you have relationships in your dataset, is this a 1:1 relationship or 1:N?

If it's a 1:1 relationship (i.e., sales for a bakery correspond to a single row in the table) you could include your lat/long coordinates for each bakery within the XLSX file hosted on OneDrive. 

Then the workflow would look like this:

• Publish your XLSX from OneDrive to Online via New Item 
• Author your map in MapViewer with bakery point locations 
• Create a Dashboard from your web map
• Leverage 'Update Data' from the point layer's Item Details page to overwrite the file using the new XLSX file on OneDrive*. When the hosted feature layer is updated, the updated data should automatically flow through to your Dashboard. 

For automating your updates, I would suggest leveraging ArcGIS Notebooks to schedule file updates. 

* You'll need to make sure you update the actual XLSX file hosted on OneDrive with the new data before starting the Overwrite operation. 

[avillamo]

If you have ArcGIS Pro you can add the data interoperability extension ArcGIS Data Interoperability Extension | Get Data to Work in Your Workflows (esri.com)