Building a Scalable Market Insights Tool with Power Apps & SharePoint

Using Excel as a Database

Initially, Excel was used as the primary data source for storing brand and therapeutic area lists. However, Power Apps has a delegation limit of 2,000 rows when working with Excel files. This posed a significant challenge for scalability and performance. Filtering, searching, and sorting operations became inefficient because of the high data volume.

Daily Sync Requirement from Excel to SharePoint

To overcome the Excel delegation issue, the data was migrated to SharePoint lists. However, the master Excel sheet continued to be updated daily by the client. This introduced the need for a daily data sync mechanism to ensure SharePoint always reflected the latest updates.

SharePoint Lists: Functional but Less User-Friendly

While SharePoint lists solved the delegation and performance, from the client's perspective, Excel is much easier to navigate, update, and manage, especially for non-technical users. SharePoint’s UI, list view customization, and permissions management aren’t as intuitive, which led a few initial usability concerns from the stakeholders.

User Access to Data Sources

Power Apps depends on users having the right level of access to the underlying data sources. This required careful management of SharePoint permissions. Managing these permissions securely, especially across multiple countries and user roles, was a key challenge and required close coordination with the client’s IT/security team.

Constant Feature Requests and Scope Creep

As the app was being developed, stakeholders began to see new possibilities and requested enhancements on the fly, such as adding general comments, sending automated emails, and submission of multiple comments. While these requests were valuable, they also led to scope creep and required rapid iterations without compromising app stability.

01.

Excel as a Database

To address the limitations of using Excel with Power Apps, we created copies of the Excel sheets as SharePoint lists. These lists were then used as the primary data source within the app to enable delegation and improve performance. SharePoint lists allowed better integration with Power Platform tools and supported larger datasets. This change significantly improved data handling, reliability, and response time in the app.

02.

Daily Sync from Excel to SharePoint

We implemented an automated ETL (Extract, Transform, Load) process using Power Automate. This flow runs daily to extract updated data from the client’s master Excel sheet and update the corresponding SharePoint lists. The logic includes checks to avoid duplication and ensure data consistency. This ensured that the SharePoint data always reflected the most recent updates without any manual intervention.

03.

SharePoint Usability Concerns

While SharePoint lists are less intuitive than Excel, this wasn’t a major issue since users interacted with the data through the app interface, not directly with the lists. We guided the client through the initial learning curve and ensured proper documentation was provided. Most backend data management was automated, minimizing the need for manual list edits. As a result, SharePoint's complexity had limited impact on user experience.

04.

User Access to Data Sources

We established strict access controls to ensure each user had only the permissions they needed. Commenters were given contribute access to submit data, while approvers had edit access to their respective sections. Admin access to the master Excel and SharePoint configurations was limited to select personnel. Every role and permission was double-checked to ensure there were no loose ends or security gaps.

05.

Frequent Feature Requests

The app was designed with scalability and flexibility in mind, allowing us to accommodate frequent client requests. We followed a layered approach, building features on top of existing logic without disrupting previous functionality. Thorough testing was done after each update to ensure the system remained stable. This iterative development approach helped deliver continuous improvements without compromising the app’s integrity.