# Code

Copy and past the following code into your appscript. 

You can review the documentation for all the [codes functions here](code_functions.md). 

```
// Code.gs - Gmail to Google Sheets Export Script with Real-time Output

/**
 * =====================================
 * CONFIGURATION SECTION
 * =====================================
 * Modify these variables to customize the script behavior
 */

// Email Label Configuration
const LABEL_NAME = "ocm_autopull"; // Gmail label to process

// Processing Configuration  
const BATCH_SIZE = 10; // Number of emails to write to sheet immediately (smaller for real-time output)
const MAX_EXECUTION_TIME = 5 * 60 * 1000; // Maximum script run time (5 minutes)

// Spreadsheet Configuration
const SPREADSHEET_NAME_PREFIX = "OCM_Contact_Export"; // Prefix for generated spreadsheet name
const MAIN_SHEET_NAME = "OCM Contact Export"; // Name of the main data sheet
const ERROR_SHEET_NAME = "Errors"; // Name of the error log sheet
const LOG_SHEET_NAME = "Processing Log"; // Name of the processing log sheet

// Generate unique sheet names with timestamp to avoid conflicts
function getUniqueSheetNames() {
  var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd_HH-mm");
  return {
    mainSheet: MAIN_SHEET_NAME,
    errorSheet: `${ERROR_SHEET_NAME}_${timestamp}`,
    logSheet: `${LOG_SHEET_NAME}_${timestamp}`
  };
}

// Column Headers Configuration
const SHEET_HEADERS = [
  "Thread Number", "Confirmation Number", "Submission Date", "First Name", "Last Name", 
  "Email Address", "Phone Number", "Fax", "Organization", "Street Address", 
  "Street Address (cont.)", "City", "Province", "Country", "Postal Code", 
  "Full Address", "Subject", "Comments", "Gmail Date", "Gmail From", 
  "Gmail Subject", "Thread ID", "Email ID"
];

// Error Log Headers
const ERROR_HEADERS = ["Thread Number", "Error Type", "Error Details", "Email Date", "Email From", "Timestamp"];

// Processing Log Headers
const LOG_HEADERS = ["Timestamp", "Thread Number", "Action", "Details", "Status"];

// Validation Configuration - RELAXED FOR LABELED EMAILS
const REQUIRED_OCM_PHRASES = ["Contact Council web form"]; // Only require this phrase since all labeled emails should be OCM
const REPLY_INDICATORS = ["re:", "fwd:", "fw:"]; // Subject line indicators of replies/forwards
const SKIP_SENDER_DOMAINS = ["edmonton.ca"]; // Skip emails from these domains
const REQUIRED_FIELDS = []; // No required fields - process all labeled emails

// Address Construction Configuration
const EXCLUDE_COUNTRIES = ["canada"]; // Countries to exclude from full address (case insensitive)

// Progress Logging Configuration  
const PROGRESS_LOG_INTERVAL = 10; // Log progress every N threads
const TIME_CHECK_INTERVAL = 10; // Check execution time every N threads

// Resume Configuration
const RESUME_DELAY_MINUTES = 1; // Minutes to wait before auto-resuming

// Global variables for sheets (will be initialized in main function)
var dataSheet, errorSheet, logSheet;

/**
 * =====================================
 * MAIN EXPORT FUNCTION
 * =====================================
 */

/**
 * Main function to export emails by label to Google Sheets with real-time output
 */
function downloadEmailsByLabel() {
  var startTime = new Date().getTime();
  
  try {
    // Check if we're resuming from a checkpoint
    var checkpoint = getCheckpoint();
    var isResume = checkpoint && checkpoint.spreadsheetId;
    
    var spreadsheet, label, allThreads;
    var processedCount = 0;
    var startIndex = 0;
    var totalEmails = 0;
    
    if (isResume) {
      // Resume from checkpoint
      logMessage("RESUME", 0, "Resuming export from checkpoint", `Processed ${checkpoint.processedCount} threads`);
      spreadsheet = SpreadsheetApp.openById(checkpoint.spreadsheetId);
      
      // FIXED: Initialize sheets for resume (this will now preserve existing data)
      initializeSheets(spreadsheet, true); // Pass true to indicate this is a resume
      
      label = GmailApp.getUserLabelByName(LABEL_NAME);
      allThreads = getAllThreadsForLabel(label);
      processedCount = checkpoint.processedCount;
      startIndex = checkpoint.processedCount;
      totalEmails = checkpoint.totalEmailsFound || 0;
      
    } else {
      // Start fresh - Create spreadsheet and sheets immediately
      logMessage("START", 0, "Starting new export", "Creating spreadsheet and sheets");
      
      // Get the label first
      label = GmailApp.getUserLabelByName(LABEL_NAME);
      if (!label) {
        throw new Error(`Label "${LABEL_NAME}" not found. Please check the label name.`);
      }
      
      // Create spreadsheet with timestamp - ALWAYS NEW SPREADSHEET
      var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd_HH-mm-ss");
      var spreadsheetName = `${SPREADSHEET_NAME_PREFIX}_${timestamp}`;
      spreadsheet = SpreadsheetApp.create(spreadsheetName);
      
      // ✅ OUTPUT SPREADSHEET URL IMMEDIATELY
      var spreadsheetUrl = spreadsheet.getUrl();
      Logger.log("🔗 NEW SPREADSHEET CREATED!");
      Logger.log("📊 Spreadsheet Name: " + spreadsheet.getName());
      Logger.log("🌐 Spreadsheet URL: " + spreadsheetUrl);
      Logger.log("📅 Created: " + new Date().toString());
      Logger.log("=".repeat(80));
      
      // FIXED: Initialize all sheets (creates fresh sheets with unique names)
      initializeSheets(spreadsheet, false); // Pass false to indicate this is a fresh start
      
      logMessage("INIT", 0, "Spreadsheet created", `Name: ${spreadsheet.getName()}`);
      logMessage("INIT", 0, "Spreadsheet URL", spreadsheetUrl);
      
      // Get all threads
      logMessage("SCAN", 0, "Getting all threads for label", LABEL_NAME);
      allThreads = getAllThreadsForLabel(label);
      logMessage("SCAN", 0, "Thread scan complete", `Found ${allThreads.length} total threads`);
      
      // Save initial checkpoint
      saveCheckpoint({
        spreadsheetId: spreadsheet.getId(),
        labelName: LABEL_NAME,
        totalThreads: allThreads.length,
        processedCount: 0,
        totalEmailsFound: 0,
        startTime: startTime
      });
    }
    
    // Process threads starting from checkpoint
    for (var i = startIndex; i < allThreads.length; i++) {
      // Check execution time every 10 threads
      if (i % TIME_CHECK_INTERVAL === 0) {
        var currentTime = new Date().getTime();
        var elapsedTime = currentTime - startTime;
        
        if (elapsedTime > MAX_EXECUTION_TIME) {
          logMessage("CHECKPOINT", i, "Approaching time limit", `Saving checkpoint at thread ${i}`);
          
          // Save checkpoint and schedule continuation
          saveCheckpoint({
            spreadsheetId: spreadsheet.getId(),
            labelName: LABEL_NAME,
            totalThreads: allThreads.length,
            processedCount: i,
            totalEmailsFound: totalEmails,
            startTime: startTime
          });
          
          // Schedule next run
          scheduleResume();
          
          logMessage("CHECKPOINT", i, "Checkpoint saved", `Processed ${i}/${allThreads.length} threads, found ${totalEmails} emails. Next batch in 1 minute`);
          Logger.log(`Checkpoint saved. Processed ${i}/${allThreads.length} threads, found ${totalEmails} emails`);
          Logger.log(`Next batch will be triggered automatically in 1 minute`);
          return spreadsheet.getUrl();
        }
      }
      
      try {
        var thread = allThreads[i];
        var messages = thread.getMessages();
        var threadNumber = i + 1;
        
        logMessage("PROCESS", threadNumber, "Processing thread", `${messages.length} messages`);
        
        // Sort messages by date to ensure we get the original email
        messages.sort(function(a, b) {
          return a.getDate().getTime() - b.getDate().getTime();
        });
        
        // Only process the FIRST email (root email) in each thread
        if (messages.length > 0) {
          var rootMessage = messages[0];
          var rowData = parseOCMContactForm(rootMessage, thread, threadNumber);
          
          // Process ALL labeled emails (they should all be OCM forms)
          if (rowData) {
            // Write immediately to sheet (real-time output)
            writeRowToSheet(dataSheet, rowData);
            totalEmails++;
            
            logMessage("SUCCESS", threadNumber, "Email processed", `${rowData[3]} ${rowData[4]} - Confirmation: ${rowData[1]}`);
            
            // Update checkpoint periodically
            if (totalEmails % BATCH_SIZE === 0) {
              saveCheckpoint({
                spreadsheetId: spreadsheet.getId(),
                labelName: LABEL_NAME,
                totalThreads: allThreads.length,
                processedCount: i + 1,
                totalEmailsFound: totalEmails,
                startTime: startTime
              });
            }
          } else {
            logMessage("SKIP", threadNumber, "Email skipped", "Did not meet processing criteria");
          }
        } else {
          logMessage("SKIP", threadNumber, "Thread skipped", "No messages found");
        }
        
        processedCount = i + 1;
        
        // Log progress every configured interval
        if ((i + 1) % PROGRESS_LOG_INTERVAL === 0) {
          logMessage("PROGRESS", i + 1, "Progress update", `Processed ${i + 1}/${allThreads.length} threads, found ${totalEmails} emails`);
          Logger.log(`Processed ${i + 1}/${allThreads.length} threads, found ${totalEmails} emails`);
        }
        
      } catch (threadError) {
        var errorMsg = `Error processing thread ${i + 1}: ${threadError.toString()}`;
        Logger.log(errorMsg);
        logMessage("ERROR", i + 1, "Thread processing error", threadError.toString());
        logError(i + 1, "Thread Processing Error", threadError.toString(), "Unknown", "Unknown");
        continue;
      }
    }
    
    // Format the sheets
    formatSheet(dataSheet, dataSheet.getLastRow());
    
    // Clear checkpoint - we're done!
    clearCheckpoint();
    
    logMessage("COMPLETE", allThreads.length, "Export completed", `Processed ${totalEmails} emails from ${allThreads.length} threads`);
    Logger.log(`✅ EXPORT COMPLETE! Processed ${totalEmails} emails from ${allThreads.length} threads`);
    Logger.log(`Spreadsheet URL: ${spreadsheet.getUrl()}`);
    
    // Send completion notification
    GmailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      `✅ OCM Contact Form Export COMPLETE - ${LABEL_NAME}`,
      `OCM Contact form export completed successfully!\n\n` +
      `Label: ${LABEL_NAME}\n` +
      `Threads processed: ${allThreads.length}\n` +
      `Emails found: ${totalEmails}\n` +
      `Spreadsheet: ${spreadsheet.getName()}\n` +
      `URL: ${spreadsheet.getUrl()}\n\n` +
      `Your OCM contact form data is now fully parsed and organized!\n` +
      `Check the "Errors" and "Processing Log" sheets for details.`
    );
    
    return spreadsheet.getUrl();
    
  } catch (error) {
    var errorMsg = `❌ Error: ${error.toString()}`;
    Logger.log(errorMsg);
    
    if (typeof logMessage === 'function') {
      logMessage("FATAL", 0, "Fatal error occurred", error.toString());
    }
    
    // Send error notification
    GmailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      "❌ OCM Export Error",
      `An error occurred during export:\n\n${error.toString()}\n\nCheckpoint data preserved for resume.`
    );
    throw error;
  }
}

/**
 * FIXED: Initialize all sheets in the spreadsheet - Now preserves data when resuming
 * @param {Spreadsheet} spreadsheet - The spreadsheet object
 * @param {boolean} isResume - Whether this is a resume operation (true) or fresh start (false)
 */
function initializeSheets(spreadsheet, isResume) {
  try {
    if (isResume) {
      // RESUMING: Connect to existing sheets without clearing data
      Logger.log("🔄 Resuming - connecting to existing sheets...");
      
      // Find existing sheets by name patterns
      var sheets = spreadsheet.getSheets();
      dataSheet = null;
      errorSheet = null;
      logSheet = null;
      
      // Find the main data sheet
      for (var i = 0; i < sheets.length; i++) {
        var sheetName = sheets[i].getName();
        if (sheetName === MAIN_SHEET_NAME || sheetName.includes("OCM Contact Export")) {
          dataSheet = sheets[i];
          Logger.log(`📋 Found existing data sheet: "${sheetName}"`);
          break;
        }
      }
      
      // Find error and log sheets (look for most recent ones)
      for (var i = 0; i < sheets.length; i++) {
        var sheetName = sheets[i].getName();
        if (sheetName.includes("Errors") && !errorSheet) {
          errorSheet = sheets[i];
          Logger.log(`❌ Found existing error sheet: "${sheetName}"`);
        }
        if (sheetName.includes("Log") && !logSheet) {
          logSheet = sheets[i];
          Logger.log(`📝 Found existing log sheet: "${sheetName}"`);
        }
      }
      
      // If we can't find existing sheets, create new ones (fallback)
      if (!dataSheet) {
        Logger.log("⚠️  Data sheet not found, creating new one...");
        dataSheet = spreadsheet.getActiveSheet();
        if (dataSheet.getName() !== MAIN_SHEET_NAME) {
          dataSheet.setName(MAIN_SHEET_NAME);
        }
        // Only add headers if the sheet is empty
        if (dataSheet.getLastRow() === 0) {
          dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setValues([SHEET_HEADERS]);
          dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setFontWeight("bold");
          dataSheet.setFrozenRows(1);
        }
      }
      
      if (!errorSheet) {
        var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd_HH-mm");
        errorSheet = spreadsheet.insertSheet(`${ERROR_SHEET_NAME}_${timestamp}`);
        errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setValues([ERROR_HEADERS]);
        errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setFontWeight("bold");
        errorSheet.setFrozenRows(1);
      }
      
      if (!logSheet) {
        var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd_HH-mm");
        logSheet = spreadsheet.insertSheet(`${LOG_SHEET_NAME}_${timestamp}`);
        logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS]);
        logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setFontWeight("bold");
        logSheet.setFrozenRows(1);
      }
      
      Logger.log(`✅ Resume initialization complete! Data preserved.`);
      Logger.log(`   📊 Data rows: ${dataSheet.getLastRow() - 1}`);
      
    } else {
      // FRESH START: Create new sheets with unique names
      var sheetNames = getUniqueSheetNames();
      
      Logger.log("🔧 Fresh start - creating new sheets with unique names:");
      Logger.log(`   📋 Main: "${sheetNames.mainSheet}"`);
      Logger.log(`   ❌ Errors: "${sheetNames.errorSheet}"`);
      Logger.log(`   📝 Log: "${sheetNames.logSheet}"`);
      
      // Set up main data sheet (rename the default sheet)
      dataSheet = spreadsheet.getActiveSheet();
      dataSheet.setName(sheetNames.mainSheet);
      
      // Clear any existing content and set up headers for data sheet
      dataSheet.clear();
      dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setValues([SHEET_HEADERS]);
      dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setFontWeight("bold");
      dataSheet.setFrozenRows(1);
      
      // Create error sheet with unique name
      errorSheet = spreadsheet.insertSheet(sheetNames.errorSheet);
      errorSheet.clear();
      errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setValues([ERROR_HEADERS]);
      errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setFontWeight("bold");
      errorSheet.setFrozenRows(1);
      
      // Create log sheet with unique name
      logSheet = spreadsheet.insertSheet(sheetNames.logSheet);
      logSheet.clear();
      logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS]);
      logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setFontWeight("bold");
      logSheet.setFrozenRows(1);
      
      Logger.log(`✅ Fresh sheets created successfully!`);
    }
    
  } catch (error) {
    Logger.log(`❌ Error initializing sheets: ${error.toString()}`);
    Logger.log(`🔧 Attempting alternative sheet creation method...`);
    
    // Fallback: Try with even more unique names
    try {
      var fallbackTimestamp = new Date().getTime();
      
      if (!dataSheet) {
        dataSheet = spreadsheet.getActiveSheet();
        dataSheet.setName(`Data_${fallbackTimestamp}`);
        if (!isResume) {
          dataSheet.clear();
        }
        // Only add headers if the sheet is empty
        if (dataSheet.getLastRow() === 0) {
          dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setValues([SHEET_HEADERS]);
          dataSheet.getRange(1, 1, 1, SHEET_HEADERS.length).setFontWeight("bold");
          dataSheet.setFrozenRows(1);
        }
      }
      
      if (!errorSheet) {
        errorSheet = spreadsheet.insertSheet(`Errors_${fallbackTimestamp}`);
        errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setValues([ERROR_HEADERS]);
        errorSheet.getRange(1, 1, 1, ERROR_HEADERS.length).setFontWeight("bold");
        errorSheet.setFrozenRows(1);
      }
      
      if (!logSheet) {
        logSheet = spreadsheet.insertSheet(`Log_${fallbackTimestamp}`);
        logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS]);
        logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setFontWeight("bold");
        logSheet.setFrozenRows(1);
      }
      
      Logger.log(`✅ Fallback sheet creation successful!`);
      
    } catch (fallbackError) {
      Logger.log(`❌ Fallback also failed: ${fallbackError.toString()}`);
      throw fallbackError;
    }
  }
}

/**
 * Write a single row of data immediately to the sheet
 */
function writeRowToSheet(sheet, rowData) {
  try {
    var lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]);
  } catch (error) {
    Logger.log(`Error writing row to sheet: ${error.toString()}`);
    throw error;
  }
}

/**
 * Log a message to the processing log sheet
 */
function logMessage(action, threadNumber, message, details) {
  try {
    if (!logSheet) return; // Skip if log sheet not initialized yet
    
    var timestamp = new Date();
    var status = (action === "ERROR" || action === "FATAL") ? "ERROR" : "SUCCESS";
    
    var logData = [timestamp, threadNumber, action, message + (details ? ": " + details : ""), status];
    
    var lastRow = logSheet.getLastRow();
    logSheet.getRange(lastRow + 1, 1, 1, logData.length).setValues([logData]);
    
    // Also log to console for immediate feedback
    Logger.log(`${action} - Thread ${threadNumber}: ${message}${details ? " - " + details : ""}`);
    
  } catch (error) {
    Logger.log(`Error writing to log sheet: ${error.toString()}`);
  }
}

/**
 * Log an error to the error sheet
 */
function logError(threadNumber, errorType, errorDetails, emailDate, emailFrom) {
  try {
    if (!errorSheet) return; // Skip if error sheet not initialized yet
    
    var timestamp = new Date();
    var errorData = [threadNumber, errorType, errorDetails, emailDate, emailFrom, timestamp];
    
    var lastRow = errorSheet.getLastRow();
    errorSheet.getRange(lastRow + 1, 1, 1, errorData.length).setValues([errorData]);
    
  } catch (error) {
    Logger.log(`Error writing to error sheet: ${error.toString()}`);
  }
}

/**
 * Gets ALL threads for a label, handling Gmail's pagination limits
 */
function getAllThreadsForLabel(label) {
  var allThreads = [];
  var start = 0;
  var maxResults = 500; // Gmail's max per request
  
  do {
    var threads = label.getThreads(start, maxResults);
    allThreads = allThreads.concat(threads);
    start += threads.length;
    
    logMessage("SCAN", 0, "Retrieved thread batch", `${threads.length} threads (total so far: ${allThreads.length})`);
    
    // Break if we got fewer than max results (end of results)
    if (threads.length < maxResults) {
      break;
    }
    
    // Brief pause between batches
    Utilities.sleep(200);
    
  } while (threads.length === maxResults);
  
  return allThreads;
}

/**
 * Parses OCM Contact form from email body and extracts structured data
 * Since all emails have the label, we'll process them all and extract what we can
 */
function parseOCMContactForm(message, thread, threadNumber) {
  try {
    var plainBody = message.getPlainBody() || "";
    var subject = (message.getSubject() || "").toLowerCase();
    var from = message.getFrom() || "";
    
    // Skip if it's clearly a reply or forward (basic filtering)
    var isReply = REPLY_INDICATORS.some(function(indicator) {
      return subject.includes(indicator);
    });
    
    var isFromSkippedDomain = SKIP_SENDER_DOMAINS.some(function(domain) {
      return from.includes(domain);
    });
    
    if (isReply || isFromSkippedDomain) {
      logMessage("SKIP", threadNumber, "Reply/forward detected", `Subject: ${subject}, From: ${from}`);
      return null; // Skip this email
    }
    
    // Initialize all fields with defaults
    var confirmationNumber = "";
    var submissionDate = "";
    var firstName = "";
    var lastName = "";
    var emailAddress = "";
    var phoneNumber = "";
    var fax = "";
    var organization = "";
    var streetAddress = "";
    var streetAddressCont = "";
    var city = "";
    var province = "";
    var country = "";
    var postalCode = "";
    var subjectField = "";
    var comments = "";
    
    // Parse each field using regex patterns - but don't fail if fields are missing
    var patterns = {
      confirmationNumber: /Confirmation Number:\s*([^\r\n]+?)(?:\s|$)/i,
      submissionDate: /Submission Date:\s*([^\r\n]+?)(?:\s*Contact Information|$)/i,
      firstName: /First Name:\s*([^\r\n]+?)(?:\s*Last Name|$)/i,
      lastName: /Last Name:\s*([^\r\n]+?)(?:\s*Email Address|$)/i,
      emailAddress: /Email Address:\s*([^\r\n<\s]+?)(?:<mailto:|Phone Number|\s|$)/i,
      phoneNumber: /Phone Number:\s*([^\r\n]+?)(?:\s*Fax|$)/i,
      fax: /Fax:\s*([^\r\n]*?)(?:\s*Organization|$)/i,
      organization: /Organization:\s*([^\r\n]*?)(?:\s*Street Address|$)/i,
      streetAddress: /Street Address:\s*([^\r\n]+?)(?:\s*Street Address \(cont\.\)|City:|$)/i,
      streetAddressCont: /Street Address \(cont\.\):\s*([^\r\n]*?)(?:\s*City:|$)/i,
      city: /City:\s*([^\r\n]+?)(?:\s*Province:|$)/i,
      province: /Province:\s*([^\r\n]+?)(?:\s*Country:|$)/i,
      country: /Country:\s*([^\r\n]+?)(?:\s*Postal Code:|$)/i,
      postalCode: /Postal Code:\s*([^\r\n]+?)(?:\s*Message|$)/i,
      subject: /Subject:\s*([^\r\n]+?)(?:\s*Comments:|$)/i,
      comments: /Comments:\s*([\s\S]*?)(?=The contents of this message|$)/i
    };
    
    // Extract each field - continue even if some fields are missing
    for (var field in patterns) {
      var match = plainBody.match(patterns[field]);
      if (match && match[1]) {
        var value = match[1].trim();
        
        // Clean up common parsing issues
        value = value.replace(/^[:\s]+|[:\s]+$/g, ''); // Remove leading/trailing colons and spaces
        value = value.replace(/^[>]+\s*/, ''); // Remove email quote markers
        value = value.replace(/\s+/g, ' '); // Normalize whitespace
        
        // Specific cleaning for email addresses
        if (field === 'emailAddress') {
          value = value.replace(/<mailto:.*?>/g, '').replace(/[<>]/g, '').trim();
        }
        
        // Specific cleaning for organization (often empty)
        if (field === 'organization' && (value === '' || value === ':' || value.length < 2)) {
          value = '';
        }
        
        // Specific cleaning for fax (often empty)
        if (field === 'fax' && (value === '' || value === ':' || value.length < 2)) {
          value = '';
        }
        
        // Clean up comments (remove extra whitespace and footer)
        if (field === 'comments') {
          value = value.replace(/The contents of this message.*$/i, '').trim();
          value = value.replace(/^[>\s]+/gm, '').trim(); // Remove quote markers
        }
        
        // Clean up street address continuation (often empty)
        if (field === 'streetAddressCont' && (value === '' || value === ':' || value.length < 2)) {
          value = '';
        }
        
        // Assign to variables
        switch(field) {
          case 'confirmationNumber': confirmationNumber = value; break;
          case 'submissionDate': submissionDate = value; break;
          case 'firstName': firstName = value; break;
          case 'lastName': lastName = value; break;
          case 'emailAddress': emailAddress = value; break;
          case 'phoneNumber': phoneNumber = value; break;
          case 'fax': fax = value; break;
          case 'organization': organization = value; break;
          case 'streetAddress': streetAddress = value; break;
          case 'streetAddressCont': streetAddressCont = value; break;
          case 'city': city = value; break;
          case 'province': province = value; break;
          case 'country': country = value; break;
          case 'postalCode': postalCode = value; break;
          case 'subject': subjectField = value; break;
          case 'comments': comments = value; break;
        }
      }
    }
    
    // Use placeholder values for missing essential data
    if (!firstName || firstName.trim() === '') {
      firstName = "Unknown";
    }
    if (!lastName || lastName.trim() === '') {
      lastName = "Unknown";
    }
    if (!confirmationNumber || confirmationNumber.trim() === '') {
      confirmationNumber = "Unknown";
    }
    
    // Construct full address
    var fullAddress = constructFullAddress(streetAddress, streetAddressCont, city, province, country, postalCode);
    
    return [
      threadNumber,
      confirmationNumber,
      submissionDate,
      firstName,
      lastName,
      emailAddress,
      phoneNumber,
      fax,
      organization,
      streetAddress,
      streetAddressCont,
      city,
      province,
      country,
      postalCode,
      fullAddress,
      subjectField,
      comments,
      message.getDate(),
      message.getFrom(),
      message.getSubject(),
      thread.getId(),
      message.getId()
    ];
    
  } catch (error) {
    logError(threadNumber, "Parsing Error", error.toString(), 
             message.getDate ? message.getDate() : "Unknown", 
             message.getFrom ? message.getFrom() : "Unknown");
    
    Logger.log(`Error parsing email for thread ${threadNumber}: ${error.toString()}`);
    return [
      threadNumber,
      "ERROR",
      "ERROR PARSING",
      "Unknown",
      "Unknown",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "Error: " + error.toString(),
      message.getDate ? message.getDate() : new Date(),
      message.getFrom ? message.getFrom() : "Unknown",
      message.getSubject ? message.getSubject() : "Unknown",
      thread.getId(),
      message.getId ? message.getId() : "Unknown"
    ];
  }
}

/**
 * Constructs a full address from individual components
 */
function constructFullAddress(street, streetCont, city, province, country, postal) {
  var addressParts = [];
  
  // Clean and validate each component first
  street = (street || "").trim();
  streetCont = (streetCont || "").trim();
  city = (city || "").trim();
  province = (province || "").trim();
  country = (country || "").trim();
  postal = (postal || "").trim();
  
  // Add street address (only if it doesn't already contain city info)
  if (street && street !== "" && !street.toLowerCase().includes("city:")) {
    addressParts.push(street);
  }
  
  // Add continuation of street address (only if different from main street and not empty)
  if (streetCont && streetCont !== "" && streetCont !== street && !streetCont.toLowerCase().includes("city:")) {
    addressParts.push(streetCont);
  }
  
  // Add city (clean up any "City:" prefix that might have snuck in)
  if (city && city !== "") {
    city = city.replace(/^City:\s*/i, '').trim();
    if (city !== "") {
      addressParts.push(city);
    }
  }
  
  // Add province (clean up any prefixes)
  if (province && province !== "") {
    province = province.replace(/^Province:\s*/i, '').trim();
    if (province !== "") {
      addressParts.push(province);
    }
  }
  
  // Add postal code (clean up any prefixes)
  if (postal && postal !== "") {
    postal = postal.replace(/^Postal Code:\s*/i, '').trim();
    if (postal !== "") {
      addressParts.push(postal);
    }
  }
  
  // Add country only if it's not in the exclude list and not empty
  if (country && country !== "") {
    country = country.replace(/^Country:\s*/i, '').trim();
    var isExcludedCountry = EXCLUDE_COUNTRIES.some(function(excludedCountry) {
      return country.toLowerCase() === excludedCountry.toLowerCase();
    });
    
    if (country !== "" && !isExcludedCountry) {
      addressParts.push(country);
    }
  }
  
  return addressParts.join(", ");
}

/**
 * Formats the spreadsheet for better readability
 */
function formatSheet(sheet, totalRows) {
  try {
    // Auto-resize columns
    sheet.autoResizeColumns(1, sheet.getLastColumn());
    
    // Set column widths for OCM data
    sheet.setColumnWidth(1, 80);  // Thread Number
    sheet.setColumnWidth(2, 120); // Confirmation Number
    sheet.setColumnWidth(3, 150); // Submission Date
    sheet.setColumnWidth(4, 100); // First Name
    sheet.setColumnWidth(5, 100); // Last Name
    sheet.setColumnWidth(6, 200); // Email Address
    sheet.setColumnWidth(7, 120); // Phone Number
    sheet.setColumnWidth(8, 80);  // Fax
    sheet.setColumnWidth(9, 150); // Organization
    sheet.setColumnWidth(10, 200); // Street Address
    sheet.setColumnWidth(11, 150); // Street Address (cont.)
    sheet.setColumnWidth(12, 100); // City
    sheet.setColumnWidth(13, 100); // Province
    sheet.setColumnWidth(14, 100); // Country
    sheet.setColumnWidth(15, 100); // Postal Code
    sheet.setColumnWidth(16, 300); // Full Address
    sheet.setColumnWidth(17, 200); // Subject
    sheet.setColumnWidth(18, 400); // Comments
    
    // Add alternating row colors
    if (totalRows > 1) {
      var range = sheet.getRange(2, 1, totalRows - 1, sheet.getLastColumn());
      range.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);
    }
    
    // Format date columns
    if (totalRows > 1) {
      // Submission Date (column 3)
      var submissionDateRange = sheet.getRange(2, 3, totalRows - 1, 1);
      submissionDateRange.setNumberFormat("yyyy-mm-dd hh:mm:ss");
      
      // Gmail Date (column 19)
      var gmailDateRange = sheet.getRange(2, 19, totalRows - 1, 1);
      gmailDateRange.setNumberFormat("yyyy-mm-dd hh:mm:ss");
    }
    
  } catch (error) {
    Logger.log(`Error formatting sheet: ${error.toString()}`);
  }
}

/**
 * =====================================
 * TEST FUNCTIONS
 * =====================================
 */

/**
 * Test function to check label and get count - RUN THIS FIRST
 */
function testLabelCount() {
  try {
    Logger.log("=".repeat(50));
    Logger.log("GMAIL EXPORT READINESS TEST");
    Logger.log("=".repeat(50));
    
    // Test 1: Check if label exists
    Logger.log("Test 1: Checking if label exists...");
    var label = GmailApp.getUserLabelByName(LABEL_NAME);
    if (!label) {
      Logger.log(`❌ FAILED: Label "${LABEL_NAME}" not found.`);
      Logger.log("\nAvailable labels:");
      var labels = GmailApp.getUserLabels();
      for (var i = 0; i < labels.length; i++) {
        Logger.log(`- ${labels[i].getName()}`);
      }
      return false;
    }
    Logger.log(`✅ PASSED: Label "${LABEL_NAME}" found successfully`);
    
    // Test 2: Get thread count
    Logger.log("\nTest 2: Getting thread count...");
    var allThreads = getAllThreadsForLabel(label);
    Logger.log(`✅ PASSED: Found ${allThreads.length} threads with label "${LABEL_NAME}"`);
    
    if (allThreads.length === 0) {
      Logger.log("⚠️  WARNING: No threads found with this label. Nothing to export.");
      return false;
    }
    
    // Test 3: Sample message analysis
    Logger.log("\nTest 3: Analyzing sample messages...");
    var sampleSize = Math.min(5, allThreads.length);
    var totalSampleMessages = 0;
    var threadSizes = [];
    
    for (var i = 0; i < sampleSize; i++) {
      var messages = allThreads[i].getMessages();
      totalSampleMessages += messages.length;
      threadSizes.push(messages.length);
    }
    
    var avgMessagesPerThread = totalSampleMessages / sampleSize;
    var maxMessages = Math.max.apply(Math, threadSizes);
    var minMessages = Math.min.apply(Math, threadSizes);
    
    Logger.log(`✅ PASSED: Analyzed ${sampleSize} sample threads`);
    Logger.log(`   - Average messages per thread: ${avgMessagesPerThread.toFixed(1)}`);
    Logger.log(`   - Largest thread: ${maxMessages} messages`);
    Logger.log(`   - Smallest thread: ${minMessages} messages`);
    
    // Test 4: Sample parsing
    Logger.log("\nTest 4: Testing email parsing...");
    var parseableCount = 0;
    for (var i = 0; i < Math.min(3, allThreads.length); i++) {
      var thread = allThreads[i];
      var messages = thread.getMessages();
      if (messages.length > 0) {
        var rootMessage = messages[0];
        var testData = parseOCMContactForm(rootMessage, thread, i + 1);
        if (testData) {
          parseableCount++;
          Logger.log(`   - Thread ${i + 1}: Successfully parsed data`);
        }
      }
    }
    Logger.log(`✅ PASSED: Successfully parsed ${parseableCount} sample emails`);
    
    // Final summary
    Logger.log("\n" + "=".repeat(50));
    Logger.log("READINESS TEST SUMMARY");
    Logger.log("=".repeat(50));
    Logger.log(`Label: ${LABEL_NAME}`);
    Logger.log(`Threads: ${allThreads.length}`);
    Logger.log(`Sample parsing success: ${parseableCount}/${Math.min(3, allThreads.length)}`);
    Logger.log("Status: ✅ READY FOR FULL EXPORT");
    Logger.log("\nTo proceed with full export, run: downloadEmailsByLabel()");
    
    return true;
    
  } catch (error) {
    Logger.log(`❌ CRITICAL ERROR: ${error.toString()}`);
    return false;
  }
}

/**
 * Quick test with just 3 submissions to verify parsing works with real-time output - RUN THIS SECOND
 */
function testSmallSample() {
  const TEST_LIMIT = 3; // Only process 3 submissions for testing
  
  try {
    Logger.log("Starting email parsing test with real-time output...");
    
    var label = GmailApp.getUserLabelByName(LABEL_NAME);
    if (!label) {
      throw new Error(`Label "${LABEL_NAME}" not found`);
    }
    
    // Create test spreadsheet with all sheets
    var spreadsheetName = `TEST_${SPREADSHEET_NAME_PREFIX}_${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd_HH-mm-ss")}`;
    var spreadsheet = SpreadsheetApp.create(spreadsheetName);
    
    // Initialize all sheets
    initializeSheets(spreadsheet, false); // Fresh start for test
    
    logMessage("TEST", 0, "Test started", `Testing with up to ${TEST_LIMIT} emails`);
    
    // Get first few threads
    var threads = label.getThreads(0, 10); // Get first 10 threads
    var submissionCount = 0;
    
    for (var i = 0; i < threads.length && submissionCount < TEST_LIMIT; i++) {
      var thread = threads[i];
      var messages = thread.getMessages();
      var threadNumber = i + 1;
      
      logMessage("TEST", threadNumber, "Testing thread", `${messages.length} messages`);
      
      // Sort messages by date to get the root email
      messages.sort(function(a, b) {
        return a.getDate().getTime() - b.getDate().getTime();
      });
      
      if (messages.length > 0) {
        var rootMessage = messages[0];
        var rowData = parseOCMContactForm(rootMessage, thread, threadNumber);
        
        // Process the email regardless (since it's labeled)
        if (rowData) {
          writeRowToSheet(dataSheet, rowData);
          submissionCount++;
          
          logMessage("TEST", threadNumber, "Test email processed", `${rowData[3]} ${rowData[4]} - ${rowData[1]}`);
          Logger.log(`✅ Found labeled email ${submissionCount}: ${rowData[3]} ${rowData[4]} - ${rowData[1]}`);
        }
      }
    }
    
    if (submissionCount === 0) {
      logMessage("TEST", 0, "Test completed", "No emails found in first 10 threads");
      Logger.log("⚠️  No emails found in the first 10 threads. Check your label.");
      return null;
    }
    
    // Format the test sheet
    formatSheet(dataSheet, dataSheet.getLastRow());
    
    logMessage("TEST", 0, "Test completed successfully", `Processed ${submissionCount} emails`);
    Logger.log(`✅ Test completed successfully!`);
    Logger.log(`Found and parsed ${submissionCount} labeled emails`);
    Logger.log(`Test spreadsheet: ${spreadsheet.getUrl()}`);
    Logger.log(`\nReview the parsed data in all sheets. If it looks good, run downloadEmailsByLabel() for full export.`);
    
    return spreadsheet.getUrl();
    
  } catch (error) {
    Logger.log(`❌ Test failed: ${error.toString()}`);
    throw error;
  }
}

