Overview
This guide walks you through automating contact enrichment in Google Sheets using the Lusha Bulk Person API V2. You’ll be able to send contact data in bulk to Lusha, retrieve detailed information, update your sheet seamlessly, and track the status of each enrichment request in real-time.
How it works?
1. Create a New Google Sheet
Start by creating a new, blank Google Sheet.
You’ll use this sheet to store and enrich contact data with the Lusha API. The required column headers will be added automatically after you complete the setup and refresh the sheet.
2. Add the Script to Google Sheet
Open the Script Editor:
Go to Extensions > Apps Script in your Google Sheets file.
Paste the Script:
In the script editor, paste the provided code below. If there’s any existing code, you can delete it before pasting.
Save the Script:
Click the save icon or press Ctrl+S (Windows) / Cmd+S (Mac) to save the script.
// 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();
// Check and set up input headers if they don't exist
setupInputHeaders();
}
// Function to set up input headers in columns A-F
function setupInputHeaders() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
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
const existingHeaders = sheet.getRange("A1:F1").getValues()[0];
const hasHeaders = existingHeaders.some(header => header !== "");
// If no headers exist, add them
if (!hasHeaders) {
sheet.getRange("A1:F1").setValues([inputHeaders]);
sheet.getRange("A1:F1").setFontWeight("bold");
sheet.getRange("A1:F1").setBackground("#f3f3f3");
// Format the sheet for better usability
sheet.setFrozenRows(1); // Freeze the header row
sheet.autoResizeColumns(1, 6); // Auto-resize columns A-F
}
}
// 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
if (isNaN(rowNumber) || rowNumber < 2) {
ui.alert('Invalid row number. Please enter a number greater than or equal to 2.');
return;
}
// Call the enrichment function with the specific row number
startEnrichment(true, rowNumber);
}
}
// Function to show a progress modal dialog
function showProgressDialog() {
// Create a unique key for this execution session
const sessionKey = 'lusha_progress_' + new Date().getTime();
// Store initial progress in PropertiesService (0%)
PropertiesService.getScriptProperties().setProperty(sessionKey, JSON.stringify({
current: 0,
total: 100,
message: 'Initializing...',
stage: 'init'
}));
const htmlOutput = HtmlService.createHtmlOutput(`
<html>
<head>
<style>
body {
font-family: Arial, sans-serif;
padding: 20px;
text-align: center;
}
.progress-container {
margin: 20px auto;
width: 100%;
max-width: 400px;
}
.progress-bar {
background-color: #f1f1f1;
border-radius: 5px;
height: 20px;
width: 100%;
position: relative;
}
.progress-fill {
background-color: #4CAF50;
height: 20px;
border-radius: 5px;
width: 0%;
transition: width 0.3s ease;
}
.progress-text {
margin-top: 8px;
font-size: 14px;
}
.stats-container {
margin-top: 15px;
text-align: left;
}
.stats-basic {
margin: 0 auto;
max-width: 350px;
font-size: 13px;
}
.stats-detailed {
margin: 0 auto;
max-width: 350px;
font-size: 13px;
display: none;
}
.button-container {
margin-top: 20px;
}
button {
padding: 8px 16px;
background-color: #4285f4;
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
}
button:hover {
background-color: #3367d6;
}
</style>
</head>
<body>
<h3 id="progress-title">Enriching contacts...</h3>
<div class="progress-container">
<div class="progress-bar">
<div id="progress-fill" class="progress-fill"></div>
</div>
<div id="progress-text" class="progress-text">0%</div>
</div>
<p id="progress-message">Please do not close this dialog. This window will update automatically.</p>
<div id="stats-container" class="stats-container">
<div id="stats-basic" class="stats-basic">
<div><b>Contacts processed:</b> <span id="stats-processed">0</span></div>
<div><b>API batches sent:</b> <span id="stats-batches">0</span></div>
</div>
<div id="stats-detailed" class="stats-detailed">
<div><b>Successfully enriched:</b> <span id="stats-success">0</span></div>
<div><b>No data found:</b> <span id="stats-nodata">0</span></div>
<div><b>Failed:</b> <span id="stats-failed">0</span></div>
</div>
</div>
<div class="button-container">
<button onclick="google.script.host.close()">Close dialog</button>
</div>
<script>
// Store the session key for use in our polling function
const SESSION_KEY = "${sessionKey}";
let lastStage = '';
// Function to update the progress bar
function updateProgress() {
google.script.run
.withSuccessHandler(function(progressData) {
if (progressData) {
const percent = progressData.total > 0 ?
Math.round((progressData.current / progressData.total) * 100) : 0;
document.getElementById('progress-fill').style.width = percent + '%';
document.getElementById('progress-text').innerHTML = percent + '%';
if (progressData.message) {
document.getElementById('progress-message').innerHTML = progressData.message;
}
// Show stats if they exist
if (progressData.stats) {
// Show/hide appropriate stats sections based on stage
if (progressData.stage === 'complete') {
// When complete, show only detailed stats
document.getElementById('stats-basic').style.display = 'none';
document.getElementById('stats-detailed').style.display = 'block';
document.getElementById('stats-success').innerText = progressData.stats.success;
document.getElementById('stats-nodata').innerText = progressData.stats.nodata;
document.getElementById('stats-failed').innerText = progressData.stats.failed;
} else {
// During processing, show only basic stats
document.getElementById('stats-basic').style.display = 'block';
document.getElementById('stats-detailed').style.display = 'none';
document.getElementById('stats-processed').innerText = progressData.stats.processed;
document.getElementById('stats-batches').innerText = progressData.stats.batches;
}
}
// Check if the stage has changed to 'complete'
if (progressData.stage === 'complete' && lastStage !== 'complete') {
document.getElementById('progress-title').innerHTML = 'Enrichment Complete!';
document.getElementById('progress-message').innerHTML = 'You can close this dialog now.';
lastStage = 'complete';
} else if (progressData.stage !== 'complete') {
lastStage = progressData.stage;
// Continue polling if not complete
setTimeout(updateProgress, 2000); // Poll every 2 seconds
}
} else {
// If no data, try again after a delay
setTimeout(updateProgress, 3000);
}
})
.withFailureHandler(function(error) {
console.error('Error updating progress:', error);
// Try again after a delay
setTimeout(updateProgress, 5000);
})
.getProgressData(SESSION_KEY);
}
// Start the polling when the page loads
window.onload = function() {
updateProgress();
};
</script>
</body>
</html>
`)
.setWidth(450)
.setHeight(350)
.setTitle('Processing...');
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Processing...');
// Return the session key so the main function can update progress
return sessionKey;
}
// Function to update progress data
function updateProgress(sessionKey, current, total, message, stage, stats = null, forceUpdate = false) {
const progressData = {
current: current,
total: total,
message: message || '',
stage: stage || 'processing'
};
if (stats) {
progressData.stats = stats;
// Log the stats for debugging
Logger.log("Updating progress with stats - Success: " + stats.success +
", No data: " + stats.nodata +
", Failed: " + stats.failed +
", Total processed: " + stats.processed +
", Batches: " + stats.batches);
}
// Always update the progress data in script properties
PropertiesService.getScriptProperties().setProperty(
sessionKey,
JSON.stringify(progressData)
);
}
// Function that the dialog can call to get current progress
function getProgressData(sessionKey) {
const cached = PropertiesService.getScriptProperties().getProperty(sessionKey);
if (cached) {
return JSON.parse(cached);
}
return null;
}
// 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');
// Show progress dialog and get the session key
const sessionKey = showProgressDialog();
// Create and store enrichment state
const state = {
processAll: processAll,
startRow: customStartRow || 2,
chunkSize: 1000, // Process data in chunks of 1000 rows at a time
batchSize: 100, // Lusha API batch size
sessionKey: sessionKey,
stats: {
processed: 0,
success: 0,
nodata: 0,
failed: 0,
batches: 0
}
};
// Save state
saveState(state);
// 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...");
// IMPORTANT: Get the last row with any data (not just in columns A-F)
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 progress
updateProgress(
state.sessionKey,
state.stats.processed,
lastRow - state.startRow + 1,
`Processing rows ${chunkStart} to ${chunkEnd} (of ${lastRow})...`,
'processing',
state.stats
);
// Process this chunk
processChunk(chunkStart, chunkEnd);
// Update state for next chunk
state.currentChunkStart = chunkEnd + 1;
saveState(state);
// 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'.");
}
// Ensure headers are set correctly
ensureHeaders(sheet);
// 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;
// DEBUG: Log the row being checked
Logger.log("Checking row " + rowIndex + ": " + JSON.stringify(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 progress to show what we're processing
updateProgress(
state.sessionKey,
state.stats.processed,
sheet.getLastRow() - state.startRow + 1,
`Processed ${state.stats.processed} contacts (${state.stats.batches} batches)...`,
'processing',
state.stats,
true // Force update
);
// Process the batch
processBatch(batchContacts, API_KEY);
// Save state after each batch
saveState(state);
}
}
// Function to ensure headers are set
function ensureHeaders(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(1, 7, 1, outputHeaders.length).getValues()[0];
if (existingHeaders.some((header, i) => header !== outputHeaders[i])) {
sheet.getRange(1, 7, 1, outputHeaders.length).setValues([outputHeaders]);
sheet.getRange(1, 7, 1, outputHeaders.length).setFontWeight("bold");
}
}
// 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 responseData = JSON.parse(response.getContentText());
// Prepare data for batch updates to the sheet
const updateData = [];
const statusUpdates = [];
const noteCells = [];
// Process each contact in the response
batchData.forEach(contact => {
const rowIndex = parseInt(contact.contactId);
const contactResponse = responseData.contacts[contact.contactId];
if (statusCode === 201 && contactResponse && !contactResponse.error) {
// Success case
const contactData = contactResponse.data || {};
const companyData = responseData.companies[contactData.companyId] || {};
// Prepare data for this row
const fullDescription = companyData.description || "";
const truncatedDescription = fullDescription.length > 200 ?
fullDescription.substring(0, 200) + "..." : fullDescription;
// Add to note cells if there's a description
if (fullDescription) {
noteCells.push({
row: rowIndex,
column: 40, // Company Description column
note: fullDescription
});
}
// Prepare row data
const rowData = [
"Success", // Status
contactResponse.isCreditCharged || 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 || false,
contactData.phoneNumbers?.[1]?.number || "", contactData.phoneNumbers?.[1]?.phoneType || "", contactData.phoneNumbers?.[1]?.doNotCall || false,
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 || "", contactData.jobTitle?.title || "", contactData.jobTitle?.departments?.join(", ") || "",
contactData.jobTitle?.seniority || "", contactData.socialLinks?.linkedin || "",
companyData.name || "", truncatedDescription, 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 || "", 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
const errorMsg = contactResponse?.error?.message || "Failed";
const emptyRow = Array(65).fill(""); // Empty row for all columns
emptyRow[0] = errorMsg; // Set status message
updateData.push({
row: rowIndex,
data: [errorMsg, ...emptyRow.slice(1)]
});
// Check if this is a "no data" message
if (errorMsg === "Could not find requested data") {
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, noteCells);
// Save the updated state
saveState(state);
} catch (error) {
// Handle connection errors
contacts.forEach(contact => {
const rowIndex = contact.rowIndex;
sheet.getRange(rowIndex, 7).setValue(`Failed: ${error.message}`);
sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
state.stats.failed++;
});
saveState(state);
}
}
// Function to perform batch updates to the sheet
function batchUpdateSheet(sheet, rowUpdates, statusUpdates, noteCells) {
// 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 => {
sheet.getRange(rowIndex, 7, 1, rowGroups[rowIndex].length).setValues([rowGroups[rowIndex]]);
});
}
// Update status colors
statusUpdates.forEach(update => {
sheet.getRange(update.row, 7).setFontColor(update.color);
});
// Set notes
noteCells.forEach(noteCell => {
sheet.getRange(noteCell.row, noteCell.column).setNote(noteCell.note);
});
}
// Function to complete the processing
function completeProcessing(state) {
// Get accurate final statistics from the sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get all status values from column G (excluding header)
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() > 1) {
try {
const statusRange = sheet.getRange(2, 7, sheet.getLastRow() - 1, 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);
// Apply text wrapping to the Company Description column
sheet.getRange("AN2:AN" + sheet.getLastRow()).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
} catch (e) {
// Ignore formatting errors
Logger.log('Error counting final statistics: ' + e);
}
}
// Update progress to complete with accurate statistics
updateProgress(
state.sessionKey,
100,
100,
'Processing complete!',
'complete',
finalStats,
true
);
// 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)
}
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:
Error Messages: Check the Status column for specific error messages indicating what went wrong (e.g., invalid API key, missing required data).
Invalid JSON Response: Ensure your API key is correctly entered under Project Settings and that the data in Columns A to F matches the expected structure.
Automate with Triggers (Optional)
To keep your contact data continuously updated, you can set up a time-driven trigger in Google Apps Script:
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.
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.