Building a Content Operations Workflow with Google Workspace

The Business Situation

Juniper Vale Software is a fictional 58-person B2B software company with a six-person marketing department. The team includes a marketing operations manager, two content marketers, a product marketer, a designer, and a marketing leader who provides final approval. Product managers and customer-facing specialists participate as subject reviewers.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The marketing department coordinates approximately 42 content items per month. Typical work includes blog articles, customer stories, product pages, webinars, email campaigns, and supporting social posts.

The company already uses Google Workspace. Ideas arrive through chat messages, email, meeting notes, and an informal Google Sheet. Briefs are drafted in Google Docs, while designs and supporting files are stored in different Google Drive folders. Publishing remains a controlled manual action in the company’s content management system.

There was no single record connecting the original request, content owner, draft, design, subject review, final approval, target date, publishing checks, and final URL. The marketing operations manager spent considerable time asking for updates and reconciling conflicting status information.

The team needed to change the process because content volume was rising, deadlines were becoming less predictable, and the spreadsheet could not reliably show which person needed to act next. The goal was not to automate editorial judgment. It was to automate intake, standardization, assignment, reminders, status control, asset organization, and reporting around that judgment.

The Existing Process

The original workflow followed a recognizable sequence, but each step was maintained through a different combination of chat, email, documents, and spreadsheet entries.

1. A marketer posted an idea in a chat channel or mentioned it during a planning meeting.
2. The marketing operations manager copied selected ideas into a planning spreadsheet.
3. A content marketer created a brief in Google Docs and added its link to the spreadsheet.
4. The marketer contacted a product specialist or customer-facing employee for subject review.
5. Review feedback arrived as document comments, email messages, or chat replies.
6. The designer received a separate request, often without the latest copy or final dimensions.
7. The content owner completed SEO and publishing checks using a personal checklist.
8. The marketing leader approved the content by email or chat.
9. A marketer scheduled or published the item in the content management system.
10. The spreadsheet was updated with the final URL when someone remembered to do so.

Duplicate data entry was especially common. A title, campaign, owner, and date might appear in the planning sheet, a brief document, a design request, and a publishing checklist. When one location changed, the other versions were not automatically updated.

The spreadsheet also lacked controlled statuses. Entries such as review, with product, almost done, and waiting could mean different things. These descriptions did not identify the current owner, required action, or deadline.

Finally, the process had weak operational history. The team could see a current status if the spreadsheet was updated, but it could not reliably answer when a record entered review, who approved it, why it was returned, or whether a reminder had been sent.

What the New System Needed to Do

The team defined business requirements before selecting technology. The requirements deliberately separated deterministic workflow rules from editorial decisions that required human judgment.

The system also needed to handle incomplete records, duplicate events, unavailable reviewers, failed notifications, inaccessible Drive folders, expired credentials, and malformed AI responses. A failed automation could not leave the record looking successfully processed.

Implementation Approaches Considered

Simply cleaning up the existing spreadsheet was rejected because it retained the same dependence on manual updates and follow-ups. Dropdowns would improve consistency, but they would not create folders, enforce stage gates, send reminders, or write audit records.

Airtable was a credible alternative. Its relational records and multiple views are well suited to editorial operations. It was not selected because the representative company already had governed Google accounts, Drive storage, and a reporting practice based on Looker Studio. Introducing Airtable would require another permission model and a decision about whether attachments belonged in Airtable or Drive.

Notion was attractive for combining editorial documentation and project records. It was less suitable for this scenario because the team wanted the operational record to behave like controlled data rather than a flexible page. External automation would still be required for stronger validation, reminders, and failure logging.

The Microsoft 365 design could provide comparable functionality with Microsoft Lists, SharePoint, Power Automate, and Power BI. It would be more appropriate for a company already operating in Microsoft 365. It was not appropriate to replace an established Google environment solely for this workflow.

Zapier, Make, or n8n would become more compelling if the workflow needed direct connections to a separate project management system, digital asset management platform, customer relationship management system, or content management system. The initial implementation did not require that extra platform because Apps Script could connect the selected Google services.

The Selected Solution

Juniper Vale Software selected a Google Workspace implementation built around Google Forms, Google Sheets, Apps Script, Google Drive, Gmail, and Looker Studio. The optional AI enhancement uses an approved external AI API only when an authorized content owner requests assistance.

The company retained Google Docs for writing, its existing design application for creative production, and its content management system for publishing. Those tools were linked to the content record rather than replaced.

Manual copying, free-text status updates, routine folder creation, owner lookups, reminder emails, and dashboard preparation were removed. Editorial direction, subject accuracy, design approval, final marketing approval, and the actual publishing action remained human-controlled.

System Architecture and Data Flow

The architecture uses Google Sheets as the central operational record. Google Forms captures requests, Apps Script processes events and applies rules, Google Drive stores assets, Gmail delivers notifications, and Looker Studio reads standardized reporting tables.

1. Form submission: A requester submits a Google Form. The spreadsheet form-submit trigger receives the response row. Required fields and allowed values are checked again in Apps Script, even though the form already performs front-end validation.
2. Standardization: Apps Script trims text, converts the requested date into a real date value, maps the content type to a reporting category, builds a concise deterministic summary, and calculates a duplicate key.
3. Duplicate check: The script compares the duplicate key with existing content records. A repeated event returns the existing record ID and does not create another operational record.
4. Record creation: The script generates an ID such as CNT-2026-0043, assigns a content owner from the Users sheet, calculates the first stage deadline, and appends the record to the Content Tracker sheet.
5. Asset storage: Apps Script creates a Drive folder named with the record ID and working title. The returned folder URL is written to the record.
6. Notification: The requester and content owner receive the new record ID, title, status, requested publishing date, and link to the asset folder.
7. Workflow progression: Authorized users change controlled status and review fields in the Content Tracker. An installable edit trigger validates transitions, updates ownership and dates, and rejects changes that bypass required gates.
8. Review and approval: Subject approval can advance the record to design and SEO. Final human approval can advance it to Scheduled only when the publishing checklist passes.
9. Publishing: A marketer publishes in the content management system, records the final URL and optional CMS identifier, and moves the item to Published.
10. Monitoring: A daily trigger sends reminders, escalates overdue stages, retries selected integration failures, and rebuilds the Calendar View sheet.
11. Reporting: Looker Studio reads the Content Tracker and Calendar View. Dashboard users filter by owner, status, campaign, channel, category, priority, and publishing date.
12. Failure path: Failures are written to the Error Log and the affected record is marked Retry Required or Manual Review. Staff can correct the data and rerun the appropriate action without creating a new content record.

• Intake: Google Forms
• System of record: Google Sheets Content Tracker
• Automation layer: Google Apps Script installable triggers and menu actions
• Document storage: Google Drive folders and Google Docs
• Notifications: Gmail messages sent through Apps Script
• Reporting: Looker Studio connected to Google Sheets
• AI layer: Optional, manually invoked API request with structured output and human review

Data Structure

The spreadsheet contains related sheets rather than one overloaded worksheet. The raw Google Forms response sheet is retained as submitted evidence. The Content Tracker contains one row per content item. Audit, error, and AI logs contain multiple rows related back to the Content Tracker through Record ID.

Related Sheets

Form Responses

Raw submissions plus processing status and returned Record ID. Marketing staff do not use this as the operational tracker.

Users

Active users, roles, category coverage, channel coverage, and backup email addresses used for assignment.

Config

Drive folder ID, escalation timing, AI endpoint, approved model name, and operational email configuration.

Audit Log

Append-only events containing timestamp, Record ID, event name, actor, and concise details.

Error Log

Failures containing the affected step, message, stack information, context, and resolution fields.

Calendar View

Derived rows sorted by requested publishing date, including week start, day name, status, owner, and links.

AI Log

Model, request ID, token usage, output status, confidence, and character counts. It does not store the full prompt or content.

Record ID creates a one-to-many relationship between Content Tracker and each log. Drive folders have a one-to-one relationship with content records. The Calendar View is a derived reporting table and must not be edited directly.

Workflow Statuses and Ownership

A record moves backward when a reviewer requests changes, a publishing check fails, or the content owner identifies missing information. Final rejection moves the record to Cancelled. A temporary dependency moves it to Blocked without losing the record history.

Changing a dropdown is not sufficient to bypass a gate. The Apps Script edit trigger reads the complete row, checks the requested transition, and restores the previous status when required evidence is missing.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify the Google Workspace account that will own the form, spreadsheet, Apps Script project, reporting data sources, and root Drive folder. Use a managed operational account rather than a departing employee’s personal account.
2. Create a shared Drive folder named Marketing Content Operations. If shared drives are unavailable, use a centrally owned folder with restricted membership and documented ownership.
3. Grant the marketing operations manager edit access to the spreadsheet and root folder. Content owners need edit access to operational fields. Reviewers may receive edit access to specific review fields or use protected ranges and a separate review interface.
4. Grant dashboard viewers read access to the reporting spreadsheet or use the data-credential controls appropriate to the organization’s Looker Studio policy.
5. Create test identities for a requester, content owner, subject reviewer, final approver, and marketing operations administrator. Do not test approval paths using one identity for every role.
6. Confirm that the Apps Script owner can use Spreadsheet, Drive, Document, Mail, external request, and trigger services. Authorization prompts will reflect the services used by the installed script.
7. If the optional AI action will be enabled, obtain an approved API key and model name. Store the key in Apps Script project properties under OPENAI_API_KEY. Do not put the key in the spreadsheet.
8. Create a separate test form and spreadsheet copy for development. Production triggers should not point to the test spreadsheet.

Subscription requirements depend on the organization’s Google Workspace configuration, Drive model, Looker Studio requirements, security controls, and AI provider. Confirm that the selected accounts support the required sharing, automation, and reporting features rather than assuming a particular plan includes them.

Step 2: Build the Intake

Create a Google Form named Content Request. Connect it to the implementation spreadsheet as its response destination. Use the following questions exactly if the supplied script will be used without changing its field mapping.

Use form branching to display Priority Reason when Urgent is selected. Apps Script also validates this rule because form configuration can change and submissions can arrive through copied forms or edited response rows.

If file uploads are added, restrict the form to approved users and review the resulting storage location and ownership. File upload behavior can require sign-in and can vary with Workspace administration. The initial design uses governed Drive links because they are easier to validate and do not move files unexpectedly.

The confirmation message should tell the requester that the submission is not yet approved for publication and that a Record ID will be sent after processing. Include a concise privacy notice explaining how brief data, reviewer identities, and optional AI processing will be handled.

Google Forms provides basic spam resistance when submissions require authenticated organizational accounts. If external submissions are allowed, add a moderation queue and do not automatically notify internal reviewers until the request is confirmed.

Step 3: Create the System of Record

Open the form response spreadsheet and add a bound Apps Script project. The setup function supplied later creates these sheets:

• Content Tracker
• Users
• Config
• Audit Log
• Error Log
• Calendar View
• AI Log

The Content Tracker is the operational table. Do not use merged cells, decorative title rows, or blank header rows. Row 1 contains field names, and every subsequent row contains one content record.

The script creates validation lists for Status, Priority, review fields, Yes/No controls, Automation Status, and AI Status. Protect formula, identifier, logging, and automation-managed columns from ordinary editors. Leave only the fields needed for each role editable.

Record IDs use the format CNT-YYYY-NNNN. The sequence is stored in Apps Script properties and generated under a script lock. The lock prevents two simultaneous submissions from receiving the same identifier.

The duplicate key is a SHA-256 digest of normalized requester email, working title, campaign, and requested publishing date. The digest is used for exact matching. It is not intended as encryption or as a substitute for access control.

Create filtered views for current owner, status, overdue work, exceptions, automation failures, and recently published content. Filtered views are preferable to sorting the shared sheet because they do not disrupt other users.

Step 4: Connect the Tools

