Automated Google Sheets Reporting for Client Data

Technologies & Skills:
PHP, Laravel, Eloquent ORM, Google Sheets API, Google Cloud service accounts, REST API integration, backend architecture, data aggregation, Sequel Ace, PHP CLI, JSON BatchUpdate requests, modular service design, documentation, stakeholder communication.

Problem

A client required direct visibility into the performance of their active features.
Although this data already existed within our internal database and application, it wasn’t accessible to the client or our management team in a convenient, shareable format.

To improve transparency and efficiency, the goal was to automatically populate a Google Sheet with the relevant data and enable our internal management team to trigger updates on demand from the frontend.
Delivering this solution would eliminate repetitive manual reporting, saving hours of work per week and improving client satisfaction through real-time visibility.

Challenges

The project required extensive communication with internal stakeholders to clarify data definitions, reporting requirements, and desired functionality — details that were not fully documented.

Additionally, I had no prior experience with the Google Sheets API, and the project involved extending a pre-existing Laravel service written by another developer who was unavailable at the time.
This meant I had to analyze, reverse-engineer, and enhance unfamiliar code independently (with some help from LLM tools) while managing a heavy backlog and a tight delivery timeline.

Another challenge involved understanding complex database relationships to identify the exact data points needed for each client feature.

Solution

I enhanced the backend by extending our existing GoogleSheetService component in Laravel, introducing a modular architecture that supported multiple clients through a new client label parameter.
This made the service easily extensible for future use cases.

Using a Google Cloud service account, I authenticated API access and implemented backend logic to fetch data via Eloquent ORM queries, aggregating and transforming it into the required reporting format.
I tested and validated queries through Sequel Ace and the PHP CLI to ensure data accuracy and completeness.

To improve presentation and usability, I leveraged the Google Sheets BatchUpdate API to apply conditional formatting and styling directly during sheet population.
Routing and command triggers were integrated into the frontend, allowing management to refresh the data at any time with a single action.

The project was delivered on time, fully functional, and well received by both the client and our internal stakeholders — enabling quick, accurate, and automated reporting that significantly improved operational efficiency.

Future Improvements

Next steps include migrating ownership of the Google Sheet to the client’s own Google Workspace, which will involve generating a new service account with proper permissions.

Additionally, the internal success of this feature has led to growing interest from other teams.
Future improvements will focus on scaling the service — making it easier to adapt for multiple clients, varying database schemas, and different Google Sheet configurations.