Description
Summary We are seeking an experienced freelancer to develop a comprehensive CRM system. The ideal candidate will have a strong background in CRM development and be able to design a system that meets our business needs. The project involves creating a user-friendly interface, integrating data management features, and ensuring seamless communication between departments. The candidate should be able to work independently and deliver high-quality results within the specified timeframe .ob Title: Google Workspace Expert / Apps Script Developer for Pest Control Custom CRM (Sheets + Forms + Calendar + Contacts API) Project Overview: We are pest control company based in Bahrain. We manage a high-volume, multi-vehicle field operation (around 20 appointments daily). We need an expert Google Apps Script developer to upgrade/build a custom, highly automated CRM entirely based on the Google Workspace ecosystem (Sheets, Forms, Calendar, Contacts). The goal is to eliminate manual data entry and connect sales, field dispatch, accounting, pesticide inventory, and automated customer follow-ups seamlessly. Scope of Work & Technical Requirements: Module 1: Smart Lead Capture (Google Forms to Sheets) Add specific Dropdown menus for Pest Control services (e.g., German Cockroaches, Termites, Bed Bugs, Drain Flies). Implement strict Dropdowns for Areas in Bahrain, Payment Methods, Lead Sources, and Vehicle Assignment. Ensure a mandatory field for "Google Maps Location Link" is captured. Module 2: One-Click Field Dispatching (Sheets to Google Calendar) Create custom, automated triggers using Apps Script. When a specific vehicle/technician is selected and the dispatch button is pressed, the system must automatically create an event in the designated technician's Google Calendar. The calendar event must automatically include: Customer Name, Phone Number, Pest/Service Type, Price, and the clickable Google Maps URL directly in the "Location" field. Module 3: Post-Service Reporting & Pesticide Inventory (Technician Form) Optimize the "Technician Report Form" for mobile users. Enable file/audio uploads for on-site quality logging (before/after treatment photos). Inventory Tracking Feature: Add a section for professional-grade pesticides and materials used on-site. The script must automatically deduct these amounts from a Master Inventory Tab in the Google Sheet and flag low-stock items in red. Module 4: Automated Contact Syncing (Google People API) Develop a background script that triggers once a job is marked as completed or paid. The script must automatically inject the customer's data into a specific centralized Google Contacts account. The contact name format must be saved automatically using this dynamic string: [Customer Name] - [Pest Type] - [Amount Paid] - [Date]. This will serve as an instant Caller ID across all sales devices. Module 5: Follow-up Engine (Warranty & Retreatment Radar) Build an automated "Follow-up Queue" tab. Based on the service date and pest type, customer names must automatically appear there after 3 days and 14 days for quality checks and warranty retreatment reminders. Set up conditional formatting to highlight overdue follow-ups. Create a clean Dashboard tab summarizing: Total Revenue, Paid vs. Pending amounts, Performance per Vehicle, and Sales closing rates. Required Skills: Advanced Google Apps Script (JavaScript). Deep understanding of Google Sheets architecture and advanced formulas. Experience with Google Calendar API and Google People API (Contacts). Ability to write clean, protected code to prevent users from accidentally breaking formulas.