The Google Form and response spreadsheet connection uses Google’s native response destination. Apps Script uses installable spreadsheet triggers. Drive, Document, and Mail services use the identity that authorized the trigger.

The optional AI connection uses an API key in Script Properties and an HTTPS POST request. The source record is not updated with AI suggestions directly. The response is validated, written to a Google Doc, and linked back to the record for review.

Step 5: Build the Core Automation

The form-submit automation runs in this order:

• Trigger: A new response row is added by Google Forms.
• Conditions: Required values are present, choice fields are allowed, dates are valid, emails have a valid format, and Urgent requests include a reason.
• Actions: Standardize values, calculate the duplicate key, generate the Record ID, categorize the request, assign the content owner, calculate the first stage deadline, append the content record, create the Drive folder, and send notifications.
• Fields updated: All brief fields, category, summary, owners, dates, status, folder link, automation status, and source response row.
• Notification: Requester and owner receive the Record ID and next action.
• Exception: Invalid submissions are marked in the response sheet; partial integration failures are marked Retry Required and logged.

Duplicate prevention occurs before record creation. The script obtains a lock, calculates the brief key, and searches the Brief Key column. If the key already exists, the response row is marked Duplicate and the existing Record ID is returned.

Status changes are processed separately through an installable edit trigger:

1. The trigger captures the requested new status and restores the old status temporarily.
2. The script confirms that the transition is allowed from the current stage.
3. The complete row is checked for required evidence.
4. If the gate fails, the previous status remains and the Error Message identifies the missing fields.
5. If the gate passes, the new status is written.
6. The current owner is changed to the appropriate requester, content owner, reviewer, designer, approver, or marketing operations user.
7. Stage Entered Date, Stage Due Date, Last Updated, and Last Automation Run are updated.
8. An audit event is appended.
9. The new owner receives a notification.
10. The Calendar View is rebuilt.

Bulk pasting status values is prohibited because an edit event may not represent each row as an independent workflow decision. Authorized users should update records individually or use a separately tested bulk transition function.

Step 6: Add Approvals, Reminders, and Escalations

Subject review and final approval use controlled fields rather than interpreting chat messages.

• Subject approval trigger: Subject Review Status changes to Approved.
• Condition: The current workflow status is Subject Review and a Draft Link exists.
• Action: Record Subject Review Date and advance to Design and SEO.
• Return action: Changes Requested returns the record to Drafting and assigns the content owner.

• Final approval trigger: Approval Status changes to Approved, Changes Requested, or Rejected.
• Approved condition: Required design, SEO, link, alt-text, and proofreading checks are complete.
• Approved action: Record Approval Date and advance to Scheduled.
• Changes Requested action: Return to Drafting and preserve review feedback.
• Rejected action: Move to Cancelled and retain approval evidence.

Final approval is a human action. The automation only validates the evidence and performs the resulting deterministic status update.

The daily monitor compares Stage Due Date with the current date. It sends one reminder per day after the due date. After the configured escalation period, marketing operations is copied. The Users sheet includes a Backup Email field that can be used when the current owner is unavailable.

Delegation is handled by updating the relevant reviewer or approver email and recording a Reassignment event in the Audit Log. Marketing operations confirms the delegate’s authority before making the change.

Step 7: Add Documents and File Management

The root Drive folder contains one folder per content record:

Marketing Content Operations
CNT-2026-0043 - Reducing Approval Delays in Field Service Teams
CNT-2026-0044 - Customer Onboarding Webinar
CNT-2026-0045 - Product Release Email

The folder name starts with Record ID because titles can change. Apps Script removes characters that are unsuitable for a folder name and limits the title portion to a manageable length.

Inside each record folder, the team uses a consistent structure when the number of files warrants it:

• 01 Brief and Research
• 02 Drafts
• 03 Design
• 04 Review Evidence
• 05 Published Assets

The supplied script creates the record-level folder. Subfolders can be added to the folder creation function if the team consistently needs them. Avoid creating empty subfolders that add navigation without operational value.

Draft files use names such as CNT-2026-0043 Draft v1. Google Docs version history remains the primary version-control mechanism. Teams should not replace a governed file with an unrelated file at the same link.

The Drive folder inherits access from the root folder. Shared links should not be public. Sensitive customer evidence, licensed media, and unreleased product information require narrower access where appropriate.

If folder creation fails, the record remains in the Content Tracker with Automation Status set to Retry Required. The daily recovery function searches by the unique folder name before creating anything, reducing the chance of duplicate folders.

Step 8: Add Reporting and Operational Views

Connect Looker Studio to the implementation spreadsheet. Create one data source for Content Tracker and one for Calendar View. Configure the first row as headers and confirm that date and timestamp columns are recognized correctly.

Create these operational pages:

• Work Queue: Active items grouped by status and current owner.
• Overdue Work: Items with Stage Due Date before today, excluding Published and Cancelled.
• Editorial Calendar: Planned items sorted by Requested Publish Date with week and day fields.
• Exceptions: Needs Information, Blocked, Retry Required, Manual Review, and rejected records.
• Throughput: Submissions and published items by month, type, channel, and campaign.
• Cycle Time: Days from Created Date to Published Date for completed records.
• Automation Health: Records by Automation Status, Retry Count, and latest automation run.

Useful Looker Studio calculated fields include:

Overdue Flag
CASE
  WHEN Status NOT IN ("Published", "Cancelled")
   AND Stage Due Date < CURRENT_DATE()
  THEN "Overdue"
  ELSE "On time"
END

Cycle Days
DATE_DIFF(Published Date, Created Date)

For the calendar page, use Calendar View as the source. Add a date-range control, a detail table sorted by Publish Date, and a pivot table with Week Start as rows and Day Name as columns. The metric can be Record Count, with title and status available in the detail table. This produces a practical weekly editorial calendar without requiring users to edit a visual calendar separately.

Apps Script rebuilds Calendar View after controlled status changes and during the daily monitor. Looker Studio refresh timing depends on the connector and report configuration, so users should understand that the report is operationally current but not a transactional interface.

The marketing operations manager owns dashboard definitions. Alert thresholds, such as more than five overdue items or any Manual Review records, should be agreed with leadership rather than chosen solely for visual effect.

Step 9: Add Security and Governance Controls

• Limit spreadsheet editing to the marketing team and approved reviewers.
• Protect Record ID, Brief Key, timestamps, calculated ownership fields, automation status, and logs.
• Use role-based Drive groups where available rather than adding users individually to many folders.
• Restrict public and organization-wide link sharing for draft assets.
• Store the AI API key in Apps Script project properties, not Config, source code, or a document.
• Review trigger ownership after role changes and remove former employees promptly.
• Retain the Audit Log and Error Log according to the company’s marketing, privacy, and legal policies.
• Back up the spreadsheet and Apps Script source before material releases.
• Do not send credentials, regulated health information, payment data, contract secrets, or unapproved customer personal data to the AI service.
• Require final human approval before scheduling or publication.
• Record policy exceptions and manual overrides instead of silently bypassing gates.

Apps Script executions, spreadsheet version history, Drive activity, Audit Log entries, and approval fields together provide operational evidence. They do not automatically satisfy a specific regulatory framework. Formal compliance requirements need a separate assessment.

Step 10: Deploy and Test

1. Build the form and spreadsheet in the test environment.
2. Paste the supplied script into the bound Apps Script project.
3. Run setupWorkbook and authorize the requested Google services.
4. Replace Config placeholders, populate Users with test identities, and run runConfigurationCheck.
5. Run installTriggers to create the form-submit, edit, and daily time triggers.
6. Submit at least ten sample records covering all content types, priorities, and design or SEO combinations.
7. Complete every forward, backward, rejected, blocked, and recovery path.
8. Verify Drive folders, email messages, audit rows, error rows, and Calendar View output.
9. Connect a test Looker Studio report and confirm date types, filters, counts, and overdue calculations.
10. Conduct user acceptance testing with a requester, content owner, reviewer, approver, and marketing operations administrator.
11. Pilot the workflow with one campaign for two weeks or one complete content cycle.
12. Document defects and resolve them before copying configuration into production.
13. Activate the production triggers only after the production folder, users, permissions, and reporting sources are confirmed.
14. Keep a rollback copy of the prior planning spreadsheet and a documented method for pausing triggers.
15. Send launch instructions that explain statuses, ownership, review fields, folder use, failure reporting, and where support requests should go.

Code and Configuration

The implementation requires code because native Google Forms and Sheets features do not provide the required duplicate detection, unique identifiers, status gates, owner changes, folder creation, audit logging, reminders, retries, or optional structured AI request.

The following complete Apps Script is placed in the spreadsheet that receives Google Form responses. Open the spreadsheet, open its Apps Script editor, replace the default editor content, and paste the script into a single Code.gs file. Additional files can be created later for maintainability without changing function names.

const APP = Object.freeze({
  sheets: {
    content: "Content Tracker",
    users: "Users",
    config: "Config",
    audit: "Audit Log",
    errors: "Error Log",
    calendar: "Calendar View",
    aiLog: "AI Log"
  },
  headers: {
    content: [
      "Record ID", "Brief Key", "Created Date", "Last Updated",
      "Requester Name", "Requester Email", "Working Title",
      "Business Objective", "Audience", "Content Type", "Channel",
      "Campaign", "Funnel Stage", "Category", "Priority",
      "Priority Reason", "Requested Publish Date", "Published Date",
      "Stage Due Date", "Stage Entered Date", "Status",
      "Content Owner Name", "Content Owner Email",
      "Owner Name", "Owner Email", "Subject Reviewer Email",
      "Subject Review Status", "Subject Review Date",
      "Final Approver Email", "Approval Status", "Approval Date",
      "Design Required", "SEO Required", "Brief Summary",
      "Source Notes", "Source Asset Links", "Draft Link", "Design Link",
      "Asset Folder Link", "SEO Title", "Meta Description",
      "Primary Keyword", "Links Checked", "Alt Text Complete",
      "Proofread Complete", "Scheduled Date", "Published URL",
      "External CMS ID", "Review Feedback", "Exception Type",
      "Automation Status", "Last Automation Run",
      "Last Reminder Sent", "Last Escalation Sent", "Retry Count",
      "Error Message", "AI Allowed", "AI Status", "AI Confidence",
      "AI Output Link", "AI Last Run", "Notes", "Form Response Row"
    ],
    users: [
      "Name", "Email", "Role", "Categories", "Channels",
      "Active", "Backup Email"
    ],
    config: ["Key", "Value", "Description"],
    audit: ["Timestamp", "Record ID", "Event", "Actor", "Details"],
    errors: [
      "Timestamp", "Record ID", "Step", "Message", "Stack",
      "Context", "Resolved", "Resolved By", "Resolved Date"
    ],
    calendar: [
      "Publish Date", "Week Start", "Day Name", "Record ID",
      "Working Title", "Content Type", "Channel", "Campaign",
      "Status", "Owner Name", "Approval Status",
      "Asset Folder Link", "Published URL"
    ],
    aiLog: [
      "Timestamp", "Record ID", "Model", "Status", "Confidence",
      "Input Characters", "Output Characters", "Input Tokens",
      "Output Tokens", "Request ID", "Error"
    ]
  },
  statuses: [
    "Needs Information", "Brief Ready", "Drafting", "Subject Review",
    "Design and SEO", "Final Approval", "Scheduled", "Published",
    "Blocked", "Cancelled"
  ],
  contentTypes: [
    "Blog Article", "Case Study", "Product Page", "Webinar",
    "Email Campaign", "Social Post"
  ],
  channels: [
    "Website", "Email", "Social", "Webinar", "Sales Enablement"
  ],
  funnelStages: ["Awareness", "Consideration", "Decision", "Customer"],
  priorities: ["Standard", "High", "Urgent"]
});

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Content Operations")
    .addItem("Set up workbook", "setupWorkbook")
    .addItem("Install or replace triggers", "installTriggers")
    .addItem("Run configuration check", "runConfigurationCheck")
    .addItem("Refresh calendar view", "refreshCalendarView")
    .addItem("Run daily monitor now", "dailyWorkflowMonitor")
    .addSeparator()
    .addItem("Generate AI assist for selected row", "generateAiAssistForSelectedRow")
    .addToUi();
}

