Automate Contact Data Enrichment

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude

// Enhanced Lusha Enrichment Script with Contact Validation Fixes
// Google Apps Script for Lusha Contact Enrichment

// Function to create the custom menu when the spreadsheet opens
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Lusha Enrichment')
    .addItem('Enrich All Contacts', 'enrichAllContacts')
    .addItem('Enrich From Specific Row', 'enrichFromSpecificRow')
    .addToUi();
    
  // Set up just the status row and input headers - NOT the output headers
  setupInitialStructure();
}

// Function to set up the initial spreadsheet structure
function setupInitialStructure() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set up status row (row 1)
  setupStatusRow(sheet);
  
  // Set up input headers (row 2)
  setupInputHeaders(sheet);
  
  // Format the sheet for better usability
  sheet.setFrozenRows(2);  // Freeze both header rows
}

// Function to set up the status row (row 1)
function setupStatusRow(sheet) {
  // Check if status row already exists
  const hasStatusLabel = sheet.getRange("A1").getValue() === "Enrichment Status";
  
  if (!hasStatusLabel) {
    // Set up simplified status area in row 1
    sheet.getRange("A1").setValue("Enrichment Status");
    sheet.getRange("A1").setFontWeight("bold");
    
    sheet.getRange("C1").setValue("Last Updated:");
    sheet.getRange("C1").setFontWeight("bold");
    
    // Move final stats to start from column G
    sheet.getRange("G1").setValue("Final Stats:");
    sheet.getRange("G1").setFontWeight("bold");
    
    // Set placeholder values
    sheet.getRange("B1").setValue("Not started");
    sheet.getRange("D1").setValue("-");
    
    // Set placeholder values for final stats starting at column H
    sheet.getRange("H1").setValue("Success: -");
    sheet.getRange("I1").setValue("No Data: -");
    sheet.getRange("J1").setValue("Failed: -");
    
    // Format the status row
    sheet.getRange("A1:J1").setBackground("#f3f3f3");
  }
}

// Function to set up input headers in row 2 (columns A-F)
function setupInputHeaders(sheet) {
  const inputHeaders = [
    "First Name (Input)", 
    "Last Name (Input)", 
    "Company Name (Input)", 
    "Company Domain (Input)", 
    "Email Address (Input)", 
    "LinkedIn URL (Input)"
  ];
  
  // Check if headers already exist in row 2
  const existingHeaders = sheet.getRange("A2:F2").getValues()[0];
  const hasHeaders = existingHeaders.some(header => header !== "");
  
  // If no headers exist, add them to row 2
  if (!hasHeaders) {
    sheet.getRange("A2:F2").setValues([inputHeaders]);
    sheet.getRange("A2:F2").setFontWeight("bold");
    sheet.getRange("A2:F2").setBackground("#f3f3f3");
    
    // Auto-resize columns for better visibility
    sheet.autoResizeColumns(1, 6);
  }
}

// Function to set up output headers in row 2 (starting at column G)
function setupOutputHeaders(sheet) {
  const outputHeaders = [
    "Status", "Is Credit Charged", "Person ID", "First Name", "Last Name", "Full Name", 
    "Email 1", "Email Type 1", "Email Confidence 1", 
    "Email 2", "Email Type 2", "Email Confidence 2",
    "Phone Number 1", "Phone Type 1", "Do Not Call 1", 
    "Phone Number 2", "Phone Type 2", "Do Not Call 2",
    "Contact Tags", 
    "Contact Location Country", "Contact Location Country ISO2", 
    "Contact Location Continent", "Contact Location City", 
    "Contact Location State", "Contact Location State Code",
    "Contact Location Coordinates (Latitude)", "Contact Location Coordinates (Longitude)", 
    "Is EU Contact", "Job Title", "Job Departments", 
    "Seniority Level", "LinkedIn Profile", 
    "Company Name", "Company Description", "Company Homepage",
    "Company Location", "Company Location City", "Company Location State", 
    "Company Location State Code", "Company Location Country", 
    "Company Location Country ISO2", "Company Location Continent", 
    "Company Location Coordinates (Latitude)", "Company Location Coordinates (Longitude)", 
    "Company Size Min", "Company Size Max", 
    "Revenue Range Min", "Revenue Range Max", 
    "Company Logo URL", "Company LinkedIn", "Company Crunchbase", 
    "Technologies", "Funding Rounds (String)", "Total Funding Rounds", 
    "Total Funding Amount", "Is IPO", "Last Funding Type", 
    "Last Funding Amount", "Last Funding Date", 
    "Intent Topics (String)", "Specialities", 
    "Previous Job Title", "Previous Company Name", "Previous Company Domain"
  ];
  
  // Check if headers already exist and are correct
  const existingHeaders = sheet.getRange(2, 7, 1, outputHeaders.length).getValues()[0];
  if (existingHeaders.some((header, i) => header !== outputHeaders[i])) {
    sheet.getRange(2, 7, 1, outputHeaders.length).setValues([outputHeaders]);
    sheet.getRange(2, 7, 1, outputHeaders.length).setFontWeight("bold");
    sheet.getRange(2, 7, 1, outputHeaders.length).setBackground("#f3f3f3");
  }
}

// Function to update status in the first row
function updateStatusInTopRow(message, processed, total) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Ensure status row exists
  setupStatusRow(sheet);
  
  // Update only status and timestamp - no progress or processed counts
  sheet.getRange("B1").setValue(message);
  
  // Format date and time with both date and time
  sheet.getRange("D1").setValue(Utilities.formatDate(
    new Date(), 
    Session.getScriptTimeZone(), 
    "yyyy-MM-dd HH:mm:ss"
  ));
  
  // Set status background
  if (message.includes("Complete")) {
    sheet.getRange("B1").setBackground("#d9ead3"); // Green for complete
  } else if (message.includes("Error")) {
    sheet.getRange("B1").setBackground("#f4cccc"); // Red for error
  } else {
    sheet.getRange("B1").setBackground("#fff2cc"); // Yellow for in progress
  }
}

