Skip to main content

Automate Contact Data Enrichment

Carolina Portela avatar
Written by Carolina Portela
Updated over a week ago

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

  1. Open the Script Editor:

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

  2. Paste the Script:

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

  3. Save the Script:

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

// Function to create the custom menu when the spreadsheet opens
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Lusha Enrichment')
.addItem('Enrich All Contacts', 'enrichAllContacts')
.addItem('Enrich From Specific Row', 'enrichFromSpecificRow')
.addToUi();

// Set up just the status row and input headers - NOT the output headers
setupInitialStructure();
}

// Function to set up the initial spreadsheet structure
function setupInitialStructure() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Set up status row (row 1)
setupStatusRow(sheet);

// Set up input headers (row 2)
setupInputHeaders(sheet);

// Format the sheet for better usability
sheet.setFrozenRows(2); // Freeze both header rows
}

// Function to set up the status row (row 1)
function setupStatusRow(sheet) {
// Check if status row already exists
const hasStatusLabel = sheet.getRange("A1").getValue() === "Enrichment Status";

if (!hasStatusLabel) {
// Set up simplified status area in row 1
sheet.getRange("A1").setValue("Enrichment Status");
sheet.getRange("A1").setFontWeight("bold");

sheet.getRange("C1").setValue("Last Updated:");
sheet.getRange("C1").setFontWeight("bold");

// Move final stats to start from column G
sheet.getRange("G1").setValue("Final Stats:");
sheet.getRange("G1").setFontWeight("bold");

// Set placeholder values
sheet.getRange("B1").setValue("Not started");
sheet.getRange("D1").setValue("-");

// Set placeholder values for final stats starting at column H
sheet.getRange("H1").setValue("Success: -");
sheet.getRange("I1").setValue("No Data: -");
sheet.getRange("J1").setValue("Failed: -");

// Format the status row
sheet.getRange("A1:J1").setBackground("#f3f3f3");
}
}

// Function to set up input headers in row 2 (columns A-F)
function setupInputHeaders(sheet) {
const inputHeaders = [
"First Name (Input)",
"Last Name (Input)",
"Company Name (Input)",
"Company Domain (Input)",
"Email Address (Input)",
"LinkedIn URL (Input)"
];

// Check if headers already exist in row 2
const existingHeaders = sheet.getRange("A2:F2").getValues()[0];
const hasHeaders = existingHeaders.some(header => header !== "");

// If no headers exist, add them to row 2
if (!hasHeaders) {
sheet.getRange("A2:F2").setValues([inputHeaders]);
sheet.getRange("A2:F2").setFontWeight("bold");
sheet.getRange("A2:F2").setBackground("#f3f3f3");

// Auto-resize columns for better visibility
sheet.autoResizeColumns(1, 6);
}
}

// Function to set up output headers in row 2 (starting at column G)
function setupOutputHeaders(sheet) {
const outputHeaders = [
"Status", "Is Credit Charged", "Person ID", "First Name", "Last Name", "Full Name",
"Email 1", "Email Type 1", "Email Confidence 1",
"Email 2", "Email Type 2", "Email Confidence 2",
"Phone Number 1", "Phone Type 1", "Do Not Call 1",
"Phone Number 2", "Phone Type 2", "Do Not Call 2",
"Contact Tags",
"Contact Location Country", "Contact Location Country ISO2",
"Contact Location Continent", "Contact Location City",
"Contact Location State", "Contact Location State Code",
"Contact Location Coordinates (Latitude)", "Contact Location Coordinates (Longitude)",
"Is EU Contact", "Job Title", "Job Departments",
"Seniority Level", "LinkedIn Profile",
"Company Name", "Company Description", "Company Homepage",
"Company Location", "Company Location City", "Company Location State",
"Company Location State Code", "Company Location Country",
"Company Location Country ISO2", "Company Location Continent",
"Company Location Coordinates (Latitude)", "Company Location Coordinates (Longitude)",
"Company Size Min", "Company Size Max",
"Revenue Range Min", "Revenue Range Max",
"Company Logo URL", "Company LinkedIn", "Company Crunchbase",
"Technologies", "Funding Rounds (String)", "Total Funding Rounds",
"Total Funding Amount", "Is IPO", "Last Funding Type",
"Last Funding Amount", "Last Funding Date",
"Intent Topics (String)", "Specialities",
"Previous Job Title", "Previous Company Name", "Previous Company Domain"
];

// Check if headers already exist and are correct
const existingHeaders = sheet.getRange(2, 7, 1, outputHeaders.length).getValues()[0];
if (existingHeaders.some((header, i) => header !== outputHeaders[i])) {
sheet.getRange(2, 7, 1, outputHeaders.length).setValues([outputHeaders]);
sheet.getRange(2, 7, 1, outputHeaders.length).setFontWeight("bold");
sheet.getRange(2, 7, 1, outputHeaders.length).setBackground("#f3f3f3");
}
}