/**
 * Checkpoint and resume functions
 */
function saveCheckpoint(checkpointData) {
  try {
    PropertiesService.getScriptProperties().setProperty('OCM_EXPORT_CHECKPOINT', JSON.stringify(checkpointData));
    Logger.log('Checkpoint saved successfully');
  } catch (error) {
    Logger.log('Error saving checkpoint: ' + error.toString());
  }
}

function getCheckpoint() {
  try {
    var checkpointString = PropertiesService.getScriptProperties().getProperty('OCM_EXPORT_CHECKPOINT');
    if (checkpointString) {
      return JSON.parse(checkpointString);
    }
  } catch (error) {
    Logger.log('Error loading checkpoint: ' + error.toString());
  }
  return null;
}

function clearCheckpoint() {
  try {
    PropertiesService.getScriptProperties().deleteProperty('OCM_EXPORT_CHECKPOINT');
    Logger.log('Checkpoint cleared');
  } catch (error) {
    Logger.log('Error clearing checkpoint: ' + error.toString());
  }
}

/**
 * Schedule the next run using a time-based trigger
 */
function scheduleResume() {
  try {
    // Delete any existing resume triggers first
    var triggers = ScriptApp.getProjectTriggers();
    for (var i = 0; i < triggers.length; i++) {
      if (triggers[i].getHandlerFunction() === 'resumeExport') {
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    
    // Create new trigger to run after configured delay
    ScriptApp.newTrigger('resumeExport')
      .timeBased()
      .after(RESUME_DELAY_MINUTES * 60 * 1000) // Convert minutes to milliseconds
      .create();
      
    Logger.log(`Resume trigger scheduled for ${RESUME_DELAY_MINUTES} minute(s)`);
  } catch (error) {
    Logger.log('Error scheduling resume: ' + error.toString());
  }
}

/**
 * Resume function called by trigger
 */
function resumeExport() {
  Logger.log('Resuming OCM export from checkpoint...');
  
  // Clean up the trigger that called this function
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'resumeExport') {
      ScriptApp.deleteTrigger(triggers[i]);
      break;
    }
  }
  
  // Continue the export
  downloadEmailsByLabel();
}

