# Automate Contact Data Enrichment

## Overview

This guide walks you through automating contact enrichment in Google Sheets using the Lusha Bulk Person API V2. You'll be able to send contact data in bulk to Lusha, retrieve detailed information, update your sheet seamlessly, and track the status of each enrichment request in real-time.

## How it Works?

### 1. Create a New Google Sheet

Start by creating a new, blank Google Sheet.

You'll use this sheet to store and enrich contact data with the Lusha API. The required column headers will be added automatically after you complete the setup and refresh the sheet.

### 2. Add the Script to Google Sheet

#### Open the Script Editor:

Go to **Extensions > Apps Script** in your Google Sheets file.

#### Paste the Script:

In the script editor, paste the provided code below. If there's any existing code, you can delete it before pasting.

#### Save the Script:

Click the save icon or press **Ctrl+S** (Windows) / **Cmd+S** (Mac) to save the script.


```javascript
// 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 Start Date", "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 valid contacts for this chunk
  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 has data to process - be more lenient
    if (linkedinUrl || emailAddress || firstName || lastName || companyName || companyDomain) {
      Logger.log("Row " + rowIndex + " is valid and will be processed");
      validContacts.push({
        rowIndex: rowIndex,
        data: row
      });
    } else {
      // Mark as invalid immediately
      Logger.log("Row " + rowIndex + " has no valid data");
      sheet.getRange(rowIndex, 7).setValue("Failed: Missing required fields");
      sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
      state.stats.failed++;
    }
  });
  
  Logger.log("Valid contacts 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 + "%");
}

// Function to process a batch of contacts
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';
  const batchData = [];
  const rowMap = {};
  
  // Prepare the contacts for the API request
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    const fullName = [firstName, lastName].filter(Boolean).join(" ");
    const companies = (companyName || companyDomain) 
      ? [{ name: companyName || "", domain: companyDomain || "", isCurrent: true }]
      : [];
    
    let contactPayload = {
      contactId: String(contact.rowIndex),
      linkedinUrl: linkedinUrl || "",
      email: emailAddress || "",
      companies: companies
    };
    
    if (!(firstName || lastName) && emailAddress) {
      delete contactPayload.fullName;
    } else {
      contactPayload.fullName = fullName || "";
    }
    
    batchData.push(contactPayload);
    rowMap[contact.rowIndex] = contact.rowIndex;
  });
  
  // Make the API request
  const requestPayload = { contacts: batchData };
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': apiKey },
    muteHttpExceptions: true,
    payload: JSON.stringify(requestPayload)
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    
    // Error handling for HTTP status codes
    if (statusCode < 200 || statusCode >= 300) {
      let errorMessage = `API Error (HTTP ${statusCode}): `;
      
      try {
        // Try to parse the error response as JSON
        const errorResponse = JSON.parse(responseText);
        
        // Extract detailed error information if available
        if (errorResponse.error) {
          errorMessage += errorResponse.error.message || "Unknown error";
          if (errorResponse.error.code) {
            errorMessage += ` (Code: ${errorResponse.error.code})`;
          }
          if (errorResponse.error.details) {
            errorMessage += ` - ${errorResponse.error.details}`;
          }
        } else if (errorResponse.message) {
          errorMessage += errorResponse.message;
        } else {
          errorMessage += "Unknown API error";
        }
      } catch (parseError) {
        // If response is not valid JSON, use the raw response text
        errorMessage += responseText || "No error details available";
      }
      
      // Log the detailed error
      Logger.log(`Lusha API error: ${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;
    }
    
    // If we get here, the status code was in the 200 range
    // Parse the response data
    let responseData;
    try {
      responseData = JSON.parse(responseText);
    } catch (parseError) {
      const errorMessage = `Failed to parse 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;
    }
    
    // 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(66).fill(""); // Updated to 66 columns (added 1 for Job Start Date)
        emptyRow[0] = `Failed: ${errorMsg}`;
        
        updateData.push({
          row: rowIndex,
          data: emptyRow
        });
        
        statusUpdates.push({
          row: rowIndex,
          color: "#8B0000" // Red
        });
        
        state.stats.failed++;
        return;
      }
      
      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.jobStartDate || "", // *** ADDED JOB START DATE HERE ***
          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 - Provide detailed error information
        let errorMsg;
        
        if (contactResponse.error) {
          // Special case for "Could not find requested data" error - don't format as an error
          if (contactResponse.error.message === "Could not find requested data" || 
              contactResponse.error.code === "DATA_NOT_FOUND") {
            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
            errorMsg += contactResponse.error.message || "Unknown error";
            
            // Include details if available
            if (contactResponse.error.details) {
              errorMsg += ` - ${contactResponse.error.details}`;
            }
            
            // 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 {
          errorMsg = "Failed: Unknown API error";
        }
        
        const emptyRow = Array(66).fill(""); // Updated to 66 columns (added 1 for Job Start Date)
        emptyRow[0] = errorMsg; // Set status message
        
        updateData.push({
          row: rowIndex,
          data: emptyRow
        });
        
        // Check if this is a "no data" message - more specific check
        if (contactResponse?.error?.message === "Could not find requested data" || 
            contactResponse?.error?.code === "DATA_NOT_FOUND") {
          statusUpdates.push({
            row: rowIndex, 
            color: "#FF8C00" // Orange
          });
          state.stats.nodata++;
        } else {
          statusUpdates.push({
            row: rowIndex,
            color: "#8B0000" // Red
          });
          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
    const errorMessage = `API 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);
  }
}

