How we automated the generation of PDF reports with Python, Google Cloud and Google Workspace?

Automating PDF Report Generation: Unveiling the Recruitment Report Generator (RRG)

RRG, or Recruitment Report Generator, is a custom-built service developed in-house at Datumo. Its primary purpose is to automate the generation of PDF feedback reports based on the assessment of recruitment assignments completed by our developers. As described in our  previous article, the creation of RRG was driven by the need to streamline our recruitment process. In this article, we delve into the technical details of RRG, shedding light on its architecture and operational aspects.

Simplifying Report Generation: An In-Depth Look at RRG's User-Friendly Process

In the following section, we provide a detailed breakdown of the technical process behind generating feedback reports using RRG. This step-by-step guide illustrates how RRG automates the conversion of developer assessments into accurate PDF reports whilst streamlining our recruitment evaluation workflow.

1. Assessment via Google Forms - Developer evaluates the candidate's solution using a structured Google Forms questionnaire, capturing specific assessment criteria.
2. Form Submission - Upon submitting the Google Form, RRG service is triggered, initiating the automation process.
3. Slack Notification - The RRG service immediately sends a notification to a designated Slack channel, summarizing the assessment, including the candidate's name and overall score.
4. Accessing the PDF Report - The Slack notification contains a direct link to a Google Drive folder housing the carefully formatted PDF report. This report includes the candidate's assessment, developer ratings, comments, and additional feedback.
5. Professional PDF Report - The PDF file is more than raw data; it's a carefully refined document with a professional appearance, offering the candidate a clear insight into their performance. This report is ready for distribution to the candidate, ensuring an efficient and informative feedback process.

From a user perspective, the process of generating feedback reports is remarkably simple and fast. Within seconds of evaluating a candidate's assignment, the report magically appears on Slack, ready to be shared. This efficiency enhances our recruitment process, making it seamless and user-friendly.

Behind the Scenes: RRG System Architecture and Workflow

At its core, our system seamlessly integrates Google Forms, Google Sheets, Google Drive, Google Cloud Platform (GCP), and our own Python package known as RRG package. Here's a high-level overview of how these components interact

1. Google Forms and Google Sheets - When a developer completes the Google Forms questionnaire, the data is immediately recorded in Google Sheets. This serves as an initial repository for all assessment information.
2. Google Apps Script Trigger - Upon the arrival of new data in Google Sheets, a trigger activates an Apps Script function responsible for initiating the subsequent steps in the process.
3. Google Cloud Function - This function contains the core logic of RRG, our Python package, which implements the generation of feedback reports.

RRG is a comprehensive system that seamlessly integrates various tools and services to automate the generation of feedback reports. Below, you will find a summary ofthe components and their responsibilities:

1.Google Forms - Collects input data from developers and ingests data in Google Sheets

2.Google Sheets - Serves as a data repository

3.Google Apps Script - Triggers Cloud Function in response to new entries in Google Sheets 

4.Google Cloud Functions - Executes RRG service with specific parameters based on incoming requests

5.RRG Python package - Retrieves and processes data, generates PDF reports, uploads reports to Google Drive, and sends summary messages on Slack

6.Google Drive - Stores generated reports

7.Slack - Publishes messages sent by RRG Cloud Function

8.GCP IAM -Manages permissions and identities for RRG activities

9.Secret Manager - Stores sensitive values

10.Terraform - Manages infrastructure on GCP

Harnessing Google Forms for Data Collection in RRG

Google Forms is responsible for collecting input data from developers as they fill out specially designed forms. This versatile tool not only allows for the collection of essential data but also offers valuable configuration options. We've configured our Google Forms to exclusively collect responses from our company's employees, automatically storing their email addresses. Additionally, we've seamlessly integrated a Google Sheets spreadsheet for response collection and enabled user-friendly response editing, all managed directly within Google Forms.

Here you can find guides for proper configuration: 

1. Restricted Access
2. Automatic Identity Storage
3. Response Editing
4. Native Google Sheets Integration

These configurations have not only enhanced data security but have also improved the efficiency of our candidate evaluation process.

Leveraging Google Sheets for Data Management in RRG

Google Sheets serves as a data repository within the RRG system. It makes collected data accessible for further processing and analysis. Typically, there is no need to edit responses from within the Google Sheets spreadsheet. If necessary, responses can be edited shortly after submission using the edit link provided in the email confirmation, which provides a more convenient and secure way to make adjustments.

In cases where extensive edits are required, designated administrators have direct access to the spreadsheet and can make necessary corrections. However, it's important to emphasize that administrators should refrain from changing entire columns or deleting entries. Such actions may disrupt the system's functionality and data integrity.

Automating Workflows with Apps Script in RRG

Google Apps Script plays a pivotal role in the RRG system, serving as the trigger mechanism that connects Google Workspace with the core logic of the RRG service in the Google Cloud Function. This scripting platform allows for automation of tasks and workflows within Google Workspace, and it is particularly essential in our process.

In our case, Google Apps Script is specifically integrated with Google Sheets. When a developer submits a response through Google Forms and the data is saved in Google Sheets, the triggered Apps Script function activates a Google Cloud Function on GCP that contains the core logic of RRG. An example of such configuration has been shown in the image. Through Google Apps Script, the Cloud Function is invoked with the appropriate parameters, ensuring that the data flows seamlessly through each component of the system.

Configuring Google Apps Script requires an initial setup, including configuring permissions and scopes to enable the successful execution of the Cloud Function. You can check how to do it in this blog post by Nik Vaklev. It helped me a lot to do it correctly! While the trigger function itself is relatively straightforward, it plays a crucial role in initiating the entire feedback report generation process.

‍

Executing RRG's Core Logic: Understanding Cloud Function Integration

