Updating ArcGIS Online hosted feature layer with Smartsheets using ArcGIS API for Python
Introduction
Smartsheet is a dynamic workspace that empowers teams to manage projects, automate workflows, and rapidly build new solutions. Smartsheet uses spreadsheets, referred to as sheets, as the basis of everything it does, but the difference between Smartsheet and spreadsheet programs like Microsoft Excel or Google Sheets is that Smartsheet has all sorts of collaboration functionality incorporated into it.
The screenshot below is a sample Smartsheet data.
In this blog, we will update a point feature layer in ArcGIS Online (AGOL) with a Smartsheet dataset using ArcGIS API for Python. In the process, first we will convert this Smartsheet into a Pandas Data Frame, remove rows with no coordinates and then update the feature layer. It is notable that the data contains longitude and latitude in X and Y columns.
The following screenshot is the feature layer that has already been published as a hosted feature layer from the Smartsheet into AGOL before and after updates. As such both the hosted feature layer and the Smartsheet have the same schema and fields. This Smartsheet gets updated on a weekly basis. Therefore the hosted feature layer needs to be updated as well to reflect the updates on the web map. Using ArcGIS API for Python, this process can be automated.
Requirements
There are three items that are required before data conversion:
- Install Smartsheet library by running this command “pip install smartsheet-python-sdk” in Python Command Prompt (here python is in D drive: “D:\ArcGIS\Server\framework\runtime\ArcGIS\bin\Python\envs\arcgispro-py3\Scripts>”)
- Smartsheet Access Token - here’s a link which shows how to create one
- Sheet ID – to obtain the sheet ID, in Smartsheet go to File > Properties > Sheet ID
Methodology
1. Smartsheet Conversion into a Data Frame
First import the modules.
Input the access token generated from the Smartsheet in the script below. After the Smartsheet authentication, a function is used to implement the Smartsheet to data frame conversion.
Input the sheet ID copied from the properties into the following script, then we call the function to create the data frame.
The rows with no latitudes or longitudes are removed.
Note that X and Y columns are the longitude and latitude coordinates respectively.
2. Export Data Frame into a CSV
Once the data frame is created, it is output as a temporary csv file in a folder. Here, a TEMP folder in C drive is used.
3. Upload CSV to AGOL
Connect to AGOL, then remove any existing csv item with the same title from AGOL.
When properties are set with title, description, and tags, upload the csv file to the Smartsheet's folder in AGOL. We can skip the description and tags here and can just use the title since this item is temporary and gets deleted when a new csv is added.
4. Truncate and Append
After that we get the hosted feature layer by id and we truncate it. This hosted feature layer becomes empty but still retains all the properties and settings.
Finally, we use the Append method to update this feature layer with the newly added csv file.
The good thing about using Append method is that the feature layer’s id does not change. This means that if the feature layer is already used in a web map or a web application, it will not break.
Now the hosted feature layer updated with the Smartsheet is displayed in the web map.
There are some points to note:
- The hosted feature layer has initially been created from the same Smartsheet. Hence the schema, field names and types of both data are the same.
- Append is currently only available in AGOL and cannot be used in Portal for ArcGIS.
- I did not talk about the data used here as it can be any data as long as it contains coordinates since the emphasis is predominantly on the methodology.
This script can be used in e.g., Windows Task Scheduler to run on a regular basis for automation of the whole process.
Summary
Smartsheet integration with AGOL has been made easier and more efficient using ArcGIS API for Python with just a few lines of code. Therefore, when there are some updates in Smartsheet, these updates will automatically occur in the hosted feature layer whenever the script is run. This ensures the web maps and web applications such as Operations Dashboard display the latest features.