function setupWorkbook() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  PropertiesService.getScriptProperties()
    .setProperty("CONTENT_OPS_SPREADSHEET_ID", ss.getId());

  ensureSheet_(ss, APP.sheets.content, APP.headers.content);
  ensureSheet_(ss, APP.sheets.users, APP.headers.users);
  ensureSheet_(ss, APP.sheets.config, APP.headers.config);
  ensureSheet_(ss, APP.sheets.audit, APP.headers.audit);
  ensureSheet_(ss, APP.sheets.errors, APP.headers.errors);
  ensureSheet_(ss, APP.sheets.calendar, APP.headers.calendar);
  ensureSheet_(ss, APP.sheets.aiLog, APP.headers.aiLog);

  seedConfig_();
  seedUsers_();
  applyValidations_();
  refreshCalendarView();

  SpreadsheetApp.getUi().alert(
    "Workbook structure created. Replace Config and Users placeholders, " +
    "then run the configuration check and install triggers."
  );
}

function installTriggers() {
  const ss = getSpreadsheet_();
  const managedHandlers = [
    "handleFormSubmit",
    "handleContentEdit",
    "dailyWorkflowMonitor"
  ];

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (managedHandlers.indexOf(trigger.getHandlerFunction()) !== -1) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger("handleFormSubmit")
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger("handleContentEdit")
    .forSpreadsheet(ss)
    .onEdit()
    .create();

  ScriptApp.newTrigger("dailyWorkflowMonitor")
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();

  SpreadsheetApp.getUi().alert(
    "Installed form-submit, edit, and daily monitoring triggers."
  );
}

function runConfigurationCheck() {
  const issues = [];
  const config = getConfig_();
  const ss = getSpreadsheet_();

  Object.keys(APP.sheets).forEach(function(key) {
    if (!ss.getSheetByName(APP.sheets[key])) {
      issues.push("Missing sheet: " + APP.sheets[key]);
    }
  });

  if (!config.ROOT_FOLDER_ID ||
      config.ROOT_FOLDER_ID.indexOf("YOUR_") === 0) {
    issues.push("Replace ROOT_FOLDER_ID in Config.");
  } else {
    try {
      DriveApp.getFolderById(config.ROOT_FOLDER_ID).getName();
    } catch (error) {
      issues.push("ROOT_FOLDER_ID is inaccessible: " + error.message);
    }
  }

  if (!isEmail_(config.MARKETING_OPS_EMAIL)) {
    issues.push("Set a valid MARKETING_OPS_EMAIL in Config.");
  }

  const owner = resolveUser_("Content Owner", "*", "*");
  if (!owner.email) {
    issues.push("Add at least one active Content Owner in Users.");
  }

  if (String(config.AI_ENABLED).toUpperCase() === "TRUE") {
    if (!config.AI_ENDPOINT ||
        !config.AI_MODEL ||
        config.AI_MODEL.indexOf("YOUR_") === 0) {
      issues.push("AI is enabled but endpoint or approved model is missing.");
    }
    if (!PropertiesService.getScriptProperties()
        .getProperty("OPENAI_API_KEY")) {
      issues.push("AI is enabled but OPENAI_API_KEY is not in Script Properties.");
    }
  }

  SpreadsheetApp.getUi().alert(
    issues.length === 0
      ? "Configuration check passed."
      : "Configuration issues:\n\n" + issues.join("\n")
  );
}

function handleFormSubmit(e) {
  if (!e || !e.range) {
    throw new Error("This function requires a spreadsheet form-submit event.");
  }

  try {
    processSubmission_(e);
  } catch (error) {
    const context = {
      sheet: e.range.getSheet().getName(),
      row: e.range.getRow()
    };

    logError_("", "FORM_SUBMISSION", error, context);
    updateResponseProcessing_(e, "ERROR", "", error.message);

    try {
      const config = getConfig_();
      sendMail_(
        [config.MARKETING_OPS_EMAIL],
        "Content intake automation failed",
        "A form response failed on row " + e.range.getRow() +
        ". Review the Error Log.\n\n" + error.message
      );
    } catch (notificationError) {
      console.error(notificationError);
    }

    throw error;
  }
}

function processSubmission_(e) {
  const submitted = readSubmission_(e);
  validateSubmission_(submitted);

  const briefKey = buildBriefKey_(submitted);
  const contentSheet = getSpreadsheet_().getSheetByName(APP.sheets.content);
  const lock = LockService.getScriptLock();
  let recordId;
  let recordRow;

  lock.waitLock(30000);

  try {
    const duplicateRow = findRowByValue_(
      contentSheet,
      "Brief Key",
      briefKey
    );

    if (duplicateRow) {
      const existing = rowObject_(contentSheet, duplicateRow);
      updateResponseProcessing_(
        e,
        "DUPLICATE",
        existing["Record ID"],
        "Existing record returned; no new record created."
      );
      logAudit_(
        existing["Record ID"],
        "DUPLICATE_IGNORED",
        submitted.requesterEmail,
        "Form response row " + e.range.getRow()
      );
      return;
    }

    recordId = nextRecordId_(contentSheet);
    const category = categorize_(submitted.contentType);
    const contentOwner = resolveUser_(
      "Content Owner",
      category,
      submitted.channel
    );
    const now = new Date();
    const stageDue = calculateStageDue_(
      "Brief Ready",
      submitted.publishDate
    );

    const record = {
      "Record ID": recordId,
      "Brief Key": briefKey,
      "Created Date": now,
      "Last Updated": now,
      "Requester Name": submitted.requesterName,
      "Requester Email": submitted.requesterEmail,
      "Working Title": submitted.workingTitle,
      "Business Objective": submitted.businessObjective,
      "Audience": submitted.audience,
      "Content Type": submitted.contentType,
      "Channel": submitted.channel,
      "Campaign": submitted.campaign,
      "Funnel Stage": submitted.funnelStage,
      "Category": category,
      "Priority": submitted.priority,
      "Priority Reason": submitted.priorityReason,
      "Requested Publish Date": submitted.publishDate,
      "Published Date": "",
      "Stage Due Date": stageDue,
      "Stage Entered Date": now,
      "Status": "Brief Ready",
      "Content Owner Name": contentOwner.name,
      "Content Owner Email": contentOwner.email,
      "Owner Name": contentOwner.name,
      "Owner Email": contentOwner.email,
      "Subject Reviewer Email": submitted.subjectReviewerEmail,
      "Subject Review Status": "Pending",
      "Subject Review Date": "",
      "Final Approver Email": submitted.finalApproverEmail,
      "Approval Status": "Pending",
      "Approval Date": "",
      "Design Required": submitted.designRequired,
      "SEO Required": submitted.seoRequired,
      "Brief Summary": buildBriefSummary_(submitted, category),
      "Source Notes": submitted.sourceNotes,
      "Source Asset Links": submitted.existingAssetLinks,
      "Draft Link": "",
      "Design Link": "",
      "Asset Folder Link": "",
      "SEO Title": "",
      "Meta Description": "",
      "Primary Keyword": "",
      "Links Checked": "No",
      "Alt Text Complete": "No",
      "Proofread Complete": "No",
      "Scheduled Date": "",
      "Published URL": "",
      "External CMS ID": "",
      "Review Feedback": "",
      "Exception Type": "",
      "Automation Status": "Processing",
      "Last Automation Run": now,
      "Last Reminder Sent": "",
      "Last Escalation Sent": "",
      "Retry Count": 0,
      "Error Message": "",
      "AI Allowed": "No",
      "AI Status": "Not Requested",
      "AI Confidence": "",
      "AI Output Link": "",
      "AI Last Run": "",
      "Notes": "",
      "Form Response Row": e.range.getRow()
    };

    recordRow = appendObjectRow_(contentSheet, record);
  } finally {
    lock.releaseLock();
  }

  const integrationErrors = [];

  try {
    const folder = createOrGetAssetFolder_(
      recordId,
      submitted.workingTitle
    );
    setField_(contentSheet, recordRow, "Asset Folder Link", folder.getUrl());
  } catch (error) {
    integrationErrors.push("ASSET_FOLDER: " + error.message);
    logError_(recordId, "ASSET_FOLDER", error, { row: recordRow });
  }

  try {
    const current = rowObject_(contentSheet, recordRow);
    sendCreationNotice_(current);
  } catch (error) {
    integrationErrors.push("NOTIFICATION: " + error.message);
    logError_(recordId, "CREATION_NOTIFICATION", error, { row: recordRow });
  }

  const now = new Date();
  setField_(contentSheet, recordRow, "Last Automation Run", now);
  setField_(
    contentSheet,
    recordRow,
    "Automation Status",
    integrationErrors.length ? "Retry Required" : "Complete"
  );
  setField_(
    contentSheet,
    recordRow,
    "Exception Type",
    integrationErrors.length ? "INTEGRATION_FAILURE" : ""
  );
  setField_(
    contentSheet,
    recordRow,
    "Error Message",
    integrationErrors.join(" | ")
  );

  updateResponseProcessing_(
    e,
    integrationErrors.length ? "PROCESSED_WITH_WARNING" : "PROCESSED",
    recordId,
    integrationErrors.join(" | ")
  );

  logAudit_(
    recordId,
    "RECORD_CREATED",
    submitted.requesterEmail,
    JSON.stringify({
      status: "Brief Ready",
      owner: rowObject_(contentSheet, recordRow)["Owner Email"],
      responseRow: e.range.getRow()
    })
  );

  refreshCalendarView();
}

function readSubmission_(e) {
  return {
    requesterName: clean_(formValue_(e, "Requester Name")),
    requesterEmail: clean_(formValue_(e, "Requester Email")).toLowerCase(),
    workingTitle: clean_(formValue_(e, "Working Title")),
    businessObjective: clean_(formValue_(e, "Business Objective")),
    audience: clean_(formValue_(e, "Primary Audience")),
    contentType: clean_(formValue_(e, "Content Type")),
    channel: clean_(formValue_(e, "Primary Channel")),
    campaign: clean_(formValue_(e, "Campaign")),
    funnelStage: clean_(formValue_(e, "Funnel Stage")),
    publishDate: asDate_(formValue_(e, "Requested Publish Date")),
    priority: clean_(formValue_(e, "Priority")),
    priorityReason: clean_(formValue_(e, "Priority Reason")),
    designRequired: clean_(formValue_(e, "Design Required")),
    seoRequired: clean_(formValue_(e, "SEO Required")),
    subjectReviewerEmail: clean_(
      formValue_(e, "Subject Reviewer Email")
    ).toLowerCase(),
    finalApproverEmail: clean_(
      formValue_(e, "Final Approver Email")
    ).toLowerCase(),
    sourceNotes: clean_(formValue_(e, "Source Notes")),
    existingAssetLinks: clean_(formValue_(e, "Existing Asset Links"))
  };
}

