Building a Google Workspace Applicant Tracking System

The Business Situation

Cedarline Consulting is a fictional 55-person professional services firm. It typically recruits for three to five consulting, project management, and administrative roles at a time.

The People Operations team consists of an HR manager and a recruiting coordinator. Department leaders act as hiring managers, while approximately 12 employees participate in interviews during a typical quarter.

The firm receives about 60 applications each month through three channels:

• A careers page that directs applicants to a web form.
• LinkedIn job listings that either direct applicants to the form or produce application emails requiring recruiter review.
• Direct applications sent to a shared recruiting mailbox.

Candidate details were tracked in one Google Sheet. Resumes were stored inconsistently in Gmail, personal Google Drive folders, or a shared recruiting folder. Interview scheduling happened through email and Google Calendar, but calendar event identifiers and feedback status were not connected to the candidate record.

The spreadsheet was useful as a list, but it was not functioning as an applicant tracking system. The recruiting coordinator had to create rows, rename files, create folders, notify hiring managers, schedule interviews, send reminders, and reconcile statuses manually.

The business needed a more controlled process before application volume increased or responsibility moved to another employee.

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 Existing Process

The original workflow followed a broadly consistent sequence, but most steps depended on the recruiting coordinator remembering to complete them.

1. An applicant completed the website form, applied through LinkedIn, or emailed a resume.
2. The recruiting coordinator opened the submission and checked whether a corresponding spreadsheet row already existed.
3. The coordinator copied the applicant’s name, email address, job, source, and notes into the candidate spreadsheet.
4. The resume was downloaded or moved into a Drive folder. Folder and file names depended on who processed the application.
5. The coordinator looked up the responsible recruiter and hiring manager, then sent a review request by email.
6. The hiring manager replied by email or verbally indicated whether the candidate should advance.
7. The coordinator asked the candidate for interview availability, created a calendar event, and invited the interview panel.
8. After the interview, the coordinator followed up with panel members for feedback.
9. Offer approval, rejection, and candidate communications were managed through separate email threads.
10. The spreadsheet status was updated when the coordinator had time to reconcile the emails and calendar events.

The main problem was not the spreadsheet itself. It was the absence of controlled intake, validation, automatic ownership, defined status transitions, document links, reminders, and an audit history around the spreadsheet.

What the New System Needed to Do

The implementation requirements were agreed before selecting a tool. This prevented the project from becoming a spreadsheet redesign without meaningful process improvement.

Implementation Approaches Considered

A more disciplined manual process

The existing spreadsheet could have been improved with required columns, documented stage definitions, and recurring calendar reminders. This would reduce inconsistency, but it would not remove duplicate entry or connect source messages, folders, emails, and interviews.

Google Workspace with Apps Script

This option retained familiar tools and added an automation layer. It could create records and folders, send messages, create calendar events, enforce workflow rules, and log failures without moving HR data into another business application.

Airtable

Airtable would provide stronger relational views and a more application-like interface than a spreadsheet. It remained a viable alternative, particularly if the business wanted richer forms and record interfaces. It was not selected because the firm wanted to keep candidate records within its existing Google Workspace administration and avoid another permission and licensing model.

A dedicated applicant tracking platform

A dedicated platform would be the strongest option for job-board syndication, candidate portals, high-volume hiring, configurable interview plans, compliance reporting, and vendor support. At 60 applications per month and a small HR team, Cedarline Consulting first needed a controlled internal workflow rather than a larger recruiting platform.

The Selected Solution

The selected implementation used Google Sheets as the system of record, Google Forms and Gmail as intake channels, Google Drive for candidate documents, Google Apps Script for workflow automation, Gmail for notifications, and Google Calendar for interviews.

LinkedIn was not scraped, and no unsupported LinkedIn integration was assumed. Where a listing allowed an external application URL, it directed candidates to the Google Form with a prefilled source value. Applications received through email were forwarded to the shared recruiting mailbox using a structured header and then labeled for processing.

The implementation removed routine row creation, folder creation, file naming, acknowledgement emails, assignment notices, standard reminders, and calendar-event reconciliation.

Candidate screening, interview decisions, rejection decisions, offer approval, offer terms, and final hiring decisions remained human-controlled.

System Architecture and Data Flow

• Intake: Google Forms and a labeled Gmail shared mailbox.
• System of record: Google Sheets containing Candidates, Jobs, Interviews, Audit Log, Errors, and Dashboard sheets.
• Automation layer: Google Apps Script installed triggers and scheduled functions.
• Document storage: Google Drive candidate folders with Resume, Interviews, and Offer subfolders.
• Notifications: Gmail messages and Google Calendar invitations.
• Reporting: Google Sheets filtered views, formulas, and charts.
• AI layer: Optional resume extraction through an approved AI API, with recruiter review.

1. Submission: A candidate submits a Google Form or an application email receives the Gmail label ATS/To Process. The form submit trigger runs immediately, while email intake is polled every 15 minutes.

2. Validation: Apps Script validates the candidate name, email address, job code, source, and consent indicator. Missing job or resume information can create a controlled Needs Information record. An invalid email address is sent to the error queue rather than creating an unusable candidate record.

3. Duplicate check: The script first checks the form response or Gmail message ID. It then checks normalized candidate email plus job code against non-terminal candidate records.

4. Job lookup: The job code is matched to the Jobs sheet. The script obtains the job title, recruiter, hiring manager, offer approver, and initial service-level due date.

5. Record and folder creation: The script generates an identifier such as AP-2026-0042, creates the candidate folder hierarchy, copies resume files, and appends a Candidate row.

6. Confirmation and assignment: Gmail sends one acknowledgement to the candidate. The owner is assigned from the Jobs sheet, and the record enters Recruiter Review or Needs Information.

7. Status actions: An installed edit trigger detects authorized status changes. It sends internal review notices, scheduling requests, approval requests, rejection messages, or approved offer messages when the relevant prerequisites are satisfied.

8. Interview creation: When a recruiter selects Interview Scheduled, Apps Script validates the dates and panel, creates a Calendar event, appends an Interviews record, and stores the returned calendar event ID.

9. Reminders and escalation: A daily trigger finds overdue candidate actions, upcoming interviews, overdue feedback, pending approvals, and failed automation. It sends reminders and escalates after defined thresholds.

10. Failure path: A failed action updates Automation Status, increments Retry Count, stores a concise error, and appends an Errors record. Retryable status actions are attempted up to three times. Other failures remain visible for manual recovery.

Data Structure

The master spreadsheet contains related sheets rather than placing every type of record in one wide table.

Candidates

One row per candidate and job combination.

Jobs

One row per approved requisition or open role.

Interviews

One row per scheduled interview round. Multiple Interviews rows can reference one Candidate ID.

Audit Log

Append-only records of significant workflow and communication actions.

Errors

Automation failures requiring retry or manual review.

Dashboard

Calculated operational metrics sourced from Candidates and Interviews.

The Interviews sheet uses Candidate ID as a foreign key. Although Google Sheets does not enforce database foreign keys, Apps Script rejects interview creation when the candidate record does not exist.

Workflow Statuses and Ownership

A candidate can move backward when a manager requests more information, an approver returns an offer, or another interview round is required. Rejection, offer approval, and hiring remain explicit human decisions. The automation only executes the approved operational action.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or designate a Google Workspace automation account, such as a controlled recruiting operations account. Installable triggers run under the account that creates them.
2. Create the shared recruiting spreadsheet and place it in an HR-controlled location. The account running the script needs edit access.
3. Create a root Drive folder named Recruiting Candidates. Record its folder ID from the URL.
4. Create or identify a shared recruiting calendar. Record the calendar ID from its calendar settings.
5. Create a shared mailbox or delegated Gmail account for applications. The automation account must be able to read messages and apply labels in the mailbox where the script runs.
6. Create Gmail labels named ATS/To Process, ATS/Processed, and ATS/Error. The setup function can also create them.
7. Create two test users: one acting as a recruiter and one acting as a hiring manager or offer approver.
8. Restrict the production spreadsheet to HR and authorized hiring managers. Interview panel members do not automatically need access to the complete candidate register.
9. Use a separate test spreadsheet, test root folder, test calendar, and test mailbox labels during development.

The Apps Script project requests access to Sheets, Drive, Gmail, Calendar, script properties, and external requests if the optional AI function is enabled. Review these permissions before authorization. Google provides official guidance for installable Apps Script triggers.

No service account is required for this design. A dedicated Workspace automation user is generally easier to operate because Gmail and Calendar actions are performed with that user’s delegated authority.

Step 2: Build the Intake

Create a Google Form named Employment Application and connect its responses to the master spreadsheet. Use the following question titles exactly if the supplied script is used without modification.

Google Forms file-upload questions require the respondent to sign in with a Google account. That can create friction for external applicants. The form therefore also provides a resume-link field and a recruiting email alternative. If unrestricted public uploads are a firm requirement, a dedicated applicant platform or controlled public form service may be more appropriate.

The confirmation message should state that the application was received, that submission does not guarantee an interview, and how the candidate can request a correction or withdrawal. Do not include internal hiring timelines unless the business can maintain them.

For email intake, publish a plain-text template:

Job Code: CON-2026-01
Candidate Name: Applicant Name
Candidate Email: [email protected]
Source: LinkedIn
Phone: Optional
Location: Optional
Work Authorization: Optional
Resume Link: Optional

Attach the resume when a file is available.

A Gmail filter may label messages sent to a dedicated application alias. LinkedIn-originated messages should only be processed when they contain the candidate’s permitted contact details and comply with the source platform’s terms. The automation does not retrieve protected platform data or bypass access controls.

Step 3: Create the System of Record

Create one spreadsheet containing the following sheets:

• Candidates
• Jobs
• Interviews
• Audit Log
• Errors
• Dashboard
• The Google Form response sheet created by Forms

The supplied setupAts function creates the controlled sheets, exact headers, dropdown validation, filters, formats, and dashboard formulas. It does not delete existing candidate data.

Populate the Jobs sheet before testing intake. A representative row is:

Candidate IDs are generated from the year and a script-property sequence. Script locking prevents two simultaneous submissions from receiving the same sequence value.

The primary duplicate rule is normalized candidate email plus job code for non-terminal records. This does not prevent the same person from applying to two jobs. A separate source-record check prevents the same form event or Gmail message from being processed twice.

Step 4: Connect the Tools

Form field names are mapped by question title rather than fixed response-column positions. This makes the intake less sensitive to form column order, but changing a question title still requires a corresponding code update.

Email intake maps structured Field: Value lines. Direct emails without a structured header use the sender name and sender email, but they enter Needs Information if the script cannot determine the job or resume.

Step 5: Build the Core Automation

Form application automation

• Trigger: Installed spreadsheet form-submit event.
• Conditions: The source response contains a valid name, email, job code, source, and consent value.
• Actions: Check idempotency, check duplicates, look up the job, generate an ID, create folders, copy the resume, append the record, send acknowledgement, and add an audit entry.
• Fields updated: Candidate identity, ownership, status, due date, document IDs, automation status, and timestamps.
• Notification: One applicant acknowledgement.
• Exception: Invalid email goes to Errors. Missing resume or invalid job creates a manual-review record where possible.

