Google Sheets Data Aggregation

BUSINESS PROBLEM

Our client identified significant waste related to employees manually updating Google Sheets that fed business critical decision making information. The client operated in a projectized environment with one Google Sheet per project. With a portfolio size of 150+ simultaneously executing projects, executive leadership needed a way to aggregate the data without manual copy/paste.

OUR MISSION

Using Google Workspace, build a tool to dynamically identify key data elements across 150+ Google Sheets. The solution must be robust enough to gather 100+ data elements per sheet despite a variety of different sheet formats. Furthermore, the solution should further enable migration of the same data set into Salesforce.

OUR SOLUTION

KilPen built a solution containerized within Google Sheets that crawled previously indexed sheets and extracted key data elements. A data model was designed as well as a method to identify missing information and other data integrity problems. The data model, with strong integrity, enabled custom report creation allowing executive leadership insight despite the distributed nature of their data.

TIMELINE

One month