// Function to update status in the first row
function updateStatusInTopRow(message, processed, total) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Ensure status row exists
setupStatusRow(sheet);

// Update only status and timestamp - no progress or processed counts
sheet.getRange("B1").setValue(message);

// Format date and time with both date and time
sheet.getRange("D1").setValue(Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
"yyyy-MM-dd HH:mm:ss"
));

// Set status background
if (message.includes("Complete")) {
sheet.getRange("B1").setBackground("#d9ead3"); // Green for complete
} else if (message.includes("Error")) {
sheet.getRange("B1").setBackground("#f4cccc"); // Red for error
} else {
sheet.getRange("B1").setBackground("#fff2cc"); // Yellow for in progress
}
}

// Function to update final statistics (now starting at column H)
function updateFinalStats(success, nodata, failed) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Set final statistics in row 1, starting at column H
sheet.getRange("H1").setValue("Success: " + success);
sheet.getRange("I1").setValue("No Data: " + nodata);
sheet.getRange("J1").setValue("Failed: " + failed);

// Format stats
sheet.getRange("H1:J1").setBackground("#e8f4fe");
}

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

// Function to enrich from a specific row
function enrichFromSpecificRow() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt(
'Enrich From Specific Row',
'Enter the row number to start enrichment from:',
ui.ButtonSet.OK_CANCEL
);

// Check if the user clicked "OK"
if (response.getSelectedButton() == ui.Button.OK) {
const rowNumber = parseInt(response.getResponseText());

// Validate that it's a valid row number (must be >= 3 now that headers are in row 2)
if (isNaN(rowNumber) || rowNumber < 3) {
ui.alert('Invalid row number. Please enter a number greater than or equal to 3.');
return;
}

// Call the enrichment function with the specific row number
startEnrichment(true, rowNumber);
}
}

// Main function to start enrichment process
function startEnrichment(processAll, customStartRow) {
// Clear any previous enrichment state to ensure a fresh start
PropertiesService.getScriptProperties().deleteProperty('enrichment_state');

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Set up the output headers now (only when enrichment is actually starting)
setupOutputHeaders(sheet);

// Get sheet and calculate total rows to process
const lastRow = sheet.getLastRow();
const startRow = customStartRow || 3; // Start from row 3 by default (after headers)
const totalRowsToProcess = lastRow < 3 ? 0 : (lastRow - startRow + 1);

// Initialize the status area
updateStatusInTopRow("In progress: Starting enrichment process...", 0, totalRowsToProcess);

// Create and store enrichment state
const state = {
processAll: processAll,
startRow: startRow,
chunkSize: 1000, // Process data in chunks of 1000 rows at a time
batchSize: 100, // Lusha API batch size
totalRowsToProcess: totalRowsToProcess, // Store total rows for progress tracking
stats: {
processed: 0,
success: 0,
nodata: 0,
failed: 0,
batches: 0
}
};

// Save state
saveState(state);

// Display toast notification to inform user
SpreadsheetApp.getActiveSpreadsheet().toast(
"Enrichment process has started. You can continue working while the process runs in the background. Check the status in row 1 for updates.",
"Process Started",
10 // Show for 10 seconds
);

// If no data to process, complete immediately
if (totalRowsToProcess <= 0) {
updateStatusInTopRow("Complete: No data to process", 0, 0);
updateFinalStats(0, 0, 0);
return;
}

// Start the first chunk processing
processNextChunk();
}

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

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

// Function to process the next chunk of data
function processNextChunk() {
const state = getState();
if (!state) return;

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// DEBUG: Log what we're doing
Logger.log("Processing next chunk...");
Logger.log("Current processed count: " + state.stats.processed);

// IMPORTANT: Get the last row with any data
const lastRow = sheet.getLastRow();
Logger.log("Last row with any data: " + lastRow);

// Check if we've processed all rows
if (state.currentChunkStart && state.currentChunkStart > lastRow) {
Logger.log("All rows processed. Current chunk start: " + state.currentChunkStart + ", Last row: " + lastRow);
completeProcessing(state);
return;
}

// Determine the current chunk boundaries
const chunkStart = state.currentChunkStart || state.startRow;
const chunkEnd = Math.min(chunkStart + state.chunkSize - 1, lastRow);

Logger.log("Processing chunk from row " + chunkStart + " to " + chunkEnd);

// Update status
updateStatusInTopRow(
`In progress: Processing rows ${chunkStart} to ${chunkEnd}`,
state.stats.processed,
state.totalRowsToProcess
);

// Process this chunk
processChunk(chunkStart, chunkEnd);

// Update state for next chunk
state.currentChunkStart = chunkEnd + 1;
saveState(state);

// Log the updated state
Logger.log("After processing chunk, processed count is now: " + state.stats.processed);

// Schedule the next chunk processing
if (chunkEnd < lastRow) {
processNextChunk();
} else {
completeProcessing(state);
}
}

