Stitching ArcGIS Online apps together with webhooks
Last week, I co-presented with a colleague at the ESRI User Conference about how ArcGIS Survey123 made it easy for us to collect data in an accessible, user-friendly front end, while storing that data in a complex and efficient backend. And ArcGIS Dashboard presented those survey-takers with eye-catching visuals that helped inform and persuade them to action. In my part of the presentation, I explained how we extended these apps with other technologies to get them to do exactly what we needed.
Process flow
This is high-level view of the overall process, so you can see the different apps we used, in what order.
It all starts with ESRI’s ArcGIS Survey123. Land developers submit their project information there first. Their survey responses are stored in an underlying feature layer as GIS point locations, one point for each project.
Once the table gets populated by a new survey, two webhooks are triggered that let the survey and the dashboard communicate with each other.
Our first webhook is a function that is hosted on the Google Cloud Platform, which is part of Google’s suite of cloud computing services. It creates pivot tables from the underlying feature layer which feed the summary charts on the dashboard.
The second webhook is a “flow” that we constructed with Microsoft Power Automate. It creates a hyperlink that includes a special id value from the underlying feature layer. It sends that link in an email to the user that they can click on go to our ArcGIS Dashboard.
When the user fills out the follow-up survey that’s embedded in the dashboard, they are sent a final summary email, and the same underlying feature layer is updated with their responses.
Power Automate
Once the developer submits the starting survey, they get an email with a special hyperlink. We decided to use Microsoft Power Automate to send this email. Power Automate is a service for automating workflows that is available through Microsoft Office 365. It offers a large collection of connectors you can choose from to connect apps, data, and devices in the cloud. One of the applications that you can connect to is Survey123. You can authenticate the connection using your ArcGIS Online account, and then you can select from a gallery of pre-built actions that you can have happen in response to triggers.
The flow we made is triggered when a survey response is submitted. It waits a few seconds to make sure all the calculations in the survey have finished running. Then it executes the action of sending an email.
When we composed this email, we inserted dynamic content that is pulling from the survey’s feature attributes. We get the applicant’s name and email address, the project name, and the GlobalID of the survey record. We use the GlobalID to create a smart URL that the developer can click on to see their unique survey results.
Filtering summary dashboard
When the developer clicks the link in their email, they see a unique version of the dashboard that is custom tailored just for them. We can do this because ArcGIS Dashboard allows us to configure a parameter in the URL that we can use to filter the information that is shown on the dashboard.
This example chart shows how the dashboard looks without the parameter. This is the default version of the dashboard, but nobody would want to look at this way. It shows all the data for every survey that has been submitted, which makes it unreadable.
To narrow things down, we made a parameter out of the GlobalID, which is the unique identifier for each survey record.
This example chart shows how the dashboard looks with the GlobalID added to the end of the URL. Now, only one survey’s worth of data is shown at a time, and we can control which survey it is, by changing the web address.
Creating user-friendly charts
When the developer looks at the dashboard, they see these beautiful stacked bar charts. In order to get the charts to look like this, we needed to create some new tables in ArcGIS Online with the data organized in a different way. We needed to unpivot the data, in other words, to create rows for some of the values that were stored in columns. For example, we have some monthly values that needed 12 columns to be converted to 12 rows.
I wrote a python script that would do that. My script is based on the ArcGIS API for Python. I create a new empty Pandas data frame with the column names that I specify, and an index that is the 12 months of the year. An index is like a reference id, or an address, that is used to locate a certain row.
# Reshape the survey data for the submitted surveyunpivoted_data = pd.DataFrame(columns=['ParentGlobalID', 'Mon', 'TOTNPOT_DEM', 'RW', 'SW','CONCOL', 'GW', 'BW', 'FD_SUP'],
index=['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
Then I get the survey data from the original table that is stored in ArcGIS Online, and I use the index to find each row in the new table and populate it with data pulled from the original table. Then I convert my data frame to a featureset and save my edits to ArcGIS Online.
# Get all the survey datagis = GIS("http://name.maps.arcgis.com", username="", password="")
survey_layer = gis.content.get('itemID').layers[0]# The submitted row gets unpivoted to 12 rows (one per month)for index, row in survey_data.iterrows():
unpivoted_data.loc['Jan'] = [{" + str(row['globalid']) + "}", 'Jan',row['TOTNPOT_DEM_JAN'], row['RW_JAN'], row['SW_JAN'], row['CONCOL_JAN'], row['GW_JAN'], row['BW_JAN'],row['FD_SUP']]unpivoted_data.loc['Feb'] = [{" + str(row['globalid']) + "}", 'Feb',row['TOTNPOT_DEM_FEB'], row['RW_FEB'], row['SW_FEB'], row['CONCOL_FEB'], row['GW_FEB'], row['BW_FEB'],row['FD_FEB']]...chart_table.edit_features(
adds=unpivoted_data.spatial.to_featureset())
This script does what we need, but we had to get creative about when and how to run it.
Python in the cloud
We have a lot of scripts that run on a schedule on our local scripting server, and we thought about scheduling this one to run once and hour or so. But then we discovered we could make the script run right after the survey is submitted, by creating a webhook.
Survey123 has an option to launch a URL upon survey submission. And Google lets you publish python scripts to their cloud storage servers, called the Google Cloud Platform. After you publish a script they provide you with a trigger URL that will launch it. I took the trigger URL from Google, and put into the payload URL in Survey123.
I also checked the box to include the submitted record with the payload. This allows me to get the ObjectID in my script so that I know which row needs to be unpivoted.
# Get the ObjectID of the submitted surveyrequest_json = request.get_json(silent=True)
object_id = request_json['feature']['result']['objectId']# Query the survey layer for the matching ObjectIDsurvey_data = survey_layer.query(where='objectid = ' + str(object_id))
The Google Cloud Platform has a nice logging feature built into it, which keeps track of each time the script runs, and provides a stack trace if there were any errors.
With this solution in place, the script doesn’t run any more times than it needs to, and there is no delay between when the survey is submitted and when the script starts. The charts are ready for the user to look at them as soon as the email is sent with the link to the dashboard.
Creating user-friendly tables
We also have informative data tables on the dashboard for the developer to look at. Similar to the charts, we had to take some extra special steps to get them to look as good as they do.
ArcGIS Dashboard has a relatively new feature that lets you insert a data table based on a feature layer. You can either group the values or have one row for each feature. But, same as with the charts, we didn’t need either of those things. We needed multiple rows per feature. This time, we solved the problem by inserting a list instead. This gave us more flexibility with how we could arrange the data, because we could insert dynamic fields wherever we wanted them.
We could format the list as an html table, so it still looks like a table. And we can edit the html source code for even more options! We made heavy use of this. We created header, subheader, and summary rows with html formatting. We added custom background colors, font colors and font sizes to certain rows using style tags, and merged columns together using colspan tags.
Linking both surveys/ features
Finally, I want to return to talking about the GlobalID parameter, because it is also used in the last step of our project flow. We use that parameter to connect the follow up survey to the original survey through the same GlobalID. We include it at the end of the URL when we embed the survey into the dashboard. When the follow up survey is submitted, the GlobalID is captured. We do this because we want the follow up survey to know to update the same record that was created by the first survey.
There is one other thing that we do besides including the GlobalID. We also have edited the submission URL for the survey in Survey 123 Connect. We changed the item ID in this URL to the item id of the original survey.
Now, instead of creating a new table to store the survey results, it edits the original table. And it knows which row to edit because we have provided the GlobalID. This is great because we can have two surveys, and keep all of their information in just one table, in a single row in that table. It is much easier to keep track of the data when we’re looking for it all in the same place.
I thought I’d put this information on my blog to make it available to those who weren’t able to make it to the conference. You really should visit San Diego when you get a chance.
