Matthew Gisi

Google Workspace Apps Script Automation Google Drive

Purchase Order
Digitization

Edgewood High School · Technology Assistant

Replaced a fully paper-based purchase order process with an end-to-end digital workflow — a single Google Form submission triggers an Apps Script pipeline that looks up account numbers, increments per-department PO counters, creates the correct Drive folder tree, fills a Doc template with all order details, exports a finished PDF, and emails the business office with the document attached. Staff interact only with a familiar Google Form; everything after that is automatic.

Overview

Built around
three goals.

🗂️

Reliability

Every PO is automatically filed in the correct Drive folder tree — root, department, and fiscal year. Folders are created on demand if they don't exist yet, so no order can be misfiled or lost between inboxes.

🔍

Traceability

Per-department PO counters auto-increment on every submission, producing display strings like 26-007. The PDF filename and Drive path both include this number, so the business office can locate any order instantly.

✳️

Zero Friction

Staff interact only with a standard Google Form. Folder creation, template filling, PDF generation, and email notification all happen silently in the background — the requester gets a confirmation, the business office gets the finished document.

Workflow

From form submission
to filed PDF.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  • Staff Member
  • Google Form
  • Apps Script
  • Google Sheets
  • Google Drive
  • Google Docs
  • Gmail
  • Admin / Business Office

Click any step to expand details · Toggle the test path above

Pipeline Detail

What happens
inside the script.

Trigger

onFormSubmit(e)

An installable Apps Script trigger fires on every Google Form submission. The raw e.values array is read and column positions are resolved dynamically by matching headers — no hardcoded indexes.

Sheets Lookups

Account & PO Number

The ChargeAccounts named range maps the department's account header to an account number. The DeptPONums named range finds the department row, increments the counter, and returns the formatted PO string (e.g. 26-007).

Drive

Folder Tree Resolution

The script walks root → department folder → fiscal year folder, creating any missing level on demand. New department folders are seeded from a template FY structure so the hierarchy is always consistent.

Docs

Template Fill & PDF Export

The master Doc template is copied into the FY folder. Every {{placeholder}} — vendor name, account number, PO string, up to 10 line items, totals, and checkbox states — is replaced. The completed Doc is exported as a PDF, then trashed.

Email Notification

An email is sent to the business office with the finished PDF attached and a Drive link included. If the submitter provided a valid school email address, they receive a copy as confirmation.

Testing

Test Script Path

A secondary function constructs a synthetic e.values array and calls onFormSubmit(e) directly — exercising the full pipeline without a real form submission. Useful for verifying template changes or new departments.

Tools

Built entirely
in Google Workspace.

📝

Google Forms

Staff-facing input. Collects department, vendor, payment type, check delivery, and up to 10 line items per submission.

⚙️

Apps Script

The automation backbone. Installable trigger, dynamic column parsing, all business logic for lookups, folder creation, template filling, and notifications.

📊

Google Sheets

Stores form responses and the KEYS sheet — the ChargeAccounts and DeptPONums named ranges that drive account resolution and PO numbering.

📁

Google Drive

Houses the final PDF archive. Folder tree is auto-managed per department and fiscal year. Template structure is seeded on first use.

📄

Google Docs

Master PO template with placeholder tokens. Copied per submission, filled programmatically, exported to PDF, then trashed — never visible to staff.

✉️

Gmail

Delivers the finished PO to the business office with PDF attached. Optional CC to the submitter if a valid school email was provided.

Skills & Tools

Google Apps ScriptGoogle FormsGoogle SheetsGoogle DriveGoogle DocsGmail APIWorkflow AutomationProcess DesignDocument GenerationGoogle Workspace