function validateSubmission_(submitted) {
  const required = {
    "Requester Name": submitted.requesterName,
    "Requester Email": submitted.requesterEmail,
    "Working Title": submitted.workingTitle,
    "Business Objective": submitted.businessObjective,
    "Primary Audience": submitted.audience,
    "Content Type": submitted.contentType,
    "Primary Channel": submitted.channel,
    "Campaign": submitted.campaign,
    "Funnel Stage": submitted.funnelStage,
    "Requested Publish Date": submitted.publishDate,
    "Priority": submitted.priority,
    "Design Required": submitted.designRequired,
    "SEO Required": submitted.seoRequired,
    "Subject Reviewer Email": submitted.subjectReviewerEmail,
    "Final Approver Email": submitted.finalApproverEmail
  };

  const missing = Object.keys(required).filter(function(key) {
    const value = required[key];
    return value === "" || value === null ||
      (value instanceof Date && isNaN(value.getTime()));
  });

  if (missing.length) {
    throw new Error("Missing or invalid required fields: " + missing.join(", "));
  }

  if (!isEmail_(submitted.requesterEmail) ||
      !isEmail_(submitted.subjectReviewerEmail) ||
      !isEmail_(submitted.finalApproverEmail)) {
    throw new Error("One or more email addresses are invalid.");
  }

  validateChoice_(submitted.contentType, APP.contentTypes, "Content Type");
  validateChoice_(submitted.channel, APP.channels, "Primary Channel");
  validateChoice_(submitted.funnelStage, APP.funnelStages, "Funnel Stage");
  validateChoice_(submitted.priority, APP.priorities, "Priority");
  validateChoice_(submitted.designRequired, ["Yes", "No"], "Design Required");
  validateChoice_(submitted.seoRequired, ["Yes", "No"], "SEO Required");

  if (submitted.priority === "Urgent" && !submitted.priorityReason) {
    throw new Error("Priority Reason is required for Urgent requests.");
  }
}

function handleContentEdit(e) {
  if (!e || !e.range || e.range.getNumRows() !== 1 ||
      e.range.getNumColumns() !== 1) {
    return;
  }

  const sheet = e.range.getSheet();
  if (sheet.getName() !== APP.sheets.content ||
      e.range.getRow() === 1) {
    return;
  }

  const header = sheet.getRange(1, e.range.getColumn()).getValue();
  const row = e.range.getRow();
  const actor = eventActor_(e);
  const now = new Date();

  setField_(sheet, row, "Last Updated", now);

  if (header === "Status") {
    const requestedStatus = clean_(e.value);
    const oldStatus = clean_(e.oldValue);
    e.range.setValue(oldStatus);

    try {
      changeStatus_(sheet, row, requestedStatus, actor);
    } catch (error) {
      markRowException_(
        sheet,
        row,
        "STATUS_GATE_FAILED",
        error.message,
        "Manual Review"
      );
      logError_(
        rowObject_(sheet, row)["Record ID"],
        "STATUS_CHANGE",
        error,
        { oldStatus: oldStatus, requestedStatus: requestedStatus }
      );
    }
    return;
  }

  if (header === "Subject Review Status") {
    handleSubjectReviewEdit_(sheet, row, clean_(e.value), actor);
    return;
  }

  if (header === "Approval Status") {
    handleApprovalEdit_(sheet, row, clean_(e.value), actor);
    return;
  }

  const auditedFields = [
    "Owner Email", "Subject Reviewer Email", "Final Approver Email",
    "Draft Link", "Design Link", "Published URL", "External CMS ID",
    "Links Checked", "Alt Text Complete", "Proofread Complete",
    "AI Allowed", "AI Status"
  ];

  if (auditedFields.indexOf(header) !== -1) {
    const record = rowObject_(sheet, row);
    logAudit_(
      record["Record ID"],
      "FIELD_UPDATED",
      actor,
      JSON.stringify({ field: header })
    );
  }
}

function handleSubjectReviewEdit_(sheet, row, value, actor) {
  if (["Pending", "Approved", "Changes Requested"].indexOf(value) === -1) {
    markRowException_(
      sheet,
      row,
      "INVALID_REVIEW_VALUE",
      "Invalid Subject Review Status.",
      "Manual Review"
    );
    return;
  }

  const record = rowObject_(sheet, row);

  logAudit_(
    record["Record ID"],
    "SUBJECT_REVIEW_UPDATED",
    actor,
    value
  );

  if (value === "Approved") {
    setField_(sheet, row, "Subject Review Date", new Date());
    try {
      changeStatus_(sheet, row, "Design and SEO", actor);
    } catch (error) {
      markRowException_(
        sheet,
        row,
        "SUBJECT_REVIEW_GATE",
        error.message,
        "Manual Review"
      );
    }
  }

  if (value === "Changes Requested") {
    changeStatus_(sheet, row, "Drafting", actor, true);
  }
}

function handleApprovalEdit_(sheet, row, value, actor) {
  const allowed = [
    "Pending", "Approved", "Changes Requested", "Rejected"
  ];

  if (allowed.indexOf(value) === -1) {
    markRowException_(
      sheet,
      row,
      "INVALID_APPROVAL_VALUE",
      "Invalid Approval Status.",
      "Manual Review"
    );
    return;
  }

  const record = rowObject_(sheet, row);

  logAudit_(
    record["Record ID"],
    "FINAL_APPROVAL_UPDATED",
    actor,
    value
  );

  if (value === "Approved") {
    setField_(sheet, row, "Approval Date", new Date());

    try {
      changeStatus_(sheet, row, "Scheduled", actor);
    } catch (error) {
      setField_(
        sheet,
        row,
        "Owner Name",
        record["Content Owner Name"]
      );
      setField_(
        sheet,
        row,
        "Owner Email",
        record["Content Owner Email"]
      );
      markRowException_(
        sheet,
        row,
        "PUBLISHING_CHECKS_INCOMPLETE",
        error.message,
        "Manual Review"
      );
    }
  }

  if (value === "Changes Requested") {
    changeStatus_(sheet, row, "Drafting", actor, true);
  }

  if (value === "Rejected") {
    changeStatus_(sheet, row, "Cancelled", actor, true);
  }
}

function changeStatus_(sheet, row, newStatus, actor, bypassTransition) {
  const record = rowObject_(sheet, row);
  const oldStatus = clean_(record["Status"]);

  if (APP.statuses.indexOf(newStatus) === -1) {
    throw new Error("Invalid target status: " + newStatus);
  }

  if (newStatus === oldStatus) {
    return;
  }

  if (!bypassTransition) {
    validateTransition_(oldStatus, newStatus);
  }

  validateStatusGate_(record, newStatus);

  const now = new Date();
  setField_(sheet, row, "Status", newStatus);
  setField_(sheet, row, "Stage Entered Date", now);
  setField_(sheet, row, "Last Updated", now);
  setField_(sheet, row, "Last Automation Run", now);
  setField_(
    sheet,
    row,
    "Stage Due Date",
    calculateStageDue_(newStatus, record["Requested Publish Date"])
  );
  setField_(sheet, row, "Exception Type", "");
  setField_(sheet, row, "Error Message", "");
  setField_(sheet, row, "Automation Status", "Complete");

  if (newStatus === "Final Approval") {
    setField_(sheet, row, "Approval Status", "Pending");
  }

  if (newStatus === "Scheduled" && !record["Scheduled Date"]) {
    setField_(sheet, row, "Scheduled Date", now);
  }

  if (newStatus === "Published") {
    setField_(sheet, row, "Published Date", now);
  }

  const owner = ownerForStatus_(record, newStatus);
  setField_(sheet, row, "Owner Name", owner.name);
  setField_(sheet, row, "Owner Email", owner.email);

  logAudit_(
    record["Record ID"],
    "STATUS_CHANGED",
    actor,
    JSON.stringify({ from: oldStatus, to: newStatus, owner: owner.email })
  );

  try {
    const updated = rowObject_(sheet, row);
    sendStatusNotice_(updated, oldStatus, newStatus);
  } catch (error) {
    markRowException_(
      sheet,
      row,
      "NOTIFICATION_FAILURE",
      "NOTIFICATION: " + error.message,
      "Retry Required"
    );
    logError_(
      record["Record ID"],
      "STATUS_NOTIFICATION",
      error,
      { from: oldStatus, to: newStatus }
    );
  }

  refreshCalendarView();
}

function validateTransition_(fromStatus, toStatus) {
  const transitions = {
    "Needs Information": ["Brief Ready", "Drafting", "Blocked", "Cancelled"],
    "Brief Ready": ["Drafting", "Needs Information", "Blocked", "Cancelled"],
    "Drafting": ["Subject Review", "Needs Information", "Blocked", "Cancelled"],
    "Subject Review": [
      "Drafting", "Design and SEO", "Blocked", "Cancelled"
    ],
    "Design and SEO": [
      "Final Approval", "Drafting", "Blocked", "Cancelled"
    ],
    "Final Approval": ["Scheduled", "Drafting", "Blocked", "Cancelled"],
    "Scheduled": ["Published", "Blocked", "Cancelled"],
    "Published": [],
    "Blocked": [
      "Brief Ready", "Drafting", "Subject Review", "Design and SEO",
      "Final Approval", "Scheduled", "Cancelled"
    ],
    "Cancelled": []
  };

  if (!transitions[fromStatus] ||
      transitions[fromStatus].indexOf(toStatus) === -1) {
    throw new Error(
      "Transition from " + fromStatus + " to " + toStatus +
      " is not allowed."
    );
  }
}

function validateStatusGate_(record, targetStatus) {
  const missing = [];

  if (targetStatus === "Brief Ready") {
    ["Business Objective", "Audience", "Content Type", "Channel"].forEach(
      function(field) {
        if (!clean_(record[field])) {
          missing.push(field);
        }
      }
    );
  }

  if (targetStatus === "Subject Review" &&
      !clean_(record["Draft Link"])) {
    missing.push("Draft Link");
  }

  if (targetStatus === "Design and SEO" &&
      record["Subject Review Status"] !== "Approved") {
    missing.push("Subject Review Status = Approved");
  }

  if (targetStatus === "Final Approval") {
    if (!clean_(record["Draft Link"])) {
      missing.push("Draft Link");
    }
    if (record["Subject Review Status"] !== "Approved") {
      missing.push("Subject Review Status = Approved");
    }
    if (record["Design Required"] === "Yes" &&
        !clean_(record["Design Link"])) {
      missing.push("Design Link");
    }
  }

  if (targetStatus === "Scheduled") {
    if (record["Approval Status"] !== "Approved") {
      missing.push("Approval Status = Approved");
    }
    missing.push.apply(missing, publishingCheckFailures_(record));
  }

  if (targetStatus === "Published" &&
      !clean_(record["Published URL"])) {
    missing.push("Published URL");
  }

  if (missing.length) {
    throw new Error("Required gate items: " + missing.join(", "));
  }
}

function publishingCheckFailures_(record) {
  const failures = [];

  if (record["Links Checked"] !== "Yes") {
    failures.push("Links Checked = Yes");
  }

  if (record["Proofread Complete"] !== "Yes") {
    failures.push("Proofread Complete = Yes");
  }

  if (record["Design Required"] === "Yes") {
    if (!clean_(record["Design Link"])) {
      failures.push("Design Link");
    }
    if (record["Alt Text Complete"] !== "Yes") {
      failures.push("Alt Text Complete = Yes");
    }
  }

  if (record["SEO Required"] === "Yes") {
    const seoTitle = clean_(record["SEO Title"]);
    const meta = clean_(record["Meta Description"]);

    if (!seoTitle) {
      failures.push("SEO Title");
    } else if (seoTitle.length > 65) {
      failures.push("SEO Title must be 65 characters or fewer");
    }

    if (!meta) {
      failures.push("Meta Description");
    } else if (meta.length > 160) {
      failures.push("Meta Description must be 160 characters or fewer");
    }

    if (!clean_(record["Primary Keyword"])) {
      failures.push("Primary Keyword");
    }
  }

  return failures;
}

