ServiceOps Sheet CRM

Google Sheets CRM automation for service requests, quotes, follow-ups, payments, safe email workflows, and operational reporting.

ServiceOps Sheet CRM is a portfolio-ready Google Workspace automation demo for small service businesses. It turns a Google Spreadsheet into a lightweight internal CRM using Google Apps Script, Google Forms, Google Docs, Google Drive, and MailApp-safe email workflows.

The project is designed for businesses such as home repair companies, cleaning services, installation teams, property maintenance providers, and other small field-service operations that need structured request tracking without a custom web app or external database.

What It Does

• Creates a structured CRM workbook in Google Sheets.
• Adds a custom ServiceOps CRM menu.
• Builds operational tabs for requests, clients, jobs, technicians, follow-ups, payments, settings, email templates, logs, form responses, and internal documentation.
• Generates realistic portfolio-safe mock data.
• Creates and links a Google Form intake workflow.
• Installs form-submit and daily digest triggers.
• Processes new service requests from form submissions.
• Finds or creates client records.
• Creates initial follow-up tasks.
• Refreshes a dashboard with KPI cards, action items, pipeline reporting, revenue summaries, workload reporting, and weekly request trends.
• Creates a Google Docs quote template.
• Generates quote source documents and quote PDFs for selected requests.
• Stores generated files in Google Drive folders.
• Uses safe email mode by default to avoid accidental emails to real clients.
• Writes automation activity, warnings, previews, and errors to the Logs sheet.

Current Scope

Implemented:

• Workbook setup and formatting
• Settings and dropdown validation
• Conditional formatting
• Drive folder setup
• Mock data generation and reset
• Dashboard rendering
• Google Form creation and intake processing
• Installable triggers
• Safe intake notifications
• Daily digest preview and sending workflow
• Quote template creation
• Selected-request quote PDF generation
• Quote follow-up creation after PDF generation
• Internal ReadMe sheet

Intentionally deferred:

• Sending quote emails from the selected-row menu action
• Manual selected-row follow-up creation
• Payment workflow automation
• Stripe, WhatsApp, Slack, Zapier, Make.com, external CRM, or external database integrations

Technology Stack

• Google Sheets
• Google Apps Script, V8 runtime
• Google Forms
• Google Docs
• Google Drive
• MailApp
• Standard Apps Script services only

There are no npm packages, third-party APIs, paid services, or external databases required for the CRM itself.

Project Structure

.
├── g_scripts/
│   ├── appsscript.json
│   ├── Code.gs
│   ├── Config.gs
│   ├── Dashboard.gs
│   ├── Email.gs
│   ├── FollowUps.gs
│   ├── Forms.gs
│   ├── Logs.gs
│   ├── MockData.gs
│   ├── Payments.gs
│   ├── Quotes.gs
│   ├── ReadMe.gs
│   ├── Setup.gs
│   └── Utils.gs
├── scripts/
│   └── Apps Script source snapshot
├── screenshots/
│   └── Portfolio screenshots and demo videos
├── tech_spec.md
├── upwork.md
├── implementation_diary.md
├── README.md
├── LICENSE.md
└── Lisence.md

Main Script Modules

File	Responsibility
Code.gs	Custom Google Sheets menu and top-level menu routing
Config.gs	Shared constants, sheet names, headers, status lists, settings keys, and Drive folder definitions
Setup.gs	Workbook setup, headers, formatting, validation, settings, and Drive folders
Forms.gs	Google Form creation, form response reconciliation, intake processing, and trigger installation
MockData.gs	Deterministic demo data generation and reset workflow
Dashboard.gs	Dashboard model building, KPI cards, reporting tables, and charts
Quotes.gs	Google Docs quote template creation and selected-request quote PDF generation
Email.gs	Safe email helper, daily digest preview, and daily digest sending
FollowUps.gs	Placeholder for manual selected-row follow-up workflow
Payments.gs	Placeholder for selected-row payment workflow
Logs.gs	Automation logging
ReadMe.gs	Internal workbook documentation sheet
Utils.gs	Shared helpers

Workbook Tabs

The setup workflow creates and manages these sheets:

• Dashboard
• Requests
• Clients
• Jobs
• Technicians
• FollowUps
• Payments
• Settings
• EmailTemplates
• Logs
• FormResponses
• ReadMe

The Requests sheet is the central operational table. It tracks request ID, source, client details, service type, priority, address, description, preferred date, assigned technician, status, quote amount, quote PDF URL, follow-up date, last contact date, payment status, and internal notes.

