coc-generator

For each shipment marked ready-for-coc in a Google Sheet, automatically copies a CoC Doc template, substitutes the merge fields with the row's data, exports a PDF, files it to Drive, emails it to the customer's buyer with the PDF attached, and marks the Sheet row coc-sent with the PDF link. One CoC per scheduled run; backlog catches up automatically. Zero AI in the pipeline — fully deterministic, audit-grade, no per-run cost.

Use case

Every aerospace, medical, and IATF/ISO 9001 shop ships parts with a Certificate of Conformity. Today most shops generate them by hand: copy a Word template, type in the part number, quantity, lot, processes, save as PDF, attach to email. Five to ten minutes per shipment. This workflow does it in zero — the QE just flips the Sheet row's status to ready-for-coc after final inspection, and the next scheduled fire delivers the CoC.

Because it's deterministic (Doc find-replace, not AI), the CoC content is exactly what the template says — defensible under customer audit. The shop owns the template; the workflow is just a substitution engine.

Setup

1. Credentials (Credentials view): - google-workspace — Google Workspace service-account JSON, domain-wide delegation
2. Runtime environment (Runtime Environments view): - bash — InTouch auto-creates this on Linux/macOS install from /bin/bash. Must include bash, awk, mktemp, date, python3, and the gog CLI on PATH. - Windows: InTouch auto-creates powershell and command prompt instead. To run this workflow on Windows: install Git Bash or WSL and add a bash runtime env in InTouch pointing at it (e.g. C:\Program Files\Git\bin\bash.exe), OR change runtimeEnvName: "bash" to "powershell" in workflow.yaml and rewrite the script blocks in PowerShell.
3. One-time GCP setup: - Enable the Google Docs API for your GCP project. This is what powers docs find-replace. Drive/Sheets/Gmail are not enough. If you skip this step, the workflow runs once, fails at the generate task with Google API error (403 accessNotConfigured): Google Docs API has not been used in project <N> before... — that error includes the enablement URL.
4. Drive prep: - Create a CoCs folder for the generated PDFs (any name). Copy its folder ID from the URL.
5. Doc template prep: - Create a Google Doc CoC template with your shop's letterhead, statement of conformity, signature lines, etc. - Use these merge-field placeholders verbatim in the template body, anywhere you want the data substituted:
   • {{REF_ID}} — shipment reference id from the Sheet
   • {{DATE}} — today's date (YYYY-MM-DD), the date the CoC was generated
   • {{CUSTOMER}} — customer name
   • {{CUSTOMER_PO}} — customer PO number
   • {{PART_NO}} — part number
   • {{REV}} — drawing revision
   • {{QTY}} — quantity shipped
   • {{MATERIAL_LOT}} — material lot / heat number
   • {{PROCESSES}} — comma-separated list of processes performed
   • {{INSPECTION_DATE}} — final inspection date
   • {{QE_NAME}} — quality engineer who signed off
   • {{SHIP_DATE}} — date shipped
   • {{SHOP_NAME}} — your shop's name (set once in YAML)
   • Copy the template's document ID from its URL (https://docs.google.com/document/d/<THIS_IS_THE_ID>/edit).
6. Sheets prep: - Create a Sheet with tab ShipLog and 14 columns: A ref_id | B customer | C customer_email | D customer_po | E part_number | F rev | G qty | H material_lot | I processes | J inspection_date | K qe_name | L ship_date | M status | N coc_pdf_link - Row 1 is the header. Data rows start at row 2. - Status flow: leave M empty while building the shipment → set to ready-for-coc after final inspection → the workflow sets it to coc-sent and writes the PDF link to N. - Column I (processes) accepts comma-separated values; the workflow substitutes them verbatim into {{PROCESSES}}. - Copy the spreadsheet ID from the URL.
7. Edit workflow.yaml — replace the four placeholders: - <<SHIP_LOG_SHEET_ID>> — ShipLog spreadsheet ID - <<COC_TEMPLATE_DOC_ID>> — CoC template doc ID - <<COC_DRIVE_FOLDER_ID>> — Drive folder ID for generated PDFs - <<SHOP_NAME>> — your shop's name (used in the email body and as {{SHOP_NAME}} in the template)
8. Schedule the workflow every 5 minutes.

Pipeline

1. find-workflow — runtimeenv (bash + gog + python3). Reads ShipLog!A2:N, finds the first row with status ready-for-coc, emits a JSON blob with all 13 source fields plus the sheet row number. If no row matches, emits {"empty":true}.
2. generate — runtimeenv (bash + gog + python3). Copies the template doc into the CoCs folder, runs docs find-replace for each of the 13 merge fields, exports the result as PDF, uploads the PDF to Drive, caches the local PDF at /tmp/intouch-coc-<refid>.pdf for the next task. Emits the augmented JSON blob.
3. deliver — runtimeenv (bash + gog + python3). Sends a Gmail to the customer's email with the PDF attached and a summary in the body. Updates the ShipLog row's status to coc-sent and writes the PDF link into column N (in a single sheets update call on M<row>:N<row>). Deletes the local PDF.

If find-workflow finds nothing, the rest of the pipeline exits cleanly: no template copies, no API calls beyond the one Sheet read.

What runs per scheduled fire

sheets get ShipLog!A2:N
        ↓ first row where status == "ready-for-coc"
docs copy <template> --parent <CoCs folder ID>
        ↓ new doc id
docs find-replace × 13 merge fields
        ↓
docs export --format pdf --out /tmp/<file>.pdf
drive upload /tmp/<file>.pdf --parent <CoCs folder ID>
        ↓ pdf link
gmail send --to <customer> --attach <local pdf>
sheets update ShipLog!M<row>:N<row> "coc-sent|<pdf link>"

Customization

• Template content — edit the Doc; no code change. Add merge fields with the same {{NAME}} pattern and add the corresponding replace line in generate.
• Sheet schema — if you add columns, expand the Python field-mapping in find-workflow and the read range. Match the new col letter in the deliver update range.
• Multiple status states — change the match string in find-workflow (currently ready-for-coc) or accept multiple states with a list.
• Multiple recipients — set customer_email to a comma-separated list; gog gmail send --to accepts comma-separated recipients.
• CC the estimator or owner — add --cc [email protected] to the gmail send call.
• Send from a specific alias — add --from [email protected] if the service-account user has that send-as alias verified.
• Cleanup retention — generate writes /tmp/intouch-coc-<refid>.pdf and deliver removes it after sending. If you want a local copy retained, drop the rm -f line in deliver.

Companion: rfq-triage

rfq-triage builds the front of the same lifecycle: customer email → QuoteLog row. The natural full chain is rfq-triage (intake) → manual estimating + production → coc-generator (shipment). Both share the google-workspace and bash infrastructure; no new setup beyond the Docs-API enable and the ShipLog sheet/template.

Verified

Flag/command syntax for all sheets, drive, and gmail calls verified against the bundled gog v0.19.0 (2026-06-01); re-verify output shapes on first run. docs find-replace and docs copy flag names verified from gog --help; the find-replace runtime requires the Docs API enabled in your GCP project (one-time setup, error message points at the enablement URL).

Source

See workflow.yaml.