function ownerForStatus_(record, status) {
  if (status === "Needs Information") {
    return {
      name: record["Requester Name"],
      email: record["Requester Email"]
    };
  }

  if (["Brief Ready", "Drafting"].indexOf(status) !== -1) {
    return {
      name: record["Content Owner Name"],
      email: record["Content Owner Email"]
    };
  }

  if (status === "Subject Review") {
    return {
      name: "Subject Reviewer",
      email: record["Subject Reviewer Email"]
    };
  }

  if (status === "Design and SEO") {
    return resolveUser_("Designer", record["Category"], record["Channel"]);
  }

  if (status === "Final Approval") {
    return {
      name: "Final Approver",
      email: record["Final Approver Email"]
    };
  }

  if (["Scheduled", "Published", "Cancelled"].indexOf(status) !== -1) {
    return resolveUser_(
      "Marketing Operations",
      record["Category"],
      record["Channel"]
    );
  }

  return {
    name: record["Owner Name"],
    email: record["Owner Email"]
  };
}

function dailyWorkflowMonitor() {
  const ss = getSpreadsheet_();
  const sheet = ss.getSheetByName(APP.sheets.content);
  const config = getConfig_();
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) {
    refreshCalendarView();
    return;
  }

  const headers = values[0];
  const today = dateOnly_(new Date());
  const escalationDays = Number(config.ESCALATION_DAYS || 2);

  for (let i = 1; i < values.length; i++) {
    const record = objectFromValues_(headers, values[i]);
    const status = record["Status"];

    if (["Published", "Cancelled"].indexOf(status) !== -1) {
      continue;
    }

    const due = asDate_(record["Stage Due Date"]);
    if (!due || isNaN(due.getTime()) || dateOnly_(due) >= today) {
      continue;
    }

    const row = i + 1;
    const overdueDays = daysBetween_(dateOnly_(due), today);
    const reminderSent = asDate_(record["Last Reminder Sent"]);
    const reminderAlreadySent = reminderSent &&
      sameDate_(reminderSent, today);

    if (!reminderAlreadySent) {
      const recipients = [record["Owner Email"]];
      let escalated = false;

      if (overdueDays >= escalationDays) {
        recipients.push(config.MARKETING_OPS_EMAIL);
        escalated = true;
      }

      try {
        sendMail_(
          recipients,
          "Overdue content action: " + record["Record ID"],
          "Record: " + record["Record ID"] +
          "\nTitle: " + record["Working Title"] +
          "\nStatus: " + status +
          "\nOwner: " + record["Owner Email"] +
          "\nStage due date: " + formatDate_(due) +
          "\nDays overdue: " + overdueDays +
          "\n\nOpen the Content Tracker and complete or reassign the action."
        );

        setField_(sheet, row, "Last Reminder Sent", new Date());

        if (escalated) {
          setField_(sheet, row, "Last Escalation Sent", new Date());
        }

        logAudit_(
          record["Record ID"],
          escalated ? "OVERDUE_ESCALATION" : "OVERDUE_REMINDER",
          "AUTOMATION",
          "Days overdue: " + overdueDays
        );
      } catch (error) {
        markRowException_(
          sheet,
          row,
          "NOTIFICATION_FAILURE",
          "NOTIFICATION: " + error.message,
          "Retry Required"
        );
        logError_(
          record["Record ID"],
          "OVERDUE_NOTIFICATION",
          error,
          { row: row }
        );
      }
    }
  }

  retryFailedRecords_();
  refreshCalendarView();
}

function retryFailedRecords_() {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.content);
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) {
    return;
  }

  const headers = values[0];

  for (let i = 1; i < values.length; i++) {
    const record = objectFromValues_(headers, values[i]);

    if (record["Automation Status"] !== "Retry Required") {
      continue;
    }

    const row = i + 1;
    const retryCount = Number(record["Retry Count"] || 0);

    if (retryCount >= 3) {
      setField_(sheet, row, "Automation Status", "Manual Review");
      continue;
    }

    const errors = [];
    const priorError = clean_(record["Error Message"]);

    if (!clean_(record["Asset Folder Link"]) ||
        priorError.indexOf("ASSET_FOLDER") !== -1) {
      try {
        const folder = createOrGetAssetFolder_(
          record["Record ID"],
          record["Working Title"]
        );
        setField_(sheet, row, "Asset Folder Link", folder.getUrl());
      } catch (error) {
        errors.push("ASSET_FOLDER: " + error.message);
      }
    }

    if (priorError.indexOf("NOTIFICATION") !== -1) {
      try {
        sendMail_(
          [record["Owner Email"]],
          "Recovered content workflow notification: " +
            record["Record ID"],
          "The automation recovered a previously failed action.\n\n" +
          "Record: " + record["Record ID"] +
          "\nTitle: " + record["Working Title"] +
          "\nStatus: " + record["Status"]
        );
      } catch (error) {
        errors.push("NOTIFICATION: " + error.message);
      }
    }

    setField_(sheet, row, "Retry Count", retryCount + 1);
    setField_(sheet, row, "Last Automation Run", new Date());

    if (errors.length) {
      setField_(sheet, row, "Error Message", errors.join(" | "));
      setField_(
        sheet,
        row,
        "Automation Status",
        retryCount + 1 >= 3 ? "Manual Review" : "Retry Required"
      );
    } else {
      setField_(sheet, row, "Error Message", "");
      setField_(sheet, row, "Exception Type", "");
      setField_(sheet, row, "Automation Status", "Complete");
      logAudit_(
        record["Record ID"],
        "AUTOMATION_RECOVERED",
        "AUTOMATION",
        "Retry " + (retryCount + 1)
      );
    }
  }
}

function refreshCalendarView() {
  const ss = getSpreadsheet_();
  const contentSheet = ss.getSheetByName(APP.sheets.content);
  const calendarSheet = ss.getSheetByName(APP.sheets.calendar);

  if (!contentSheet || !calendarSheet) {
    return;
  }

  const values = contentSheet.getDataRange().getValues();
  const output = [APP.headers.calendar];

  if (values.length > 1) {
    const headers = values[0];

    for (let i = 1; i < values.length; i++) {
      const record = objectFromValues_(headers, values[i]);
      const publishDate = asDate_(record["Requested Publish Date"]);

      if (!publishDate || isNaN(publishDate.getTime())) {
        continue;
      }

      const weekStart = startOfWeek_(publishDate);

      output.push([
        publishDate,
        weekStart,
        Utilities.formatDate(
          publishDate,
          getSpreadsheet_().getSpreadsheetTimeZone(),
          "EEEE"
        ),
        record["Record ID"],
        record["Working Title"],
        record["Content Type"],
        record["Channel"],
        record["Campaign"],
        record["Status"],
        record["Owner Name"],
        record["Approval Status"],
        record["Asset Folder Link"],
        record["Published URL"]
      ]);
    }

    output.splice(
      1,
      output.length - 1,
      ...output.slice(1).sort(function(a, b) {
        return a[0].getTime() - b[0].getTime();
      })
    );
  }

  calendarSheet.clearContents();
  calendarSheet.getRange(1, 1, output.length, output[0].length)
    .setValues(output);
  calendarSheet.setFrozenRows(1);

  if (output.length > 1) {
    calendarSheet.getRange(2, 1, output.length - 1, 2)
      .setNumberFormat("yyyy-mm-dd");
  }
}

function generateAiAssistForSelectedRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveRange().getRow();

  if (sheet.getName() !== APP.sheets.content || row < 2) {
    SpreadsheetApp.getUi().alert(
      "Select one content record in the Content Tracker."
    );
    return;
  }

  runAiAssistForRow_(sheet, row);
}

function runAiAssistForRow_(sheet, row) {
  const config = getConfig_();
  const record = rowObject_(sheet, row);

  if (String(config.AI_ENABLED).toUpperCase() !== "TRUE") {
    throw new Error("AI is not enabled in Config.");
  }

  if (record["AI Allowed"] !== "Yes") {
    throw new Error("Set AI Allowed to Yes after reviewing data restrictions.");
  }

  const apiKey = PropertiesService.getScriptProperties()
    .getProperty("OPENAI_API_KEY");

  if (!apiKey) {
    throw new Error("OPENAI_API_KEY is missing from Script Properties.");
  }

  setField_(sheet, row, "AI Status", "Processing");
  setField_(sheet, row, "AI Last Run", new Date());

  const systemInstruction =
    "You assist a B2B marketing content team. Use only the supplied brief. " +
    "Do not invent customer facts, product capabilities, statistics, quotes, " +
    "legal claims, or evidence. Identify missing information explicitly. " +
    "Create planning suggestions, not final approval or publication decisions. " +
    "Return only JSON matching the supplied schema.";

  const userPrompt = [
    "Record ID: " + record["Record ID"],
    "Working title: " + record["Working Title"],
    "Business objective: " + sanitizeForAi_(
      record["Business Objective"]
    ),
    "Audience: " + sanitizeForAi_(record["Audience"]),
    "Content type: " + record["Content Type"],
    "Primary channel: " + record["Channel"],
    "Campaign: " + sanitizeForAi_(record["Campaign"]),
    "Funnel stage: " + record["Funnel Stage"],
    "Category: " + record["Category"],
    "Source notes: " + sanitizeForAi_(
      truncate_(record["Source Notes"], 5000)
    ),
    "",
    "Produce:",
    "1. A concise brief summary.",
    "2. A practical outline with the purpose of each section and evidence needed.",
    "3. Up to three repurposing suggestions appropriate to the supplied channels.",
    "4. Missing information and risk flags.",
    "5. A confidence score from 0 to 1 based only on brief completeness."
  ].join("\n");

  try {
    const result = callAiApi_(
      config,
      apiKey,
      systemInstruction,
      userPrompt
    );

    const validated = validateAiOutput_(result.data);
    const outputLink = createAiOutputDocument_(record, validated);
    const status = validated.confidence < 0.75
      ? "Low Confidence"
      : "Review Required";

    setField_(sheet, row, "AI Status", status);
    setField_(sheet, row, "AI Confidence", validated.confidence);
    setField_(sheet, row, "AI Output Link", outputLink);
    setField_(sheet, row, "AI Last Run", new Date());

    logAi_({
      recordId: record["Record ID"],
      model: config.AI_MODEL,
      status: status,
      confidence: validated.confidence,
      inputCharacters: systemInstruction.length + userPrompt.length,
      outputCharacters: result.outputText.length,
      inputTokens: result.inputTokens,
      outputTokens: result.outputTokens,
      requestId: result.requestId,
      error: ""
    });

    logAudit_(
      record["Record ID"],
      "AI_ASSIST_CREATED",
      eventActor_(),
      "Status: " + status
    );
  } catch (error) {
    setField_(sheet, row, "AI Status", "Error");
    setField_(sheet, row, "Error Message", "AI: " + error.message);

    logAi_({
      recordId: record["Record ID"],
      model: config.AI_MODEL,
      status: "Error",
      confidence: "",
      inputCharacters: systemInstruction.length + userPrompt.length,
      outputCharacters: 0,
      inputTokens: "",
      outputTokens: "",
      requestId: "",
      error: error.message
    });

    logError_(
      record["Record ID"],
      "AI_ASSIST",
      error,
      { row: row }
    );

    throw error;
  }
}