// Function to update final statistics (now starting at column H)
function updateFinalStats(success, nodata, failed) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set final statistics in row 1, starting at column H
  sheet.getRange("H1").setValue("Success: " + success);
  sheet.getRange("I1").setValue("No Data: " + nodata);
  sheet.getRange("J1").setValue("Failed: " + failed);
  
  // Format stats
  sheet.getRange("H1:J1").setBackground("#e8f4fe");
}

// Trigger to enrich all contacts
function enrichAllContacts() {
  startEnrichment(true);
}

// Function to enrich from a specific row
function enrichFromSpecificRow() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Enrich From Specific Row',
    'Enter the row number to start enrichment from:',
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if the user clicked "OK"
  if (response.getSelectedButton() == ui.Button.OK) {
    const rowNumber = parseInt(response.getResponseText());
    
    // Validate that it's a valid row number (must be >= 3 now that headers are in row 2)
    if (isNaN(rowNumber) || rowNumber < 3) {
      ui.alert('Invalid row number. Please enter a number greater than or equal to 3.');
      return;
    }
    
    // Call the enrichment function with the specific row number
    startEnrichment(true, rowNumber);
  }
}

// Main function to start enrichment process
function startEnrichment(processAll, customStartRow) {
  // Clear any previous enrichment state to ensure a fresh start
  PropertiesService.getScriptProperties().deleteProperty('enrichment_state');
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set up the output headers now (only when enrichment is actually starting)
  setupOutputHeaders(sheet);
  
  // Get sheet and calculate total rows to process
  const lastRow = sheet.getLastRow();
  const startRow = customStartRow || 3; // Start from row 3 by default (after headers)
  const totalRowsToProcess = lastRow < 3 ? 0 : (lastRow - startRow + 1);
  
  // Initialize the status area
  updateStatusInTopRow("In progress: Starting enrichment process...", 0, totalRowsToProcess);
  
  // Create and store enrichment state
  const state = {
    processAll: processAll,
    startRow: startRow,
    chunkSize: 1000,  // Process data in chunks of 1000 rows at a time
    batchSize: 100,   // Lusha API batch size
    totalRowsToProcess: totalRowsToProcess, // Store total rows for progress tracking
    stats: {
      processed: 0,
      success: 0,
      nodata: 0,
      failed: 0,
      batches: 0
    }
  };
  
  // Save state
  saveState(state);
  
  // Display toast notification to inform user
  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Enrichment process has started. You can continue working while the process runs in the background. Check the status in row 1 for updates.",
    "Process Started",
    10 // Show for 10 seconds
  );
  
  // If no data to process, complete immediately
  if (totalRowsToProcess <= 0) {
    updateStatusInTopRow("Complete: No data to process", 0, 0);
    updateFinalStats(0, 0, 0);
    return;
  }
  
  // Start the first chunk processing
  processNextChunk();
}

// Function to save current state to script properties
function saveState(state) {
  PropertiesService.getScriptProperties().setProperty(
    'enrichment_state', 
    JSON.stringify(state)
  );
}

// Function to retrieve state from script properties
function getState() {
  const stateJson = PropertiesService.getScriptProperties().getProperty('enrichment_state');
  return stateJson ? JSON.parse(stateJson) : null;
}

// Function to process the next chunk of data
function processNextChunk() {
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // DEBUG: Log what we're doing
  Logger.log("Processing next chunk...");
  Logger.log("Current processed count: " + state.stats.processed);
  
  // IMPORTANT: Get the last row with any data
  const lastRow = sheet.getLastRow();
  Logger.log("Last row with any data: " + lastRow);
  
  // Check if we've processed all rows
  if (state.currentChunkStart && state.currentChunkStart > lastRow) {
    Logger.log("All rows processed. Current chunk start: " + state.currentChunkStart + ", Last row: " + lastRow);
    completeProcessing(state);
    return;
  }
  
  // Determine the current chunk boundaries
  const chunkStart = state.currentChunkStart || state.startRow;
  const chunkEnd = Math.min(chunkStart + state.chunkSize - 1, lastRow);
  
  Logger.log("Processing chunk from row " + chunkStart + " to " + chunkEnd);
  
  // Update status
  updateStatusInTopRow(
    `In progress: Processing rows ${chunkStart} to ${chunkEnd}`,
    state.stats.processed,
    state.totalRowsToProcess
  );
  
  // Process this chunk
  processChunk(chunkStart, chunkEnd);
  
  // Update state for next chunk
  state.currentChunkStart = chunkEnd + 1;
  saveState(state);
  
  // Log the updated state
  Logger.log("After processing chunk, processed count is now: " + state.stats.processed);
  
  // Schedule the next chunk processing
  if (chunkEnd < lastRow) {
    processNextChunk();
  } else {
    completeProcessing(state);
  }
}