Email application automation

• Trigger: Time-based trigger every 15 minutes.
• Conditions: Gmail thread has the ATS/To Process label and contains an unprocessed message.
• Actions: Parse structured fields, collect non-inline attachments, create the candidate record, and replace the processing label with ATS/Processed.
• Fields updated: Source is Email or the supplied source value; Source Record ID stores the Gmail message ID.
• Notification: Applicant acknowledgement after successful record creation.
• Exception: Failed threads retain the processing label and receive ATS/Error.

Status-change automation

• Trigger: Installed edit trigger on the Candidates sheet.
• Conditions: A single authorized status or approval field is changed.
• Actions: Update ownership, validate prerequisites, send the corresponding communication, create an interview, or request approval.
• Fields updated: Status dates, owner, due date, communication timestamp, Calendar event ID, approval status, and automation status.
• Notification: Depends on the stage.
• Exception: Missing prerequisites mark the record Failed and add an Errors row.

Candidate emails use action-specific audit keys such as EMAIL_ACK, EMAIL_REJECTION, and EMAIL_OFFER. Before sending, the script checks whether that candidate and action already appear in the Audit Log. This prevents a trigger retry from sending the same consequential message twice.

Step 6: Add Approvals, Reminders, and Escalations

Offer approval is sequential:

1. HR prepares the offer document in the candidate’s Offer folder.
2. HR enters the document link and changes the candidate status to Offer Approval.
3. Apps Script sets Offer Approval Status to Pending and emails the designated approver.
4. The authorized approver reviews the terms and changes the protected approval field to Approved, Rejected, or More Information.
5. An Approved value records the approval date and notifies HR.
6. HR verifies the final document, changes Communication Approved to Yes, and selects Offer Sent.
7. The script verifies all three conditions before emailing the candidate: approval is Approved, the document link exists, and communication is approved.

For higher-risk or high-value roles, a second approval field can be added. Parallel approvals should not be simulated with one field. Add a separate approval record for each approver and require all mandatory records to be Approved.

The default reminder rules are:

• Send an owner reminder when the candidate due date is reached or passed.
• Escalate to the HR manager after two reminders.
• Send an interview reminder approximately 24 hours before the event.
• Request interview feedback after the event ends.
• Escalate missing feedback after two days.
• Remind offer approvers daily after the approval due date.
• Attempt retryable failed status actions up to three times.

If an approver is unavailable, HR changes the Offer Approver Email to an authorized delegate before resending the approval request. Delegation should be recorded in Notes and the Audit Log. The automation must not infer a replacement approver.

Step 7: Add Documents and File Management

The root folder uses the following hierarchy:

Recruiting Candidates/
  2026/
    AP-2026-0042 - Candidate Name/
      Resume/
      Interviews/
      Offer/

Resume copies use the convention:

AP-2026-0042 - Resume - Original File Name.pdf

Folder names are sanitized to remove characters that make searching or exporting difficult. The Candidate row stores both folder IDs and clickable URLs. File IDs are retained because IDs are more stable than names.

HR owns the candidate root folder. Hiring managers receive only the access required to review candidates assigned to them. Interview panel members may receive a resume link when approved by HR, but they do not automatically receive access to the entire candidate directory.

The script copies form-uploaded files rather than moving them. This preserves the source form response while placing a controlled copy in the candidate folder. Email attachments are created directly in the Resume subfolder.

Failed file copies create an exception while preserving the candidate record. HR can upload the missing document manually, update Resume File IDs and Resume Link, clear the exception, and return the record to Recruiter Review.

Retention and deletion schedules must follow applicable employment, privacy, contractual, and legal requirements. The case study does not prescribe a universal retention period.

Step 8: Add Reporting and Operational Views

The Dashboard sheet contains basic counts, while filtered views in Candidates and Interviews support operational work.

Useful calculated metrics include applications by source, candidates by status, median days in current stage, interviews scheduled this week, overdue feedback, offer approval aging, automation failure count, and hires by job.

Google Sheets formulas recalculate automatically. Dashboard ownership belongs to the HR manager, while the technical owner verifies formulas after column changes. A non-zero automation-failure count should trigger daily review rather than waiting for a monthly report.

Step 9: Add Security and Governance Controls

• Give the automation account only the Gmail, Drive, Calendar, and spreadsheet access required for recruiting.
• Protect identifier, source ID, automation, approval, and audit columns against general editing.
• Limit the Candidates sheet to HR and authorized hiring managers.
• Do not use public Drive links for resumes or offers.
• Store API keys in Apps Script Properties, not in cells or source code.
• Review trigger ownership whenever the automation account or HR system owner changes.
• Remove former employees from the spreadsheet, root folder, calendar, and delegated mailbox promptly.
• Keep Audit Log and Errors append-only for ordinary users.
• Avoid collecting protected characteristics unless a documented legal and business requirement exists.
• Do not place medical, background-check, compensation, or identity-verification information in the general candidate sheet.
• Back up the spreadsheet and verify that Drive retention rules cover candidate folders.
• Require human approval for rejection, offers, access exceptions, and final hiring.

If the optional AI function is enabled, approve the provider and data-processing terms before sending resumes. Disable provider-side storage where supported, delete temporary uploaded files, restrict the prompt from considering protected characteristics, and require recruiter review of every result.

Step 10: Deploy and Test

1. Copy the production spreadsheet to create a test workbook.
2. Create a test Drive root folder and a test recruiting calendar.
3. Replace configuration placeholders with test identifiers and emails.
4. Run setupAts manually and authorize the requested permissions.
5. Populate the test Jobs sheet with one active and one inactive job.
6. Connect a duplicate test form to the test spreadsheet.
7. Run installTriggers and confirm four installed triggers exist.
8. Submit sample applications with fictional data and non-sensitive sample resumes.
9. Test every status transition, approval path, communication prerequisite, retry path, and duplicate condition.
10. Complete user acceptance testing with the recruiting coordinator, HR manager, one hiring manager, and one interviewer.
11. Pilot the workflow for one open role before adding other requisitions.
12. Compare the Candidates sheet to the form response sheet and labeled Gmail threads each day during the pilot.
13. Move approved code and configuration to production, install production triggers, and submit one final synthetic test.
14. Document the support owner, backup owner, recovery procedure, and rollback process.

Rollback consists of disabling the installed triggers, retaining the spreadsheet and folders, and temporarily returning to controlled manual processing. Records already created should not be deleted solely because the automation is paused.

Code and Configuration

The script is stored in the master Google Sheet. Open the spreadsheet, open the Apps Script editor from the Extensions menu, replace the default editor content, and paste the complete core script below.

Replace every YOUR_... value. The spreadsheet ID appears between /d/ and /edit in the Sheet URL. Folder and Calendar IDs are available from their respective URLs or settings.

const ATS = Object.freeze({
  spreadsheetId: 'YOUR_SPREADSHEET_ID',
  rootFolderId: 'YOUR_FOLDER_ID',
  calendarId: 'YOUR_SHARED_CALENDAR_ID',
  fallbackRecruiterName: 'Recruiting Coordinator',
  fallbackRecruiterEmail: 'YOUR_EMAIL_ADDRESS',
  hrManagerEmail: 'YOUR_HR_MANAGER_EMAIL',
  schedulingUrl: 'YOUR_SCHEDULING_URL',
  feedbackUrl: 'YOUR_FEEDBACK_FORM_URL',
  companyName: 'Cedarline Consulting',
  timeZone: 'YOUR_IANA_TIME_ZONE',
  processingLabel: 'ATS/To Process',
  processedLabel: 'ATS/Processed',
  errorLabel: 'ATS/Error',
  maxRetries: 3
});

const CANDIDATE_HEADERS = [
  'Candidate ID', 'Created Date', 'Last Updated', 'Source',
  'Source Record ID', 'Job Code', 'Job Title', 'Candidate Name',
  'Candidate Email', 'Candidate Phone', 'Location',
  'Work Authorization', 'LinkedIn URL', 'Portfolio URL', 'Consent',
  'Resume File IDs', 'Resume Link', 'Candidate Folder ID',
  'Candidate Folder Link', 'Owner Name', 'Owner Email',
  'Hiring Manager Email', 'Status', 'Status Changed Date', 'Priority',
  'Due Date', 'Interview Round', 'Interview Start', 'Interview End',
  'Interview Time Zone', 'Interview Panel Emails', 'Interview Mode',
  'Interview Location or Link', 'Calendar Event ID',
  'Interview Feedback Status', 'Offer Approver Email',
  'Offer Approval Status', 'Offer Approval Date', 'Offer Document Link',
  'Communication Approved', 'Last Candidate Email', 'Reminder Count',
  'Last Reminder Date', 'Exception Type', 'Automation Status',
  'Last Automation Run', 'Retry Count', 'Error Message', 'AI Status',
  'AI Confidence', 'AI Suggested Current Title',
  'AI Suggested Years Experience', 'AI Suggested Skills', 'AI Summary',
  'AI Extracted JSON', 'Notes'
];

const JOB_HEADERS = [
  'Job Code', 'Job Title', 'Department', 'Hiring Manager Name',
  'Hiring Manager Email', 'Recruiter Name', 'Recruiter Email',
  'Offer Approver Email', 'SLA Business Days', 'Active'
];

const INTERVIEW_HEADERS = [
  'Interview ID', 'Candidate ID', 'Round', 'Start', 'End', 'Time Zone',
  'Panel Emails', 'Mode', 'Location or Link', 'Calendar Event ID',
  'Feedback Status', 'Feedback Due', 'Created Date',
  'Last Reminder Date', 'Reminder Count', 'Notes'
];

const AUDIT_HEADERS = [
  'Timestamp', 'Candidate ID', 'Action', 'Actor', 'Details'
];

const ERROR_HEADERS = [
  'Timestamp', 'Candidate ID', 'Source Record ID', 'Function',
  'Error Message', 'Retry Count', 'Status', 'Resolved Date'
];

const STATUSES = [
  'Needs Information', 'Recruiter Review', 'Hiring Manager Review',
  'Interview Requested', 'Interview Scheduled', 'Interview Complete',
  'Offer Approval', 'Offer Sent', 'On Hold', 'Rejected', 'Withdrawn',
  'Hired', 'Automation Error'
];

const TERMINAL_STATUSES = ['Rejected', 'Withdrawn', 'Hired'];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Applicant Tracking')
    .addItem('Set up sheets', 'setupAts')
    .addItem('Install triggers', 'installTriggers')
    .addItem('Process labeled email now', 'processLabeledApplications')
    .addItem('Run reminders now', 'sendDailyReminders')
    .addSeparator()
    .addItem('Extract resume for active row', 'runAiForActiveRow')
    .addToUi();
}