function callAiApi_(config, apiKey, systemInstruction, userPrompt) {
  const schema = {
    type: "object",
    additionalProperties: false,
    required: [
      "brief_summary", "outline", "repurposing",
      "missing_information", "risk_flags", "confidence"
    ],
    properties: {
      brief_summary: { type: "string" },
      outline: {
        type: "array",
        items: {
          type: "object",
          additionalProperties: false,
          required: ["heading", "purpose", "evidence_needed"],
          properties: {
            heading: { type: "string" },
            purpose: { type: "string" },
            evidence_needed: {
              type: "array",
              items: { type: "string" }
            }
          }
        }
      },
      repurposing: {
        type: "array",
        maxItems: 3,
        items: {
          type: "object",
          additionalProperties: false,
          required: ["channel", "format", "draft_direction"],
          properties: {
            channel: { type: "string" },
            format: { type: "string" },
            draft_direction: { type: "string" }
          }
        }
      },
      missing_information: {
        type: "array",
        items: { type: "string" }
      },
      risk_flags: {
        type: "array",
        items: { type: "string" }
      },
      confidence: {
        type: "number",
        minimum: 0,
        maximum: 1
      }
    }
  };

  const payload = {
    model: config.AI_MODEL,
    input: [
      {
        role: "system",
        content: [{ type: "input_text", text: systemInstruction }]
      },
      {
        role: "user",
        content: [{ type: "input_text", text: userPrompt }]
      }
    ],
    text: {
      format: {
        type: "json_schema",
        name: "content_operations_assist",
        strict: true,
        schema: schema
      }
    }
  };

  let lastError;

  for (let attempt = 0; attempt < 3; attempt++) {
    const response = UrlFetchApp.fetch(config.AI_ENDPOINT, {
      method: "post",
      contentType: "application/json",
      headers: {
        Authorization: "Bearer " + apiKey
      },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    const statusCode = response.getResponseCode();
    const body = response.getContentText();
    const headers = response.getHeaders();

    if (statusCode >= 200 && statusCode < 300) {
      const parsed = JSON.parse(body);
      const outputText = extractAiOutputText_(parsed);
      const data = JSON.parse(outputText);
      const usage = parsed.usage || {};

      return {
        data: data,
        outputText: outputText,
        inputTokens: usage.input_tokens || "",
        outputTokens: usage.output_tokens || "",
        requestId: headers["x-request-id"] ||
          headers["X-Request-Id"] || ""
      };
    }

    lastError = new Error(
      "AI API returned HTTP " + statusCode + ": " +
      truncate_(body, 1000)
    );

    if ([429, 500, 502, 503, 504].indexOf(statusCode) === -1) {
      break;
    }

    Utilities.sleep(1000 * Math.pow(2, attempt));
  }

  throw lastError || new Error("AI API request failed.");
}

function extractAiOutputText_(response) {
  if (response.output_text) {
    return response.output_text;
  }

  const output = response.output || [];

  for (let i = 0; i < output.length; i++) {
    const content = output[i].content || [];

    for (let j = 0; j < content.length; j++) {
      if (content[j].type === "output_text" && content[j].text) {
        return content[j].text;
      }
    }
  }

  throw new Error("AI response did not contain output text.");
}

function validateAiOutput_(data) {
  if (!data || typeof data !== "object") {
    throw new Error("AI output is not an object.");
  }

  if (typeof data.brief_summary !== "string" ||
      !Array.isArray(data.outline) ||
      !Array.isArray(data.repurposing) ||
      !Array.isArray(data.missing_information) ||
      !Array.isArray(data.risk_flags) ||
      typeof data.confidence !== "number" ||
      data.confidence < 0 ||
      data.confidence > 1) {
    throw new Error("AI output failed structural validation.");
  }

  return data;
}

function createAiOutputDocument_(record, data) {
  const title = record["Record ID"] + " AI Planning Assist " +
    Utilities.formatDate(
      new Date(),
      getSpreadsheet_().getSpreadsheetTimeZone(),
      "yyyyMMdd-HHmm"
    );

  const doc = DocumentApp.create(title);
  const body = doc.getBody();

  body.appendParagraph("AI Planning Assist");
  body.appendParagraph("Record ID: " + record["Record ID"]);
  body.appendParagraph("Working title: " + record["Working Title"]);
  body.appendParagraph(
    "Review status: Human review required before use."
  );

  body.appendParagraph("Brief Summary");
  body.appendParagraph(data.brief_summary);

  body.appendParagraph("Outline");
  data.outline.forEach(function(item, index) {
    body.appendParagraph((index + 1) + ". " + item.heading);
    body.appendParagraph("Purpose: " + item.purpose);
    body.appendParagraph(
      "Evidence needed: " +
      (item.evidence_needed.length
        ? item.evidence_needed.join("; ")
        : "None identified")
    );
  });

  body.appendParagraph("Repurposing Suggestions");
  data.repurposing.forEach(function(item) {
    body.appendParagraph(
      item.channel + " | " + item.format + " | " + item.draft_direction
    );
  });

  body.appendParagraph("Missing Information");
  body.appendParagraph(
    data.missing_information.length
      ? data.missing_information.join("; ")
      : "None identified"
  );

  body.appendParagraph("Risk Flags");
  body.appendParagraph(
    data.risk_flags.length ? data.risk_flags.join("; ") : "None identified"
  );

  body.appendParagraph("Model Confidence");
  body.appendParagraph(String(data.confidence));

  doc.saveAndClose();

  const file = DriveApp.getFileById(doc.getId());
  const folderUrl = clean_(record["Asset Folder Link"]);

  if (folderUrl) {
    const folderId = extractDriveId_(folderUrl);
    if (folderId) {
      file.moveTo(DriveApp.getFolderById(folderId));
    }
  }

  return doc.getUrl();
}

function createOrGetAssetFolder_(recordId, title) {
  const config = getConfig_();

  if (!config.ROOT_FOLDER_ID ||
      config.ROOT_FOLDER_ID.indexOf("YOUR_") === 0) {
    throw new Error("ROOT_FOLDER_ID is not configured.");
  }

  const root = DriveApp.getFolderById(config.ROOT_FOLDER_ID);
  const safeTitle = truncate_(
    clean_(title).replace(/[<>:"\/\\|?*]/g, ""),
    80
  );
  const folderName = recordId + " - " + safeTitle;
  const existing = root.getFoldersByName(folderName);

  if (existing.hasNext()) {
    return existing.next();
  }

  return root.createFolder(folderName);
}

function sendCreationNotice_(record) {
  sendMail_(
    [record["Requester Email"], record["Owner Email"]],
    "Content request created: " + record["Record ID"],
    "Record: " + record["Record ID"] +
    "\nTitle: " + record["Working Title"] +
    "\nStatus: " + record["Status"] +
    "\nRequested publish date: " +
      formatDate_(record["Requested Publish Date"]) +
    "\nCurrent owner: " + record["Owner Email"] +
    "\nAsset folder: " + record["Asset Folder Link"] +
    "\n\nThis record is not approved for publication until all gates pass."
  );
}

function sendStatusNotice_(record, oldStatus, newStatus) {
  sendMail_(
    [record["Owner Email"]],
    "Content action assigned: " + record["Record ID"],
    "Record: " + record["Record ID"] +
    "\nTitle: " + record["Working Title"] +
    "\nPrevious status: " + oldStatus +
    "\nNew status: " + newStatus +
    "\nCurrent owner: " + record["Owner Email"] +
    "\nStage due date: " + formatDate_(record["Stage Due Date"]) +
    "\nAsset folder: " + record["Asset Folder Link"]
  );
}

function sendMail_(recipients, subject, body) {
  const validRecipients = Array.from(
    new Set(
      recipients
        .map(function(value) {
          return clean_(value).toLowerCase();
        })
        .filter(isEmail_)
    )
  );

  if (!validRecipients.length) {
    throw new Error("No valid notification recipient.");
  }

  MailApp.sendEmail({
    to: validRecipients.join(","),
    subject: subject,
    body: body,
    name: "Content Operations"
  });
}

function resolveUser_(role, category, channel) {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.users);
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) {
    return { name: "", email: "" };
  }

  const headers = values[0];
  let fallback = null;

  for (let i = 1; i < values.length; i++) {
    const user = objectFromValues_(headers, values[i]);

    if (String(user.Active).toUpperCase() !== "TRUE" ||
        clean_(user.Role) !== role ||
        !isEmail_(clean_(user.Email))) {
      continue;
    }

    if (!fallback) {
      fallback = {
        name: clean_(user.Name),
        email: clean_(user.Email).toLowerCase(),
        backupEmail: clean_(user["Backup Email"]).toLowerCase()
      };
    }

    const categories = clean_(user.Categories).split("|");
    const channels = clean_(user.Channels).split("|");
    const categoryMatch = categories.indexOf("*") !== -1 ||
      categories.indexOf(category) !== -1;
    const channelMatch = channels.indexOf("*") !== -1 ||
      channels.indexOf(channel) !== -1;

    if (categoryMatch && channelMatch) {
      return {
        name: clean_(user.Name),
        email: clean_(user.Email).toLowerCase(),
        backupEmail: clean_(user["Backup Email"]).toLowerCase()
      };
    }
  }

  return fallback || { name: "", email: "", backupEmail: "" };
}

function calculateStageDue_(status, publishDateValue) {
  if (["Published", "Cancelled"].indexOf(status) !== -1) {
    return "";
  }

  const publishDate = asDate_(publishDateValue);
  const today = dateOnly_(new Date());

  if (status === "Needs Information") {
    return addDays_(today, 2);
  }

  if (status === "Blocked") {
    return addDays_(today, 1);
  }

  const daysBeforePublish = {
    "Brief Ready": 20,
    "Drafting": 14,
    "Subject Review": 10,
    "Design and SEO": 7,
    "Final Approval": 4,
    "Scheduled": 2
  };

  const offset = daysBeforePublish[status];

  if (!publishDate || offset === undefined) {
    return addDays_(today, 3);
  }

  const calculated = addDays_(dateOnly_(publishDate), -offset);
  return calculated <= today ? addDays_(today, 3) : calculated;
}

function categorize_(contentType) {
  const map = {
    "Blog Article": "Thought Leadership",
    "Case Study": "Customer Proof",
    "Product Page": "Product Marketing",
    "Webinar": "Events",
    "Email Campaign": "Lifecycle Marketing",
    "Social Post": "Social"
  };

  return map[contentType] || "Other";
}

function buildBriefSummary_(submitted, category) {
  return truncate_(
    submitted.contentType + " for " + submitted.audience +
    " in the " + submitted.funnelStage + " stage. Objective: " +
    submitted.businessObjective + ". Campaign: " + submitted.campaign +
    ". Reporting category: " + category + ".",
    500
  );
}

function buildBriefKey_(submitted) {
  const raw = [
    submitted.requesterEmail.toLowerCase(),
    submitted.workingTitle.toLowerCase().replace(/\s+/g, " ").trim(),
    submitted.campaign.toLowerCase().replace(/\s+/g, " ").trim(),
    Utilities.formatDate(
      submitted.publishDate,
      getSpreadsheet_().getSpreadsheetTimeZone(),
      "yyyy-MM-dd"
    )
  ].join("|");

  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    raw,
    Utilities.Charset.UTF_8
  );

  return digest.map(function(byte) {
    const normalized = (byte + 256) % 256;
    return ("0" + normalized.toString(16)).slice(-2);
  }).join("").slice(0, 24);
}

function nextRecordId_(contentSheet) {
  const properties = PropertiesService.getScriptProperties();
  let sequence = Number(properties.getProperty("CONTENT_SEQUENCE") || 0);
  const year = new Date().getFullYear();
  let candidate;

  do {
    sequence++;
    candidate = "CNT-" + year + "-" +
      String(sequence).padStart(4, "0");
  } while (findRowByValue_(contentSheet, "Record ID", candidate));

  properties.setProperty("CONTENT_SEQUENCE", String(sequence));
  return candidate;
}

function updateResponseProcessing_(e, status, recordId, message) {
  const sheet = e.range.getSheet();
  const headers = sheet.getRange(
    1,
    1,
    1,
    Math.max(sheet.getLastColumn(), 1)
  ).getValues()[0];

  const processingHeaders = [
    "Processing Status", "Content Record ID",
    "Processing Message", "Processed At"
  ];

  processingHeaders.forEach(function(header) {
    if (headers.indexOf(header) === -1) {
      headers.push(header);
      sheet.getRange(1, headers.length).setValue(header);
    }
  });

  const map = headerMap_(sheet);
  sheet.getRange(e.range.getRow(), map["Processing Status"])
    .setValue(status);
  sheet.getRange(e.range.getRow(), map["Content Record ID"])
    .setValue(recordId);
  sheet.getRange(e.range.getRow(), map["Processing Message"])
    .setValue(message || "");
  sheet.getRange(e.range.getRow(), map["Processed At"])
    .setValue(new Date());
}

function formValue_(e, header) {
  const sheet = e.range.getSheet();
  const headers = sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getValues()[0];
  const index = headers.indexOf(header);

  if (index === -1) {
    return "";
  }

  return sheet.getRange(e.range.getRow(), index + 1).getValue();
}

function ensureSheet_(ss, name, headers) {
  let sheet = ss.getSheetByName(name);

  if (!sheet) {
    sheet = ss.insertSheet(name);
  }

  const existing = sheet.getLastColumn()
    ? sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    : [];

  headers.forEach(function(header) {
    if (existing.indexOf(header) === -1) {
      existing.push(header);
    }
  });

  sheet.getRange(1, 1, 1, existing.length).setValues([existing]);
  sheet.setFrozenRows(1);
  return sheet;
}

function seedConfig_() {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.config);
  const defaults = [
    [
      "ROOT_FOLDER_ID",
      "YOUR_FOLDER_ID",
      "Drive folder that contains all content-record folders"
    ],
    [
      "MARKETING_OPS_EMAIL",
      "[email protected]",
      "Escalation and failure recipient"
    ],
    [
      "ESCALATION_DAYS",
      "2",
      "Overdue days before marketing operations is copied"
    ],
    [
      "AI_ENABLED",
      "FALSE",
      "Set TRUE only after security and data review"
    ],
    [
      "AI_ENDPOINT",
      "https://api.openai.com/v1/responses",
      "Approved AI Responses API endpoint"
    ],
    [
      "AI_MODEL",
      "YOUR_APPROVED_MODEL",
      "Approved model identifier"
    ]
  ];

  const existing = {};
  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, 1)
      .getValues()
      .forEach(function(row) {
        existing[clean_(row[0])] = true;
      });
  }

  defaults.forEach(function(row) {
    if (!existing[row[0]]) {
      sheet.appendRow(row);
    }
  });
}