// Function to process a chunk of data
function processChunk(startRow, endRow) {
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the API key
  const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
  if (!API_KEY) {
    throw new Error("API key not found in Script Properties. Please set it under Project Settings with the key 'api_key'.");
  }
  
  // Get data for this chunk
  const dataRange = sheet.getRange(startRow, 1, endRow - startRow + 1, 6);
  const data = dataRange.getValues();
  
  // DEBUG: Log the data we're attempting to process
  Logger.log("Processing rows from " + startRow + " to " + endRow);
  Logger.log("Number of rows in data: " + data.length);
  
  // Get statuses if needed
  let statuses = [];
  if (!state.processAll) {
    statuses = sheet.getRange(startRow, 7, endRow - startRow + 1, 1).getValues().flat();
    Logger.log("Not processing all - number of status values: " + statuses.length);
  } else {
    Logger.log("Processing all rows regardless of status");
  }
  
  // Collect contacts for this chunk with improved validation
  const validContacts = [];
  data.forEach((row, index) => {
    const rowIndex = startRow + index;
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = row;
    
    // Skip if already successful and not processing all
    if (!state.processAll && statuses[index] === "Success") {
      Logger.log("Skipping row " + rowIndex + " because status is Success");
      return;
    }
    
    // Check if this row has ANY data at all
    const hasAnyData = firstName || lastName || companyName || companyDomain || emailAddress || linkedinUrl;
    
    if (hasAnyData) {
      // Let processBatch handle the detailed validation
      Logger.log("Row " + rowIndex + " has data and will be processed");
      validContacts.push({
        rowIndex: rowIndex,
        data: row
      });
    } else {
      // Completely empty row
      Logger.log("Row " + rowIndex + " is completely empty");
      sheet.getRange(rowIndex, 7).setValue("Failed: No data provided");
      sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
      state.stats.failed++;
    }
  });
  
  Logger.log("Contacts with data found: " + validContacts.length);
  
  // Process contacts in API batches
  for (let i = 0; i < validContacts.length; i += state.batchSize) {
    const batchContacts = validContacts.slice(i, i + state.batchSize);
    Logger.log("Processing batch of " + batchContacts.length + " contacts");
    
    // Update processed count before sending to API
    state.stats.processed += batchContacts.length;
    state.stats.batches++;
    
    // Update status with simplified message
    updateStatusInTopRow(
      `In progress: Processing batch ${state.stats.batches}`,
      state.stats.processed,
      state.totalRowsToProcess
    );
    
    // Process the batch
    processBatch(batchContacts, API_KEY);
    
    // Save state after each batch
    saveState(state);
  }
  
  // Save state after all batches in this chunk
  saveState(state);
  
  // Log final progress after this chunk
  const percentComplete = (state.stats.processed / state.totalRowsToProcess) * 100;
  Logger.log("After all batches, progress is: " + state.stats.processed + 
            " / " + state.totalRowsToProcess + " = " + percentComplete + "%");
}

// FIXED: Enhanced processBatch that filters insufficient contacts before API calls
function processBatch(contacts, apiKey) {
  if (contacts.length === 0) return;
  
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const url = 'https://api.lusha.com/v2/person';
  
  // STEP 1: Filter contacts into valid and insufficient categories
  const validContacts = [];
  const insufficientContacts = [];
  
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    // Check if contact has sufficient identifiers for Lusha API
    const hasStrongIdentifier = emailAddress || linkedinUrl || companyName || companyDomain;
    
    if (hasStrongIdentifier) {
      validContacts.push(contact);
    } else {
      // Only first/last name is not sufficient for Lusha API
      insufficientContacts.push(contact);
    }
  });
  
  Logger.log(`Contact filtering: ${validContacts.length} valid, ${insufficientContacts.length} insufficient`);
  
  // STEP 2: Immediately mark insufficient contacts as failed (don't send to API)
  insufficientContacts.forEach(contact => {
    const rowIndex = contact.rowIndex;
    const [firstName, lastName] = contact.data;
    const fullName = [firstName, lastName].filter(Boolean).join(" ");
    
    sheet.getRange(rowIndex, 7).setValue(`Failed: Insufficient data - need email, LinkedIn, or company info (only have: ${fullName})`);
    sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
    state.stats.failed++;
    
    Logger.log(`Row ${rowIndex}: Marked as insufficient - only has name: ${fullName}`);
  });
  
  // STEP 3: Process valid contacts if any exist
  if (validContacts.length > 0) {
    // Separate valid contacts into two groups: with company info and without
    const contactsWithCompany = [];
    const contactsWithoutCompany = [];
    
    validContacts.forEach(contact => {
      const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
      
      // Check if we have any company information
      if (companyName || companyDomain) {
        contactsWithCompany.push(contact);
      } else {
        contactsWithoutCompany.push(contact);
      }
    });
    
    Logger.log(`Valid contact split: ${contactsWithCompany.length} with company, ${contactsWithoutCompany.length} without company`);
    
    // Process contacts with company info first
    if (contactsWithCompany.length > 0) {
      processBatchWithCompanyInfo(contactsWithCompany, apiKey, url, sheet, state);
    }
    
    // Process contacts without company info separately
    if (contactsWithoutCompany.length > 0) {
      processBatchWithoutCompanyInfo(contactsWithoutCompany, apiKey, url, sheet, state);
    }
  } else {
    Logger.log("No valid contacts to send to API in this batch");
  }
  
  // Save updated state after processing
  saveState(state);
}

