Automated Work Order Processing for UPS

This project automated the creation of work orders in Jobber from emailed PDF work orders, extracting data from the PDFs and populating custom fields in Jobber using Google Apps Script, GraphQL, webhooks, and a custom web app. This streamlined UPS’s workflow, saving significant time and reducing manual effort.

Project Name: Automated Work Order Processing for UPS

The Need/Problem/Why

UPS received work orders as PDF attachments via email (originating from a fax machine), requiring manual data entry into their Jobber system. This process was time-consuming, inefficient, and prone to errors. The project aimed to automate this workflow, eliminating manual data entry, improving accuracy, and freeing up staff to focus on other tasks. The need for custom fields in Jobber added complexity to the solution.

The Workflow and User Journey at a high level

Receive Email (with PDF Attachment):

The system receives an email containing a PDF work order attachment.

Extract PDF Data (Google Apps Script/Web App):

A Google Apps Script, potentially triggered by a webhook or running on a schedule, accesses the email and extracts the relevant data from the PDF attachment. This likely involves OCR (Optical Character Recognition) and data parsing techniques.

Data Transformation and Mapping (Google Apps Script):

The extracted data is transformed and mapped to the corresponding fields in Jobber, including the custom fields.

Create Jobber Work Order (Jobber API - GraphQL):

The script uses the Jobber API (GraphQL) to create a new work order in Jobber, populating all the necessary fields with the extracted and transformed data.

Mark Email as Read (Gmail API):

The script marks the processed email as read to prevent duplicate processing.

Error Handling and Logging:

The system includes error handling to manage issues like invalid PDF formats, missing data, or API connection problems. Logging ensures that any errors can be tracked and addressed.

Video Demonstration

The Client

UPS (Highlight their industry - Logistics). Quantify the time savings and cost savings achieved through the automation (6.25 hours/day and over $57,000 annually).

Technology & Skills Demonstrated:

• Google Apps Script: Core automation logic, data processing, and integration with Google services.

• Jobber API (GraphQL): Creating and managing work orders in Jobber.

• PDF Data Extraction (OCR and Parsing): Extracting data from PDF documents. Specify any libraries or tools you used for PDF processing.

• Webhooks and Web Apps: Receiving email notifications and facilitating data transfer. Specify the platform or services used for webhooks and web apps (e.g., Google Cloud Functions, App Engine).

• Data Transformation and Mapping: Handling data inconsistencies and mapping data to custom fields.

• Error Handling and Logging: Implementing robust error handling and logging mechanisms.

Key Metrics & Achievements (if available):

• 6.25 hours/day saved.

• $57,000+ annual cost savings.

• Improved data accuracy.

• Reduced manual effort.

Alternative Project Name

• Streamlined Work Order Management with Jobber Integration

• Automated Work Order Creation from Email PDFs

• Efficient Work Order Automation for Logistics

• Email to Jobber Workflow Automation