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?
Create a New Google Sheet: Start by setting up a Google Sheets template to store your contact data. Your sheet should contain Columns A to F with the following headers:
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 allows you to enter essential contact information in a format easily recognized by the script for enrichment.
Minimum Requirements: Each row of contact information should contain at least one of the following combinations:
Full Name (First Name + Last Name) and Company (Name or Domain)
Email Address
LinkedIn URL
Ensuring each contact meets one of these combinations is crucial for successful enrichment with Lusha’s API.
2. Add the Script to Google Sheets
Open the Script Editor: Go to Extensions > Apps Script in your Google Sheets file.
Replace Existing Code: Delete any existing code, then paste the provided script (see below).
Save the Script: Click the save icon or press Ctrl+S (Windows) or 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 New/Failed Contacts', 'enrichNewOrFailedContacts')
.addToUi();
}
// Trigger to enrich all contacts
function enrichAllContacts() {
fetchLushaDataWithBatchProcessing(true); // Pass true to process all rows
}
// Trigger to enrich only new or failed contacts
function enrichNewOrFailedContacts() {
fetchLushaDataWithBatchProcessing(false); // Pass false to process only new/failed rows
}
// Main function to fetch Lusha data with batch processing
function fetchLushaDataWithBatchProcessing(processAll) {
const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
const url = 'https://api.lusha.com/v2/person';
if (!API_KEY) {
throw new Error("API key not found in Script Properties. Please set it under Project Settings with the key 'api_key'.");
}
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const startRow = 2; // Assuming headers are in row 1
const dataRange = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow + 1, 6); // Columns A-F
const data = dataRange.getValues();
// Define headers for the output data in the spreadsheet starting from Column G
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"
];
const headerRange = sheet.getRange(1, 7, 1, outputHeaders.length);
headerRange.setValues([outputHeaders]);
headerRange.setFontWeight("bold");
const batchSize = 100; // Set the batch size limit
let contactsBatch = [];
let rowMap = new Map(); // Map to track the original row for each contact in the batch
data.forEach((row, index) => {
const rowIndex = startRow + index;
const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = row;
// Skip processing if the row already has a successful status and we are not processing all rows
const status = sheet.getRange(rowIndex, 7).getValue(); // Column G - Status
if (!processAll && status === "Success") return;
// Construct the fullName by combining first and last name if available
const fullName = [firstName, lastName].filter(Boolean).join(" ");
// Determine the companies array based on the availability of companyName or companyDomain
const companies = (companyName || companyDomain)
? [{ name: companyName || "", domain: companyDomain || "", isCurrent: true }]
: [];
// Construct the contact payload based on the available data
let contact = {
contactId: String(rowIndex), // Row index as contactId
linkedinUrl: linkedinUrl || "", // LinkedIn URL or empty string
email: emailAddress || "", // Email or empty string
companies: companies // Array with company info or empty array
};
// If only email is provided, remove fullName; otherwise, include it if available
if (!(firstName || lastName) && emailAddress) {
delete contact.fullName;
} else {
contact.fullName = fullName || "";
}
// Check if contact meets the API criteria
if (linkedinUrl || (fullName && (companyName || companyDomain)) || emailAddress) {
// Add valid contact to batch and row map
contactsBatch.push(contact);
rowMap.set(contact.contactId, rowIndex);
} else {
// If criteria is not met, mark the status as failed for that specific row
const statusCell = sheet.getRange(rowIndex, 7);
statusCell.setValue("Failed: Missing required fields (LinkedIn URL, Full Name + Company, or Email)");
statusCell.setFontColor("#8B0000"); // Dark red for missing required fields
}
// Process the batch when it reaches the batch size or when we are on the last contact
if (contactsBatch.length === batchSize || index === data.length - 1) {
const requestPayload = { contacts: contactsBatch };
const options = {
method: 'POST',
contentType: 'application/json',
headers: { 'api_key': API_KEY },
muteHttpExceptions: true,
payload: JSON.stringify(requestPayload)
};
try {
const response = UrlFetchApp.fetch(url, options);
const statusCode = response.getResponseCode();
const responseData = JSON.parse(response.getContentText());
// Process each contact in the batch
contactsBatch.forEach(contact => {
const rowIndex = rowMap.get(contact.contactId);
const contactDataResponse = responseData.contacts[contact.contactId];
const statusCell = sheet.getRange(rowIndex, 7); // Status cell
if (statusCode === 201 && contactDataResponse && !contactDataResponse.error) {
statusCell.setValue("Success");
statusCell.setFontColor("#006400"); // Dark green for success
const contactData = contactDataResponse.data || {};
const companyData = responseData.companies[contactData.companyId] || {};
// Truncate the "Company Description" to 200 characters and add as tooltip
const fullDescription = companyData.description || "";
const truncatedDescription = fullDescription.length > 200 ? fullDescription.substring(0, 200) + "..." : fullDescription;
const outputRow = [
contactDataResponse.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 || ""
];
sheet.getRange(rowIndex, 8, 1, outputRow.length).setValues([outputRow]);
// Add full description as a note (tooltip)
sheet.getRange(rowIndex, 40).setNote(fullDescription); // Assuming "Company Description" is column AN (index 40)
} else {
const errorMsg = contactDataResponse?.error?.message || "Failed";
statusCell.setValue(errorMsg);
statusCell.setFontColor("#8B0000"); // Dark red for other failures
}
});
} catch (error) {
contactsBatch.forEach(contact => {
const rowIndex = rowMap.get(contact.contactId);
const statusCell = sheet.getRange(rowIndex, 7);
statusCell.setValue(`Failed: ${error.message}`);
statusCell.setFontColor("#8B0000"); // Dark red for general failures
});
}
// Reset batch arrays after processing
contactsBatch = [];
rowMap.clear();
}
});
// Apply text wrapping to the Company Description column (assuming it’s in column AN)
sheet.getRange("AN2:AN" + sheet.getLastRow()).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
// Set a fixed row height for all rows with data to avoid row expansion
const lastRow = sheet.getLastRow();
sheet.setRowHeightsForced(2, lastRow - 1, 21); // Set to standard row height
}
3. Customize and Run
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 the key api_key and your actual Lusha API key as the value. This keeps your API key secure and out of the main code.
After saving the properties, refresh the Google Sheets page. You’ll now see a new menu option, Lusha Enrichment, with the following actions:
Enrich All Contacts: Runs enrichment on all rows.
Enrich New/Failed Contacts: Runs enrichment only on rows without a “Success” status.
You can now enrich all or specific rows based on their status, making it easy to manage updates efficiently.
Review and Analyze Results
After running the script, Google Sheets will automatically populate with enriched contact information. Here’s what to expect:
Status in Column G: Displays “Success” for successful enrichments or a specific error message if the request failed.
Additional Contact and Company Details: Including job title, location, company information, and more in Columns H onward.
Each row is updated with valuable data points that can improve your contact records and help you verify critical information.
Output Structure:
Status (Column G): Success, Failed, or a specific error message
Contact and Company Details: Columns H onward, including enriched fields such as job title, location, company size, and other data.
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.