function seedUsers_() {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.users);

  if (sheet.getLastRow() > 1) {
    return;
  }

  const rows = [
    [
      "Content Lead", "[email protected]", "Content Owner",
      "*", "*", true, "[email protected]"
    ],
    [
      "Designer", "[email protected]", "Designer",
      "*", "*", true, "[email protected]"
    ],
    [
      "Marketing Operations", "[email protected]",
      "Marketing Operations", "*", "*", true,
      "[email protected]"
    ]
  ];

  sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

function applyValidations_() {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.content);
  const map = headerMap_(sheet);
  const rowCount = Math.max(sheet.getMaxRows() - 1, 1);

  setListValidation_(
    sheet,
    2,
    map["Status"],
    rowCount,
    APP.statuses
  );
  setListValidation_(
    sheet,
    2,
    map["Priority"],
    rowCount,
    APP.priorities
  );
  setListValidation_(
    sheet,
    2,
    map["Subject Review Status"],
    rowCount,
    ["Pending", "Approved", "Changes Requested"]
  );
  setListValidation_(
    sheet,
    2,
    map["Approval Status"],
    rowCount,
    ["Pending", "Approved", "Changes Requested", "Rejected"]
  );

  [
    "Design Required", "SEO Required", "Links Checked",
    "Alt Text Complete", "Proofread Complete", "AI Allowed"
  ].forEach(function(field) {
    setListValidation_(sheet, 2, map[field], rowCount, ["Yes", "No"]);
  });

  setListValidation_(
    sheet,
    2,
    map["Automation Status"],
    rowCount,
    ["Processing", "Complete", "Retry Required", "Manual Review"]
  );

  setListValidation_(
    sheet,
    2,
    map["AI Status"],
    rowCount,
    [
      "Not Requested", "Processing", "Review Required",
      "Low Confidence", "Accepted", "Rejected", "Error"
    ]
  );
}

function setListValidation_(sheet, startRow, column, rows, values) {
  if (!column) {
    return;
  }

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(startRow, column, rows, 1).setDataValidation(rule);
}

function getConfig_() {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.config);
  const values = sheet.getDataRange().getValues();
  const config = {};

  for (let i = 1; i < values.length; i++) {
    const key = clean_(values[i][0]);
    if (key) {
      config[key] = clean_(values[i][1]);
    }
  }

  return config;
}

function appendObjectRow_(sheet, object) {
  const headers = sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getValues()[0];

  const row = headers.map(function(header) {
    return Object.prototype.hasOwnProperty.call(object, header)
      ? object[header]
      : "";
  });

  sheet.appendRow(row);
  return sheet.getLastRow();
}

function rowObject_(sheet, row) {
  const headers = sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getValues()[0];
  const values = sheet.getRange(
    row,
    1,
    1,
    headers.length
  ).getValues()[0];

  return objectFromValues_(headers, values);
}

function objectFromValues_(headers, values) {
  const object = {};

  headers.forEach(function(header, index) {
    object[header] = values[index];
  });

  return object;
}

function headerMap_(sheet) {
  const headers = sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getValues()[0];
  const map = {};

  headers.forEach(function(header, index) {
    map[header] = index + 1;
  });

  return map;
}

function setField_(sheet, row, field, value) {
  const column = headerMap_(sheet)[field];

  if (!column) {
    throw new Error("Missing field in " + sheet.getName() + ": " + field);
  }

  sheet.getRange(row, column).setValue(value);
}

function findRowByValue_(sheet, field, value) {
  const map = headerMap_(sheet);
  const column = map[field];

  if (!column || sheet.getLastRow() < 2) {
    return 0;
  }

  const match = sheet.getRange(
    2,
    column,
    sheet.getLastRow() - 1,
    1
  ).createTextFinder(String(value))
    .matchEntireCell(true)
    .findNext();

  return match ? match.getRow() : 0;
}

function markRowException_(sheet, row, type, message, automationStatus) {
  setField_(sheet, row, "Exception Type", type);
  setField_(sheet, row, "Error Message", message);
  setField_(sheet, row, "Automation Status", automationStatus);
  setField_(sheet, row, "Last Automation Run", new Date());
}

function logAudit_(recordId, event, actor, details) {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.audit);
  sheet.appendRow([
    new Date(),
    recordId,
    event,
    actor || "UNKNOWN",
    truncate_(details || "", 2000)
  ]);
}

function logError_(recordId, step, error, context) {
  try {
    const sheet = getSpreadsheet_().getSheetByName(APP.sheets.errors);
    sheet.appendRow([
      new Date(),
      recordId,
      step,
      error && error.message ? error.message : String(error),
      error && error.stack ? truncate_(error.stack, 5000) : "",
      truncate_(JSON.stringify(context || {}), 2000),
      false,
      "",
      ""
    ]);
  } catch (loggingError) {
    console.error(loggingError);
  }
}

function logAi_(entry) {
  const sheet = getSpreadsheet_().getSheetByName(APP.sheets.aiLog);
  sheet.appendRow([
    new Date(),
    entry.recordId,
    entry.model,
    entry.status,
    entry.confidence,
    entry.inputCharacters,
    entry.outputCharacters,
    entry.inputTokens,
    entry.outputTokens,
    entry.requestId,
    entry.error
  ]);
}

function validateChoice_(value, allowed, label) {
  if (allowed.indexOf(value) === -1) {
    throw new Error(label + " has an invalid value: " + value);
  }
}

function getSpreadsheet_() {
  const id = PropertiesService.getScriptProperties()
    .getProperty("CONTENT_OPS_SPREADSHEET_ID");

  if (id) {
    return SpreadsheetApp.openById(id);
  }

  return SpreadsheetApp.getActiveSpreadsheet();
}

function clean_(value) {
  if (value === null || value === undefined) {
    return "";
  }

  if (value instanceof Date) {
    return value;
  }

  return String(value).trim();
}

function truncate_(value, length) {
  const text = clean_(value);
  return text.length > length ? text.slice(0, length) : text;
}

function isEmail_(value) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(clean_(value));
}

function asDate_(value) {
  if (!value) {
    return null;
  }

  if (value instanceof Date) {
    return new Date(value.getTime());
  }

  const parsed = new Date(value);
  return isNaN(parsed.getTime()) ? null : parsed;
}

function dateOnly_(date) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

function addDays_(date, days) {
  const copy = new Date(date.getTime());
  copy.setDate(copy.getDate() + days);
  return copy;
}

function daysBetween_(start, end) {
  return Math.floor((end.getTime() - start.getTime()) / 86400000);
}

function sameDate_(a, b) {
  return a.getFullYear() === b.getFullYear() &&
    a.getMonth() === b.getMonth() &&
    a.getDate() === b.getDate();
}

function startOfWeek_(date) {
  const copy = dateOnly_(date);
  const day = copy.getDay();
  const offset = day === 0 ? -6 : 1 - day;
  return addDays_(copy, offset);
}

function formatDate_(value) {
  const date = asDate_(value);

  if (!date) {
    return "Not set";
  }

  return Utilities.formatDate(
    date,
    getSpreadsheet_().getSpreadsheetTimeZone(),
    "yyyy-MM-dd"
  );
}

function sanitizeForAi_(value) {
  return clean_(value)
    .replace(/[^\s@]+@[^\s@]+\.[^\s@]+/g, "[EMAIL REDACTED]")
    .replace(/\+?\d[\d\s().-]{7,}\d/g, "[PHONE REDACTED]");
}

function extractDriveId_(url) {
  const match = clean_(url).match(/[-\w]{25,}/);
  return match ? match[0] : "";
}

function eventActor_(e) {
  try {
    if (e && e.user && e.user.getEmail()) {
      return e.user.getEmail();
    }

    const active = Session.getActiveUser().getEmail();
    return active || "UNKNOWN";
  } catch (error) {
    return "UNKNOWN";
  }
}

Configuration and Deployment Instructions

1. Run setupWorkbook manually from the Apps Script editor. Approve the requested spreadsheet, Drive, document, mail, and external request permissions.
2. Open the Config sheet and replace YOUR_FOLDER_ID with the ID of the governed root Drive folder.
3. Replace the example operational email with the monitored marketing operations address.
4. Replace sample Users rows with actual active users, roles, category mappings, channel mappings, and backup addresses.
5. Use a vertical bar to separate multiple categories or channels in Users. Use * as a deliberate wildcard.
6. Run runConfigurationCheck. Resolve every issue before installing production triggers.
7. Run installTriggers. Confirm the project contains one form-submit trigger, one edit trigger, and one daily time trigger.
8. Submit a test form response. Confirm that a Content Tracker row, Record ID, Drive folder, response processing status, email, audit event, and Calendar View row are created.
9. Open Apps Script execution history to inspect duration, result, trigger type, and error details. The Error Log contains business-facing recovery information, while execution history contains platform-level diagnostic detail.
10. For optional AI, add a Script Property named OPENAI_API_KEY, replace YOUR_APPROVED_MODEL, confirm the endpoint with the organization’s approved provider configuration, and set AI_ENABLED to TRUE.

The API request uses bearer-token authentication, POST, a JSON request body, and structured JSON output. It retries HTTP 429 and selected server failures up to three attempts with increasing delays. It does not retry ordinary validation or authorization failures.

The expected successful response contains output text that parses into the requested schema. Token usage and a provider request ID are recorded when returned. Full brief content is not copied into the AI Log.

Likely setup errors include a mismatched Form question name, inaccessible root folder, missing Users role, unauthorized Mail service, expired API credential, unapproved model identifier, or a spreadsheet header changed after setup. Correct the configuration and rerun the failed test before enabling production use.

Failure Handling and Operational Reliability

The Error Log and Manual Review queue act as a practical dead-letter process. Failed records are not discarded. Staff can filter Content Tracker for Automation Status equal to Retry Required or Manual Review, then use the Error Message, Last Automation Run, Retry Count, and Error Log to diagnose the issue.