// Function to process contacts that have company information - ENHANCED
function processBatchWithCompanyInfo(contacts, apiKey, url, sheet, state) {
  const batchData = [];
  const rowMap = {};
  
  Logger.log("Processing batch WITH company info: " + contacts.length + " contacts");
  
  // Prepare the contacts for the API request with validation
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    const fullName = [firstName, lastName].filter(Boolean).join(" ");
    
    // Clean and validate company data
    const cleanCompanyName = companyName ? companyName.toString().trim() : "";
    const cleanCompanyDomain = companyDomain ? companyDomain.toString().trim() : "";
    
    // Only include companies array if we have valid company data
    const companies = [];
    if (cleanCompanyName || cleanCompanyDomain) {
      companies.push({ 
        name: cleanCompanyName, 
        domain: cleanCompanyDomain, 
        isCurrent: true 
      });
    }
    
    let contactPayload = {
      contactId: String(contact.rowIndex),
      linkedinUrl: linkedinUrl ? linkedinUrl.toString().trim() : "",
      email: emailAddress ? emailAddress.toString().trim() : ""
    };
    
    // Only add fullName if we have name data
    if (fullName) {
      contactPayload.fullName = fullName;
    }
    
    // Only add companies if we have company data
    if (companies.length > 0) {
      contactPayload.companies = companies;
    }
    
    // Validate the contact payload
    const validationIssues = validateContactPayload(contactPayload, contact.rowIndex);
    if (validationIssues.length > 0) {
      Logger.log(`WARNING: Contact ${contact.rowIndex} has validation issues but will still be sent to API`);
    }
    
    batchData.push(contactPayload);
    rowMap[contact.rowIndex] = contact.rowIndex;
  });
  
  // Send the batch request
  sendBatchRequest(batchData, rowMap, contacts, apiKey, url, sheet, state);
}

// Function to process contacts that don't have company information - ENHANCED
function processBatchWithoutCompanyInfo(contacts, apiKey, url, sheet, state) {
  const batchData = [];
  const rowMap = {};
  
  Logger.log("Processing batch WITHOUT company info: " + contacts.length + " contacts");
  
  // Prepare the contacts for the API request (without companies array)
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    const fullName = [firstName, lastName].filter(Boolean).join(" ");
    
    let contactPayload = {
      contactId: String(contact.rowIndex),
      linkedinUrl: linkedinUrl ? linkedinUrl.toString().trim() : "",
      email: emailAddress ? emailAddress.toString().trim() : ""
    };
    
    // Only include fullName if we have name information
    if (fullName) {
      contactPayload.fullName = fullName;
    }
    
    // CRITICAL: Don't include an empty companies array - omit it entirely
    // This prevents API errors when processing contacts without company info
    
    // Validate the contact payload
    const validationIssues = validateContactPayload(contactPayload, contact.rowIndex);
    if (validationIssues.length > 0) {
      Logger.log(`WARNING: Contact ${contact.rowIndex} has validation issues but will still be sent to API`);
    }
    
    batchData.push(contactPayload);
    rowMap[contact.rowIndex] = contact.rowIndex;
  });
  
  // Send the batch request
  sendBatchRequest(batchData, rowMap, contacts, apiKey, url, sheet, state);
}