How To Use

1. Create a Google Spreadsheet

Create a new blank Google Spreadsheet. Open Extensions > Apps Script.

2. Add the Apps Script Files

Copy the files from g_scripts/ into the Apps Script project. Keep the same file names where possible.

If you use clasp, the typical workflow is:

clasp create
clasp push
clasp open

The project does not require external dependencies, so there is no package installation step for the Apps Script code.

3. Reload the Spreadsheet

Reload the Google Spreadsheet. A custom menu named ServiceOps CRM should appear.

4. Run Initial Setup

Use:

ServiceOps CRM > Initial Setup

This creates the workbook structure, headers, settings, email templates, formatting, validation, Drive folders, dashboard skeleton, and internal ReadMe sheet.

SAFE_EMAIL_MODE is set to TRUE by default.

5. Configure Demo Owner Email

Open the Settings sheet and update:

DEMO_OWNER_EMAIL

Use your own email address if you want safe-mode notifications and daily digest emails to be delivered to you. If the owner email is not configured, email output is written to Logs as a preview.

6. Generate Demo Data

Use:

ServiceOps CRM > Generate Mock Data

This fills the CRM with fictional clients, requests, technicians, jobs, follow-ups, and payments. The data is designed for portfolio screenshots and demos.

7. Create the Demo Intake Form

Use:

ServiceOps CRM > Create Demo Form

The script creates or updates a Google Form named ServiceOps Demo - New Service Request, links it to the spreadsheet, and stores the form ID in Settings.

8. Install Triggers

Use:

ServiceOps CRM > Install Triggers

This installs:

• A spreadsheet form-submit trigger for processFormSubmission
• A daily time-driven trigger for sendDailyDigest

9. Test the Intake Workflow

Submit the demo Google Form. The script will:

• Read the form submission.
• Validate required intake fields.
• Generate a request ID.
• Find or create the client.
• Add a request row.
• Add an initial follow-up.
• Send or preview safe notifications.
• Refresh the dashboard.
• Write activity to Logs.

10. Generate a Quote PDF

Use:

ServiceOps CRM > Create Quote Template

Then select a data row in the Requests sheet and run:

ServiceOps CRM > Generate Quote PDF for Selected Request

The script copies the quote template, replaces placeholders, creates a PDF, stores the generated source document and PDF in Drive, writes the PDF URL back to the request row, updates the request status, creates a quote follow-up, and refreshes the dashboard.

11. Refresh Reporting

Use:

ServiceOps CRM > Refresh Dashboard

The dashboard summarizes total requests, open requests, new requests this week, overdue follow-ups, unpaid completed jobs, estimated revenue, paid revenue, quote conversion rate, action-needed items, request pipeline, technician workload, revenue by service type, follow-up control, and weekly request volume.

12. Preview the Daily Digest

Use:

ServiceOps CRM > Preview Daily Digest

This writes a safe preview to the Logs sheet.

Safe Email Mode

The project is designed for portfolio demonstrations and test work. SAFE_EMAIL_MODE is enabled by default.

When safe mode is active:

• Client emails are not sent directly to client addresses.
• Messages are redirected to DEMO_OWNER_EMAIL when it is configured.
• If no valid demo owner email is configured, messages are written to Logs as previews.
• Email subjects are prefixed with [DEMO SAFE MODE].

Only disable safe mode in a controlled test environment after reviewing the recipients and templates.

Portfolio Assets

The screenshots/ folder contains portfolio media, including dashboard, setup, form, mock data, generated quote, and demo video assets.

Good portfolio screenshots include:

• Custom ServiceOps CRM menu
• Dashboard tab with mock data
• Requests tab
• Settings tab
• EmailTemplates tab
• Logs tab
• Google Form intake
• Generated quote PDF
• Drive folder structure

Development Notes

• The Apps Script runtime is V8.
• The default timezone is Europe/Warsaw.
• The code uses only built-in Google Apps Script services.
• Mock client emails use safe example-style addresses.
• The dashboard is regenerated from workbook data rather than manually edited.
• Logs are part of the operational design and should be reviewed during demos and tests.

Documentation

• tech_spec.md contains the original technical specification.
• upwork.md contains portfolio positioning and project summary material.
• implementation_diary.md contains local implementation notes.

License

This project is released under the MIT License. See LICENSE.md.

Lisence.md is also included because that filename was requested explicitly, but LICENSE.md is the conventional filename used by GitHub and most public repositories.