function setupAts() {
  validateConfiguration_(false);
  const book = getBook_();

  const candidates = ensureSheet_(book, 'Candidates', CANDIDATE_HEADERS);
  const jobs = ensureSheet_(book, 'Jobs', JOB_HEADERS);
  const interviews = ensureSheet_(book, 'Interviews', INTERVIEW_HEADERS);
  ensureSheet_(book, 'Audit Log', AUDIT_HEADERS);
  ensureSheet_(book, 'Errors', ERROR_HEADERS);
  const dashboard = book.getSheetByName('Dashboard') ||
    book.insertSheet('Dashboard');

  applyCandidateValidation_(candidates);
  applyJobValidation_(jobs);
  applyInterviewValidation_(interviews);
  buildDashboard_(dashboard);

  [ATS.processingLabel, ATS.processedLabel, ATS.errorLabel].forEach(name => {
    if (!GmailApp.getUserLabelByName(name)) {
      GmailApp.createLabel(name);
    }
  });

  SpreadsheetApp.flush();
  console.log('ATS sheets, validation, dashboard, and Gmail labels are ready.');
}

function installTriggers() {
  validateConfiguration_(true);

  const handlers = [
    'handleFormSubmit',
    'handleCandidateEdit',
    'processLabeledApplications',
    'sendDailyReminders'
  ];

  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (handlers.includes(trigger.getHandlerFunction())) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('handleFormSubmit')
    .forSpreadsheet(ATS.spreadsheetId)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger('handleCandidateEdit')
    .forSpreadsheet(ATS.spreadsheetId)
    .onEdit()
    .create();

  ScriptApp.newTrigger('processLabeledApplications')
    .timeBased()
    .everyMinutes(15)
    .create();

  ScriptApp.newTrigger('sendDailyReminders')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();

  console.log('Four production triggers were installed.');
}

function handleFormSubmit(event) {
  try {
    if (!event || !event.range || !event.namedValues) {
      throw new Error('Form event does not contain range and namedValues.');
    }

    const sourceId = 'FORM:' +
      event.range.getSheet().getSheetId() + ':' +
      event.range.getRow();

    const input = {
      source: namedValue_(event.namedValues, 'Application Source') || 'Website',
      sourceRecordId: sourceId,
      jobCode: namedValue_(event.namedValues, 'Job Code'),
      candidateName: namedValue_(event.namedValues, 'Full Name'),
      candidateEmail: namedValue_(event.namedValues, 'Email'),
      candidatePhone: namedValue_(event.namedValues, 'Phone'),
      location: namedValue_(event.namedValues, 'Location'),
      workAuthorization: namedValue_(event.namedValues, 'Work Authorization'),
      linkedInUrl: namedValue_(event.namedValues, 'LinkedIn URL'),
      portfolioUrl: namedValue_(event.namedValues, 'Portfolio URL'),
      consent: namedValue_(event.namedValues, 'Consent'),
      resumeLink: namedValue_(event.namedValues, 'Resume Link')
    };

    const uploadValue = namedValue_(event.namedValues, 'Resume Upload');
    const driveFileIds = extractDriveIds_(uploadValue);

    ingestCandidate_(input, driveFileIds, []);
  } catch (error) {
    recordError_('', '', 'handleFormSubmit', error, 0);
    console.error(error.stack || error);
    throw error;
  }
}

function processLabeledApplications() {
  validateConfiguration_(true);

  const processing = GmailApp.getUserLabelByName(ATS.processingLabel);
  const processed = GmailApp.getUserLabelByName(ATS.processedLabel);
  const errorLabel = GmailApp.getUserLabelByName(ATS.errorLabel);

  if (!processing || !processed || !errorLabel) {
    throw new Error('Required Gmail labels are missing. Run setupAts first.');
  }

  const threads = processing.getThreads(0, 25);

  threads.forEach(thread => {
    let handled = false;

    try {
      const messages = thread.getMessages();

      for (let index = 0; index < messages.length; index += 1) {
        const message = messages[index];
        const sourceId = 'GMAIL:' + message.getId();

        if (findCandidateRowByField_('Source Record ID', sourceId)) {
          handled = true;
          break;
        }

        const parsed = parseApplicationEmail_(message);
        const attachments = message.getAttachments({
          includeInlineImages: false,
          includeAttachments: true
        });

        ingestCandidate_(parsed, [], attachments);
        handled = true;
        break;
      }

      if (handled) {
        thread.removeLabel(processing);
        thread.removeLabel(errorLabel);
        thread.addLabel(processed);
      }
    } catch (error) {
      thread.addLabel(errorLabel);
      recordError_('', 'GMAIL_THREAD:' + thread.getId(),
        'processLabeledApplications', error, 0);
      console.error(error.stack || error);
    }
  });
}

function ingestCandidate_(input, driveFileIds, attachmentBlobs) {
  validateCandidateInput_(input);

  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const existingSourceRow = findCandidateRowByField_(
      'Source Record ID', input.sourceRecordId
    );

    if (existingSourceRow) {
      console.log('Source event already processed: ' + input.sourceRecordId);
      return getCandidateRecord_(existingSourceRow);
    }

    const duplicateRow = findActiveDuplicate_(
      input.candidateEmail, input.jobCode
    );

    if (duplicateRow) {
      const duplicate = getCandidateRecord_(duplicateRow);
      audit_(duplicate['Candidate ID'], 'DUPLICATE_SUPPRESSED',
        'Source ' + input.sourceRecordId +
        ' matched email and job code.');
      return duplicate;
    }

    const job = findJob_(input.jobCode);
    const candidateId = nextCandidateId_();
    const folderSet = createCandidateFolders_(
      candidateId, input.candidateName
    );

    const storedFiles = storeResumes_(
      candidateId,
      folderSet.resumeFolder,
      driveFileIds || [],
      attachmentBlobs || []
    );

    const hasResume = storedFiles.fileIds.length > 0 ||
      Boolean(input.resumeLink);

    const jobIsValid = Boolean(job);
    const status = jobIsValid && hasResume ?
      'Recruiter Review' : 'Needs Information';

    const exceptionParts = [];
    if (!jobIsValid) exceptionParts.push('Invalid Job');
    if (!hasResume) exceptionParts.push('Missing Resume');

    const ownerName = job ?
      job['Recruiter Name'] : ATS.fallbackRecruiterName;
    const ownerEmail = job ?
      job['Recruiter Email'] : ATS.fallbackRecruiterEmail;
    const slaDays = job ?
      Number(job['SLA Business Days']) || 3 : 1;

    const now = new Date();
    const record = {
      'Candidate ID': candidateId,
      'Created Date': now,
      'Last Updated': now,
      'Source': input.source,
      'Source Record ID': input.sourceRecordId,
      'Job Code': input.jobCode,
      'Job Title': job ? job['Job Title'] : '',
      'Candidate Name': input.candidateName,
      'Candidate Email': normalizeEmail_(input.candidateEmail),
      'Candidate Phone': input.candidatePhone || '',
      'Location': input.location || '',
      'Work Authorization': input.workAuthorization || '',
      'LinkedIn URL': input.linkedInUrl || '',
      'Portfolio URL': input.portfolioUrl || '',
      'Consent': input.consent,
      'Resume File IDs': storedFiles.fileIds.join(','),
      'Resume Link': storedFiles.firstLink || input.resumeLink || '',
      'Candidate Folder ID': folderSet.candidateFolder.getId(),
      'Candidate Folder Link': folderSet.candidateFolder.getUrl(),
      'Owner Name': ownerName,
      'Owner Email': ownerEmail,
      'Hiring Manager Email': job ? job['Hiring Manager Email'] : '',
      'Status': status,
      'Status Changed Date': now,
      'Priority': 'Normal',
      'Due Date': addBusinessDays_(now, slaDays),
      'Interview Feedback Status': 'Not Required',
      'Offer Approver Email': job ? job['Offer Approver Email'] : '',
      'Offer Approval Status': 'Not Required',
      'Communication Approved': 'No',
      'Reminder Count': 0,
      'Exception Type': exceptionParts.join(', '),
      'Automation Status': 'Running',
      'Last Automation Run': now,
      'Retry Count': 0,
      'AI Status': 'Not Run',
      'Notes': storedFiles.errors.join(' | ')
    };

    const sheet = getBook_().getSheetByName('Candidates');
    sheet.appendRow(objectToRow_(record, CANDIDATE_HEADERS));
    const row = sheet.getLastRow();

    audit_(candidateId, 'CANDIDATE_CREATED',
      'Created from ' + input.source + ' with status ' + status + '.');

    try {
      sendCandidateOnce_(
        row,
        'EMAIL_ACK',
        'Application received for ' + (record['Job Title'] || input.jobCode),
        'Thank you for applying to ' + ATS.companyName +
        '. We received your application for ' +
        (record['Job Title'] || input.jobCode) +
        '. Our recruiting team will review the information provided.'
      );

      updateCandidateFields_(row, {
        'Automation Status': exceptionParts.length ?
          'Manual Review' : 'Completed',
        'Last Automation Run': new Date(),
        'Error Message': ''
      });
    } catch (error) {
      markCandidateFailure_(row, 'ingestCandidate_', error);
    }

    return getCandidateRecord_(row);
  } finally {
    lock.releaseLock();
  }
}

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

  const sheet = event.range.getSheet();
  const row = event.range.getRow();
  if (row < 2) return;

  if (sheet.getName() === 'Candidates') {
    handleCandidateSheetEdit_(event, row);
  } else if (sheet.getName() === 'Interviews') {
    handleInterviewSheetEdit_(event, row);
  }
}

function handleCandidateSheetEdit_(event, row) {
  const sheet = event.range.getSheet();
  const field = sheet.getRange(1, event.range.getColumn()).getValue();

  if (!['Status', 'Offer Approval Status',
        'Communication Approved'].includes(field)) {
    return;
  }

  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    updateCandidateFields_(row, {
      'Last Updated': new Date(),
      'Last Automation Run': new Date()
    });

    if (field === 'Status') {
      const status = String(event.value || '').trim();
      if (!STATUSES.includes(status)) {
        throw new Error('Invalid candidate status: ' + status);
      }

      updateCandidateFields_(row, {
        'Status Changed Date': new Date(),
        'Automation Status': 'Running',
        'Error Message': ''
      });

      assignOwnerForStatus_(row, status);
      processStatusChange_(row, status);

      updateCandidateFields_(row, {
        'Automation Status': 'Completed',
        'Last Automation Run': new Date(),
        'Error Message': ''
      });
    }

    if (field === 'Offer Approval Status') {
      processOfferApprovalChange_(row, String(event.value || ''));
    }

    if (field === 'Communication Approved' &&
        String(event.value || '') === 'Yes') {
      const record = getCandidateRecord_(row);
      if (['Rejected', 'Offer Sent'].includes(record['Status'])) {
        processStatusChange_(row, record['Status']);
      }
    }
  } catch (error) {
    markCandidateFailure_(row, 'handleCandidateEdit', error);
    console.error(error.stack || error);
  } finally {
    lock.releaseLock();
  }
}

function handleInterviewSheetEdit_(event, row) {
  const field = event.range.getSheet()
    .getRange(1, event.range.getColumn()).getValue();

  if (field !== 'Feedback Status') return;

  const interview = recordFromRow_('Interviews', row, INTERVIEW_HEADERS);
  const candidateRow = findCandidateRowByField_(
    'Candidate ID', interview['Candidate ID']
  );

  if (candidateRow) {
    updateCandidateFields_(candidateRow, {
      'Interview Feedback Status': event.value || '',
      'Last Updated': new Date()
    });

    audit_(interview['Candidate ID'], 'FEEDBACK_STATUS_UPDATED',
      'Interview ' + interview['Interview ID'] +
      ' changed to ' + event.value + '.');
  }
}