/**
 * Manual function to check checkpoint status
 */
function checkCheckpointStatus() {
  var checkpoint = getCheckpoint();
  if (checkpoint) {
    Logger.log('=== CHECKPOINT STATUS ===');
    Logger.log(`Spreadsheet ID: ${checkpoint.spreadsheetId}`);
    Logger.log(`Label: ${checkpoint.labelName}`);
    Logger.log(`Progress: ${checkpoint.processedCount}/${checkpoint.totalThreads} threads`);
    Logger.log(`Emails found: ${checkpoint.totalEmailsFound || 'Unknown'}`);
    Logger.log(`Started: ${new Date(checkpoint.startTime)}`);
    
    try {
      var spreadsheet = SpreadsheetApp.openById(checkpoint.spreadsheetId);
      Logger.log(`Spreadsheet URL: ${spreadsheet.getUrl()}`);
    } catch (e) {
      Logger.log(`Error accessing spreadsheet: ${e.toString()}`);
    }
    
    Logger.log(`\nTo resume manually, run: downloadEmailsByLabel()`);
    Logger.log(`To clear checkpoint, run: clearCheckpoint()`);
  } else {
    Logger.log('No checkpoint found. No export in progress.');
  }
}

/**
 * Emergency function to manually clear stuck checkpoints
 */
function clearCheckpointAndTriggers() {
  // Clear checkpoint
  clearCheckpoint();
  
  // Delete all resume triggers
  var triggers = ScriptApp.getProjectTriggers();
  var deletedCount = 0;
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'resumeExport') {
      ScriptApp.deleteTrigger(triggers[i]);
      deletedCount++;
    }
  }
  
  Logger.log(`Cleared checkpoint and deleted ${deletedCount} resume triggers`);
  Logger.log('You can now start a fresh export with downloadEmailsByLabel()');
}

/**
 * Utility function to get the current spreadsheet URL if one exists
 */
function getCurrentSpreadsheetUrl() {
  var checkpoint = getCheckpoint();
  if (checkpoint && checkpoint.spreadsheetId) {
    try {
      var spreadsheet = SpreadsheetApp.openById(checkpoint.spreadsheetId);
      Logger.log(`Current spreadsheet URL: ${spreadsheet.getUrl()}`);
      return spreadsheet.getUrl();
    } catch (e) {
      Logger.log(`Error accessing current spreadsheet: ${e.toString()}`);
    }
  } else {
    Logger.log('No active export found.');
  }
  return null;
}
```