The Google Cloud Functions is a serverless runtime for our core RRG library, which is responsible for generating feedback reports. This highly scalable serverless service has proven to be the ideal solution for responding to developer form submissions. We only pay for its execution, and the server remains inactive the rest of the time. This feature is of utmost importance in our case, because we call RRG several times a day at most. It optimizes costs and resource utilization. To help you better understand this crucial component, we've prepared a brief FAQ:

How is the Cloud Function triggered?

The Cloud Function is triggered by incoming HTTPS requests.

Who can trigger the Cloud Function? 

The function can only be triggered by authorized users with special permissions, which are exclusively granted to employees of our company.

How does the Cloud Function know who is triggering it?

To verify our identity, we employ a consent screen in Google Cloud Platform (GCP), as mentioned in the Google Apps Script section. This allows us to obtain an identification token that is then sent to the Cloud Function to confirm our identity before execution.

Infrastructure Management with Terraform in RRG

We employ Terraform for automated deployment of applications on the Google Cloud Platform (GCP), utilizing its functionalities to manage resources like Google Cloud Storage buckets and Cloud Functions. Additionally, Terraform assists in configuring IAM permissions, ensuring smooth access control for authorized users. This comprehensive approach to infrastructure management ensures the effective operation and updates of our Cloud Functions within the GCP environment.

The Heart of RRG: Exploring the RRG Python Package

The RRG package is the heart of the system, responsible for retrieving and processing data, generating PDF reports, uploading reports to Google Drive, and sending summary messages on Slack. It is a Python library comprising individual RRG packages for various responsibilities, and its dependencies include gspread, pandas, pandera, weasyprint, and google-api-python-client.

Let's take a closer look at the role of the RRG python package within the architecture:

1. Data Retrieval from Google Sheets: RRG package accesses Google Sheets to retrieve the relevant assessment data, ensuring it has the most up-to-date information.
2. Data Processing and Transformation: RRG package processes and transforms the retrieved data into a format suitable for generating PDF reports.
3. PDF Report Generation: Using the processed data, RRG python application fills in the PDF report template, creating a detailed feedback document.
4. Storing the PDF Report: Once the PDF file is generated, RRG package stores it in a designated Google Drive folder, accessible to authorized company personnel.
5. Sending Slack Notification: RRG python application generates a Slack message that includes a link to the recently created PDF file and a summary of the assessment. This notification provides a quick and convenient way for our team to access the report.

Effective Cloud Storage Solutions: Managing PDF Reports with Google Drive in RRG

One essential requirement for this project was to effectively manage the storage of the PDF files generated by the RRG service. We had several specific requirements for this integration:

1. Organized storage: Reports needed to be stored in a company folder on Google Drive.
2. Access for recruiters: These reports should be accessible to our recruitment team.
3. Overwrite on report re-generation: When data in a report was edited, it should replace the existing report, not create a new one.
4. User-friendly naming: The report name should be suitable for sending directly to candidates without requiring any additional editing.
5. Candidate identification: The report name should also provide a means of identifying the candidate.
6. Dealing with duplicate candidate full names: We needed to address situations where candidates shared the same name.

To meet these requirements, we implemented the following solutions:

1. Company folder: We created a company folder, using the "parent_id" parameter in the request during storage. It was essential to set the "supportAllDrives" parameter to True to ensure the application could access this folder.
2. Access for recruiters: We shared the folder with our recruiters through the Google Drive UI, ensuring they could access it easily.
3. Overwrite or create: We accomplished this by utilizing the appropriate update and create methods of the Google Drive API. Before creating a new report, we used the search method with specific search criteria to check if a file with the same name already existed.
4. User-friendly naming: We adopted the naming convention "Feedback report - <name and surname of the candidate>" to provide a user-friendly report name.
5. Candidate identification: The report's name should also provide a means of identifying the candidate.
6. Dealing with duplicate names: Handling duplicate names in a unique manner posed a challenge. To address this, each report is placed within a folder named after the candidate's GitHub username (extracted from the link to their repository, which is linked to their application). This approach ensures that reports for candidates with the same name are stored in separate folders, resolving any potential conflicts during file retrieval.

This integration with Google Drive plays a critical role in seamlessly managing and organizing the PDF reports generated by our system, ensuring accessibility, consistency, and ease of use for our recruitment team.

Facilitating Communication: Integrating Slack with RRG

Slack receives and displays messages sent by the RRG service. Our Slack integration leverages Slack app functionality and utilizes Incoming Webhooks for seamless communication and notification delivery. The protection and management of sensitive information, such as the Incoming Webhook API URL (which is enough to activate the sending of messages after the setup of the  Webhook app), are vital aspects in this setup. To ensure security, we employ Google Cloud's Secret Manager, granting exclusive access to the API URL for a designated service account within our Cloud Function execution environment.

For comprehensive instructions on configuring the Webhook and Slack application, please refer to the following link: Slack API - Messaging Webhooks. 

A Summary of RRG: bridging the gap in report generation automation

And there you have it - the tale of the Recruitment Report Generator (RRG). In this journey, we've explored a powerful Python-based solution that automates the intricate process of PDF report generation. RRG seamlessly integrates with Google Workspace and the Google Cloud Platform, turning data from Google Forms into highly polished reports that find their way to Google Drive and Slack.

From a user perspective, we've seen RRG in action, making complex tasks appear simple, all thanks to its behind-the-scenes technical prowess. This tool bridges the gap between user requirements and technical implementation, providing a reliable and versatile solution for report generation.

Ready to harness the power of data and cloud solutions for your business?

At Datumo, we've mastered the art of automation and efficiency, as showcased by RRG. Let us handle your data and cloud needs, just like we do with our internal tools. Check our services webpage to learn more about how we can elevate your operations and drive success together.