Flowgrammer

Google Sheets Contract Tracker

Implement the published contract register in Google Sheets with owners, dates, Drive file pointers, and operational notice flags. Not legal advice and not a CLM.

— Craig Major

A Google Sheets contract tracker is the published Contract Tracker register imported and run in Sheets. Each row names an owner, a closed status, a Drive file and version pointer, and the effective, expiry, renewal, and notice dates. Sheets constrains the lists, computes operational notice flags, and holds filter views. A person still records renew, amend, or terminate. Sheets does not give legal advice, replace a CLM, send e-sign, or decide renewal from a reminder timeout.

The shared field list lives in the published Contract Register and Renewal Template. The paired Google Sheets Contract Register Template is the XLSX you import. If you still need named authority before a downstream write, stay on the document approval workflow.

Who this is for

Use this when signed files already live in Drive and the immediate job is one visible register in Google Sheets: who owns the row, which file version is authoritative, which notice window is open, and which decision is still missing.

Prerequisites:

  • One contract family for the first register. The worked example is fictional Cedar & Quay Fabrication Ltd.
  • Agreement that this is an operational register, not legal advice and not a CLM
  • A named owner, not a shared inbox
  • A Drive file pointer plus a native id or version key when Drive provides one
  • A written rule for what happens when a reminder gets no reply
  • Agreement that formulas and scripts never set renewed or terminated

Stay out of this first sheet: clause interpretation, auto-renewal legality, e-sign send, a full approval matrix, invoice amounts, tax, PO, payment fields, and any claim that a protected range is a security control.

If you still need the platform-neutral model, stay on the parent tracker. If the job is bills, amounts, and payment status in Sheets, use the Google Sheets invoice tracker. That register uses a different schema. Do not copy its money fields into this one.

What Sheets implements

The parent template defines the shared field list. This workbook uses the same names so you can move data without remapping it.

Parent piece Sheets twin
Field dictionary Register columns, same names
Status model Dropdown on status
Duplicate key Formula on duplicate_key
Roles and exceptions RolesAuthority and ExceptionTaxonomy tabs
Sample rows REG-CQ-2026-0914-0920 Same fictional Cedar & Quay cases
Notice formulas notice_date, days_to_notice, days_to_expiry versus Instructions!B4
Approval pointer approval_status only

Do not rename fields for Sheets convenience. The same names make later imports and migrations easier.

Sheets-native additions that are not a second schema:

  • Data validation dropdowns for status, type, exception, approval pointer, and access test
  • Computed notice flags and a renewal-block helper
  • An autoFilter on the Register table
  • Filter views you create after import
  • HYPERLINK beside a Drive file_pointer_url
  • An optional reminder-only Apps Script example

Import and sharing

Google documents File > Import for Excel data. In Sheets: File > Import, choose the XLSX, then Create new spreadsheet or Replace spreadsheet. The Excel file is not changed. Official steps: Use both Excel and Sheets.

You can also import each CSV onto a matching tab. Recreate dropdowns from the Lookups tab if validation did not survive the import. Google documents in-cell dropdowns under Data > Data validation (Create an in-cell dropdown list).

Share the owner and reviewer as Editor (Share files from Google Drive). Version history lets you see editors and restore a version (Collaborate in Sheets). That is collaboration history. It is not a certified legal or renewal audit log. Keep named decisions on the DecisionRecord tab.

This package is an XLSX twin. No live native Google Sheet was created or verified in this pass. Do not treat the download as a Template Gallery listing.

Protected ranges are not security

If you later protect a sheet or range, Google says this should not be used as a security measure. People can still print, copy, paste, import, and export a copy. You cannot password-protect a sheet. Official wording: Protect, hide, and edit sheets.

Protection can warn an editor. It does not stop an unauthorized status change from being a process exception. Case 5 still logs unauthorized_status_change and reverts or holds.

Worked example