// 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 66 columns, ensure our data has exactly 66 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 (updated to 66)
        while (data.length > 66) {
          data.pop(); // Trim extra columns
        }
        while (data.length < 66) {
          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 35 columns after G (updated due to new column)
      // Let's determine its position from the headers to be safe
      const headers = sheet.getRange(2, 7, 1, 66).getValues()[0]; // Updated to 66
      let companyDescIndex = headers.findIndex(header => header === "Company Description");
      
      if (companyDescIndex === -1) {
        // If we can't find it, use a default position (35 columns after G)
        companyDescIndex = 35; // Updated from 34 to 35
      }
      
      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
      statusValues.forEach(status => {
        if (status === "Success") {
          finalStats.success++;
        } else if (status === "Could not find requested data") {
          finalStats.nodata++;
        } else if (status && status !== "") {
          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)
}

// Add a utility function for better debugging API errors
function debugApiErrors(apiResponse) {
  try {
    const statusCode = apiResponse.getResponseCode();
    const responseText = apiResponse.getContentText();
    
    Logger.log(`API Response Code: ${statusCode}`);
    
    // Log headers
    const allHeaders = apiResponse.getAllHeaders();
    Logger.log(`API Headers: ${JSON.stringify(allHeaders)}`);
    
    // Parse and log response body if it's JSON
    try {
      const responseData = JSON.parse(responseText);
      Logger.log(`API Response Body: ${JSON.stringify(responseData, null, 2).substring(0, 500)}...`);
      
      // Log specific error information if available
      if (responseData.error) {
        Logger.log(`Error Code: ${responseData.error.code || 'N/A'}`);
        Logger.log(`Error Message: ${responseData.error.message || 'N/A'}`);
        Logger.log(`Error Details: ${responseData.error.details || 'N/A'}`);
      }
    } catch (e) {
      // Not JSON, log as text
      Logger.log(`API Response (not JSON): ${responseText.substring(0, 500)}...`);
    }
    
    return {
      statusCode,
      responseText,
      headers: allHeaders
    };
  } catch (error) {
    Logger.log(`Error debugging API response: ${error.message}`);
    return null;
  }
}
```

### 3. Add Your API Key

I. Go to **Project Settings** (gear icon in the script editor)

II. **Set Script Properties**:
Under Script Properties, add a new property with:

- **Key**: `api_key`
- **Value**: your actual Lusha API key


This keeps your API key secure and out of the main code.

### 4. Refresh the Spreadsheet

After saving the script and adding your API key, refresh the Google Sheets page.

You'll now see a new menu option: **Lusha Enrichment**, and your sheet will automatically populate the required headers in Columns A–F:

- **Column A**: First Name
- **Column B**: Last Name
- **Column C**: Company Name
- **Column D**: Company Domain
- **Column E**: Email Address
- **Column F**: LinkedIn URL


This layout is used by the script to send enrichment requests.

## Minimum Input Requirements

Each row should include at least one of the following valid combinations:

- **Full Name (First + Last) and Company Name or Domain**
- **Email Address**
- **LinkedIn URL**


This ensures compatibility with Lusha's API for enrichment.

## Use the Script

The **Lusha Enrichment** menu provides the following options:

- **Enrich All Contacts**: Enriches every row with data
- **Enrich From a Specific Row**: This lets you specify a starting row number and enriches all rows from that point onward


## Review and Analyze Results

Once enrichment is complete, your sheet will update with the following:

- **Column G (Status)**: "Success" or a specific error message
- **Columns H onward**: Additional data such as job title, location, company size, and more


## Troubleshooting

If you encounter issues, here are some troubleshooting tips:

- **Error Messages**: Check the Status column for specific error messages indicating what went wrong (e.g., invalid API key, missing required data)
- **Invalid JSON Response**: Ensure your API key is correctly entered under Project Settings and that the data in Columns A to F matches the expected structure


## Automate with Triggers (Optional)

To keep your contact data continuously updated, you can set up a time-driven trigger in Google Apps Script:

1. **Set Up Triggers**: In the Apps Script editor, go to Triggers (clock icon in the left sidebar)
2. **Choose Time-Driven**: Set up a time-driven trigger to run the script on a recurring basis (e.g., daily or weekly)


This automation keeps your contact data up-to-date without requiring manual intervention.

> ## Additional Resources
If you have any questions on the above feel free to reach out the support team:
• Via live chat from the [Lusha website](http://www.lusha.com/)
• Your [Lusha Dashboard](http://dashboard.lusha.com/dashboard)
• Via email: [support@lusha.com](mailto:support@lusha.com)