// ENHANCED sendBatchRequest function with comprehensive error handling and debugging
function sendBatchRequest(batchData, rowMap, contacts, apiKey, url, sheet, state) {
  // Log the request payload for debugging
  const requestPayload = { contacts: batchData };
  
  // Log first few contacts for debugging (don't log all to avoid quota issues)
  Logger.log("=== API REQUEST DEBUG ===");
  Logger.log("Number of contacts in batch: " + batchData.length);
  Logger.log("Sample contact payload (first contact): " + JSON.stringify(batchData[0], null, 2));
  Logger.log("API URL: " + url);
  Logger.log("Has API key: " + (apiKey ? "Yes" : "No"));
  
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': apiKey },
    muteHttpExceptions: true,
    payload: JSON.stringify(requestPayload)
  };
  
  try {
    Logger.log("Sending API request with " + batchData.length + " contacts");
    const response = UrlFetchApp.fetch(url, options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    
    Logger.log("=== API RESPONSE DEBUG ===");
    Logger.log("HTTP Status Code: " + statusCode);
    Logger.log("Response Headers: " + JSON.stringify(response.getAllHeaders()));
    Logger.log("Response Body (first 1000 chars): " + responseText.substring(0, 1000));
    
    // Enhanced error handling for HTTP status codes
    if (statusCode < 200 || statusCode >= 300) {
      let errorMessage = `API Error (HTTP ${statusCode}): `;
      let detailedError = "";
      
      try {
        // Try to parse the error response as JSON
        const errorResponse = JSON.parse(responseText);
        Logger.log("Parsed error response: " + JSON.stringify(errorResponse, null, 2));
        
        // Handle different possible error response structures
        if (errorResponse.error) {
          // Structure: { error: { message, code, details } }
          detailedError = errorResponse.error.message || "No error message provided";
          if (errorResponse.error.code) {
            detailedError += ` (Code: ${errorResponse.error.code})`;
          }
          if (errorResponse.error.details) {
            detailedError += ` - Details: ${errorResponse.error.details}`;
          }
        } else if (errorResponse.errors && Array.isArray(errorResponse.errors)) {
          // Structure: { errors: [{ message, code }] }
          detailedError = errorResponse.errors.map(err => 
            (err.message || "Unknown error") + (err.code ? ` (${err.code})` : "")
          ).join("; ");
        } else if (errorResponse.message) {
          // Structure: { message: "error message" }
          detailedError = errorResponse.message;
        } else if (errorResponse.detail) {
          // Structure: { detail: "error message" }
          detailedError = errorResponse.detail;
        } else if (typeof errorResponse === 'string') {
          // Response is just a string
          detailedError = errorResponse;
        } else {
          // Unknown structure - log the keys and provide what we can
          const keys = Object.keys(errorResponse);
          Logger.log("Unknown error response structure. Keys: " + keys.join(", "));
          detailedError = `Unknown error structure. Available fields: ${keys.join(", ")}`;
          
          // Try to extract any field that might contain error info
          for (const key of keys) {
            if (typeof errorResponse[key] === 'string' && errorResponse[key].length > 0) {
              detailedError += `. ${key}: ${errorResponse[key]}`;
              break;
            }
          }
        }
        
        errorMessage += detailedError;
        
      } catch (parseError) {
        // If response is not valid JSON, use the raw response text
        Logger.log("Failed to parse error response as JSON: " + parseError.message);
        errorMessage += `Raw response: ${responseText}`;
        
        // Also try to extract common error patterns from non-JSON responses
        if (responseText.toLowerCase().includes('unauthorized')) {
          errorMessage += " (Check your API key)";
        } else if (responseText.toLowerCase().includes('rate limit')) {
          errorMessage += " (Rate limit exceeded)";
        } else if (responseText.toLowerCase().includes('quota')) {
          errorMessage += " (Quota exceeded)";
        }
      }
      
      // Log the final error message
      Logger.log(`Final error message: ${errorMessage}`);
      
      // Mark all contacts in this batch as failed
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(errorMessage);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      // Update status with error
      updateStatusInTopRow(
        `Error: ${errorMessage}`,
        state.stats.processed,
        state.totalRowsToProcess
      );
      
      saveState(state);
      return;
    }
    
    // Success case - parse response
    let responseData;
    try {
      responseData = JSON.parse(responseText);
      Logger.log("Successfully parsed response JSON");
    } catch (parseError) {
      const errorMessage = `Failed to parse successful API response: ${parseError.message}`;
      Logger.log(errorMessage);
      Logger.log(`Response that failed to parse: ${responseText.substring(0, 500)}...`);
      
      // Mark all contacts as failed
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      saveState(state);
      return;
    }
    
    // Validate response structure
    if (!responseData.contacts) {
      const errorMessage = "API response missing 'contacts' field";
      Logger.log(errorMessage);
      Logger.log("Response structure: " + JSON.stringify(Object.keys(responseData)));
      
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      saveState(state);
      return;
    }
    
    // Log successful response structure
    Logger.log("Response has " + Object.keys(responseData.contacts).length + " contacts");
    Logger.log("Companies in response: " + (responseData.companies ? Object.keys(responseData.companies).length : "none"));
    
    // Prepare data for batch updates to the sheet
    const updateData = [];
    const statusUpdates = [];
    
    // Process each contact in the response
    batchData.forEach(contact => {
      const rowIndex = parseInt(contact.contactId);
      const contactResponse = responseData.contacts[contact.contactId];
      
      if (!contactResponse) {
        // Missing contact in response
        const errorMsg = "Contact missing from API response";
        const emptyRow = Array(65).fill("");
        emptyRow[0] = `Failed: ${errorMsg}`;
        
        updateData.push({
          row: rowIndex,
          data: emptyRow
        });
        
        statusUpdates.push({
          row: rowIndex,
          color: "#8B0000" // Red
        });
        
        state.stats.failed++;
        return;
      }
      
      // Log individual contact response for debugging (first few only)
      if (batchData.indexOf(contact) < 3) {
        Logger.log(`Contact ${contact.contactId} response: ${JSON.stringify(contactResponse, null, 2).substring(0, 500)}`);
      }
      
      if (statusCode === 201 && contactResponse && !contactResponse.error) {
        // Success case
        const contactData = contactResponse.data || {};
        const companyData = responseData.companies[contactData.companyId] || {};
        
        // Fix company description: trim, normalize spaces, and remove line breaks
        let companyDescription = companyData.description || "";
        companyDescription = companyDescription.trim().replace(/\s+/g, ' ').replace(/[\r\n]+/g, ' ');
        
        // Prepare row data - Convert booleans to strings to avoid issues with setValues
        const rowData = [
          "Success", // Status
          contactResponse.isCreditCharged ? "TRUE" : "FALSE", // Convert boolean to string "TRUE"/"FALSE"
          contactData.personId || "", 
          contactData.firstName || "", 
          contactData.lastName || "", 
          contactData.fullName || "",
          contactData.emailAddresses?.[0]?.email || "", 
          contactData.emailAddresses?.[0]?.emailType || "", 
          contactData.emailAddresses?.[0]?.emailConfidence || "",
          contactData.emailAddresses?.[1]?.email || "", 
          contactData.emailAddresses?.[1]?.emailType || "", 
          contactData.emailAddresses?.[1]?.emailConfidence || "",
          contactData.phoneNumbers?.[0]?.number || "", 
          contactData.phoneNumbers?.[0]?.phoneType || "", 
          contactData.phoneNumbers?.[0]?.doNotCall ? "TRUE" : "FALSE", // Convert boolean
          contactData.phoneNumbers?.[1]?.number || "", 
          contactData.phoneNumbers?.[1]?.phoneType || "", 
          contactData.phoneNumbers?.[1]?.doNotCall ? "TRUE" : "FALSE", // Convert boolean
          contactData.contactTags?.map(tag => tag.name).join(", ") || "",
          contactData.location?.country || "", 
          contactData.location?.country_iso2 || "", 
          contactData.location?.continent || "",
          contactData.location?.city || "", 
          contactData.location?.state || "", 
          contactData.location?.state_code || "",
          contactData.location?.location_coordinates?.[1] || "", 
          contactData.location?.location_coordinates?.[0] || "",
          contactData.location?.is_eu_contact ? "TRUE" : "FALSE", // Convert boolean
          contactData.jobTitle?.title || "", 
          contactData.jobTitle?.departments?.join(", ") || "",
          contactData.jobTitle?.seniority || "", 
          contactData.socialLinks?.linkedin || "",
          companyData.name || "", 
          companyDescription, 
          companyData.domains?.homepage || "",
          companyData.location?.rawLocation || "", 
          companyData.location?.city || "",
          companyData.location?.state || "", 
          companyData.location?.stateCode || "",
          companyData.location?.country || "", 
          companyData.location?.countryIso2 || "",
          companyData.location?.continent || "",
          companyData.location?.locationCoordinates?.[1] || "", 
          companyData.location?.locationCoordinates?.[0] || "",
          companyData.companySize?.[0] || "", 
          companyData.companySize?.[1] || "",
          companyData.revenueRange?.[0] || "", 
          companyData.revenueRange?.[1] || "",
          companyData.logoUrl || "", 
          companyData.social?.linkedin || "", 
          companyData.social?.crunchbase || "",
          companyData.technologies?.map(tech => tech.name).join(", ") || "",
          companyData.funding?.rounds?.map(f => `${f.roundType} (${f.roundAmount} ${f.currency}, ${f.roundDate})`).join(", ") || "",
          companyData.funding?.totalRounds || "", 
          companyData.funding?.totalRoundsAmount || "",
          companyData.funding?.isIpo ? "TRUE" : "FALSE", // Convert boolean
          companyData.funding?.lastRoundType || "",
          companyData.funding?.lastRoundAmount || "", 
          companyData.funding?.lastRoundDate || "",
          companyData.intent?.detectedTopics?.map(i => `${i.topicName} (Score: ${i.metadata.topicScore}, Trend: ${i.metadata.topicTrend})`).join(", ") || "",
          companyData.specialities?.join(", ") || "",
          contactData.previousJob?.jobTitle?.title || "", 
          contactData.previousJob?.company?.name || "", 
          contactData.previousJob?.company?.domain || ""
        ];
        
        updateData.push({
          row: rowIndex,
          data: rowData
        });
        
        statusUpdates.push({
          row: rowIndex,
          color: "#006400" // Green
        });
        
        state.stats.success++;
      } else {
// Error case - Enhanced error information with debugging
let errorMsg;

if (contactResponse.error) {
  Logger.log(`Contact ${contact.contactId} error details: ${JSON.stringify(contactResponse.error)}`);
  
  // Handle different types of "no data" scenarios
  const isNoDataError = 
    contactResponse.error.message === "Could not find requested data" || 
    contactResponse.error.code === "DATA_NOT_FOUND" ||
    contactResponse.error.code === "3" ||  // Lusha's "no data found" error code
    (contactResponse.error.code === 3) ||  // In case it's a number instead of string
    contactResponse.error.message === "No data found" ||
    contactResponse.error.message === "Contact not found" ||
    (contactResponse.error.message === "" && contactResponse.error.code === "3") ||
    (contactResponse.error.message === "" && contactResponse.error.code === 3);
  
  if (isNoDataError) {
    errorMsg = "Could not find requested data";
  } else {
    // For other errors, include detailed information
    errorMsg = "Error: ";
    
    // Include error code if available
    if (contactResponse.error.code) {
      errorMsg += `[${contactResponse.error.code}] `;
    }
    
    // Include error message
    const message = contactResponse.error.message || "No error message provided";
    errorMsg += message;
    
    // Include details if available
    if (contactResponse.error.details) {
      errorMsg += ` - ${contactResponse.error.details}`;
    }
    
    // If we still have a generic error, provide more context
    if (message === "No error message provided" || message === "Unknown error" || message === "") {
      Logger.log(`Generic/empty error for contact ${contact.contactId}. Full contact response: ${JSON.stringify(contactResponse)}`);
      
      // Check if this might actually be a "no data" case based on error code
      if (contactResponse.error.code === "3" || contactResponse.error.code === 3) {
        errorMsg = "Could not find requested data";
      } else {
        errorMsg = `Error: API returned error code ${contactResponse.error.code} with no message`;
      }
    }
    
    // Check for rate limiting
    if (contactResponse.error.code === "RATE_LIMIT_EXCEEDED") {
      errorMsg += " (Consider slowing down the requests)";
    }
    
    // Check for credit issues
    if (contactResponse.error.code === "INSUFFICIENT_CREDITS") {
      errorMsg += " (Please check your account credits)";
    }
  }
} else {
  Logger.log(`Contact ${contact.contactId} failed without error object. Full response: ${JSON.stringify(contactResponse)}`);
  errorMsg = `Failed: No error details available (Contact ID: ${contact.contactId}, HTTP: ${statusCode})`;
}

const emptyRow = Array(65).fill(""); // Empty row for all columns
emptyRow[0] = errorMsg; // Set status message

updateData.push({
  row: rowIndex,
  data: emptyRow
});

// Enhanced categorization for coloring
const isNoDataCase = errorMsg === "Could not find requested data";

if (isNoDataCase) {
  statusUpdates.push({
    row: rowIndex, 
    color: "#FF8C00" // Orange for no data
  });
  state.stats.nodata++;
} else {
  statusUpdates.push({
    row: rowIndex,
    color: "#8B0000" // Red for actual errors
  });
  state.stats.failed++;
}
      }
    });
    
    // Batch update the sheet
    batchUpdateSheet(sheet, updateData, statusUpdates);
    
    // Save the updated state
    saveState(state);
    
  } catch (error) {
    // Handle connection errors and other exceptions with enhanced logging
    Logger.log("=== EXCEPTION DEBUG ===");
    Logger.log("Exception type: " + error.name);
    Logger.log("Exception message: " + error.message);
    Logger.log("Exception stack: " + error.stack);
    
    const errorMessage = `Connection Error: ${error.message}`;
    Logger.log(errorMessage);
    
    contacts.forEach(contact => {
      const rowIndex = contact.rowIndex;
      sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
      sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
      state.stats.failed++;
    });
    
    // Update status with error
    updateStatusInTopRow(
      `Error: ${errorMessage}`,
      state.stats.processed,
      state.totalRowsToProcess
    );
    
    saveState(state);
  }
}

