Contract Automation

What this system does

The workflow is built around three legal documents and one master data schema. A Google Apps Script layer turns a set of staged Google Forms and a master Google Sheet into a working document pipeline.

The three documents

The master data schema

A single Google Sheet is the source of truth with three tabs:

The five-stage lifecycle

Four sequential Google Forms feed the pipeline. Each form is a superset of the prior stage; each stage produces a dynamic pre-fill URL for the next.

Workflow folders

Paste each folder's Drive id on the workbook Deployment tab (FOLDER_1_AUTOFILL_OFFER through FOLDER_7_EXECUTED_ASSIGNMENT_INVOICE). The script copies drafts into folders 2, 4, and 6. Folders 3, 5, and 7 are the executed-file destinations.

Data flow

Google Form N  ──submit──▶  Tab 2.0 (Form Responses)
                                 │  ┌ button click / menu action ┐
                                 ▼  ▼
                          Tab 1.0 (Field Table, master row)
                                 │        │
                    dynamic pre-fill URL  │  generate agreements
                    for Stage N+1 form    ▼
                               Purchase Agreement · NDNCA · Assignment Addendum
                                      (Google Docs, saved to Drive folders)

Two things happen on the button click for each stage:

1. The latest Form Responses row is copied into the matching row of Tab 1.0, with multi-select checkboxes converted to 1/0 binary encoding.
2. A dynamic pre-fill URL for the next stage's form is generated and written back to the sheet.

Repository layout

apps-script/          Google Apps Script project (deliverable for this phase)
  appsscript.json     Manifest (scopes, timezone)
  Config.gs           Field map, stage rules, Deployment-tab loader
  Menu.gs             onOpen custom menu + button-assignable entry points
  Ingest.gs           Form Responses (Tab 2.0) → Field Table (Tab 1.0)
  Prefill.gs          Dynamic pre-fill URL generation (FormApp)
  DocumentMerge.gs    Field Table row → 3 agreement Google Docs (replaceText)
  DriveManager.gs     Copy generated docs into workflow Drive folders
  Audit.gs            Runtime discovery: tab layout, forms, config check
  Utils.gs            Shared helpers (checkbox→binary, row lookup, headers)
  README.md           Deployment & configuration guide
build-files/          Source workbook, Word templates, dev notes (not live ids)
  templates/          Var1 purchase agreement, NDNCA, addendum (.docx)
  workbook/           MASTER_WHOLESALE_AUTOMATION_WORKFLOW_var-1.1 (.xlsx)
  notes/              Apps Script pre-fill notes and wholesale dev notes
docs/
  FIELD_MAPPING.md    Form Responses header → Field Table header reference
  TEMPLATE_TOKENS.md  {{token}} mapping for the three agreements
templates/            Tokenized markdown drafts used to build the Google Docs
README.md             This file

Roadmap

Known limitations

• Google e-Sign cannot pre-populate signer fields and does not enforce signer order → resolved in Phase 3 via DocuSign.
• Google Forms cannot pre-fill from existing data → mitigated via dynamic pre-fill URLs; ideal resolution is CRM-driven entry in Phase 2.
• Property valuation (comps, ARV, tax card, deed/lien search) is manual → Phase 4.

Container-bound Google Apps Script for the master workbook MASTER_WHOLESALE_AUTOMATION_WORKFLOW_var-1.1. It moves data from the staged Google Forms into the Field Table and generates the three agreement documents.

Script files

What each button does

Ingest (per stage) — reads the latest row on the stage's response tab, maps each answer to its Tab 1.0 column, writes inspection indicators as true/false, and writes them to the deal's master row (Stage 1 creates the row; Stages 2–4 find it by property_street_address and update it). It then builds the pre-fill URL for the next stage.

Generate documents — reads the selected Tab 1.0 row and replaces every {{Column_Header}} token in each template with that row's value, filing the copy into the document's Drive folder and stamping the tracking date.

Deploy (container-bound)

1. Open the master Google Sheet → Extensions → Apps Script.
2. Create one script file per .gs here and paste the contents (or use clasp, below).
3. Add appsscript.json (enable Project Settings → Show "appsscript.json").
4. Save, then reload the sheet — a Wholesale Automation menu appears.
5. Run any item once and grant the OAuth scopes when prompted (Sheets, Docs, Drive, Forms).

Or with clasp

