Overview
This guide will walk you through automating your prospecting process by searching and enriching contact data directly in Google Sheets using the Lusha’s Prospecting API. With this setup, you’ll be able to populate a list of contacts based on specific filters, retrieve detailed contact data, and track enrichment progress efficiently.
Setup Instructions
Step 1: Create a Google Sheets Template
Start by creating a new Google Sheet.
Name the sheet as "Sheet1" or update the variable sheetName in the script with your sheet name.
Step 2: Add the Script to Google Sheets
Open your copied Google Sheets file.
Navigate to Extensions > Apps Script.
Delete any existing code in the script editor.
Replace it with the provided script:
const SHEET_NAME = "Sheet1"; // Update this with your actual sheet name
const API_KEY = 'YOUR API KEY HERE'; // Replace with your actual API key
const BASE_URL = 'https://api.lusha.com/prospecting';
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Lusha Actions')
.addItem('Search Contacts', 'populateContacts')
.addItem('Enrich Contacts', 'enrichContacts')
.addToUi();
}
function getHeaders() {
return { 'api_key': API_KEY };
}
function getSheet() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
if (!sheet) {
throw new Error(`Sheet with name '${SHEET_NAME}' not found.`);
}
return sheet;
}
function setupHeaders(sheet) {
const headers = [
'First Name', 'Last Name', 'Full Name', 'Job Title', 'Company Name', 'Company Website',
'Has Work Email', 'Has Phones', 'Has Mobile Phone', 'Has Direct Phone', 'Request ID', 'Contact ID',
'isShown', 'Enriched ✅'
];
const headerRange = sheet.getRange("A4:N4");
if (!headerRange.getValues().flat().some(h => h)) {
headerRange.setValues([headers]);
}
headerRange.setFontWeight("bold");
// Add "Prospecting Page" label to A1 and set it to italic
const labelCell = sheet.getRange("A1");
labelCell.setValue("Prospecting Page");
labelCell.setFontStyle("italic");
// Additional headers for enrichment, including phone numbers
const enrichHeaders = [
'LinkedIn URL', 'Department', 'Seniority', 'Email(s)', 'Phone(s)', 'Phone Types'
];
const enrichHeaderRange = sheet.getRange("O4:T4"); // Expanded range to include phone data
if (!enrichHeaderRange.getValues().flat().some(h => h)) {
enrichHeaderRange.setValues([enrichHeaders]);
}
enrichHeaderRange.setFontWeight("bold");
applyConditionalFormatting(sheet);
}
function applyConditionalFormatting(sheet) {
const enrichedColumn = sheet.getRange("N5:N"); // ✅ Column N, starting from row 5
let rules = sheet.getConditionalFormatRules();
// ✅ Remove existing rules for "Enriched" column (avoid duplicates)
rules = rules.filter(rule => rule.getRanges()[0].getA1Notation() !== enrichedColumn.getA1Notation());
// ✅ Add "NO" (Red) rule
const noRule = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("No")
.setBackground("#F5B7B1") // Light Red
.setRanges([enrichedColumn])
.build();
// ✅ Apply new rules
rules.push(noRule);
sheet.setConditionalFormatRules(rules);
}
function populateContacts() {
const apiUrl = `${BASE_URL}/contact/search/`;
const sheet = getSheet();
const userProperties = PropertiesService.getUserProperties();
setupHeaders(sheet);
const pageCell = sheet.getRange("A2");
let currentPage = Number(pageCell.getValue()) || 0; // Default to 0 if empty
const payload = { // UPDATE THEY PAYLOAD TO MATCH YOUR ICP
"pages": { "page": currentPage, "size": 40 },
"filters": {
"companies": {
"include": {
"locations": [{ "state": "New York", "country": "United States" }],
"sizes": [{ "min": 51, "max": 500 }]
}
}
}
};
const options = {
method: 'POST',
contentType: 'application/json',
headers: getHeaders(),
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());
Logger.log(`🔍 Full API Response: ${JSON.stringify(result, null, 2)}`);
if (result.requestId) {
userProperties.setProperty('requestId', result.requestId);
}
if (result.data && Array.isArray(result.data)) {
Logger.log(`📌 Contacts received: ${JSON.stringify(result.data, null, 2)}`);
const contactIds = result.data.map(contact => String(contact.contactId));
userProperties.setProperty('contactIdsList', JSON.stringify(contactIds));
appendContactsToSheet(sheet, result.data);
currentPage++;
pageCell.setValue(currentPage);
} else {
Logger.log("No data found in response.");
}
} catch (error) {
Logger.log(`Request failed: ${error.message}`);
}
}
function appendContactsToSheet(sheet, contacts) {
if (!contacts || contacts.length === 0) {
Logger.log("⚠️ No contacts to append.");
return;
}
const data = contacts.map(contact => [
contact.name?.first || '',
contact.name?.last || '',
contact.name?.full || '',
contact.jobTitle || '',
contact.companyName || '',
contact.fqdn || '',
contact.hasWorkEmail ? 'Yes' : 'No',
contact.hasPhones ? 'Yes' : 'No',
contact.hasMobilePhone ? 'Yes' : 'No',
contact.hasDirectPhone ? 'Yes' : 'No',
PropertiesService.getUserProperties().getProperty('requestId') || '',
contact.contactId || '',
contact.isShown ? 'Yes' : 'No',
'No' // Default "Enriched" column to "No"
]);
const lastRow = sheet.getLastRow();
const startRow = lastRow < 4 ? 5 : lastRow + 1;
try {
sheet.getRange(startRow, 1, data.length, data[0].length).setValues(data);
Logger.log(`✅ Successfully appended ${data.length} contacts.`);
} catch (error) {
Logger.log(`❌ Error appending contacts: ${error.message}`);
}
}
function enrichContacts() {
const apiUrl = `${BASE_URL}/contact/enrich`;
const sheet = getSheet();
const userProperties = PropertiesService.getUserProperties();
const requestId = userProperties.getProperty('requestId');
const contactIdsList = JSON.parse(userProperties.getProperty('contactIdsList') || '[]');
Logger.log(`📌 Stored Request ID: ${requestId}`);
Logger.log(`📌 Stored Contact IDs: ${JSON.stringify(contactIdsList)}`);
if (!requestId || contactIdsList.length === 0) {
Logger.log("❌ No requestId or contact IDs available for enrichment.");
return;
}
const payload = { requestId: requestId, contactIds: contactIdsList };
const options = {
method: 'POST',
contentType: 'application/json',
headers: getHeaders(),
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());
Logger.log(`🔍 API Response: ${JSON.stringify(result, null, 2)}`);
if (result.contacts && Array.isArray(result.contacts)) {
appendEnrichedContactsToSheet(sheet, result.contacts);
Logger.log(`✅ Successfully enriched ${result.contacts.length} contacts.`);
} else {
Logger.log("⚠️ No contacts returned in enrichment response.");
}
} catch (error) {
Logger.log(`❌ Enrichment request failed: ${error.message}`);
}
}
function appendEnrichedContactsToSheet(sheet, enrichedContacts) {
if (!enrichedContacts || enrichedContacts.length === 0) {
Logger.log("⚠️ No enriched contacts to append.");
return;
}
const sheetData = sheet.getDataRange().getValues();
const contactIdColumnIndex = 12; // Column L
const enrichedColumnIndex = 14; // Column N (New "Enriched" column)
let rowMapping = {}; // Stores row numbers for each contactId
for (let i = 4; i < sheetData.length; i++) {
let contactId = String(sheetData[i][contactIdColumnIndex - 1]).trim();
if (contactId) {
rowMapping[contactId] = i + 1;
}
}
enrichedContacts.forEach((contact) => {
const contactId = String(contact.id || contact.contactId || '').trim();
if (!contactId) return;
let rowToUpdate = rowMapping[contactId];
if (!rowToUpdate) {
Logger.log(`❌ Contact ID ${contactId} not found in sheet. Skipping.`);
return;
}
// Extract enrichment fields
const linkedIn = contact.data?.socialLinks?.linkedin || 'N/A';
const department = (contact.data?.departments || []).join(', ') || 'N/A';
const seniority = (contact.data?.seniority || []).map(s => s.value).join(', ') || 'N/A';
const emails = (contact.data?.emailAddresses || []).map(e => e.email).join(', ') || 'N/A';
const phoneNumbers = (contact.data?.phoneNumbers || []).map(p => p.number).join(', ') || 'N/A';
const phoneTypes = (contact.data?.phoneNumbers || []).map(p => p.phoneType).join(', ') || 'N/A';
// ✅ Update the "Enriched" column to "Yes"
sheet.getRange(rowToUpdate, enrichedColumnIndex).setValue("Yes ");
// ✅ Make the cell green immediately
sheet.getRange(rowToUpdate, enrichedColumnIndex).setBackground("#A9DFBF"); // Light Green
// ✅ Ensure we correctly write multiple values in a single row across multiple columns
sheet.getRange(rowToUpdate, 15, 1, 6).setValues([
[linkedIn, department, seniority, emails, phoneNumbers, phoneTypes]
]);
Logger.log(`✅ Updated Row ${rowToUpdate} | Enriched: Yes`);
});
}
// ✅ Function for Clicking "Enrich Row"
function onEnrichRowClick() {
const sheet = getSheet();
const range = sheet.getActiveRange(); // Get selected rows
if (!range) {
Logger.log("❌ No rows selected.");
return;
}
const values = range.getValues();
const contactIdColumnIndex = 12; // Column L (Contact ID)
const contactIds = [];
for (let i = 0; i < values.length; i++) {
const contactId = values[i][contactIdColumnIndex - 1];
if (contactId) {
contactIds.push(String(contactId).trim());
}
}
if (contactIds.length === 0) {
Logger.log("❌ No valid Contact IDs found in selected rows.");
return;
}
Logger.log(`✅ Enriching Contacts: ${JSON.stringify(contactIds)}`);
enrichContactsByIds(contactIds);
}
// ✅ Function to Enrich Only Selected Contacts
function enrichContactsByIds(contactIds) {
const apiUrl = `${BASE_URL}/contact/enrich`;
const sheet = getSheet();
const userProperties = PropertiesService.getUserProperties();
const requestId = userProperties.getProperty('requestId');
if (!requestId || contactIds.length === 0) {
Logger.log("❌ No requestId or valid contact IDs available for enrichment.");
return;
}
const payload = { requestId: requestId, contactIds: contactIds };
const options = {
method: 'POST',
contentType: 'application/json',
headers: getHeaders(),
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());
Logger.log(`🔍 API Response: ${JSON.stringify(result, null, 2)}`);
if (result.contacts && Array.isArray(result.contacts)) {
appendEnrichedContactsToSheet(sheet, result.contacts);
Logger.log(`✅ Successfully enriched ${result.contacts.length} contacts.`);
} else {
Logger.log("⚠️ No contacts returned in enrichment response.");
}
} catch (error) {
Logger.log(`❌ Enrichment request failed: ${error.message}`);
}
}
Step 3: Customize and Run
Make sure to provide your API Key to enable communication with Lusha’s API. You can access your API key here (only admins and managers have access to this page).
Once you have your API key, locate the lines below in your script:
const apiKey = 'YOUR API KEY HERE';
Before running the script, you must configure it to match your prospecting needs. Here’s how to customize the filters and set up your API key. This script template includes pre-configured filters for:
Locations:
Country set to United States.
State set to New York.
Sizes: Set for Min 51 and Max 500.
You can modify these filters to match your prospecting needs by locating the payload object in the script. Refer to the Lusha API documentation for all the available contact and company filters.
Make sure to save the script after applying your changes. These customizations allow the script to tailor the company prospecting process to your requirements while ensuring your API key is securely embedded.
Step 4: Review and Analyze Results
Go back to your Google Sheet.
Open the “Lusha Actions” menu added by the script.
Select “Search Contacts” to populate the sheet with contacts matching your filters.
Use “Enrich Contacts” to retrieve detailed data for the listed contacts.
💡 Note: The “Search” and “Enrich” actions are executed in batches of 40 contacts at a time. For the Enrich function trigger, a credit will be consumed each time the function is activated. If you need the ability to enrich contacts individually per row, please refer to the instructions below.
“Enrich Row” button: The “Enrich Row” button allows you to manually enrich only the selected contact in your Google Sheet. This is useful when you don’t want to enrich the entire batch but just a few rows on demand.
Open your Google Sheet > click on Insert in the top menu > select Drawing.
In the Drawing tool, click on the Shape icon → Select Shapes → Rectangle.
Click inside the rectangle and type “Enrich Row” (or any label you want).
Save and Close.
Click on the button (the shape you just created).
Click on the three dots (⋮) menu in the top-right corner of the button.
Select Assign script.
In the popup box, enter the script function name exactly: onEnrichRowClick
Done! Now, whenever a row is selected, clicking the button will enrich only the selected contact.
Troubleshooting
If issues arise during the process:
Error Messages: Check the Status Code and Message columns for details on failures (e.g., invalid filters, missing API key).
Invalid JSON Response: Verify that your API key is correct and that the sheet data matches the expected input structure.
Rate Limits: If the API returns rate-limit errors, consider spacing out requests or upgrading your API plan.
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.