// ENHANCED: More strict validation that matches what Lusha actually needs
function validateContactPayload(contact, rowIndex) {
  const issues = [];
  
  // Check required fields
  if (!contact.contactId) {
    issues.push("Missing contactId");
  }
  
  // Lusha API needs at least ONE strong identifier
  const hasEmail = contact.email && contact.email.trim() !== "";
  const hasLinkedIn = contact.linkedinUrl && contact.linkedinUrl.trim() !== "";
  const hasCompany = contact.companies && contact.companies.length > 0 && 
                     (contact.companies[0].name || contact.companies[0].domain);
  
  const hasStrongIdentifier = hasEmail || hasLinkedIn || hasCompany;
  
  if (!hasStrongIdentifier) {
    issues.push("No strong identifier - need email, LinkedIn, or company info");
  }
  
  // Check email format if provided
  if (contact.email && !isValidEmail(contact.email)) {
    issues.push("Invalid email format");
  }
  
  // Check LinkedIn URL format if provided
  if (contact.linkedinUrl && !contact.linkedinUrl.includes('linkedin.com')) {
    issues.push("LinkedIn URL doesn't contain linkedin.com");
  }
  
  // Check companies array if provided
  if (contact.companies && contact.companies.length > 0) {
    contact.companies.forEach((company, index) => {
      if (!company.name && !company.domain) {
        issues.push(`Company ${index} missing both name and domain`);
      }
    });
  }
  
  // Only log significant validation issues
  if (issues.length > 0) {
    Logger.log(`Validation issues for row ${rowIndex}: ${issues.join(", ")}`);
  }
  
  return issues;
}