function processStatusChange_(row, status) {
  const record = getCandidateRecord_(row);

  switch (status) {
    case 'Needs Information':
      notifyInternalOnce_(
        record['Candidate ID'],
        'NEEDS_INFORMATION_NOTICE',
        record['Owner Email'],
        'Application requires information: ' + record['Candidate ID'],
        'Review the missing fields or documents for ' +
          record['Candidate Name'] + '.'
      );
      break;

    case 'Hiring Manager Review':
      requireEmail_(record['Hiring Manager Email'], 'Hiring manager email');
      notifyInternalOnce_(
        record['Candidate ID'],
        'HIRING_MANAGER_REVIEW:' +
          dateKey_(record['Status Changed Date']),
        record['Hiring Manager Email'],
        'Candidate review requested: ' + record['Candidate Name'],
        'Candidate ' + record['Candidate ID'] +
          ' is ready for hiring manager review. Candidate folder: ' +
          record['Candidate Folder Link']
      );
      break;

    case 'Interview Requested':
      sendInterviewRequest_(row, record);
      break;

    case 'Interview Scheduled':
      scheduleInterview_(row, record);
      break;

    case 'Interview Complete':
      updateCandidateFields_(row, {
        'Interview Feedback Status': 'Pending'
      });
      notifyInterviewPanel_(record, 'Interview feedback requested');
      break;

    case 'Offer Approval':
      requestOfferApproval_(row, record);
      break;

    case 'Offer Sent':
      sendOffer_(row, record);
      break;

    case 'Rejected':
      sendRejection_(row, record);
      break;

    case 'Hired':
      notifyInternalOnce_(
        record['Candidate ID'],
        'HIRED_NOTICE',
        joinEmails_([
          ATS.hrManagerEmail,
          record['Hiring Manager Email'],
          record['Owner Email']
        ]),
        'Hire confirmed: ' + record['Candidate Name'],
        'HR recorded ' + record['Candidate Name'] +
          ' as hired for ' + record['Job Title'] + '.'
      );
      break;

    default:
      break;
  }

  audit_(record['Candidate ID'], 'STATUS_PROCESSED',
    'Processed status ' + status + '.');
}

function sendInterviewRequest_(row, record) {
  let instruction = 'Please reply with your availability.';
  if (!isPlaceholder_(ATS.schedulingUrl)) {
    instruction = 'Choose an available time using ' + ATS.schedulingUrl;
  }

  sendCandidateOnce_(
    row,
    'INTERVIEW_REQUEST:' + dateKey_(record['Status Changed Date']),
    'Interview availability for ' + record['Job Title'],
    'We would like to arrange an interview regarding the ' +
      record['Job Title'] + ' opportunity. ' + instruction
  );
}

function scheduleInterview_(row, record) {
  const start = record['Interview Start'];
  const end = record['Interview End'];
  const round = String(record['Interview Round'] || '').trim();
  const panel = String(record['Interview Panel Emails'] || '').trim();

  if (!(start instanceof Date) || !(end instanceof Date) ||
      end.getTime() <= start.getTime()) {
    throw new Error('Valid interview start and end values are required.');
  }
  if (!round) throw new Error('Interview Round is required.');
  if (!panel) throw new Error('At least one panel email is required.');

  const interviewId = record['Candidate ID'] + '-INT-' +
    Utilities.formatDate(start, ATS.timeZone, 'yyyyMMddHHmm');

  const existing = findInterviewById_(interviewId);
  if (existing) {
    updateCandidateFields_(row, {
      'Calendar Event ID': existing['Calendar Event ID'],
      'Interview Feedback Status': existing['Feedback Status']
    });
    return;
  }

  const calendar = CalendarApp.getCalendarById(ATS.calendarId);
  if (!calendar) {
    throw new Error('Configured recruiting calendar was not found.');
  }

  const guestEmails = joinEmails_(
    [record['Candidate Email']].concat(splitEmails_(panel))
  );

  const event = calendar.createEvent(
    record['Interview Round'] + ': ' + record['Candidate Name'],
    start,
    end,
    {
      description:
        'Candidate ID: ' + record['Candidate ID'] + '\n' +
        'Role: ' + record['Job Title'] + '\n' +
        'Candidate folder: ' + record['Candidate Folder Link'],
      location: record['Interview Location or Link'] || '',
      guests: guestEmails,
      sendInvites: true
    }
  );

  const feedbackDue = new Date(end.getTime() + 24 * 60 * 60 * 1000);
  const interviewRecord = {
    'Interview ID': interviewId,
    'Candidate ID': record['Candidate ID'],
    'Round': round,
    'Start': start,
    'End': end,
    'Time Zone': record['Interview Time Zone'] || ATS.timeZone,
    'Panel Emails': panel,
    'Mode': record['Interview Mode'] || '',
    'Location or Link': record['Interview Location or Link'] || '',
    'Calendar Event ID': event.getId(),
    'Feedback Status': 'Pending',
    'Feedback Due': feedbackDue,
    'Created Date': new Date(),
    'Reminder Count': 0
  };

  getBook_().getSheetByName('Interviews')
    .appendRow(objectToRow_(interviewRecord, INTERVIEW_HEADERS));

  updateCandidateFields_(row, {
    'Calendar Event ID': event.getId(),
    'Interview Feedback Status': 'Pending',
    'Due Date': feedbackDue
  });

  audit_(record['Candidate ID'], 'CALENDAR_CREATED:' + interviewId,
    'Created calendar event ' + event.getId() + '.');
}

function requestOfferApproval_(row, record) {
  requireEmail_(record['Offer Approver Email'], 'Offer approver email');

  if (!record['Offer Document Link']) {
    throw new Error('Offer Document Link is required before approval.');
  }

  updateCandidateFields_(row, {
    'Offer Approval Status': 'Pending',
    'Due Date': addBusinessDays_(new Date(), 2)
  });

  notifyInternalOnce_(
    record['Candidate ID'],
    'OFFER_APPROVAL_REQUEST:' + dateKey_(record['Status Changed Date']),
    record['Offer Approver Email'],
    'Offer approval requested: ' + record['Candidate Name'],
    'Review the proposed offer for ' + record['Candidate Name'] +
      '. Document: ' + record['Offer Document Link'] +
      '. Record the decision in the protected approval field.'
  );
}

function processOfferApprovalChange_(row, value) {
  const allowed = [
    'Not Required', 'Pending', 'Approved',
    'Rejected', 'More Information'
  ];
  if (!allowed.includes(value)) {
    throw new Error('Invalid offer approval value: ' + value);
  }

  const record = getCandidateRecord_(row);

  if (value === 'Approved') {
    updateCandidateFields_(row, {
      'Offer Approval Date': new Date(),
      'Automation Status': 'Completed',
      'Error Message': ''
    });

    notifyInternalOnce_(
      record['Candidate ID'],
      'OFFER_APPROVED',
      record['Owner Email'],
      'Offer approved: ' + record['Candidate Name'],
      'The authorized approver recorded approval. Verify the final document ' +
        'and candidate communication before changing status to Offer Sent.'
    );
  }

  if (value === 'Rejected' || value === 'More Information') {
    notifyInternalOnce_(
      record['Candidate ID'],
      'OFFER_RETURNED:' + value + ':' + dateKey_(new Date()),
      record['Owner Email'],
      'Offer requires HR action: ' + record['Candidate Name'],
      'The offer approval status is ' + value + '.'
    );
  }

  audit_(record['Candidate ID'], 'OFFER_APPROVAL_' + value.toUpperCase()
    .replace(/\s+/g, '_'), 'Approval field updated.');
}

function sendOffer_(row, record) {
  if (record['Offer Approval Status'] !== 'Approved') {
    throw new Error('Offer approval must be Approved.');
  }
  if (record['Communication Approved'] !== 'Yes') {
    throw new Error('Communication Approved must be Yes.');
  }
  if (!record['Offer Document Link']) {
    throw new Error('Offer Document Link is required.');
  }

  sendCandidateOnce_(
    row,
    'EMAIL_OFFER',
    'Offer information from ' + ATS.companyName,
    'We are pleased to provide your approved offer information for the ' +
      record['Job Title'] + ' opportunity. Review the document here: ' +
      record['Offer Document Link'] +
      '. Please contact your recruiter with questions.'
  );
}

function sendRejection_(row, record) {
  if (record['Communication Approved'] !== 'Yes') {
    throw new Error(
      'Communication Approved must be Yes before rejection email.'
    );
  }

  sendCandidateOnce_(
    row,
    'EMAIL_REJECTION',
    'Update regarding your application',
    'Thank you for your interest in the ' + record['Job Title'] +
      ' opportunity at ' + ATS.companyName +
      '. We have decided not to move forward with your application for ' +
      'this role. We appreciate the time you invested in the process.'
  );
}

function sendDailyReminders() {
  validateConfiguration_(true);
  processCandidateReminders_();
  processInterviewReminders_();
}

function processCandidateReminders_() {
  const sheet = getBook_().getSheetByName('Candidates');
  if (sheet.getLastRow() < 2) return;

  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, CANDIDATE_HEADERS.length
  ).getValues();

  const today = startOfDay_(new Date());

  values.forEach((rowValues, offset) => {
    const row = offset + 2;
    const record = rowToObject_(rowValues, CANDIDATE_HEADERS);

    if (!record['Candidate ID'] ||
        TERMINAL_STATUSES.includes(record['Status'])) {
      return;
    }

    try {
      if (record['Automation Status'] === 'Failed' &&
          Number(record['Retry Count'] || 0) < ATS.maxRetries) {
        processStatusChange_(row, record['Status']);
        updateCandidateFields_(row, {
          'Automation Status': 'Completed',
          'Last Automation Run': new Date(),
          'Error Message': ''
        });
      }

      const dueDate = record['Due Date'];
      const lastReminder = record['Last Reminder Date'];

      if (dueDate instanceof Date &&
          startOfDay_(dueDate).getTime() <= today.getTime() &&
          !sameDay_(lastReminder, today)) {
        const count = Number(record['Reminder Count'] || 0) + 1;
        const recipients = count > 2 ?
          joinEmails_([record['Owner Email'], ATS.hrManagerEmail]) :
          record['Owner Email'];

        requireEmail_(recipients, 'Reminder recipient');
        GmailApp.sendEmail(
          recipients,
          'Candidate action due: ' + record['Candidate ID'],
          'Action is due for ' + record['Candidate Name'] +
          ', status ' + record['Status'] + '.'
        );

        updateCandidateFields_(row, {
          'Reminder Count': count,
          'Last Reminder Date': new Date()
        });

        audit_(record['Candidate ID'], 'OWNER_REMINDER',
          'Reminder ' + count + ' sent for ' + record['Status'] + '.');
      }
    } catch (error) {
      markCandidateFailure_(row, 'processCandidateReminders_', error);
    }
  });
}

