The aim of this project was to develop a data interface that connects an external SaaS application to Google Sheets via a REST API. The solution makes it possible to automatically capture, structure and update data from the SaaS application in multiple Google Sheets. Customized “Apps Script” functions were used within Google Sheets to ensure optimal integration and performance. The solution offers a user-friendly and automated way of mapping extensive data structures in Google Sheets and using them for various analyses and reports.
Functionalities in detail
- Use of Google Apps Script for REST API integration:
- The data interface uses Google Apps Script to access the external application's REST API to retrieve data and process it directly in Google Sheets.
- By using Apps Script, it was possible to develop flexible and scalable scripts for data processing and presentation that are specifically tailored to the requirements of the SaaS application.
- Caching for faster loading times and efficient testing:
- To enable fast and smooth development and minimize load times during testing phases, all read REST API requests are heavily cached.
- This caching reduces the number of API calls and improves performance, which significantly shortens loading times for users and enables cost-efficient API usage without running into rate limits.
- Automated generation and filling of Google Sheets:
- Multiple sheets are automatically created and populated with the retrieved data. This reduces manual effort and ensures consistent and up-to-date information in the various tables.
- Automation ensures that the information is always up-to-date and that the data is transferred to the relevant Google Sheets without delay.
- Reusability of Apps Script solutions:
- The Apps Script features developed are modular in design and can be reused in multiple Google Sheets. This increases efficiency and the solution can be applied flexibly to different sheets and data requirements.
- Prototyping with Laravel for requirements analysis:
- A prototype was developed using a Laravel application to demonstrate the exact requirements and capabilities of the data interface. The “show, don't tell” approach helped to visually and functionally clarify complex requirements and shape the final design based on customer expectations.
- Supporting the customer development team:
- Once the project was completed, a comprehensive handover and training of the client development team took place. This ensured that the team understood the solution and could develop it further if required.