Overview
This guide will walk you through automating your prospecting process by searching and enriching company data directly in Google Sheets using the Lusha’s Prospecting API. With this setup, you’ll be able to populate a list of companies based on specific filters, retrieve detailed company data, and track enrichment progress efficiently.
How it works?
Step 1: Create a Google Sheets Template
Start by making a new sheet on Google Sheets.
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 sheetName = "Sheet1"; // Update this with your actual sheet name
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Lusha Actions')
.addItem('Search Companies', 'populateCompanies')
.addItem('Enrich Companies', 'enrichCompanies')
.addToUi();
}
function populateCompanies() {
const apiKey = 'API key here'; // Replace with your API key
const apiUrl = 'https://api.lusha.com/prospecting/company/search/';
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const userProperties = PropertiesService.getUserProperties(); // Access User Properties
if (!sheet) {
Logger.log(`Sheet with name '${sheetName}' not found.`);
return;
}
// Set header if not present
if (sheet.getLastRow() < 3) {
const headers = ['Account Name', 'Website', 'Industry', 'Employee Size', 'Revenue', 'Founded', 'Country'];
sheet.getRange("A4:G4").setValues([headers]);
}
// Set page number in A2 for tracking API pagination
const pageCell = sheet.getRange("A2");
let currentPage = pageCell.getValue();
if (!currentPage) {
currentPage = 0; // Start from page 0 if not set
}
const payload = {
pages: { page: 0, size: 40 },
filters: {
companies: {
include: {
locations: [{ country: "Mexico" }],
mainIndustriesIds: [18, 19, 20, 12, 13, 3, 16, 8],
sizes: [{ min: 51 }]
}
}
}
};
const options = {
method: 'POST',
contentType: 'application/json',
headers: { 'api_key': apiKey },
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());
// Store the requestId and companyIds in User Properties
if (result.requestId) {
Logger.log("Captured requestId: " + result.requestId);
userProperties.setProperty('requestId', result.requestId); // Store requestId in User Properties
} else {
Logger.log("No requestId found in response: " + JSON.stringify(result));
}
// Process and store company IDs in User Properties
if (result.data && Array.isArray(result.data) && result.data.length > 0) {
const companyIdsList = result.data.map(company => String(company.id)); // Store company IDs as strings
userProperties.setProperty('companyIdsList', JSON.stringify(companyIdsList)); // Store company IDs in User Properties
appendToSheet(result.data);
pageCell.setValue(currentPage + 1); // Increment page number for next request
} else {
Logger.log("No data found in response or data is empty. Full response: " + JSON.stringify(result));
}
} catch (error) {
Logger.log('Request failed: ' + error.message);
}
}
function appendToSheet(companies) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
Logger.log(`Sheet with name '${sheetName}' not found.`);
return;
}
const lastRow = sheet.getLastRow() + 1; // Find the next empty row to start appending
companies.forEach((company, index) => {
const row = [
company.name || '',
company.fqdn || ''
];
sheet.getRange(lastRow + index, 1, 1, 2).setValues([row]); // Append each company starting at the last empty row
});
Logger.log("Completed appending companies to sheet.");
}
function enrichCompanies() {
const apiKey = 'API key here'; // Replace with your API key
const apiUrl = 'https://api.lusha.com/prospecting/company/enrich';
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const userProperties = PropertiesService.getUserProperties(); // Access User Properties
// Retrieve requestId and companyIdsList from User Properties
const requestId = userProperties.getProperty('requestId');
const companyIdsList = JSON.parse(userProperties.getProperty('companyIdsList') || '[]');
if (!requestId) {
Logger.log("No requestId available for enrichment.");
return;
}
if (companyIdsList.length === 0) {
Logger.log("No company IDs available for enrichment.");
return;
}
Logger.log("Using requestId for enrichment: " + requestId);
// Find the starting row for the latest set of companies
const lastDataRow = sheet.getLastRow();
const startingRow = lastDataRow - companyIdsList.length + 1; // Calculate where the latest companies were added
for (let i = 0; i < companyIdsList.length; i++) {
const companyId = companyIdsList[i];
const row = startingRow + i; // Align each company enrichment to its original row
Logger.log(`Row ${row} - Using requestId: ${requestId} and companyId: ${companyId}`);
if (requestId && companyId) {
const payload = {
requestId: requestId,
companiesIds: [companyId] // Ensure companiesIds is an array of strings
};
Logger.log(`Making enrichment request with payload: ${JSON.stringify(payload)}`);
const options = {
method: 'POST',
contentType: 'application/json',
headers: { 'api_key': apiKey },
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());
Logger.log(`Enrichment response: ${JSON.stringify(result)}`);
if (result.companies && result.companies.length > 0) {
const companyData = result.companies[0]; // Get first company data from response
const enrichedRow = [
companyData.mainIndustry || '',
companyData.companySize?.employees_in_linkedin || companyData.employees || '',
companyData.revenueRange?.[0] || '',
'', // Placeholder if founded not available
companyData.country || ''
];
sheet.getRange(row, 3, 1, 5).setValues([enrichedRow]);
Logger.log(`Enriched data added for companyId ${companyId}: ${JSON.stringify(enrichedRow)}`);
} else {
Logger.log(`No enrichment data available for companyId ${companyId}. Full response: ${JSON.stringify(result}`);
}
} catch (error) {
Logger.log(`Request failed for companyId ${companyId}: ${error.message}`);
}
} else {
Logger.log(`Skipping row ${row}: Missing requestId or companyId.`);
}
}
}
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:
Country: Currently set to "Mexico".
Industries: Set by their IDs (e.g., [18, 19, 20, 12, 13, 3, 16, 8]).
Company Size: Minimum size set to 51 employees.
You can modify these filters to match your prospecting needs:
In the script, locate the payload object in the populateCompanies() function.
Adjust the following fields:
locations: Change the country to target other regions.
mainIndustriesIds: Update the industry IDs based on your focus. Refer to the Lusha API documentation for available industry IDs.
sizes: Modify the min field to set the minimum company size.
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 Companies” to populate the sheet with companies matching your filters.
Use “Enrich Companies” to retrieve detailed data for the listed companies.
Troubleshooting
If issues arise during the process:
Error Messages: Check the Status Code and Message columns for details on failures (e.g., invalid Company name, 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.
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.