// Simple email validation function
function isValidEmail(email) {
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return emailRegex.test(email);
}

// Updated batch update function with improved text wrapping handling
function batchUpdateSheet(sheet, rowUpdates, statusUpdates) {
  // Group updates by adjacent rows to minimize API calls
  const rowGroups = {};
  
  rowUpdates.forEach(update => {
    if (!rowGroups[update.row]) {
      rowGroups[update.row] = update.data;
    }
  });
  
  // Update data in batches
  const batchSize = 20; // Number of rows to update at once
  const rowIndices = Object.keys(rowGroups).map(Number).sort((a, b) => a - b);
  
  for (let i = 0; i < rowIndices.length; i += batchSize) {
    const batch = rowIndices.slice(i, i + batchSize);
    
    batch.forEach(rowIndex => {
      try {
        // Make sure the data array length matches the sheet's expected width
        // If the output headers have 65 columns, ensure our data has exactly 65 columns
        const data = rowGroups[rowIndex];
        
        // Verify the row index is valid
        if (rowIndex < 1) {
          Logger.log(`Skipping invalid row index: ${rowIndex}`);
          return;
        }
        
        // Ensure data array is of expected length
        while (data.length > 65) {
          data.pop(); // Trim extra columns
        }
        while (data.length < 65) {
          data.push(""); // Add missing columns
        }
        
        // Update the row data
        sheet.getRange(rowIndex, 7, 1, data.length).setValues([data]);
      } catch (error) {
        Logger.log(`Error updating row ${rowIndex}: ${error.message}`);
        // Set a simple error message for this row
        try {
          sheet.getRange(rowIndex, 7).setValue(`Failed: Error updating row (${error.message})`);
        } catch (e) {
          Logger.log(`Unable to set error message: ${e.message}`);
        }
      }
    });
  }
  
  // Update status colors
  statusUpdates.forEach(update => {
    try {
      sheet.getRange(update.row, 7).setFontColor(update.color);
    } catch (error) {
      Logger.log(`Error setting color for row ${update.row}: ${error.message}`);
    }
  });
  
  // Set text wrapping for company description column for all updated rows
  if (rowIndices.length > 0) {
    try {
      const startRow = Math.min(...rowIndices);
      const endRow = Math.max(...rowIndices);
      
      // Company Description is typically column "AN" which should be 34 columns after G
      // Let's determine its position from the headers to be safe
      const headers = sheet.getRange(2, 7, 1, 65).getValues()[0];
      let companyDescIndex = headers.findIndex(header => header === "Company Description");
      
      if (companyDescIndex === -1) {
        // If we can't find it, use a default position (34 columns after G)
        companyDescIndex = 34;
      }
      
      const companyDescColumn = 7 + companyDescIndex;
      
      // Apply proper wrapping to company description column
      sheet.getRange(startRow, companyDescColumn, endRow - startRow + 1, 1)
           .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
      
      // Set column width to ensure adequate display space
      sheet.setColumnWidth(companyDescColumn, 300); // 300 pixels should be enough for most descriptions
      
      // Make sure row height adjusts automatically - but only if there aren't too many rows
      // to avoid script timeout
      if (endRow - startRow < 100) {
        for (let row = startRow; row <= endRow; row++) {
          sheet.setRowHeight(row, -1); // -1 means automatic height based on content
        }
      }
    } catch (error) {
      Logger.log(`Error applying text wrapping: ${error.message}`);
    }
  }
}

