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.
There are three items that are required before data conversion:
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.
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.
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.
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:
This script can be used in e.g., Windows Task Scheduler to run on a regular basis for automation of the whole process.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.