function processInterviewReminders_() {
  const sheet = getBook_().getSheetByName('Interviews');
  if (sheet.getLastRow() < 2) return;

  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, INTERVIEW_HEADERS.length
  ).getValues();

  const now = new Date();

  values.forEach((rowValues, offset) => {
    const row = offset + 2;
    const interview = rowToObject_(rowValues, INTERVIEW_HEADERS);
    if (!interview['Interview ID']) return;

    const candidateRow = findCandidateRowByField_(
      'Candidate ID', interview['Candidate ID']
    );
    if (!candidateRow) return;

    const candidate = getCandidateRecord_(candidateRow);
    const start = interview['Start'];
    const end = interview['End'];
    const lastReminder = interview['Last Reminder Date'];
    const hoursUntilStart = start instanceof Date ?
      (start.getTime() - now.getTime()) / 3600000 : null;

    try {
      if (hoursUntilStart !== null &&
          hoursUntilStart >= 20 && hoursUntilStart <= 28 &&
          !sameDay_(lastReminder, now)) {
        const recipients = joinEmails_(
          [candidate['Candidate Email']]
            .concat(splitEmails_(interview['Panel Emails']))
        );

        GmailApp.sendEmail(
          recipients,
          'Interview reminder: ' + candidate['Job Title'],
          'Reminder for the ' + interview['Round'] +
          ' interview scheduled for ' +
          Utilities.formatDate(start, ATS.timeZone, 'yyyy-MM-dd HH:mm') +
          ' ' + ATS.timeZone + '.'
        );

        updateInterviewFields_(row, {
          'Last Reminder Date': now,
          'Reminder Count': Number(interview['Reminder Count'] || 0) + 1
        });

        audit_(candidate['Candidate ID'],
          'INTERVIEW_REMINDER:' + interview['Interview ID'],
          'Sent approximately 24-hour reminder.');
      }

      if (end instanceof Date && end.getTime() < now.getTime() &&
          interview['Feedback Status'] === 'Pending' &&
          !sameDay_(lastReminder, now)) {
        notifyInterviewPanel_(candidate,
          'Feedback requested for ' + interview['Round']);

        updateInterviewFields_(row, {
          'Last Reminder Date': now,
          'Reminder Count': Number(interview['Reminder Count'] || 0) + 1
        });

        if (candidate['Calendar Event ID'] ===
            interview['Calendar Event ID'] &&
            candidate['Status'] === 'Interview Scheduled') {
          updateCandidateFields_(candidateRow, {
            'Status': 'Interview Complete',
            'Status Changed Date': now,
            'Interview Feedback Status': 'Pending',
            'Owner Email': candidate['Hiring Manager Email'],
            'Due Date': interview['Feedback Due'],
            'Last Updated': now
          });
        }
      }
    } catch (error) {
      recordError_(candidate['Candidate ID'], interview['Interview ID'],
        'processInterviewReminders_', error,
        Number(interview['Reminder Count'] || 0));
    }
  });
}

function notifyInterviewPanel_(record, subject) {
  const recipients = joinEmails_(
    splitEmails_(record['Interview Panel Emails'])
      .concat([record['Hiring Manager Email'], record['Owner Email']])
  );

  requireEmail_(recipients, 'Interview feedback recipients');

  let body = 'Please submit interview feedback for ' +
    record['Candidate ID'] + '.';
  if (!isPlaceholder_(ATS.feedbackUrl)) {
    body += ' Feedback form: ' + ATS.feedbackUrl;
  }

  GmailApp.sendEmail(recipients, subject, body);
}

function assignOwnerForStatus_(row, status) {
  const record = getCandidateRecord_(row);
  const job = findJob_(record['Job Code']);
  if (!job) return;

  let ownerName = job['Recruiter Name'];
  let ownerEmail = job['Recruiter Email'];

  if (['Hiring Manager Review', 'Interview Complete'].includes(status)) {
    ownerName = job['Hiring Manager Name'];
    ownerEmail = job['Hiring Manager Email'];
  } else if (status === 'Offer Approval') {
    ownerName = 'Offer Approver';
    ownerEmail = job['Offer Approver Email'];
  }

  updateCandidateFields_(row, {
    'Owner Name': ownerName,
    'Owner Email': ownerEmail,
    'Due Date': addBusinessDays_(new Date(), 2)
  });
}

function sendCandidateOnce_(row, action, subject, body) {
  const record = getCandidateRecord_(row);
  if (hasAuditAction_(record['Candidate ID'], action)) return;

  requireEmail_(record['Candidate Email'], 'Candidate email');

  GmailApp.sendEmail(record['Candidate Email'], subject, body, {
    name: ATS.companyName + ' Recruiting',
    replyTo: ATS.fallbackRecruiterEmail
  });

  updateCandidateFields_(row, {
    'Last Candidate Email': new Date()
  });

  audit_(record['Candidate ID'], action,
    'Sent candidate email with subject: ' + subject);
}

function notifyInternalOnce_(candidateId, action, recipients, subject, body) {
  if (hasAuditAction_(candidateId, action)) return;
  requireEmail_(recipients, 'Internal notification recipient');
  GmailApp.sendEmail(recipients, subject, body);
  audit_(candidateId, action, 'Sent internal email: ' + subject);
}

function parseApplicationEmail_(message) {
  const body = message.getPlainBody();
  const fromEmail = extractEmail_(message.getFrom());
  const fromName = extractDisplayName_(message.getFrom());
  const subject = message.getSubject();

  return {
    source: lineValue_(body, 'Source') ||
      (/linkedin/i.test(subject) ? 'LinkedIn' : 'Email'),
    sourceRecordId: 'GMAIL:' + message.getId(),
    jobCode: lineValue_(body, 'Job Code'),
    candidateName: lineValue_(body, 'Candidate Name') || fromName,
    candidateEmail: lineValue_(body, 'Candidate Email') || fromEmail,
    candidatePhone: lineValue_(body, 'Phone'),
    location: lineValue_(body, 'Location'),
    workAuthorization: lineValue_(body, 'Work Authorization'),
    linkedInUrl: lineValue_(body, 'LinkedIn URL'),
    portfolioUrl: lineValue_(body, 'Portfolio URL'),
    resumeLink: lineValue_(body, 'Resume Link'),
    consent: 'Email application'
  };
}

function createCandidateFolders_(candidateId, candidateName) {
  const root = DriveApp.getFolderById(ATS.rootFolderId);
  const year = candidateId.split('-')[1];
  const yearFolder = getOrCreateFolder_(root, year);
  const safeName = sanitizeFileName_(candidateName);
  const candidateFolder = getOrCreateFolder_(
    yearFolder, candidateId + ' - ' + safeName
  );

  return {
    candidateFolder: candidateFolder,
    resumeFolder: getOrCreateFolder_(candidateFolder, 'Resume'),
    interviewFolder: getOrCreateFolder_(candidateFolder, 'Interviews'),
    offerFolder: getOrCreateFolder_(candidateFolder, 'Offer')
  };
}

function storeResumes_(candidateId, folder, driveFileIds, blobs) {
  const fileIds = [];
  const links = [];
  const errors = [];

  driveFileIds.forEach(fileId => {
    try {
      const source = DriveApp.getFileById(fileId);
      const copy = source.makeCopy(
        candidateId + ' - Resume - ' +
        sanitizeFileName_(source.getName()),
        folder
      );
      fileIds.push(copy.getId());
      links.push(copy.getUrl());
    } catch (error) {
      errors.push('Drive copy failed for ' + fileId + ': ' + error.message);
    }
  });

  blobs.forEach((blob, index) => {
    try {
      const originalName = blob.getName() || ('Attachment-' + (index + 1));
      const file = folder.createFile(blob)
        .setName(candidateId + ' - Resume - ' +
          sanitizeFileName_(originalName));
      fileIds.push(file.getId());
      links.push(file.getUrl());
    } catch (error) {
      errors.push('Attachment save failed: ' + error.message);
    }
  });

  return {
    fileIds: fileIds,
    firstLink: links[0] || '',
    errors: errors
  };
}

function findJob_(jobCode) {
  const sheet = getBook_().getSheetByName('Jobs');
  if (!sheet || sheet.getLastRow() < 2) return null;

  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, JOB_HEADERS.length
  ).getValues();

  for (let index = 0; index < values.length; index += 1) {
    const record = rowToObject_(values[index], JOB_HEADERS);
    if (String(record['Job Code']).trim().toLowerCase() ===
        String(jobCode).trim().toLowerCase() &&
        String(record['Active']).trim().toLowerCase() === 'yes') {
      return record;
    }
  }
  return null;
}

function findActiveDuplicate_(email, jobCode) {
  const sheet = getBook_().getSheetByName('Candidates');
  if (!sheet || sheet.getLastRow() < 2) return 0;

  const map = headerMap_(CANDIDATE_HEADERS);
  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, CANDIDATE_HEADERS.length
  ).getValues();

  const targetEmail = normalizeEmail_(email);
  const targetJob = String(jobCode).trim().toLowerCase();

  for (let index = 0; index < values.length; index += 1) {
    const row = values[index];
    const rowEmail = normalizeEmail_(row[map['Candidate Email']]);
    const rowJob = String(row[map['Job Code']]).trim().toLowerCase();
    const status = String(row[map['Status']]);

    if (rowEmail === targetEmail && rowJob === targetJob &&
        !TERMINAL_STATUSES.includes(status)) {
      return index + 2;
    }
  }
  return 0;
}

function findCandidateRowByField_(field, value) {
  const sheet = getBook_().getSheetByName('Candidates');
  if (!sheet || sheet.getLastRow() < 2) return 0;

  const column = CANDIDATE_HEADERS.indexOf(field) + 1;
  if (!column) throw new Error('Unknown candidate field: ' + field);

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

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

function findInterviewById_(interviewId) {
  const sheet = getBook_().getSheetByName('Interviews');
  if (!sheet || sheet.getLastRow() < 2) return null;

  const finder = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1)
    .createTextFinder(interviewId)
    .matchEntireCell(true)
    .findNext();

  return finder ?
    recordFromRow_('Interviews', finder.getRow(), INTERVIEW_HEADERS) :
    null;
}

function nextCandidateId_() {
  const year = Utilities.formatDate(new Date(), ATS.timeZone, 'yyyy');
  const key = 'ATS_SEQUENCE_' + year;
  const properties = PropertiesService.getScriptProperties();
  const next = Number(properties.getProperty(key) || 0) + 1;
  properties.setProperty(key, String(next));
  return 'AP-' + year + '-' + String(next).padStart(4, '0');
}

function updateCandidateFields_(row, updates) {
  updateFields_('Candidates', row, CANDIDATE_HEADERS, updates);
}

function updateInterviewFields_(row, updates) {
  updateFields_('Interviews', row, INTERVIEW_HEADERS, updates);
}

function updateFields_(sheetName, row, headers, updates) {
  const sheet = getBook_().getSheetByName(sheetName);
  Object.keys(updates).forEach(field => {
    const column = headers.indexOf(field) + 1;
    if (!column) throw new Error('Unknown field: ' + field);
    sheet.getRange(row, column).setValue(updates[field]);
  });
}

function getCandidateRecord_(row) {
  return recordFromRow_('Candidates', row, CANDIDATE_HEADERS);
}

