For your first question, the simple answer is "no". Or at least, "not easily".
I'm assuming that the edits are all made to the excel sheet, and the AGOL layer will just be a copy? If that's the case, you could schedule a Python script to periodically read the excel sheet and push updates to a hosted layer. We do that kind of thing in our org, and updating even very large datasets typically takes under a minute, so you could have the dashboard be nearly real-time.
Here's the process.
1. Create a standalone table in AGOL
You can do this by publishing the Excel sheet, but I find that AGOL tries to "guess" the data type when you upload a spreadsheet. Even when the upload process checks and asks for the field types, I have had the resulting layer make mistakes anyway. You're better off creating the table from scratch so that you can have full control over the fields.
2. Write a Python script
This part could be as simple or complex as you want it to be. How many rows are in the spreadsheet, by the way? If it's a large dataset, you can have your script compare the source and destination tables and only submit edits. But if it's a small dataset, you could just truncate and append. It might look like this:
from arcgis import GIS
import pandas as pd
# connect to AGOL
gis = GIS('https://your-org.maps.arcgis.com', 'user', 'password')
# load source data
df = pd.read_excel('path-to-file.xls', 'specific-sheet')
# do some reshaping here
# in general, pandas does better at inferring data types, but it can still be wrong.
# use pandas to alter and adjust fields as needed to match the output table
# get destination layer
dest_table = gis.content.get('itemid of table').tables[0]
# remove existing features
dest_table.manager.truncate()
# load in new data
dest_table.edit_features(adds=df.spatial.to_featureset())
Like I said, it could be as simple as that. Our get more complex to handle weird incoming data or to make the process more efficient, but this will still work.
When this script runs, the hosted layer will be refreshed with the latest data.
3. Schedule a Task
In Windows, you'll have to wrap this script in a BAT file, probably. However you need to do it (will depend on what machine is running this), just make sure that the script executes in a location that has access to the shared spreadsheet.
Schedule the task to run as often as you need. This may depend on how quickly the script can execute. I mentioned there are ways to make that process a lot more efficient, so let me know if it doesn't run quick enough for you. If you want the data to be near realtime, just have this run every few minutes, and you should be all set!
- Josh Carlson
Kendall County GIS
