Automate Company Search and Enrich

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 SHEET_NAME = "Sheet1"; // Update this with your actual sheet name
const API_KEY = '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 Companies', 'populateCompanies')
.addItem('Enrich Companies', 'enrichCompanies')
.addToUi();
}

function getHeaders() {
return { 
  'api_key': API_KEY,
  'x-partner-name': 'prtnr-google_sheets_connector-prod'
};
}

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 = ['Account Name', 'Website', 'Company ID', 'Description', 'Employee Size', 'Main Industry', 'Sub Industry', 'Linkedin URL', 'Revenue', 'Founded (year)', 'Country', 'Country ISO2', 'City', 'State', 'Full Address', 'Continent', 'Specialities', 'SICS Code', 'SICS Description', 'NAICS Code', 'NAICS Description', 'Industry Tags'];
const headerRange = sheet.getRange("A4:V4");
if (!headerRange.getValues().flat().some(h => h)) {
headerRange.setValues([headers]);
}

// Add "Prospecting Page" label to A1 and set it to italic
const labelCell = sheet.getRange("A1");
labelCell.setValue("Prospecting Page");
labelCell.setFontStyle("italic");

// Set font weight to bold for the header row
headerRange.setFontWeight("bold");

// Apply full borders to the Account Name column (Column A)
const accountNameRange = sheet.getRange(5, 1, sheet.getLastRow() - 3, 1); // From row 5, column 1, covering all rows below
accountNameRange.setBorder(true, true, true, true, true, true); // Apply full borders
}

function populateCompanies() {
const apiUrl = `${BASE_URL}/company/search/`;
const sheet = getSheet();
const userProperties = PropertiesService.getUserProperties();
setupHeaders(sheet);

// Pagination handling
const pageCell = sheet.getRange("A2");
let currentPage = Number(pageCell.getValue()) || 0;

const payload = {
pages: { page: currentPage, 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: getHeaders(),
payload: JSON.stringify(payload),
muteHttpExceptions: true
};

try {
const response = UrlFetchApp.fetch(apiUrl, options);
const result = JSON.parse(response.getContentText());

if (result.requestId) {
userProperties.setProperty('requestId', result.requestId);
}

if (result.data && Array.isArray(result.data)) {
const companyIds = result.data.map(company => String(company.id));
userProperties.setProperty('companyIdsList', JSON.stringify(companyIds));
appendToSheet(sheet, result.data);
pageCell.setValue(currentPage + 1);
} else {
Logger.log("No data found in response.");
}
} catch (error) {
Logger.log('Request failed: ' + error.message);
}
}

function appendToSheet(sheet, companies) {
const data = companies.map(company => [
company.name || '', // Account Name
company.fqdn || '', // Website (fqdn from API)
company.mainIndustry || '', // Industry
company.companySize?.employees_in_linkedin || company.employees || '', // Employee Size
company.revenueRange?.[0] || '', // Revenue
company.foundedYear || '', // Founded
company.country || '' // Country
]);

const lastRow = sheet.getLastRow();
const startRow = lastRow < 4 ? 5 : lastRow + 1;
sheet.getRange(startRow, 1, data.length, data[0].length).setValues(data);

// Apply full borders to the Account Name column (Column A)
const accountNameRange = sheet.getRange(5, 1, sheet.getLastRow() - 3, 1); // From row 4 to the last row in column A
accountNameRange.setBorder(true, true, true, true, true, true); // Apply full borders

Logger.log("Companies appended to the sheet.");
}

function enrichCompanies() {
const apiUrl = `${BASE_URL}/company/enrich`;
const sheet = getSheet();
const userProperties = PropertiesService.getUserProperties();

const requestId = userProperties.getProperty('requestId');
const companyIdsList = JSON.parse(userProperties.getProperty('companyIdsList') || '[]');

if (!requestId || companyIdsList.length === 0) {
Logger.log("No requestId or company IDs available for enrichment.");
return;
}

const lastDataRow = sheet.getLastRow();
const startRow = lastDataRow - companyIdsList.length + 1;

companyIdsList.forEach((companyId, i) => {
const row = startRow + i;
const payload = {
requestId: requestId,
companiesIds: [companyId]
};

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());

if (result.companies && result.companies.length > 0) {
const companyData = result.companies[0];
const enrichedRow = [
companyData.fqdn || '', // Website
companyData.id || '', // Company ID
companyData.description || '', // Description
companyData.employees || '', // Employees
companyData.mainIndustry || '', // Main Industry
companyData.subIndustry || '', // Sub Industry
companyData.social?.linkedin || '', // LinkedIn URL
companyData.revenueRange?.[0] || '', // Revenue Range
companyData.founded || '', // Founded Year
companyData.country || '', // Country
companyData.countryIso2 || '', // Country ISO 2
companyData.city || '', // City
companyData.state || '', // State
companyData.rawLocation || '', // Raw Location
companyData.continent || '', // Continent
companyData.specialities || '', // Specialties
// Extract NAICS and SICS
companyData.industryPrimaryGroupDetails?.sics?.[0]?.sic || '', // SICS Code
companyData.industryPrimaryGroupDetails?.sics?.[0]?.sic_description || '', // SICS Description
companyData.industryPrimaryGroupDetails?.naics?.[0]?.naics || '', // NAICS Code
companyData.industryPrimaryGroupDetails?.naics?.[0]?.naics_label || '', // NAICS Description
companyData.industryTags || '', // Tags
];

sheet.getRange(row, 2, 1, enrichedRow.length).setValues([enrichedRow]);
}
} catch (error) {
Logger.log(`Enrichment failed for companyId ${companyId}: ${error.message}`);
}
});

Logger.log("Enrichment completed.");
}

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

Data Fields Returned

The script populates your sheet with comprehensive company information including: Basic Company Information:

Account Name, Website, Company ID
Description, Employee Size
Founded year, Revenue information

Industry Classification:

Main Industry, Sub Industry
SICS Code & Description
NAICS Code & Description
Industry Tags

Location Data:

Country, Country ISO2, City, State
Full Address, Continent
Geographic coordinates

Business Intelligence:

LinkedIn URL, Specialties
Revenue Range, Funding information
Employee count and company size

Search & Tracking:

Request ID for batch operations
Page tracking for search results
Enrichment status indicators

Workflow Process

1. Search Phase

Configure filters for your target market (location, industry, size)
Execute “Search Companies” to populate initial company list
Results are displayed with basic company information

2. Enrichment Phase

Use “Enrich Companies” to get detailed data for all found companies
Each company is enriched individually with comprehensive business data
Progress tracking through automated logging

3. Analysis Phase

Review enriched data across 22+ data fields
Analyze company patterns and opportunities
Export or further process the enriched dataset

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

Additional Resources

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
Via email: support@lusha.com

​Overview

​How it Works?

​Step 1: Create a Google Sheets Template

​Step 2: Add the Script to Google Sheets

​Step 3: Customize and Run

​Step 4: Review and Analyze Results

​Data Fields Returned

​Workflow Process

​1. Search Phase

​2. Enrichment Phase

​3. Analysis Phase

​Troubleshooting

​Additional Resources