function recordFromRow_(sheetName, row, headers) {
  const values = getBook_().getSheetByName(sheetName)
    .getRange(row, 1, 1, headers.length)
    .getValues()[0];
  return rowToObject_(values, headers);
}

function markCandidateFailure_(row, functionName, error) {
  const record = getCandidateRecord_(row);
  const retryCount = Number(record['Retry Count'] || 0) + 1;

  updateCandidateFields_(row, {
    'Automation Status': retryCount >= ATS.maxRetries ?
      'Manual Review' : 'Failed',
    'Last Automation Run': new Date(),
    'Retry Count': retryCount,
    'Error Message': String(error.message || error).slice(0, 500),
    'Exception Type': 'Automation Failure'
  });

  recordError_(
    record['Candidate ID'],
    record['Source Record ID'],
    functionName,
    error,
    retryCount
  );
}

function audit_(candidateId, action, details) {
  getBook_().getSheetByName('Audit Log').appendRow([
    new Date(),
    candidateId,
    action,
    Session.getEffectiveUser().getEmail() || 'automation',
    String(details || '').slice(0, 1000)
  ]);
}

function hasAuditAction_(candidateId, action) {
  const sheet = getBook_().getSheetByName('Audit Log');
  if (!sheet || sheet.getLastRow() < 2) return false;

  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, AUDIT_HEADERS.length
  ).getValues();

  return values.some(row =>
    String(row[1]) === String(candidateId) &&
    String(row[2]) === String(action)
  );
}

function recordError_(candidateId, sourceId, functionName, error, retryCount) {
  const sheet = getBook_().getSheetByName('Errors');
  if (!sheet) {
    console.error('Errors sheet is unavailable: ' + error);
    return;
  }

  sheet.appendRow([
    new Date(),
    candidateId || '',
    sourceId || '',
    functionName,
    String(error.message || error).slice(0, 1000),
    retryCount || 0,
    'Open',
    ''
  ]);
}

function validateCandidateInput_(input) {
  if (!input) throw new Error('Candidate input is missing.');
  if (!String(input.candidateName || '').trim()) {
    throw new Error('Candidate name is required.');
  }
  requireEmail_(input.candidateEmail, 'Candidate email');
  if (!String(input.jobCode || '').trim()) {
    throw new Error('Job code is required.');
  }
  if (!String(input.sourceRecordId || '').trim()) {
    throw new Error('Source record ID is required.');
  }

  const consent = String(input.consent || '').toLowerCase();
  if (!consent.includes('yes') &&
      consent !== 'email application') {
    throw new Error('Consent acknowledgement is required.');
  }
}

function requireEmail_(value, label) {
  const emails = splitEmails_(value);
  if (!emails.length ||
      emails.some(email =>
        !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email))) {
    throw new Error(label + ' is missing or invalid.');
  }
}

function ensureSheet_(book, name, headers) {
  const sheet = book.getSheetByName(name) || book.insertSheet(name);

  if (sheet.getMaxColumns() < headers.length) {
    sheet.insertColumnsAfter(
      sheet.getMaxColumns(),
      headers.length - sheet.getMaxColumns()
    );
  }

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    const existing = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
    const mismatch = headers.some((header, index) =>
      existing[index] !== header
    );
    if (mismatch) {
      throw new Error('Header mismatch on sheet ' + name +
        '. Back up data before changing columns.');
    }
  }

  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');

  if (!sheet.getFilter() && sheet.getMaxRows() > 1) {
    sheet.getRange(1, 1, sheet.getMaxRows(), headers.length).createFilter();
  }

  return sheet;
}

function applyCandidateValidation_(sheet) {
  setListValidation_(sheet, 'Source',
    ['Website', 'LinkedIn', 'Email', 'Referral', 'Other']);
  setListValidation_(sheet, 'Work Authorization',
    ['Authorized', 'Sponsorship Required', 'Prefer Not to Answer']);
  setListValidation_(sheet, 'Status', STATUSES);
  setListValidation_(sheet, 'Priority', ['Normal', 'High', 'Urgent']);
  setListValidation_(sheet, 'Interview Round',
    ['Phone Screen', 'Manager Interview', 'Panel Interview',
     'Final Interview']);
  setListValidation_(sheet, 'Interview Mode',
    ['Video', 'Phone', 'In Person']);
  setListValidation_(sheet, 'Interview Feedback Status',
    ['Not Required', 'Pending', 'Complete', 'Escalated']);
  setListValidation_(sheet, 'Offer Approval Status',
    ['Not Required', 'Pending', 'Approved', 'Rejected',
     'More Information']);
  setListValidation_(sheet, 'Communication Approved', ['No', 'Yes']);
  setListValidation_(sheet, 'Automation Status',
    ['Pending', 'Running', 'Completed', 'Failed', 'Manual Review']);
  setListValidation_(sheet, 'AI Status',
    ['Not Run', 'Processing', 'Review Required', 'Accepted', 'Failed']);

  formatDateColumns_(sheet, CANDIDATE_HEADERS, [
    'Created Date', 'Last Updated', 'Status Changed Date', 'Due Date',
    'Interview Start', 'Interview End', 'Offer Approval Date',
    'Last Candidate Email', 'Last Reminder Date', 'Last Automation Run'
  ]);
}

function applyJobValidation_(sheet) {
  setListValidation_(sheet, 'Active', ['Yes', 'No'], JOB_HEADERS);
}

function applyInterviewValidation_(sheet) {
  setListValidation_(sheet, 'Feedback Status',
    ['Pending', 'Complete', 'Escalated'], INTERVIEW_HEADERS);
  formatDateColumns_(sheet, INTERVIEW_HEADERS,
    ['Start', 'End', 'Feedback Due', 'Created Date', 'Last Reminder Date']);
}

function setListValidation_(sheet, field, values, headers) {
  const headerList = headers || CANDIDATE_HEADERS;
  const column = headerList.indexOf(field) + 1;
  if (!column) return;

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

  sheet.getRange(2, column, sheet.getMaxRows() - 1, 1)
    .setDataValidation(rule);
}

function formatDateColumns_(sheet, headers, fields) {
  fields.forEach(field => {
    const column = headers.indexOf(field) + 1;
    if (column) {
      sheet.getRange(2, column, sheet.getMaxRows() - 1, 1)
        .setNumberFormat('yyyy-mm-dd hh:mm');
    }
  });
}

function buildDashboard_(sheet) {
  sheet.clear();
  sheet.getRange('A1:B1').setValues([['Metric', 'Value']])
    .setFontWeight('bold');

  const metrics = [
    ['Total candidates', '=COUNTA(Candidates!A2:A)'],
    ['Recruiter review', '=COUNTIF(Candidates!W2:W,"Recruiter Review")'],
    ['Manager review', '=COUNTIF(Candidates!W2:W,"Hiring Manager Review")'],
    ['Interviews scheduled', '=COUNTIF(Candidates!W2:W,"Interview Scheduled")'],
    ['Offer approval', '=COUNTIF(Candidates!W2:W,"Offer Approval")'],
    ['Needs information', '=COUNTIF(Candidates!W2:W,"Needs Information")'],
    ['Automation failures', '=COUNTIF(Candidates!AS2:AS,"Failed")'],
    ['Manual review', '=COUNTIF(Candidates!AS2:AS,"Manual Review")'],
    ['Hired', '=COUNTIF(Candidates!W2:W,"Hired")']
  ];

  sheet.getRange(2, 1, metrics.length, 2).setValues(metrics);
  sheet.autoResizeColumns(1, 2);
}

function addBusinessDays_(date, days) {
  const result = new Date(date);
  let remaining = Math.max(0, Number(days) || 0);

  while (remaining > 0) {
    result.setDate(result.getDate() + 1);
    const day = result.getDay();
    if (day !== 0 && day !== 6) remaining -= 1;
  }
  return result;
}

function getOrCreateFolder_(parent, name) {
  const folders = parent.getFoldersByName(name);
  return folders.hasNext() ? folders.next() : parent.createFolder(name);
}

function namedValue_(namedValues, key) {
  const value = namedValues[key];
  return Array.isArray(value) ? value.join(', ').trim() :
    String(value || '').trim();
}

function extractDriveIds_(value) {
  return String(value || '').match(/[-\w]{25,}/g) || [];
}

function lineValue_(body, label) {
  const escaped = label.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  const match = String(body || '').match(
    new RegExp('^' + escaped + '\\s*:\\s*(.+)$', 'im')
  );
  return match ? match[1].trim() : '';
}

function extractEmail_(value) {
  const match = String(value || '').match(
    /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/i
  );
  return match ? match[0].toLowerCase() : '';
}

