No PMIS is an Island: Integrating Smartsheet and Construction Viz
It is not uncommon for organizations to use many disparate web-based project management tools on a construction project. From real estate transactions, environmental assessments, and contract awards to project scheduling, the mix of software systems used across construction organizations can span many different departments and stakeholders. The challenge is integrating these various solutions into a single view of the project and ensuring the aggregated data is accurate, reliable, and timely.
Smartsheet is one such project management tool we often encounter. Smartsheet is used across many industries and can be a data source and reporting solution for construction information. So it made sense for our team to integrate Smartsheet with Construction Viz to allow our clients using this tool to have the best features of both applications.
While Smartsheet has an API for integrating data, our goal was to build flexible integrated solutions to solve our clients’ unique data challenges. Construction Viz can leverage SQL and SharePoint as the back-end database – with the specifics of how this works depending on the selected apps, client data restrictions, and client-specific customizations. Our Smartsheet integration solution thus needed to be flexible and scalable while offering real-time connectivity to Smartsheet for both SQL and SharePoint.
We deploy our SQL integration solution as an Azure WebJob within an Azure web app. Utilizing Azure web apps as the execution platform enables easy scheduling, automatic restarts, guaranteed stability and uptime, built-in logging, performance tracking, and nearly infinite scalability. Our SharePoint integration solution utilizes industry-standard connectivity via SharePoint’s REST Service.
SQL Integration highlights
- A customer needed a Two-way integration with Smartsheet, so both systems are always up to date.
- Users can use schedule data created in Smartsheet in our Construction Viz Project Tracker and a custom performance metrics application, so data needed to flow seamlessly between both systems.
- ConstViz application extensions allow the Customer to add and remove Smartsheet Links as needed for a no-code expansion of the integration scope to new projects.
- Integration can be scheduled daily with refresh rates that can be adjusted as needed.
- For troubleshooting any potential integration issues, we implemented user-managed error logs.
SharePoint Integration highlights
- A customer needed a simple One-way integration with Construction Viz, which allows Smartsheet to access document libraries.
- Ability to handle large daily data loads as Construction Viz program sites may include up to 6,000 projects.
- An integration approach that overcomes Smartsheet API limitations:
- The Smartsheet API limits automated file attachment uploads to 30MB
- The SharePoint online attachment limitation is 250MB
- We built a solution to log oversized files into Smartsheet in the appropriate locations and hyperlinks back to the original SharePoint files for retrieval via other methods