Cedar & Quay Fabrication Ltd is a fictional Toronto fabricator. Import the workbook. Leave Instructions!B4 at 8 September 2026.

  1. REG-CQ-2026-0914, Northshore Logistics MSA CTR-CQ-MSA-2024-017. Owner Avery. Status active. Drive pointer and version key present. Expiry 30 September 2027. 60-day operational notice window. No exception.
  2. REG-CQ-2026-0915 uses the same family with a nearer expiry. Status notice_window_open. Reminder scheduled to Avery. Escalation target Riley.
  3. REG-CQ-2026-0916 is on exception hold because the Drive pointer and version key are blank. A renewal decision stays blocked.
  4. REG-CQ-2026-0917 is a second Northshore plus the same contract id. Status duplicate. Canonical row REG-CQ-2026-0914 continues.
  5. REG-CQ-2026-0918 records an unauthorized attempt to mark renewed. Status returns to exception hold.
  6. REG-CQ-2026-0919 expired on 15 August 2026 with no renew, amend, or terminate note.
  7. REG-CQ-2026-0920 sent a reminder, failed escalation, and keeps renewal_decision_needed. It does not auto-renew.

Those numbers are fixture math, not a customer result.

Notice window formulas

These formulas are planning aids. They are not legal notice, statutory interpretation, or an auto-renewal rule.

Field Operational formula What a person still does
notice_date expiry_date minus notice_window_days when both exist Confirm the window days came from operations input
days_to_notice notice_date minus Instructions!B4 Decide whether to open notice_window_open
days_to_expiry expiry_date minus Instructions!B4 Decide whether the row is expired
auto_renew_flag_operational yes / no / unknown Treat unknown as a question for counsel out of band

On the as-of date 8 September 2026, the clean Cedar & Quay MSA uses expiry 30 September 2027 and a 60-day window. That produces notice date 1 August 2027, 327 days to notice, and 387 days to expiry. Case 2 uses a nearer expiry so the same family can show a window that is already open.

Use FILTER or named filter views for notice-window, missing-pointer, duplicate, and exception queues (FILTER, QUERY, Sort and filter). Filters are not access control. Prefer filter views so editors do not overwrite each other's filters. Conditional formatting can highlight flags. Visual only.

What can run automatically, and what cannot

Situation Automatic action Person required
Complete row with Drive pointer and owner Status may stay active; day counts may update Owner assignment
As-of date crosses notice_date Flag notice_window_open; schedule a reminder Owner reviews the file
Notice passed with no decision Flag renewal_decision_needed Explicit renew, amend, or terminate
File pointer or version key blank Exception missing_or_stale_file_pointer Restore the pointer before any renewal close
Second row, same counterparty and contract id Status duplicate Admin links the canonical row
Actor not on the matrix sets renewed Exception unauthorized_status_change Register admin reviews
Reminder sent, no ack, escalation unreachable Exception reminder_escalation_failed Repair the path. Do not auto-renew
Apps Script time-driven trigger May send MailApp.sendEmail, then write reminder_sent_at Must never change status or auto-renew
Payment, bank-detail change, or final accounting post Never Entirely outside

Google documents installable time-driven triggers and service quotas. Quotas can change without notice. Official pages: Installable Triggers and Quotas for Google Services. The pack includes a reminder-only .gs example. Set Script Properties SPREADSHEET_ID and REMINDER_RECIPIENTS_JSON. The script opens the workbook with SpreadsheetApp.openById, skips rows that already have reminder_sent_at, maps owner name to an approved email, sends MailApp.sendEmail first, and writes reminder_sent_at only after send succeeds. It never changes contract status. It is not executed against a live project here.

Human gates

A person keeps these decisions:

  • Named owner before a row goes active
  • Explicit renew, amend, or terminate
  • Counsel review when the client's policy requires it
  • Duplicate versus canonical row
  • Missing or stale Drive file pointer
  • Unauthorized status change
  • Failed reminder or escalation
  • Who may edit the sheet

A reminder can nudge. It cannot renew. It cannot terminate. It cannot prove that contractual notice was validly delivered.

Failure paths