function extractDisplayName_(value) {
  return String(value || '')
    .replace(/<[^>]+>/g, '')
    .replace(/["']/g, '')
    .trim();
}

function normalizeEmail_(value) {
  return String(value || '').trim().toLowerCase();
}

function sanitizeFileName_(value) {
  return String(value || 'Unknown')
    .replace(/[\\/:*?"<>|#%{}~]/g, '-')
    .replace(/\s+/g, ' ')
    .trim()
    .slice(0, 120);
}

function splitEmails_(value) {
  return String(value || '')
    .split(/[;,]/)
    .map(email => email.trim().toLowerCase())
    .filter(Boolean);
}

function joinEmails_(values) {
  const flattened = [];
  values.forEach(value => {
    splitEmails_(value).forEach(email => flattened.push(email));
  });
  return Array.from(new Set(flattened)).join(',');
}

function objectToRow_(object, headers) {
  return headers.map(header =>
    Object.prototype.hasOwnProperty.call(object, header) ?
      object[header] : ''
  );
}

function rowToObject_(values, headers) {
  return headers.reduce((object, header, index) => {
    object[header] = values[index];
    return object;
  }, {});
}

function headerMap_(headers) {
  return headers.reduce((map, header, index) => {
    map[header] = index;
    return map;
  }, {});
}

function sameDay_(first, second) {
  if (!(first instanceof Date) || !(second instanceof Date)) return false;
  return startOfDay_(first).getTime() === startOfDay_(second).getTime();
}

function startOfDay_(date) {
  const value = new Date(date);
  value.setHours(0, 0, 0, 0);
  return value;
}

function dateKey_(date) {
  const value = date instanceof Date ? date : new Date();
  return Utilities.formatDate(value, ATS.timeZone, 'yyyyMMddHHmmss');
}

function isPlaceholder_(value) {
  return !value || String(value).startsWith('YOUR_');
}

function validateConfiguration_(requireAll) {
  if (isPlaceholder_(ATS.spreadsheetId)) {
    throw new Error('Replace YOUR_SPREADSHEET_ID.');
  }

  if (requireAll) {
    [
      ['rootFolderId', ATS.rootFolderId],
      ['calendarId', ATS.calendarId],
      ['fallbackRecruiterEmail', ATS.fallbackRecruiterEmail],
      ['hrManagerEmail', ATS.hrManagerEmail],
      ['timeZone', ATS.timeZone]
    ].forEach(item => {
      if (isPlaceholder_(item[1])) {
        throw new Error('Replace configuration value: ' + item[0]);
      }
    });
  }
}

function getBook_() {
  return SpreadsheetApp.openById(ATS.spreadsheetId);
}

After replacing the values, save the project and set the Apps Script project time zone to the same IANA time zone used in ATS.timeZone. Examples of IANA values include region-based names maintained by the time-zone database. Do not use an ambiguous abbreviation such as EST.

Run setupAts once. Review the authorization screen, approve the required scopes, and inspect the execution result. Populate the Jobs sheet, then run installTriggers.

Inspect runs in the Apps Script Executions view. Console messages appear with each execution. Business failures also appear in the Errors sheet, while significant successful actions appear in Audit Log.

Duplicate execution is prevented through source IDs, email-plus-job matching, a script lock, Calendar interview IDs, and audit action keys. Failed record actions are retried by the daily process up to three times. After that, the record remains in Manual Review until a system owner corrects the data and reprocesses the intended status.

Common setup errors include incorrect folder IDs, a trigger owner without calendar access, mismatched form question titles, invalid email addresses in Jobs, and a project time zone that differs from the spreadsheet time zone.

Failure Handling and Operational Reliability

The Errors sheet serves as a lightweight dead-letter queue. It records work that could not complete normally. Records are not deleted from the queue when resolved. Instead, the owner changes Status to Resolved and records a Resolved Date, preserving the operational history.

Daily reconciliation compares:

• Recent form responses to Candidates Source Record IDs.
• Processed Gmail threads to Gmail-based Source Record IDs.
• Candidate Calendar Event IDs to Interviews records.
• Failed Candidate rows to open Errors entries.
• Offer Sent records to Approved offer status and communication authorization.

Apps Script and Google services enforce quotas and execution limits that vary by account and service. The process should use bounded batches, as the email function does, and avoid assuming unlimited messages, file operations, or execution time.

A Complete Example

A fictional applicant named Jordan Lee applies for the Senior Implementation Consultant role using the website form.

1. Jordan selects job code CON-2026-01, enters an email address, provides the required privacy acknowledgement, and uploads a sample PDF resume.
2. The form writes a response row. The installed trigger receives the named values and creates source ID FORM:184726351:27.
3. The script validates the email and checks that the source ID has not already been processed.
4. It searches active Candidate rows for Jordan’s normalized email plus CON-2026-01. No duplicate exists.
5. The Jobs lookup returns the job title, recruiting coordinator, hiring manager, offer approver, and a three-business-day review target.
6. The sequence generator returns AP-2026-0042.
7. Drive folders are created under 2026/AP-2026-0042 - Jordan Lee. The resume copy is renamed and its Drive ID is saved.
8. The Candidates row enters Recruiter Review. The recruiter becomes the owner, the due date is calculated, and Automation Status becomes Completed.
9. Gmail sends one acknowledgement. Audit Log records CANDIDATE_CREATED and EMAIL_ACK.
10. After reviewing the resume, the recruiter changes Status to Hiring Manager Review. The hiring manager receives a review request containing the Candidate ID and controlled folder link.
11. The hiring manager approves an interview. The recruiter changes Status to Interview Requested, causing Gmail to send the scheduling instructions.
12. After agreeing on a time, the recruiter enters Panel Interview, start and end times, panel emails, Video, and the approved meeting link. The recruiter changes Status to Interview Scheduled.
13. Apps Script creates the Calendar event and receives an event ID. It appends an Interviews row such as AP-2026-0042-INT-202608191400.
14. Approximately 24 hours before the event, participants receive a reminder. After the event ends, the Candidate status changes to Interview Complete, and the panel receives a feedback request.
15. The hiring manager recommends an offer. HR prepares an offer document, enters its link, and changes Status to Offer Approval.
16. The approver changes Offer Approval Status to Approved. HR reviews the final document and authorizes candidate communication.
17. When HR changes Status to Offer Sent, the script verifies approval, communication authorization, and the document link before sending the candidate email.
18. After Jordan accepts and HR verifies the acceptance, an authorized HR user changes Status to Hired. The workflow records the final status and sends the internal hiring notice.

If Calendar creation failed at step 12, the record would show Automation Status Failed, retain the intended interview data, and create an Errors row. No Calendar Event ID would be stored. After access was corrected, the system owner could rerun the status action without recreating the candidate or resending the original acknowledgement.

Implementation Cost

All amounts below are representative planning assumptions, not verified client results or Intelligex pricing. Existing Google Workspace subscription costs are excluded because the fictional firm already licenses the tools. Organizations without the required features should obtain current licensing information from Google.

A zero incremental software line does not mean the system is free. It depends on an existing Workspace subscription, implementation labour, maintenance, account administration, testing, and quota monitoring.

Estimated Time and Cost Savings

The calculation uses these representative assumptions:

• 60 applications per month.
• 32 minutes of current administrative handling per application.
• 10 minutes of routine handling after core automation.
• 12 percent of applications require exception handling.
• 12 minutes of manual work per exception.
• 2 hours of monthly maintenance.
• $45 loaded hourly labour cost.
• $0 incremental monthly core software cost because Workspace is already licensed.
• $6,200 one-time implementation cost.

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

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

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

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

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

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

Recovered time does not automatically reduce payroll. It may provide additional recruiting capacity, faster candidate turnaround, reduced overtime, fewer administrative tasks, better coverage during employee absence, or the ability to handle more open roles without adding the same amount of coordination work.

Non-financial benefits include clearer ownership, fewer follow-up emails, consistent folder organization, better audit evidence, more reliable pipeline reporting, fewer incomplete records, and a more predictable applicant experience.

Readers should replace the application volume, current handling time, automated handling time, exception rate, maintenance time, labour cost, software cost, and implementation cost with their own measured figures.

Adding AI to the Automation

AI is an optional enhancement added only after intake, status, document, communication, retry, and audit functions operate reliably.

Useful AI applications in this workflow include:

• Extracting explicit contact, employment, education, certification, and skill details from resumes.
• Creating a short factual resume summary for recruiter review.
• Identifying fields that appear missing or unclear.
• Grouping explicitly stated skills for reporting.
• Supporting semantic search across recruiter-approved summaries.

AI is not required for job-code validation, exact duplicate checks, status transitions, due-date calculations, approval thresholds, permissions, or communication prerequisites. Those tasks are better handled with deterministic rules.

The core automation creates the operational savings from intake, file management, assignments, notifications, reminders, and reporting. AI provides a narrower benefit by reducing the time required to read and transcribe unstructured resume information.

The Recommended AI Enhancement

The recommended enhancement extracts resume facts into structured JSON for recruiter review. It does not score applicants, recommend advancement, compare candidates, or make a hiring decision.

• Trigger: A recruiter selects a Candidate row and runs Extract resume for active row.
• AI input: One approved resume file from the candidate’s controlled Drive folder.
• System instruction: Extract explicit facts only, ignore protected characteristics, and do not make a recommendation.
• Expected output: Strict JSON containing contact details, recent role, years-of-experience estimate, skills, education, certifications, summary, unclear items, and confidence.
• Validation: Apps Script verifies required keys and confidence range before updating the row.
• Record update: AI fields are populated and AI Status becomes Review Required.
• Human review: A recruiter compares every field with the resume and corrects or accepts it.
• Low confidence: Any overall confidence below 0.75 remains highlighted for manual extraction.
• Prohibited data: The prompt instructs the model not to extract or infer age, race, ethnicity, religion, disability, medical information, family status, gender identity, sexual orientation, or other protected characteristics.
• Logging: The Audit Log records the model name and confidence, not the API key or full prompt response.
• Failure behavior: AI Status becomes Failed, while the core candidate workflow continues normally.

The reusable system instruction is:

You extract factual resume information for recruiter review.

Use only information explicitly present in the supplied resume. Do not infer
protected characteristics, personality, cultural fit, job suitability, or a
hiring recommendation. Do not rank or score the candidate. Ignore photographs
and any references to age, race, ethnicity, religion, disability, medical
information, family status, gender identity, sexual orientation, or other
protected characteristics.

If a value is absent or uncertain, return null, an empty array, or explain the
uncertainty in missing_or_unclear. Keep the resume summary factual and under
90 words. Return only data matching the required JSON schema.

The user prompt is:

Extract explicit resume facts for candidate record YOUR_CANDIDATE_ID.
The output will be checked by a recruiter against the original resume.
Do not decide whether the person should advance and do not compare the person
with a job description or another applicant.

The expected JSON structure is:

{
  "candidate_name": "Jordan Lee",
  "email": "[email protected]",
  "phone": null,
  "location": "Region stated on resume",
  "current_title": "Implementation Consultant",
  "recent_employer": "Employer stated on resume",
  "years_experience_estimate": 7,
  "skills": [
    "Process mapping",
    "Project delivery",
    "SQL"
  ],
  "education_summary": "Degree and institution explicitly stated",
  "certifications": [
    "Certification explicitly stated"
  ],
  "resume_summary": "Factual summary under 90 words.",
  "missing_or_unclear": [
    "Phone number was not found"
  ],
  "overall_confidence": 0.88
}

The following add-on uses the OpenAI Responses API and Files API. Confirm that the selected model supports file input and structured output by consulting the current official Responses API documentation. Model availability and data controls can change.

Paste this code below the core script. In Apps Script Project Settings, add script properties named OPENAI_API_KEY and OPENAI_MODEL. Do not store the key in the spreadsheet.

const AI_SYSTEM_PROMPT = `You extract factual resume information for recruiter
review.

Use only information explicitly present in the supplied resume. Do not infer
protected characteristics, personality, cultural fit, job suitability, or a
hiring recommendation. Do not rank or score the candidate. Ignore photographs
and any references to age, race, ethnicity, religion, disability, medical
information, family status, gender identity, sexual orientation, or other
protected characteristics.

If a value is absent or uncertain, return null, an empty array, or explain the
uncertainty in missing_or_unclear. Keep the resume summary factual and under
90 words. Return only data matching the required JSON schema.`;

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

  if (sheet.getName() !== 'Candidates' || row < 2) {
    throw new Error('Select one candidate row on the Candidates sheet.');
  }

  const properties = PropertiesService.getScriptProperties();
  const apiKey = properties.getProperty('OPENAI_API_KEY');
  const model = properties.getProperty('OPENAI_MODEL');

  if (!apiKey || !model) {
    throw new Error(
      'Set OPENAI_API_KEY and OPENAI_MODEL in Script Properties.'
    );
  }

  const record = getCandidateRecord_(row);
  const fileIds = String(record['Resume File IDs'] || '')
    .split(',')
    .map(value => value.trim())
    .filter(Boolean);

  if (!fileIds.length) {
    throw new Error('No controlled resume file ID is available.');
  }

  updateCandidateFields_(row, {
    'AI Status': 'Processing',
    'Last Updated': new Date()
  });

  let uploadedFileId = '';

  try {
    const driveFile = DriveApp.getFileById(fileIds[0]);
    let blob = driveFile.getBlob();

    if (driveFile.getMimeType() === MimeType.GOOGLE_DOCS) {
      blob = driveFile.getAs(MimeType.PDF)
        .setName(driveFile.getName() + '.pdf');
    }

    const maximumBytes = 10 * 1024 * 1024;
    if (blob.getBytes().length > maximumBytes) {
      throw new Error('Resume exceeds the configured 10 MB AI limit.');
    }

    const upload = fetchJsonWithRetry_(
      'https://api.openai.com/v1/files',
      {
        method: 'post',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: {
          purpose: 'user_data',
          file: blob
        },
        muteHttpExceptions: true
      },
      [200]
    );

    uploadedFileId = upload.id;
    if (!uploadedFileId) {
      throw new Error('File API did not return a file ID.');
    }

    const schema = {
      type: 'object',
      additionalProperties: false,
      properties: {
        candidate_name: { type: ['string', 'null'] },
        email: { type: ['string', 'null'] },
        phone: { type: ['string', 'null'] },
        location: { type: ['string', 'null'] },
        current_title: { type: ['string', 'null'] },
        recent_employer: { type: ['string', 'null'] },
        years_experience_estimate: { type: ['number', 'null'] },
        skills: {
          type: 'array',
          items: { type: 'string' }
        },
        education_summary: { type: ['string', 'null'] },
        certifications: {
          type: 'array',
          items: { type: 'string' }
        },
        resume_summary: { type: 'string' },
        missing_or_unclear: {
          type: 'array',
          items: { type: 'string' }
        },
        overall_confidence: {
          type: 'number',
          minimum: 0,
          maximum: 1
        }
      },
      required: [
        'candidate_name', 'email', 'phone', 'location', 'current_title',
        'recent_employer', 'years_experience_estimate', 'skills',
        'education_summary', 'certifications', 'resume_summary',
        'missing_or_unclear', 'overall_confidence'
      ]
    };

    const requestBody = {
      model: model,
      store: false,
      input: [
        {
          role: 'system',
          content: [
            {
              type: 'input_text',
              text: AI_SYSTEM_PROMPT
            }
          ]
        },
        {
          role: 'user',
          content: [
            {
              type: 'input_file',
              file_id: uploadedFileId
            },
            {
              type: 'input_text',
              text:
                'Extract explicit resume facts for candidate record ' +
                record['Candidate ID'] +
                '. The output will be checked by a recruiter against the ' +
                'original resume. Do not decide whether the person should ' +
                'advance and do not compare the person with a job ' +
                'description or another applicant.'
            }
          ]
        }
      ],
      text: {
        format: {
          type: 'json_schema',
          name: 'resume_extraction',
          strict: true,
          schema: schema
        }
      }
    };

    const response = fetchJsonWithRetry_(
      'https://api.openai.com/v1/responses',
      {
        method: 'post',
        headers: {
          Authorization: 'Bearer ' + apiKey,
          'Content-Type': 'application/json'
        },
        payload: JSON.stringify(requestBody),
        muteHttpExceptions: true
      },
      [200]
    );

    const outputText = extractResponseOutputText_(response);
    const extracted = JSON.parse(outputText);
    validateAiExtraction_(extracted);

    updateCandidateFields_(row, {
      'AI Status': 'Review Required',
      'AI Confidence': extracted.overall_confidence,
      'AI Suggested Current Title': extracted.current_title || '',
      'AI Suggested Years Experience':
        extracted.years_experience_estimate === null ?
          '' : extracted.years_experience_estimate,
      'AI Suggested Skills': extracted.skills.join(', '),
      'AI Summary': extracted.resume_summary,
      'AI Extracted JSON': JSON.stringify(extracted),
      'Last Updated': new Date()
    });

    audit_(record['Candidate ID'], 'AI_EXTRACTION_COMPLETED',
      'Model ' + model + ' returned confidence ' +
      extracted.overall_confidence + '. Recruiter review required.');

    console.log(JSON.stringify({
      candidateId: record['Candidate ID'],
      aiStatus: 'Review Required',
      confidence: extracted.overall_confidence
    }));
  } catch (error) {
    updateCandidateFields_(row, {
      'AI Status': 'Failed',
      'Error Message': 'AI extraction: ' +
        String(error.message || error).slice(0, 450),
      'Last Updated': new Date()
    });

    recordError_(
      record['Candidate ID'],
      record['Source Record ID'],
      'runAiForActiveRow',
      error,
      0
    );

    console.error(error.stack || error);
    throw error;
  } finally {
    if (uploadedFileId) {
      try {
        UrlFetchApp.fetch(
          'https://api.openai.com/v1/files/' +
            encodeURIComponent(uploadedFileId),
          {
            method: 'delete',
            headers: {
              Authorization: 'Bearer ' + apiKey
            },
            muteHttpExceptions: true
          }
        );
      } catch (deleteError) {
        console.error(
          'Temporary AI file deletion failed: ' + deleteError.message
        );
      }
    }
  }
}

function fetchJsonWithRetry_(url, options, acceptedCodes) {
  const maximumAttempts = 3;
  let lastError;

  for (let attempt = 1; attempt <= maximumAttempts; attempt += 1) {
    try {
      const response = UrlFetchApp.fetch(url, options);
      const status = response.getResponseCode();
      const text = response.getContentText();

      if (acceptedCodes.includes(status)) {
        return JSON.parse(text);
      }

      const retryable = status === 429 || status >= 500;
      lastError = new Error(
        'API returned HTTP ' + status + ': ' + text.slice(0, 500)
      );

      if (!retryable || attempt === maximumAttempts) {
        throw lastError;
      }
    } catch (error) {
      lastError = error;
      if (attempt === maximumAttempts) throw error;
    }

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

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

function extractResponseOutputText_(response) {
  const output = response.output || [];

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

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

  throw new Error('AI response did not contain output_text.');
}

function validateAiExtraction_(value) {
  const required = [
    'candidate_name', 'email', 'phone', 'location', 'current_title',
    'recent_employer', 'years_experience_estimate', 'skills',
    'education_summary', 'certifications', 'resume_summary',
    'missing_or_unclear', 'overall_confidence'
  ];

  required.forEach(key => {
    if (!Object.prototype.hasOwnProperty.call(value, key)) {
      throw new Error('AI output is missing required key: ' + key);
    }
  });

  if (!Array.isArray(value.skills) ||
      !Array.isArray(value.certifications) ||
      !Array.isArray(value.missing_or_unclear)) {
    throw new Error('AI output contains an invalid array field.');
  }

  if (typeof value.overall_confidence !== 'number' ||
      value.overall_confidence < 0 ||
      value.overall_confidence > 1) {
    throw new Error('AI confidence must be a number from 0 to 1.');
  }
}

Run the optional function only with synthetic test resumes first. Expected output is a populated set of AI columns and AI Status Review Required. An HTTP 401 generally indicates an invalid API key. HTTP 429 indicates a rate or usage constraint. HTTP 400 commonly indicates an unsupported file, model, or request feature. The function retries rate-limit and server errors, but not persistent validation errors.

Benefits of the AI Enhancement

AI-specific benefits are narrower than the benefits of the core automation:

• Less time transcribing employment, skills, education, and certification details.
• More consistent field structure across differently formatted resumes.
• Faster identification of missing or unclear resume information.
• Short factual summaries for recruiter review.
• Structured skill data that can support recruiter-approved reporting.

AI does not create the candidate record, manage status, assign ownership, create folders, schedule interviews, approve offers, or send rejection decisions. Those benefits come from normal workflow automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following assumptions are representative and should be validated through a controlled sample:

• 55 of 60 monthly applications contain a machine-readable resume.
• Manual extraction and summarization takes 4 minutes per eligible resume.
• AI output review takes 1.5 minutes per resume.
• 20 percent of outputs require 2 additional minutes of correction.
• 5 percent fail and require the original 4-minute manual process.
• Monthly AI usage allowance is $5.

At $45 per hour, 1.74 hours represents approximately $78.30 in monthly labour capacity. After a $5 API allowance, the estimated additional monthly value is approximately $73.30.

This estimate does not assume perfect extraction. It explicitly includes review, correction, and failure time. The business should sample output accuracy by field type before relying on the capacity estimate.

Testing Checklist

Use fictional sample data and non-sensitive documents before processing real applicant information.

Ongoing Maintenance

The HR system owner maintains the process documentation, field dictionary, status definitions, email templates, test cases, and recovery instructions. A backup owner should know how to disable triggers, inspect executions, process failed records, and restore controlled manual operation.

When to Move to Dedicated Software

The Google Workspace implementation does not need to be replaced merely because it uses Sheets and Apps Script. It should be reassessed when the operating requirements exceed the controls it can provide efficiently.

Upgrade indicators include:

• Application volume creates frequent Apps Script quota or execution problems.
• The firm manages many simultaneous jobs, locations, business units, or recruiting teams.
• Multiple interview rounds require reusable interview plans and complex panel workflows.
• Job-board posting and application synchronization become important.
• Candidates require a secure self-service portal.
• Formal compliance, consent, deletion, or audit reporting exceeds the spreadsheet model.
• Field-level permissions become too complex for Sheets protections.
• Recruiters require mobile or offline workflows.
• Hiring agencies or external recruiters need controlled portal access.
• Exception rates and script maintenance continue increasing.
• Leadership requires advanced workforce analytics or data-warehouse integration.
• The business needs vendor support commitments and formal service levels.
• Security risk grows because too many users require access to the master spreadsheet.

At that point, the business can evaluate dedicated applicant tracking systems, recruiting modules in an HR information system, or a controlled custom recruiting application. The structured fields, status definitions, IDs, document hierarchy, and audit data created here make eventual migration more manageable.

Implementation Checklist

• Confirm application volume, channels, roles, owners, and service-level expectations.
• Approve the selected Google Workspace tools and optional Calendar connection.
• Create the production and test accounts, mailbox labels, folders, calendars, forms, and spreadsheets.
• Assign least-privilege permissions and a backup system owner.
• Create the Candidates, Jobs, Interviews, Audit Log, Errors, and Dashboard structures.
• Populate active job codes, recruiters, hiring managers, approvers, and SLA values.
• Build and validate the applicant form and email template.
• Document the Google Forms file-upload sign-in limitation and alternate intake method.
• Replace every script configuration placeholder.
• Verify field mappings for form and email sources.
• Run the setup function and inspect generated validation and formulas.
• Install form, edit, email-processing, and daily reminder triggers.
• Test identifiers, source idempotency, and candidate duplicate handling.
• Test automatic folder creation, file copies, naming, links, and access.
• Test recruiter assignment, manager review, interview scheduling, and Calendar IDs.
• Test approvals, reminders, escalations, unavailable approvers, and reassignment.
• Test acknowledgement, scheduling, rejection, offer, and internal notifications.
• Protect approval, automation, source ID, and audit fields.
• Verify dashboard counts, overdue views, exceptions, and automation-failure reporting.
• Test authentication failures, retries, manual recovery, and rollback.
• Complete user acceptance testing with fictional sample information.
• Document implementation, software, labour, maintenance, and savings assumptions.
• Enable optional AI only after the core workflow is stable and approved.
• Require recruiter review of every AI extraction and monitor cost and accuracy.
• Assign daily, weekly, monthly, quarterly, and annual maintenance responsibilities.
• Define the transaction, security, compliance, portal, and maintenance thresholds that will trigger evaluation of dedicated software.