// Function to complete the processing with improved text wrapping
function completeProcessing(state) {
  // Get accurate final statistics from the sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get all status values from column G (excluding header rows)
  let finalStats = {
    processed: state.stats.processed,
    success: 0,
    nodata: 0,
    failed: 0,
    batches: state.stats.batches
  };
  
  // Only count status values if there are rows with data
  if (sheet.getLastRow() > 2) { // > 2 because row 1-2 are headers
    try {
      const statusRange = sheet.getRange(3, 7, sheet.getLastRow() - 2, 1);
      const statusValues = statusRange.getValues().flat();
      
      // Count each type of status - IMPROVED VERSION
      statusValues.forEach(status => {
        if (status === "Success") {
          finalStats.success++;
        } else if (status === "Could not find requested data") {
          finalStats.nodata++;
        } else if (status && status !== "" && !status.startsWith("Failed:")) {
          // Any other non-empty, non-failed status gets counted as failed
          finalStats.failed++;
        } else if (status && status.startsWith("Failed:")) {
          // Explicit failed messages
          finalStats.failed++;
        }
      });
      
      Logger.log("Final stats from sheet - Success: " + finalStats.success + 
                ", No data: " + finalStats.nodata + 
                ", Failed: " + finalStats.failed);
      
      // Find the Company Description column
      const headers = sheet.getRange(2, 7, 1, 65).getValues()[0];
      let companyDescIndex = headers.findIndex(header => header === "Company Description");
      
      if (companyDescIndex === -1) {
        // If we can't find it, use a default position (34 columns after G)
        companyDescIndex = 34;
      }
      
      const companyDescColumn = 7 + companyDescIndex;
      
      // Apply proper wrapping to all Company Description cells
      if (sheet.getLastRow() >= 3) {
        // Get the range for all company descriptions
        const descRange = sheet.getRange(3, companyDescColumn, sheet.getLastRow() - 2, 1);
        
        // Apply wrap strategy
        descRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
        
        // Set column width to ensure adequate display space
        sheet.setColumnWidth(companyDescColumn, 300); // 300 pixels is usually sufficient
        
        // Reset all row heights to automatic - but limit to avoid timeout
        const maxRowsToAdjust = 500;
        const rowsToAdjust = Math.min(sheet.getLastRow() - 2, maxRowsToAdjust);
        
        for (let row = 3; row <= 3 + rowsToAdjust - 1; row++) {
          try {
            sheet.setRowHeight(row, -1); // -1 means automatic row height
          } catch (err) {
            Logger.log(`Error setting row height for row ${row}: ${err.message}`);
          }
        }
        
        if (sheet.getLastRow() - 2 > maxRowsToAdjust) {
          Logger.log(`Only adjusted row heights for first ${maxRowsToAdjust} rows to avoid timeout.`);
        }
      }
      
    } catch (e) {
      // Ignore formatting errors
      Logger.log('Error setting text wrapping: ' + e);
    }
  }
  
  // Update final status with simplified message
  updateStatusInTopRow(
    `Complete!`,
    state.totalRowsToProcess,
    state.totalRowsToProcess
  );
  
  // Update final stats
  updateFinalStats(finalStats.success, finalStats.nodata, finalStats.failed);
  
  // Show toast notification to inform user
  SpreadsheetApp.getActiveSpreadsheet().toast(
    `Enrichment process complete! Successfully enriched: ${finalStats.success}, No data found: ${finalStats.nodata}, Failed: ${finalStats.failed}`,
    "Process Complete",
    10 // Show for 10 seconds
  );
  
  // Clean up state
  PropertiesService.getScriptProperties().deleteProperty('enrichment_state');
}

// Helper function to find the last row with actual data
function getLastRowWithData(sheet, numColumns) {
  const data = sheet.getDataRange().getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    for (let j = 0; j < numColumns; j++) {
      if (data[i][j] !== '') {
        return i + 1; // +1 because array is 0-indexed
      }
    }
  }
  return 1; // Return 1 if no data found (just the header row)
}

// TEST FUNCTION - Add this for debugging API issues
function testLushaAPI() {
  const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
  
  if (!API_KEY) {
    Logger.log("ERROR: No API key found!");
    return;
  }
  
  Logger.log("=== LUSHA API TEST ===");
  Logger.log("API Key exists: " + (API_KEY ? "Yes" : "No"));
  Logger.log("API Key length: " + (API_KEY ? API_KEY.length : "N/A"));
  Logger.log("API Key first 10 chars: " + (API_KEY ? API_KEY.substring(0, 10) + "..." : "N/A"));
  
  // Test with the simplest possible request
  const testPayload = {
    contacts: [
      {
        contactId: "test123",
        email: "test@example.com"
      }
    ]
  };
  
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': API_KEY },
    muteHttpExceptions: true,
    payload: JSON.stringify(testPayload)
  };
  
  Logger.log("=== TEST REQUEST ===");
  Logger.log("URL: https://api.lusha.com/v2/person");
  Logger.log("Method: POST");
  Logger.log("Headers: " + JSON.stringify(options.headers));
  Logger.log("Content-Type: " + options.contentType);
  Logger.log("Payload: " + options.payload);
  
  try {
    const response = UrlFetchApp.fetch('https://api.lusha.com/v2/person', options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    const headers = response.getAllHeaders();
    
    Logger.log("=== TEST RESPONSE ===");
    Logger.log("Status Code: " + statusCode);
    Logger.log("Response Headers: " + JSON.stringify(headers));
    Logger.log("Response Body: " + responseText);
    
    // Try to parse response
    try {
      const responseData = JSON.parse(responseText);
      Logger.log("Parsed Response: " + JSON.stringify(responseData, null, 2));
    } catch (e) {
      Logger.log("Response is not valid JSON: " + e.message);
    }
    
  } catch (error) {
    Logger.log("=== REQUEST FAILED ===");
    Logger.log("Error: " + error.message);
    Logger.log("Error stack: " + error.stack);
  }
}