Failure Expected status Renewal decision
Missing or stale Drive pointer exception_hold + missing_or_stale_file_pointer Blocked
Duplicate counterparty and contract id duplicate No second track
Unauthorized status change to renewed exception_hold + unauthorized_status_change Revert or hold
Expired without a decision expired + expired_without_renewal_decision Escalation required
Reminder or escalation failed renewal_decision_needed + reminder_escalation_failed Still required; no auto-renew
Protected range treated as security Process exception still required Still required
Apps Script sets renewed Out of scope Remove the script

Test cases

The seven Cedar & Quay cases are operational fixtures. They are not customer stories and they do not measure accuracy.

Case Input After the control
Clean active Drive pointer present active, no exception
Approaching notice Same family, nearer expiry notice_window_open
Missing pointer Blank Drive URL or version Decision blocked
Duplicate Second Northshore + same contract id duplicate; canonical continues
Unauthorized change Teammate marks renewed Exception, revert or hold
Expired, no decision Expiry 15 August 2026 expired plus escalation
Failed handoff Reminder sent, Riley unreachable Decision still open

The Google Sheets Contract Register Template walks each case on its own sheet.

What to measure

Record rows with a Drive pointer and version key, active rows with an owner, notice-window rows with a scheduled reminder, blocked unauthorized status changes, suppressed duplicates, and hours from reminder to ack. Put your numbers in the workbook. Keep them as operational counts, not as a savings, accuracy, or ranking result.

There is no live production Google Sheets contract-tracker metric here, and no Flowgrammer customer case. Keyword Planner did not supply volume for the phrase Google Sheets contract tracker. Do not invent one.

How to use the Sheets template

The paired asset is an editable XLSX plus CSV companions, not a marketplace template, not a CLM, and not legal advice.

  1. Import the workbook with File > Import and read the legal-boundary disclaimer.
  2. Confirm the inherited field list and the closed status model.
  3. Recreate dropdowns from Lookups if they dropped on import.
  4. Walk Case01 through Case07. Confirm case 3 blocks renewal and case 7 does not auto-renew.
  5. Create filter views for notice, missing pointer, duplicates, and exceptions.
  6. If you add the reminder script, set SPREADSHEET_ID and REMINDER_RECIPIENTS_JSON, send email first, stamp reminder_sent_at only after send, and never change status.

Future Notion and Airtable contract-tracker children can reuse this dictionary later. Those pages are not published, so they stay unlinked.

Realistic limitations

  • A reminder email is not proof that contractual notice was validly delivered.
  • auto_renew_flag_operational is an operations flag. It does not mean the clause is enforceable.
  • Google sheet or range protection is not a security measure.
  • IMPORTRANGE can split an optional dashboard. It needs Allow Access, has a 10MB receive cap, and must not pull private contract binaries.
  • This guide does not rank CLM or e-sign vendors, and it is not a live Google product walkthrough.
  • No live native Google Sheet and no Template Gallery listing are claimed.

Next step

Download the Google Sheets Contract Register Template and import it. Keep the parent Contract Tracker open while you fill owner, dates, Drive pointer, and the seven cases.

If owners, notice windows, or the destination register are still unclear, start with an AI Success Audit. If one contract family, named owners, and one Sheets register destination are already defined, book a fit call to scope an AI Automation Systems build.

Sources

Frequently asked questions

What is a Google Sheets contract tracker?

It is the published contract register implemented in Sheets: ownership, status, Drive file pointers, dates, and operational notice flags so a person can record an explicit renew, amend, or terminate decision.

Is this legal advice?

No. The register does not interpret clauses, decide enforceability, or replace qualified counsel.

Do notice-window formulas prove legal notice?

No. They are planning aids. Reminder delivery is not proof of valid contractual notice.

Are protected ranges a security control?

No. Google says protection should not be used as a security measure. People can still copy, print, import, and export.

Does this replace a CLM or e-sign product?

No. A Sheets register tracks the row. CLM and e-sign products do different jobs. Signature-complete is not a renewal decision.

Is this the same as the Google Sheets invoice tracker?

No. The invoice workbook owns amounts, tax, PO, and payment status. This workbook inherits the contract field map.

Can Apps Script auto-renew a contract?

No. A reminder script may email an approved mapped recipient, then stamp `reminder_sent_at`. It must never change status or auto-renew, amend, or terminate. The email is not legal notice.