Idempotency is strongest for record and folder creation. Record creation checks Brief Key, and folder creation checks the unique folder name before creating another folder. Email delivery is normally an at-least-once operation, so a retry can produce a repeated operational message. Every notification includes Record ID so recipients can recognize the same work item.

A weekly reconciliation compares raw form responses with Content Tracker Record IDs. Any processed response without a content record, or any active record without a folder, owner, due date, or automation status, enters Manual Review.

A Complete Example

A content marketer submits the following request:

• Working title: Reducing Approval Delays in Field Service Teams
• Business objective: Help operations leaders understand how structured approval ownership can reduce administrative delay.
• Audience: Field service operations managers
• Content type: Blog Article
• Channel: Website
• Campaign: Q3 Operations Education
• Funnel stage: Consideration
• Requested publishing date: September 18, 2026
• Priority: Standard
• Design required: Yes
• SEO required: Yes

The form-submit trigger validates the required fields and converts the requested date into a Sheets date value. The content type maps to the category Thought Leadership. Apps Script generates a duplicate key and finds no existing match.

The next sequence produces CNT-2026-0043. The Users sheet assigns the active Content Owner wildcard match. The record enters Brief Ready, receives a stage deadline, and stores the raw response row number.

Apps Script creates a Drive folder named CNT-2026-0043 - Reducing Approval Delays in Field Service Teams. The folder URL is written to Asset Folder Link. The requester and content owner receive the Record ID and folder link.

The content owner reviews the brief, changes the status to Drafting, creates a Google Doc in the folder, and records its link in Draft Link. When the owner requests Subject Review, the script confirms that Draft Link is present and assigns the named subject reviewer.

The reviewer requests one clarification about measurement language. Subject Review Status changes to Changes Requested, Review Feedback is recorded, and the workflow returns to Drafting. After revision, the owner submits it again. The reviewer selects Approved, the Subject Review Date is recorded, and the record advances to Design and SEO.

The designer adds a Design Link. The content owner enters an SEO title, meta description, and primary keyword, then marks Links Checked, Alt Text Complete, and Proofread Complete as Yes.

The record enters Final Approval. The marketing leader reviews the draft and design, then selects Approved. Apps Script confirms the approval and all publishing checks. It records Approval Date, changes the status to Scheduled, assigns marketing operations, and records Scheduled Date.

On the target date, the publisher creates the item in the content management system. The publisher records cms-1847 as the External CMS ID and adds the final public URL. The move to Published succeeds because Published URL is present. Published Date is recorded, the calendar and dashboard update, and the complete status history remains in the Audit Log.

If the Drive folder had failed during intake, the content record would still have existed with Automation Status set to Retry Required. The daily monitor would have retried the folder using the same Record ID and unique folder name.

Implementation Cost

The following figures are representative planning assumptions, not vendor quotations or verified client results. Actual licensing, professional rates, internal salaries, storage, AI usage, and implementation effort vary.

An organization implementing with internal technical staff would replace professional service hours with its own design, engineering, testing, security, and documentation time. A tool with no incremental license cost still requires accountable labour and maintenance.

Estimated Time and Cost Savings

The estimate covers administrative coordination only. It does not assume that writing, research, design, subject review, or publishing judgment disappears.

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

42 × 38 ÷ 60 = 26.60 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

(42 × 12 ÷ 60) + (42 × 12% × 15 ÷ 60) + 3 = 8.40 + 1.26 + 3.00 = 12.66 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

26.60 - 12.66 = 13.94 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

13.94 × $42 = $585.48

Net estimated monthly value: Monthly labour value minus recurring tool costs

$585.48 - $60 = $525.48

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

$10,800 ÷ $525.48 = approximately 20.6 months

Recovered time does not automatically reduce payroll. It can represent additional campaign capacity, quicker review turnaround, reduced overtime, fewer administrative follow-ups, or the ability to handle a higher content volume without adding the same amount of coordination work.

Non-financial benefits include clearer ownership, fewer incomplete briefs, consistent publishing checks, easier deadline management, improved approval evidence, better auditability, and more reliable operational reporting.

Readers should replace the monthly volume, handling minutes, exception rate, maintenance time, loaded labour rate, recurring costs, and implementation cost with their own measured values. A short time study across several content types is preferable to relying on memory alone.

Adding AI to the Automation

AI is an optional enhancement added after the deterministic workflow is stable. The core benefits do not depend on AI. Forms standardize intake, rules assign owners, status gates enforce evidence, Apps Script sends reminders, Drive organizes documents, and Looker Studio reports operational data.

Potential AI uses include summarizing long source notes, suggesting outlines, proposing repurposing formats, identifying missing information, categorizing unusual requests, comparing revisions, and drafting channel-specific starting points.

AI should not replace required fields, date calculations, exact duplicate keys, approval thresholds, status rules, permissions, or publishing checks. Those tasks are more reliable and auditable when handled through validation and deterministic logic.

For this implementation, the recommended enhancement is a reviewed planning package containing a brief summary, outline, evidence requirements, and up to three repurposing suggestions.

The Recommended AI Enhancement

• Trigger: An authorized user selects a Content Tracker row and chooses Generate AI assist for selected row.
• AI input: Working title, objective, audience, type, channel, campaign, funnel stage, category, and approved source notes.
• System instruction: The model must use only supplied information and must not invent claims, statistics, quotations, evidence, or product capabilities.
• Expected output: Structured JSON containing summary, outline, repurposing suggestions, missing information, risk flags, and confidence.
• Validation: Apps Script validates object type, required arrays, and confidence range.
• Record update: A Google Doc is created in the asset folder, and AI Status, confidence, output link, and run timestamp are updated.
• Human review: The content owner reviews the document and marks AI Status Accepted or Rejected.
• Low confidence: Confidence below 0.75 produces Low Confidence and requires additional brief review.
• Prohibited data: Credentials, regulated personal data, payment data, confidential legal material, and unapproved customer information.
• Failure behavior: The record receives AI Status Error, the core workflow continues, and the content owner works from the original brief.

The reusable system instruction is:

You assist a B2B marketing content team. Use only the supplied brief. Do not invent customer facts, product capabilities, statistics, quotes, legal claims, or evidence. Identify missing information explicitly. Create planning suggestions, not final approval or publication decisions. Return only JSON matching the supplied schema.

The expected structured output follows this shape:

{
  "brief_summary": "A concise summary based only on the supplied brief.",
  "outline": [
    {
      "heading": "Proposed section heading",
      "purpose": "Why this section supports the stated objective",
      "evidence_needed": [
        "Evidence or source that a human should provide"
      ]
    }
  ],
  "repurposing": [
    {
      "channel": "Email",
      "format": "Newsletter summary",
      "draft_direction": "Summarize the operational problem and link to the article"
    }
  ],
  "missing_information": [
    "Information needed before drafting"
  ],
  "risk_flags": [
    "Potential unsupported claim requiring human verification"
  ],
  "confidence": 0.82
}

AI confidence is a workflow signal, not an objective truth score. Human reviewers must verify every factual statement and decide whether any suggestion belongs in the content plan.

Benefits of the AI Enhancement

• Reduces the time spent turning a complete structured brief into an initial outline.
• Makes missing evidence more visible before drafting starts.
• Produces a consistent set of repurposing suggestions across content owners.
• Helps marketers scan long source notes without replacing source review.
• Creates structured data that can later support analysis of recurring information gaps.
• Provides a documented starting point that can be accepted, edited, or rejected.

These are AI-specific benefits. Assignment, deadline control, review gates, notifications, audit history, file organization, and dashboard reporting already come from normal automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The AI estimate is separate from the core coordination savings.

Gross additional hours: 42 × 5 ÷ 60 = 3.50 hours

Correction time: 42 × 15% × 4 ÷ 60 = 0.42 hours

Failure fallback time: 42 × 3% × 10 ÷ 60 = 0.21 hours

Net additional capacity: 3.50 - 0.42 - 0.21 - 0.50 = 2.37 hours per month

Estimated labour value: 2.37 × $42 = $99.54 per month

Net value after assumed AI cost: $99.54 - $18 = $81.54 per month

The estimate does not assume that AI eliminates errors or human review. If briefs are highly specialized, sparse, sensitive, or fact-dependent, correction time may exceed the time saved.

Testing Checklist

Use fictional sample data and non-sensitive test documents before processing real marketing, customer, or product information.

Ongoing Maintenance

The marketing operations manager is the primary system owner. A technically capable Workspace administrator or automation specialist is the backup owner.

Credential rotation should follow the organization’s security policy. API keys must be replaced in Script Properties, followed by a controlled test. Updating documentation is part of every material workflow or field change.

Templates, publishing checks, category mappings, and due-date offsets should be reviewed as the content strategy changes. Avoid adding fields without deciding who owns them, whether they are required, and how they will be reported.

When to Move to Dedicated Software

This implementation remains appropriate while the workflow is moderate in volume, centered on Google Workspace, and manageable through a small number of controlled stages.

Reassess the architecture when several of these conditions appear:

• Transaction volume causes noticeable spreadsheet or trigger performance problems.
• Multiple brands, regions, languages, or business units require separate permission boundaries.
• Review paths require complex parallel approvals or conditional legal workflows.
• A formal audit requires immutable controls beyond spreadsheet protections and append-only logs.
• Digital assets need advanced rights, licensing, rendition, and expiration management.
• The content management system must publish automatically across many destinations.
• Mobile or offline workflow access becomes operationally important.
• External agencies or customers require a secure portal.
• Exception rates and script maintenance consume more time than the automation saves.
• Advanced capacity planning, resource allocation, localization, or campaign budgeting is required.
• API volume, credential management, or security risk exceeds the support model of a bound script.
• The organization requires formal vendor support and service commitments for the workflow platform.

Possible next-step categories include work management platforms, marketing resource management systems, digital asset management platforms, content operations platforms, or a lightweight custom application backed by a managed database. Migration is not automatically necessary. The decision should compare governance, integration complexity, support requirements, lifecycle cost, and the operational value of the current system.

Implementation Checklist

• Confirm content types, volume, participants, review gates, and publishing responsibilities.
• Select and document the intake, record, automation, storage, notification, and reporting tools.
• Create managed production and test accounts.
• Assign primary and backup system owners.
• Create the governed Drive root folder and permission groups.
• Build the Google Form with exact field names, required fields, and controlled choices.
• Connect the form to the response spreadsheet.
• Create the Content Tracker, Users, Config, Audit Log, Error Log, Calendar View, and AI Log.
• Define identifiers, duplicate keys, statuses, owners, dates, and exception fields.
• Populate Users with roles, category mappings, channel mappings, and backups.
• Replace configuration placeholders and secure credentials.
• Paste, authorize, and validate the Apps Script code.
• Install the form-submit, edit, and daily monitoring triggers.
• Test field mapping from Google Forms to Content Tracker.
• Test unique ID generation and duplicate prevention.
• Test folder creation, returned links, and permission inheritance.
• Test owner assignment and status transitions.
• Test subject review, final approval, rejection, and return paths.
• Test reminders, escalations, delegation, and unavailable approvers.
• Test SEO, design, link, alt-text, proofreading, and publishing gates.
• Configure Looker Studio work queues, exceptions, calendar, throughput, and automation-health views.
• Protect automation-managed fields and restrict shared links.
• Complete security, privacy, retention, and former-user access reviews.
• Use sample records for user acceptance testing.
• Run a limited pilot before production rollout.
• Document support, rollback, retry, and manual recovery procedures.
• Replace representative cost assumptions with actual internal and vendor figures.
• Measure current and new handling time before calculating savings.
• Enable AI only after core automation, data restrictions, prompts, validation, and human review are approved.
• Assign maintenance, monitoring, documentation, and upgrade-review owners.
• Define volume, governance, security, and complexity criteria for moving to dedicated software.