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.
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.
Go to Extensions > Apps Script in your Google Sheets file.
In the script editor, paste the provided code below. If there's any existing code, you can delete it before pasting.
Click the save icon or press Ctrl+S (Windows) / Cmd+S (Mac) to save the script.
// 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')
.addSeparator()
.addItem('🔍 Diagnose Contact Data', 'diagnoseContactData')
.addItem('🧪 Test API with Working Example', 'testWithWorkingExample')
.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
// Set overflow text wrapping for all columns (better display)
try {
const lastCol = sheet.getLastColumn();
if (lastCol > 0) {
// Apply overflow to all columns in rows 1 and 2
sheet.getRange(1, 1, 2, Math.max(lastCol, 70))
.setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
}
} catch (error) {
Logger.log(`Error setting initial text wrapping: ${error.message}`);
}
}
// 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 - 100 contacts per request for optimal speed
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);
// Update batch counter BEFORE processing
state.stats.batches++;
Logger.log("========================================");
Logger.log(`PROCESSING BATCH #${state.stats.batches} of ${batchContacts.length} contacts`);
Logger.log(`Rows in this batch: ${batchContacts.map(c => c.rowIndex).join(", ")}`);
Logger.log("========================================");
// Update processed count before sending to API
state.stats.processed += batchContacts.length;
// Update status with simplified message
updateStatusInTopRow(
`In progress: Processing batch ${state.stats.batches}`,
state.stats.processed,
state.totalRowsToProcess
);
// Process the batch
const batchResult = processBatch(batchContacts, API_KEY);
// Check if we hit credit limit
if (batchResult && batchResult.outOfCredits) {
Logger.log("⚠️ OUT OF CREDITS - Stopping enrichment");
updateStatusInTopRow(
`⚠️ Stopped: Out of Lusha credits at row ${batchResult.stopRow}. Please add credits and resume.`,
state.stats.processed,
state.totalRowsToProcess
);
saveState(state);
return; // Stop processing
}
// Save state after each batch
saveState(state);
// Add delay between batches to avoid rate limiting (except for last batch)
if (i + state.batchSize < validContacts.length) {
Logger.log("⏱️ Waiting 1 second before next batch to avoid rate limiting...");
Utilities.sleep(1000); // 1 second delay between batches
}
}
// 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 + "%");
// Retry failed contacts (error code 4) in smaller batches
retryFailedContacts(state, API_KEY);
}
// 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;
// Lusha API accepts: LinkedIn URL OR Email OR (firstName AND lastName AND (companyName OR companyDomain))
const hasLinkedIn = linkedinUrl && linkedinUrl.toString().trim() !== "";
const hasEmail = emailAddress && emailAddress.toString().trim() !== "";
const hasName = (firstName && firstName.toString().trim() !== "") || (lastName && lastName.toString().trim() !== "");
const hasCompany = (companyName && companyName.toString().trim() !== "") || (companyDomain && companyDomain.toString().trim() !== "");
const hasStrongIdentifier = hasLinkedIn || hasEmail || (hasName && hasCompany);
if (hasStrongIdentifier) {
validContacts.push(contact);
} else {
// Insufficient data 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, companyName, companyDomain] = contact.data;
const fullName = [firstName, lastName].filter(Boolean).join(" ");
const company = companyName || companyDomain || "";
const dataList = [];
if (fullName) dataList.push(`name: ${fullName}`);
if (company) dataList.push(`company: ${company}`);
sheet.getRange(rowIndex, 7).setValue(
`Failed: Insufficient data - Lusha requires: (Email OR LinkedIn) OR (Name AND Company) - (currently have: ${dataList.join(', ') || 'no data'})`
);
sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
state.stats.failed++;
Logger.log(`Row ${rowIndex}: Marked as insufficient - has ${dataList.join(', ')} but needs more data`);
});
// 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`);
// Log row indices for debugging
if (contactsWithCompany.length > 0) {
Logger.log(`Rows WITH company: ${contactsWithCompany.map(c => c.rowIndex).join(", ")}`);
}
if (contactsWithoutCompany.length > 0) {
Logger.log(`Rows WITHOUT company: ${contactsWithoutCompany.map(c => c.rowIndex).join(", ")}`);
}
// Process contacts with company info first
if (contactsWithCompany.length > 0) {
const result = processBatchWithCompanyInfo(contactsWithCompany, apiKey, url, sheet, state);
if (result && result.outOfCredits) {
return result; // Propagate out of credits signal
}
}
// Process contacts without company info separately
if (contactsWithoutCompany.length > 0) {
const result = processBatchWithoutCompanyInfo(contactsWithoutCompany, apiKey, url, sheet, state);
if (result && result.outOfCredits) {
return result; // Propagate out of credits signal
}
}
} else {
Logger.log("No valid contacts to send to API in this batch");
}
// Save updated state after processing
saveState(state);
return null; // No issues
}
// Function to retry failed contacts (error code 4) in smaller batches
function retryFailedContacts(state, apiKey) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
if (lastRow < 3) return; // No data rows
Logger.log("========================================");
Logger.log("🔄 CHECKING FOR FAILED CONTACTS TO RETRY");
Logger.log("========================================");
// Get all status cells
const statusRange = sheet.getRange(3, 7, lastRow - 2, 1);
const statuses = statusRange.getValues().flat();
// Get all input data
const inputRange = sheet.getRange(3, 1, lastRow - 2, 6);
const inputData = inputRange.getValues();
// Find contacts with error code 4
const failedContacts = [];
statuses.forEach((status, index) => {
const statusStr = String(status);
// Check if this is an error code 4 failure
if (statusStr.includes("Code 4") || statusStr.includes("code 4")) {
const rowIndex = index + 3;
const row = inputData[index];
const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = row;
// Check if we have at least some data to retry with
if (firstName || lastName || companyDomain || emailAddress || linkedinUrl) {
failedContacts.push({
rowIndex: rowIndex,
data: row
});
Logger.log(`Found failed contact at row ${rowIndex}: ${firstName} ${lastName} - ${companyDomain}`);
}
}
});
if (failedContacts.length === 0) {
Logger.log("✅ No failed contacts to retry");
return;
}
Logger.log(`🔄 Found ${failedContacts.length} failed contacts to retry in smaller batches`);
// Retry in very small batches of 2 (Lusha works better with tiny batches for difficult lookups)
const retryBatchSize = 2;
let retriedCount = 0;
let retrySuccessCount = 0;
for (let i = 0; i < failedContacts.length; i += retryBatchSize) {
const retryBatch = failedContacts.slice(i, i + retryBatchSize);
Logger.log(`========================================`);
Logger.log(`🔄 RETRY BATCH ${Math.floor(i / retryBatchSize) + 1} - ${retryBatch.length} contacts`);
Logger.log(`Rows: ${retryBatch.map(c => c.rowIndex).join(", ")}`);
Logger.log(`========================================`);
// Update status to show retry
updateStatusInTopRow(
`🔄 Retrying ${failedContacts.length} failed contacts (batch ${Math.floor(i / retryBatchSize) + 1})`,
state.stats.processed,
state.totalRowsToProcess
);
// Process the retry batch
const beforeFailed = state.stats.failed;
processBatch(retryBatch, apiKey);
const afterFailed = state.stats.failed;
// Calculate how many succeeded in this retry
const retrySuccess = beforeFailed - afterFailed;
if (retrySuccess > 0) {
retrySuccessCount += retrySuccess;
Logger.log(`✅ Retry batch recovered ${retrySuccess} contacts!`);
}
retriedCount += retryBatch.length;
// Small delay between retry batches
if (i + retryBatchSize < failedContacts.length) {
Logger.log("⏱️ Waiting 2 seconds before next retry batch...");
Utilities.sleep(2000);
}
}
Logger.log(`========================================`);
Logger.log(`🔄 RETRY COMPLETE: ${retrySuccessCount}/${retriedCount} recovered`);
Logger.log(`========================================`);
saveState(state);
}
// Function to clean and validate email address
function cleanAndValidateEmail(email) {
if (!email) return { cleaned: "", isValid: true, error: null };
const cleaned = email.toString().trim().toLowerCase();
// Check for basic email format
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(cleaned)) {
return { cleaned: "", isValid: false, error: "Invalid email format" };
}
// Check for common issues
if (cleaned.includes('..')) {
return { cleaned: "", isValid: false, error: "Email contains consecutive dots" };
}
if (cleaned.startsWith('.') || cleaned.startsWith('@')) {
return { cleaned: "", isValid: false, error: "Email starts with invalid character" };
}
if (cleaned.endsWith('.') || cleaned.endsWith('@')) {
return { cleaned: "", isValid: false, error: "Email ends with invalid character" };
}
return { cleaned: cleaned, isValid: true, error: null };
}
// Function to clean and validate domain
function cleanAndValidateDomain(domain) {
if (!domain) return { cleaned: "", isValid: true, error: null };
let cleaned = domain.toString().trim().toLowerCase();
// If it's empty after trimming, return empty
if (cleaned === "") return { cleaned: "", isValid: true, error: null };
// Remove common prefixes
cleaned = cleaned.replace(/^(https?:\/\/)?(www\.)?/i, '');
// Remove trailing slashes and paths
cleaned = cleaned.split('/')[0];
// Basic validation - just check for obvious issues
// Must contain at least one dot
if (!cleaned.includes('.')) {
return { cleaned: cleaned, isValid: false, error: "Domain missing TLD (e.g., .com)" };
}
// Check for spaces (obvious error)
if (cleaned.includes(' ')) {
return { cleaned: cleaned, isValid: false, error: "Domain contains spaces" };
}
// Must have something before and after the dot
const parts = cleaned.split('.');
if (parts.some(part => part.length === 0)) {
return { cleaned: cleaned, isValid: false, error: "Invalid domain structure" };
}
// Return cleaned domain - let the API do final validation
return { cleaned: cleaned, isValid: true, error: null };
}
// Function to clean and validate LinkedIn URL
function cleanAndValidateLinkedIn(url) {
if (!url) return { cleaned: "", isValid: true, error: null };
const cleaned = url.toString().trim();
// Check if it's a LinkedIn URL
if (!cleaned.toLowerCase().includes('linkedin.com')) {
return { cleaned: "", isValid: false, error: "Not a LinkedIn URL" };
}
// Ensure it has proper format
if (!cleaned.match(/^https?:\/\//i)) {
return { cleaned: "", isValid: false, error: "LinkedIn URL missing protocol (http/https)" };
}
return { cleaned: cleaned, isValid: true, error: null };
}
// Function to process contacts that have company information - ENHANCED WITH VALIDATION
function processBatchWithCompanyInfo(contacts, apiKey, url, sheet, state) {
const batchData = [];
const rowMap = {};
const invalidContacts = [];
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;
// Clean and validate each field
const emailResult = cleanAndValidateEmail(emailAddress);
const domainResult = cleanAndValidateDomain(companyDomain);
const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
// Check for validation errors
const validationErrors = [];
if (!emailResult.isValid && emailAddress) {
validationErrors.push(`Email: ${emailResult.error}`);
}
if (!domainResult.isValid && companyDomain) {
validationErrors.push(`Domain: ${domainResult.error}`);
}
if (!linkedInResult.isValid && linkedinUrl) {
validationErrors.push(`LinkedIn: ${linkedInResult.error}`);
}
// If there are validation errors, mark contact as failed
if (validationErrors.length > 0) {
Logger.log(`Row ${contact.rowIndex}: Data validation failed - ${validationErrors.join(', ')}`);
invalidContacts.push({
rowIndex: contact.rowIndex,
errors: validationErrors
});
return;
}
const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
// Clean and validate company data
const cleanCompanyName = companyName ? companyName.toString().trim() : "";
const cleanCompanyDomain = domainResult.cleaned || "";
// Only include companies array if we have valid company data
// IMPORTANT: Include both name and domain even if one is empty (matching your working example)
const companies = [];
if (cleanCompanyName || cleanCompanyDomain) {
companies.push({
name: cleanCompanyName,
domain: cleanCompanyDomain,
isCurrent: true
});
}
// Build payload matching your working example structure EXACTLY
// IMPORTANT: Include email and linkedinUrl even if empty (API may require these fields)
let contactPayload = {
contactId: String(contact.rowIndex),
fullName: fullName || "",
linkedinUrl: linkedInResult.cleaned || "",
email: emailResult.cleaned || ""
};
// Add companies if available
if (companies.length > 0) {
contactPayload.companies = companies;
}
// Final validation check
const validationIssues = validateContactPayload(contactPayload, contact.rowIndex);
if (validationIssues.length > 0) {
Logger.log(`WARNING: Contact ${contact.rowIndex} has validation issues: ${validationIssues.join(', ')}`);
}
batchData.push(contactPayload);
rowMap[contact.rowIndex] = contact.rowIndex;
});
// Mark invalid contacts immediately
invalidContacts.forEach(contact => {
sheet.getRange(contact.rowIndex, 7).setValue(`Failed: Data validation error - ${contact.errors.join('; ')}`);
sheet.getRange(contact.rowIndex, 7).setFontColor("#8B0000");
state.stats.failed++;
});
// Send the batch request for valid contacts
if (batchData.length > 0) {
const result = sendBatchRequest(batchData, rowMap, contacts.filter(c => !invalidContacts.find(ic => ic.rowIndex === c.rowIndex)), apiKey, url, sheet, state);
if (result && result.outOfCredits) {
return result; // Propagate out of credits signal
}
}
saveState(state);
return null; // No issues
}
// Function to process contacts that don't have company information - ENHANCED WITH VALIDATION
function processBatchWithoutCompanyInfo(contacts, apiKey, url, sheet, state) {
const batchData = [];
const rowMap = {};
const invalidContacts = [];
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;
// Clean and validate each field
const emailResult = cleanAndValidateEmail(emailAddress);
const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
// Check for validation errors
const validationErrors = [];
if (!emailResult.isValid && emailAddress) {
validationErrors.push(`Email: ${emailResult.error}`);
}
if (!linkedInResult.isValid && linkedinUrl) {
validationErrors.push(`LinkedIn: ${linkedInResult.error}`);
}
// If there are validation errors, mark contact as failed
if (validationErrors.length > 0) {
Logger.log(`Row ${contact.rowIndex}: Data validation failed - ${validationErrors.join(', ')}`);
invalidContacts.push({
rowIndex: contact.rowIndex,
errors: validationErrors
});
return;
}
const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
// Build payload matching your working example structure EXACTLY
// IMPORTANT: Include email and linkedinUrl even if empty (API may require these fields)
let contactPayload = {
contactId: String(contact.rowIndex),
fullName: fullName || "",
linkedinUrl: linkedInResult.cleaned || "",
email: emailResult.cleaned || ""
};
// 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: ${validationIssues.join(', ')}`);
}
batchData.push(contactPayload);
rowMap[contact.rowIndex] = contact.rowIndex;
});
// Mark invalid contacts immediately
invalidContacts.forEach(contact => {
sheet.getRange(contact.rowIndex, 7).setValue(`Failed: Data validation error - ${contact.errors.join('; ')}`);
sheet.getRange(contact.rowIndex, 7).setFontColor("#8B0000");
state.stats.failed++;
});
// Send the batch request for valid contacts
if (batchData.length > 0) {
const result = sendBatchRequest(batchData, rowMap, contacts.filter(c => !invalidContacts.find(ic => ic.rowIndex === c.rowIndex)), apiKey, url, sheet, state);
if (result && result.outOfCredits) {
return result; // Propagate out of credits signal
}
}
saveState(state);
return null; // No issues
}
// 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 };
// Get current batch number from state (already incremented before this function is called)
const batchNumber = state.stats.batches;
// Log first few contacts for debugging (don't log all to avoid quota issues)
Logger.log("=== API REQUEST DEBUG ===");
Logger.log("Batch Number: " + batchNumber);
Logger.log("Number of contacts in this API call: " + batchData.length);
Logger.log("Sample contact payload (first contact): " + JSON.stringify(batchData[0], null, 2));
if (batchData.length > 1) {
Logger.log("Sample contact payload (second contact): " + JSON.stringify(batchData[1], null, 2));
}
if (batchData.length > 2) {
Logger.log("Sample contact payload (last contact): " + JSON.stringify(batchData[batchData.length - 1], null, 2));
}
Logger.log("Row indices in this API call: " + contacts.map(c => c.rowIndex).join(", "));
// CRITICAL: Log the full request body for the first batch to compare with working example
if (batchNumber === 1) {
Logger.log("=== FULL BATCH #1 REQUEST (first 3 contacts) ===");
Logger.log(JSON.stringify({ contacts: batchData.slice(0, 3) }, 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 = [];
let outOfCreditsFlag = false;
let outOfCreditsRow = null;
let outOfCreditsCount = 0; // Count how many contacts return OUT_OF_CREDIT
// 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 if (contactResponse.error.code === "4" || contactResponse.error.code === 4) {
// Error code 4 typically indicates invalid or insufficient data for enrichment
// Log the actual payload sent to API for debugging
const sentPayload = batchData.find(c => c.contactId === contact.contactId);
Logger.log(`=== ERROR CODE 4 DEBUG ===`);
Logger.log(`Row ${contact.contactId}: Error code 4 received`);
Logger.log(`Payload sent to API: ${JSON.stringify(sentPayload, null, 2)}`);
Logger.log(`Full error response: ${JSON.stringify(contactResponse.error, null, 2)}`);
// Check what data was actually provided - including empty strings
const dataProvided = [];
if (sentPayload) {
if (sentPayload.email !== undefined) {
dataProvided.push(`email: "${sentPayload.email}"`);
}
if (sentPayload.linkedinUrl !== undefined) {
dataProvided.push(`LinkedIn: "${sentPayload.linkedinUrl}"`);
}
if (sentPayload.fullName) {
dataProvided.push(`name: ${sentPayload.fullName}`);
}
if (sentPayload.companies && sentPayload.companies.length > 0) {
const company = sentPayload.companies[0];
dataProvided.push(`company: {name: "${company.name || ''}", domain: "${company.domain || ''}"}`);
} else {
dataProvided.push(`companies: MISSING`);
}
}
errorMsg = `Error: Invalid or insufficient input data for enrichment (Code 4) - Data provided: ${dataProvided.join(', ') || 'none'}`;
Logger.log(`Error message set to: ${errorMsg}`);
Logger.log(`IMPORTANT: Check if payload structure matches working example`);
} else if (contactResponse.error.code === "5" || contactResponse.error.code === 5) {
// Error code 5 typically indicates API validation error
errorMsg = "Error: Request validation failed (Code 5) - check data format";
Logger.log(`Error code 5 for contact ${contact.contactId}. Input data: ${JSON.stringify(contact.data)}`);
} else if (contactResponse.error.code === "2" || contactResponse.error.code === 2) {
// Error code 2 = OUT_OF_CREDIT
// Note: Lusha sometimes returns this for individual contacts even when account has credits
errorMsg = "⚠️ Credit not available for this contact (Code 2)";
Logger.log(`OUT_OF_CREDIT error for contact ${contact.contactId}`);
// Count this, but don't immediately stop - might just be this contact
outOfCreditsCount++;
if (!outOfCreditsRow) {
outOfCreditsRow = contact.rowIndex;
}
} else if (contactResponse.error.code) {
errorMsg = `Error: API returned error code ${contactResponse.error.code} - please check input data format and API documentation`;
Logger.log(`Unknown error code ${contactResponse.error.code} for contact ${contact.contactId}. Input data: ${JSON.stringify(contact.data)}`);
} else {
errorMsg = "Error: API returned an error with no code or message - please contact support";
Logger.log(`No error code or message for contact ${contact.contactId}. Full response: ${JSON.stringify(contactResponse)}`);
}
}
// 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);
// Check if we should stop due to out of credits
// Only stop if MORE THAN 50% of the batch returned OUT_OF_CREDIT
// (Lusha sometimes returns code 2 for individual contacts even when account has credits)
const batchSize = batchData.length;
const outOfCreditsPercentage = (outOfCreditsCount / batchSize) * 100;
Logger.log(`OUT_OF_CREDIT summary: ${outOfCreditsCount} out of ${batchSize} contacts (${outOfCreditsPercentage.toFixed(1)}%)`);
if (outOfCreditsCount > 0 && outOfCreditsPercentage > 50) {
// More than half the batch failed with OUT_OF_CREDIT - likely a real account issue
Logger.log(`⚠️ STOPPING: More than 50% of batch returned OUT_OF_CREDIT`);
return { outOfCredits: true, stopRow: outOfCreditsRow };
} else if (outOfCreditsCount > 0) {
// Some contacts failed with OUT_OF_CREDIT, but not enough to stop
Logger.log(`ℹ️ ${outOfCreditsCount} contacts returned OUT_OF_CREDIT, but continuing (likely false positives from Lusha API)`);
}
} 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 requires: LinkedIn URL OR Email OR (fullName AND (company.name OR company.domain))
const hasEmail = contact.email && contact.email.trim() !== "";
const hasLinkedIn = contact.linkedinUrl && contact.linkedinUrl.trim() !== "";
const hasFullName = contact.fullName && contact.fullName.trim() !== "";
const hasCompany = contact.companies && contact.companies.length > 0 &&
(contact.companies[0].name || contact.companies[0].domain);
const hasStrongIdentifier = hasEmail || hasLinkedIn || (hasFullName && hasCompany);
if (!hasStrongIdentifier) {
issues.push("No strong identifier - Lusha requires: (Email OR LinkedIn) OR (Name AND Company)");
}
// 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 status and company description columns for all updated rows
if (rowIndices.length > 0) {
try {
const startRow = Math.min(...rowIndices);
const endRow = Math.max(...rowIndices);
// Apply overflow to Status column (column 7) for all updated rows
sheet.getRange(startRow, 7, endRow - startRow + 1, 1)
.setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
// 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 overflow wrapping to company description column
sheet.getRange(startRow, companyDescColumn, endRow - startRow + 1, 1)
.setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
// 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 overflow strategy
descRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
// 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 - Test with your exact working payload structure
function testWithWorkingExample() {
const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
if (!API_KEY) {
Logger.log("ERROR: No API key found!");
SpreadsheetApp.getUi().alert("Error: No API key found in Script Properties");
return;
}
// Use the EXACT structure from your working example
const testPayload = {
"contacts": [
{
"contactId": "test1",
"fullName": "Jasmine Jordan",
"linkedinUrl": "",
"email": "",
"companies": [
{
"name": "",
"domain": "thefirmyoutrust.com",
"isCurrent": true
}
]
},
{
"contactId": "test2",
"fullName": "Laura Holloway",
"linkedinUrl": "",
"email": "",
"companies": [
{
"name": "",
"domain": "tedlaw.com",
"isCurrent": true
}
]
}
]
};
const options = {
method: 'POST',
contentType: 'application/json',
headers: { 'api_key': API_KEY },
muteHttpExceptions: true,
payload: JSON.stringify(testPayload)
};
Logger.log("=== TESTING WITH YOUR WORKING EXAMPLE ===");
Logger.log("Payload: " + JSON.stringify(testPayload, null, 2));
try {
const response = UrlFetchApp.fetch('https://api.lusha.com/v2/person', options);
const statusCode = response.getResponseCode();
const responseText = response.getContentText();
Logger.log("=== TEST RESPONSE ===");
Logger.log("Status Code: " + statusCode);
Logger.log("Response Body: " + responseText);
try {
const responseData = JSON.parse(responseText);
Logger.log("\nParsed Response:");
Logger.log(JSON.stringify(responseData, null, 2));
// Check for errors
if (responseData.contacts) {
Object.keys(responseData.contacts).forEach(contactId => {
const contact = responseData.contacts[contactId];
if (contact.error) {
Logger.log(`Contact ${contactId} ERROR: ${JSON.stringify(contact.error)}`);
} else {
Logger.log(`Contact ${contactId} SUCCESS`);
}
});
}
SpreadsheetApp.getUi().alert("Test Complete",
"Status: " + statusCode + "\nCheck the logs (View > Logs) for full details",
SpreadsheetApp.getUi().ButtonSet.OK);
} catch (e) {
Logger.log("Response is not valid JSON: " + e.message);
SpreadsheetApp.getUi().alert("Error", "Response is not valid JSON: " + e.message, SpreadsheetApp.getUi().ButtonSet.OK);
}
} catch (error) {
Logger.log("=== REQUEST FAILED ===");
Logger.log("Error: " + error.message);
Logger.log("Error stack: " + error.stack);
SpreadsheetApp.getUi().alert("Error", "Request failed: " + error.message, SpreadsheetApp.getUi().ButtonSet.OK);
}
}
// Original test function
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);
}
}
// DIAGNOSTIC FUNCTION - Run this on a specific row to diagnose data issues
function diagnoseContactData() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt(
'Diagnose Contact Data',
'Enter the row number to diagnose (e.g., 86 for the first data row after headers):',
ui.ButtonSet.OK_CANCEL
);
if (response.getSelectedButton() !== ui.Button.OK) {
return;
}
const rowNumber = parseInt(response.getResponseText());
if (isNaN(rowNumber) || rowNumber < 3) {
ui.alert('Invalid row number. Please enter a number >= 3.');
return;
}
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getRange(rowNumber, 1, 1, 6).getValues()[0];
const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = data;
Logger.log("=== CONTACT DATA DIAGNOSIS ===");
Logger.log(`Row: ${rowNumber}`);
Logger.log(`First Name: "${firstName}" (Type: ${typeof firstName}, Length: ${firstName ? firstName.toString().length : 0})`);
Logger.log(`Last Name: "${lastName}" (Type: ${typeof lastName}, Length: ${lastName ? lastName.toString().length : 0})`);
Logger.log(`Company Name: "${companyName}" (Type: ${typeof companyName}, Length: ${companyName ? companyName.toString().length : 0})`);
Logger.log(`Company Domain: "${companyDomain}" (Type: ${typeof companyDomain}, Length: ${companyDomain ? companyDomain.toString().length : 0})`);
Logger.log(`Email: "${emailAddress}" (Type: ${typeof emailAddress}, Length: ${emailAddress ? emailAddress.toString().length : 0})`);
Logger.log(`LinkedIn: "${linkedinUrl}" (Type: ${typeof linkedinUrl}, Length: ${linkedinUrl ? linkedinUrl.toString().length : 0})`);
// Run validation
Logger.log("\n=== VALIDATION RESULTS ===");
const emailResult = cleanAndValidateEmail(emailAddress);
Logger.log(`Email Validation: ${emailResult.isValid ? "PASS" : "FAIL"}`);
if (!emailResult.isValid) {
Logger.log(` Error: ${emailResult.error}`);
} else if (emailResult.cleaned) {
Logger.log(` Cleaned: "${emailResult.cleaned}"`);
}
const domainResult = cleanAndValidateDomain(companyDomain);
Logger.log(`Domain Validation: ${domainResult.isValid ? "PASS" : "FAIL"}`);
if (!domainResult.isValid) {
Logger.log(` Error: ${domainResult.error}`);
} else if (domainResult.cleaned) {
Logger.log(` Cleaned: "${domainResult.cleaned}"`);
}
const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
Logger.log(`LinkedIn Validation: ${linkedInResult.isValid ? "PASS" : "FAIL"}`);
if (!linkedInResult.isValid) {
Logger.log(` Error: ${linkedInResult.error}`);
} else if (linkedInResult.cleaned) {
Logger.log(` Cleaned: "${linkedInResult.cleaned}"`);
}
// Check for special characters
Logger.log("\n=== SPECIAL CHARACTER CHECK ===");
const checkSpecialChars = (value, fieldName) => {
if (!value) return;
const str = value.toString();
const specialChars = str.match(/[^\x20-\x7E]/g);
if (specialChars) {
Logger.log(`${fieldName} contains special characters: ${specialChars.join(', ')} (codes: ${specialChars.map(c => c.charCodeAt(0)).join(', ')})`);
} else {
Logger.log(`${fieldName}: No special characters detected`);
}
};
checkSpecialChars(firstName, "First Name");
checkSpecialChars(lastName, "Last Name");
checkSpecialChars(companyName, "Company Name");
checkSpecialChars(companyDomain, "Company Domain");
checkSpecialChars(emailAddress, "Email");
checkSpecialChars(linkedinUrl, "LinkedIn URL");
// Show what would be sent to API
Logger.log("\n=== PAYLOAD THAT WOULD BE SENT ===");
const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
const payload = {
contactId: String(rowNumber),
fullName: fullName,
email: emailResult.cleaned,
linkedinUrl: linkedInResult.cleaned
};
if (companyName || domainResult.cleaned) {
payload.companies = [{
name: companyName ? companyName.toString().trim() : "",
domain: domainResult.cleaned,
isCurrent: true
}];
}
Logger.log(JSON.stringify(payload, null, 2));
ui.alert(
'Diagnosis Complete',
'Check the Logs (View > Logs or Ctrl+Enter) for detailed diagnostics of row ' + rowNumber,
ui.ButtonSet.OK
);
}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.
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.
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.
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
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
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
To keep your contact data continuously updated, you can set up a time-driven trigger in Google Apps Script:
- Set Up Triggers: In the Apps Script editor, go to Triggers (clock icon in the left sidebar)
- 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.
If you have any questions on the above feel free to reach out the support team:
• Via live chat from the Lusha website
• Your Lusha Dashboard
• Via email: support@lusha.com