npm i -g @google/clasp
clasp login
clasp clone <SCRIPT_ID>          # from the bound project's Project Settings
cp apps-script/*.gs apps-script/appsscript.json <clasp-dir>/
clasp push

Configuration checklist

Structural settings (tab names, FIELD_MAP, checkbox labels) stay in Config.gs. File and folder ids do not. They are pasted on the workbook Deployment tab. One workbook copy = one Drive deployment.

• SHEETS.MASTER / RESPONSES / MILESTONE match your tab names → confirm with auditSheets().
• STAGES[n].responseSheet — one consolidated Form Responses tab, or four separate tabs → confirm with auditSheets().
• Deployment!STAGE_1_FORM_ID … STAGE_4_FORM_ID — each form's /d/<id>/ segment.
• Deployment!DOC_*_TEMPLATE_ID — the three tokenized Google Doc ids.
• Deployment!FOLDER_1_AUTOFILL_OFFER through FOLDER_7_EXECUTED_ASSIGNMENT_INVOICE — the workflow directory.
• CHECKBOX_FIELDS[0].options — confirm inspection labels with auditForms(), or auto-generate with syncInspectionOptionsFromForm(2).
• Confirm template tokens use {{Header}}; otherwise change CONFIG.TOKEN.

Run checkConfig() to list Deployment keys that are still blank.

Setup / audit workflow

From the sheet menu Wholesale Automation → Setup / audit, or from the Apps Script editor (pick the function → Run → View → Logs):

auditSheets()

Every tab, its headers, and whether responses are in one tab or four.

auditForms()

Each form's title and every question's title + type, including the exact checkbox choice labels (e.g. inspection options).

syncInspectionOptionsFromForm(2)

Prints a ready-to-paste CHECKBOX_FIELDS[0].options block built from the live form.

checkConfig()

Lists Deployment-tab keys that are still blank.

previewPrefillUrl(1, '<address>')

Builds the Stage 2 pre-fill URL from a master row, to sanity-check title matching.

Wiring in-sheet buttons

Insert → Drawing (or Image) → draw the button → right-click → ⋮ → Assign script → type the function name (ingestStage1, …, generateAllAgreements). Everything is also in the Wholesale Automation menu.

Notes & assumptions

• Deals are matched across stages by property_street_address (CONFIG.MATCH_KEY). For higher reliability, add a Record_ID column to Tab 1.0 and to each form (carried via the pre-fill URL), then set MATCH_KEY to it.
• Ingest acts on the latest response row. For a true onSubmit trigger, add an installable trigger calling ingestStageN — but the button-click model matches the documented workflow and avoids double-processing.
• FIELD_MAP keys must equal the live form question titles. auditForms() prints the live titles so you can confirm.

Follow-ups not yet applied to the GCP or Claude Co-Work runners. These notes record field-type and naming decisions that span the Apps Script layer and the broader pipeline.

Boolean indicators

general_inspection_ind, well_inspection_ind, pest_inspection_ind, radon_inspection_ind, and other_inspection_ind are HubSpot boolean checkboxes (true / false).

Apps Script writes true or false onto the Field Table. DocumentMerge.gs still prints X in the purchase agreement when the value is true. Legacy 1 / 0 values still count as checked so older rows keep working.

property_postal stays a string

property_postal is string / text on Contact and Deal, in the probate schema, and in RapidUpload. Leave that type until a numeric transform runs on every upsert. After that transform exists, change the schema field type. Do not change it before the transform, or ZIP+4 values will be rejected.

property_last_transfer_date

The wholesale column is property_last_transfer_date, the same field the GCP and Claude runners already read and write for the auditor's last conveyance:

• src/rapidupload/a2_runner.py
• src/rapidupload/b_runner.py
• src/rapidupload/auditor.py

The earlier worksheet name transfer_date was retired. That extra schema property was removed from the probate catalogs and from RapidUpload ingest properties. Wholesale now uses property_last_transfer_date.

parcel_number was not removed

Wholesale Parcel_Number is property_parcel on Contact and Deal. The live Deal property parcel_number is still present. The framework still uses it in config/pipeline.toml, existing_custom_fields.json, and the A1 listing extract. Removing it would change existing runner behavior, so it was left in place.

Two archive dates

document_archive_date and assignment_document_archive_date are both on the worksheet and in the portal schema. The assignment date can be tracked independently if the assignment addendum expires before transfer. document_archive_date was not renamed.

Record IDs

wholesale_record_id is the worksheet and portal id for this wholesale file. lead_uid is the existing portal UID. The HubSpot record id is unchanged. One record can exist on the portal and in another workflow, so these three ids stay separate.