// Function to process a chunk of data
function processChunk(startRow, endRow) {
const state = getState();
if (!state) return;

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get the API key
const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
if (!API_KEY) {
throw new Error("API key not found in Script Properties. Please set it under Project Settings with the key 'api_key'.");
}

// Get data for this chunk
const dataRange = sheet.getRange(startRow, 1, endRow - startRow + 1, 6);
const data = dataRange.getValues();

// DEBUG: Log the data we're attempting to process
Logger.log("Processing rows from " + startRow + " to " + endRow);
Logger.log("Number of rows in data: " + data.length);

// Get statuses if needed
let statuses = [];
if (!state.processAll) {
statuses = sheet.getRange(startRow, 7, endRow - startRow + 1, 1).getValues().flat();
Logger.log("Not processing all - number of status values: " + statuses.length);
} else {
Logger.log("Processing all rows regardless of status");
}

// Collect 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(65).fill("");
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.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(65).fill(""); // Empty row for all columns
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 65 columns, ensure our data has exactly 65 columns
const data = rowGroups[rowIndex];

// Verify the row index is valid
if (rowIndex < 1) {
Logger.log(`Skipping invalid row index: ${rowIndex}`);
return;
}

// Ensure data array is of expected length
while (data.length > 65) {
data.pop(); // Trim extra columns
}
while (data.length < 65) {
data.push(""); // Add missing columns
}

// Update the row data
sheet.getRange(rowIndex, 7, 1, data.length).setValues([data]);
} catch (error) {
Logger.log(`Error updating row ${rowIndex}: ${error.message}`);
// Set a simple error message for this row
try {
sheet.getRange(rowIndex, 7).setValue(`Failed: Error updating row (${error.message})`);
} catch (e) {
Logger.log(`Unable to set error message: ${e.message}`);
}
}
});
}

// Update status colors
statusUpdates.forEach(update => {
try {
sheet.getRange(update.row, 7).setFontColor(update.color);
} catch (error) {
Logger.log(`Error setting color for row ${update.row}: ${error.message}`);
}
});

// Set text wrapping for company description column for all updated rows
if (rowIndices.length > 0) {
try {
const startRow = Math.min(...rowIndices);
const endRow = Math.max(...rowIndices);

// Company Description is typically column "AN" which should be 34 columns after G
// Let's determine its position from the headers to be safe
const headers = sheet.getRange(2, 7, 1, 65).getValues()[0];
let companyDescIndex = headers.findIndex(header => header === "Company Description");

if (companyDescIndex === -1) {
// If we can't find it, use a default position (34 columns after G)
companyDescIndex = 34;
}

const companyDescColumn = 7 + companyDescIndex;

// Apply proper wrapping to company description column
sheet.getRange(startRow, companyDescColumn, endRow - startRow + 1, 1)
.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

// Set column width to ensure adequate display space
sheet.setColumnWidth(companyDescColumn, 300); // 300 pixels should be enough for most descriptions

// Make sure row height adjusts automatically - but only if there aren't too many rows
// to avoid script timeout
if (endRow - startRow < 100) {
for (let row = startRow; row <= endRow; row++) {
sheet.setRowHeight(row, -1); // -1 means automatic height based on content
}
}
} catch (error) {
Logger.log(`Error applying text wrapping: ${error.message}`);
}
}
}

// Function to complete the processing with improved text wrapping
function completeProcessing(state) {
// Get accurate final statistics from the sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get all status values from column G (excluding header rows)
let finalStats = {
processed: state.stats.processed,
success: 0,
nodata: 0,
failed: 0,
batches: state.stats.batches
};

// Only count status values if there are rows with data
if (sheet.getLastRow() > 2) { // > 2 because row 1-2 are headers
try {
const statusRange = sheet.getRange(3, 7, sheet.getLastRow() - 2, 1);
const statusValues = statusRange.getValues().flat();

// Count each type of status
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

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

  • 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:

  1. Error Messages: Check the Status column for specific error messages indicating what went wrong (e.g., invalid API key, missing required data).

  2. 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.

The flow is now finished 🔥


💡 Note: This feature is only available on our Scale plans.

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, or via email- support@lusha.com.

Did this answer your question?