Overview
This guide will help you enrich your company data directly in Google Sheets using the Lusha Company Enrichment API. You'll be able to send requests to Lusha to retrieve detailed company information, automatically update your sheet, and track which companies have been enriched or failed.
How it works?
1. Add the Script to Google Sheets
Open a new Google Sheets
Go to Extensions > Apps Script.
Delete any existing code and replace it with the following script:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ensureHeaders(sheet); // Ensure headers are set
ui.createMenu('Company Enrichment')
.addItem('Enrich All Companies', 'runEnrichmentAllCompanies')
.addItem('Enrich New/Failed Companies', 'runEnrichmentNewCompanies')
.addToUi();
}
function ensureHeaders(sheet) {
const expectedHeaders = [
"Company Name", "Company Domain", "Status", "Company ID", "Data Update",
"Company Name", "Company FQDN", "Website", "Founded", "Company Size",
"Specialties", "Categories", "Intent", "Technologies", "Funding",
"Revenue Range", "Company LinkedIn", "CrunchBase", "Main Industry",
"Sub Industry", "Address", "Full Location", "Country", "City", "State",
"State Code", "Country ISO2", "Description", "Status Code", "Message",
"Full Message"
];
const firstRow = sheet.getRange(1, 1, 1, expectedHeaders.length);
const existingHeaders = firstRow.getValues()[0];
if (!existingHeaders.every((val, i) => val === expectedHeaders[i])) {
firstRow.setValues([expectedHeaders]); // Set headers
sheet.setFrozenRows(1); // Freeze the first row
}
}
function runEnrichmentAllCompanies() {
runEnrichmentCompanies(true); // Enrich all companies
}
function runEnrichmentNewCompanies() {
runEnrichmentCompanies(false); // Enrich only new or failed companies
}
function runEnrichmentCompanies(all) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const apiKey = PropertiesService.getScriptProperties().getProperty('api_key');
if (!apiKey) {
Logger.log('API Key not set. Please set it in Script Properties.');
return;
}
let companiesToEnrich = [];
for (let i = 1; i < data.length; i++) {
const status = data[i][2];
const companyName = data[i][0];
const companyDomain = data[i][1];
if (all || (!status || status === "Failed")) {
if (companyName || companyDomain) {
companiesToEnrich.push({
id: i.toString(),
index: i,
name: companyName,
domain: companyDomain
});
}
}
}
if (companiesToEnrich.length > 0) {
processBatches(companiesToEnrich, sheet, apiKey);
}
}
function processBatches(companies, sheet, apiKey) {
const batchSize = 100;
const totalBatches = Math.ceil(companies.length / batchSize);
for (let batchIndex = 0; batchIndex < totalBatches; batchIndex++) {
const batch = companies.slice(batchIndex * batchSize, (batchIndex + 1) * batchSize);
processBulkCompanies(batch, sheet, apiKey);
}
}
function parseFoundedDate(dateValue) {
return typeof dateValue === 'string' ? dateValue : 'N/A';
}
function processBulkCompanies(batch, sheet, apiKey) {
const url = 'https://api.lusha.com/bulk/company';
const payload = {
companies: batch.map(company => ({
id: company.id,
...(company.name ? { name: company.name } : {}),
...(company.domain ? { domain: company.domain } : {}),
}))
};
const options = {
method: 'post',
headers: {
"Content-Type": "application/json",
"api_key": apiKey
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(url, options);
const rawResponse = response.getContentText();
const statusCode = response.getResponseCode();
Logger.log(`Batch API Response Status Code: ${statusCode}`);
Logger.log(`Batch API Response Content: ${rawResponse}`);
let results;
try {
results = JSON.parse(rawResponse);
} catch (e) {
batch.forEach(company => {
sheet.getRange(company.index + 1, 3).setValue("Failed");
sheet.getRange(company.index + 1, 17).setValue('Failed to parse response');
sheet.getRange(company.index + 1, 29).setValue(statusCode); // Column AC: Status Code
sheet.getRange(company.index + 1, 30).setValue('Parse error'); // Column AD: Status Message
});
return;
}
batch.forEach(company => {
const result = results[company.id];
const companyRow = company.index;
let statusMessage = 'Unknown error';
let isValidResponse = result && typeof result === 'object' && Object.keys(result).length > 0 &&
(result.name && result.name !== "EMPTY_DATA" && result.name !== "N/A") ||
(result.fqdn && result.fqdn !== "N/A") ||
(result.website && result.website !== "N/A") ||
(result.companySize && result.companySize.length > 0) ||
(result.industryTags && result.industryTags.length > 0);
switch (statusCode) {
case 200:
statusMessage = 'OK – Successful request';
break;
case 201:
statusMessage = 'Created – The request has been fulfilled and a new resource has been created';
break;
case 400:
statusMessage = 'Bad request';
break;
case 401:
statusMessage = 'Unauthorized – Invalid API key';
break;
case 403:
statusMessage = 'Unauthorized – Account inactive';
break;
case 404:
statusMessage = 'Not found – Endpoint not found';
break;
case 412:
statusMessage = 'Invalid Syntax';
break;
case 429:
statusMessage = 'Limit reached – Contact support';
break;
case 451:
statusMessage = 'Unprocessable – GDPR restrictions';
break;
case 499:
statusMessage = 'Timeout – Request timeout';
break;
default:
if (statusCode >= 500) {
statusMessage = 'Server error – Issue on Lusha’s end';
}
}
if (!isValidResponse) {
sheet.getRange(companyRow + 1, 3)
.setValue("Failed 😢")
.setBackground("#FFCCCC"); // Light Red for Failed
sheet.getRange(companyRow + 1, 17).setValue('No valid data returned'); // Column Q: Full Message
} else {
sheet.getRange(companyRow + 1, 3)
.setValue("Enriched 🔥")
.setBackground("#CCFFCC"); // Light Green for Enriched
}
sheet.getRange(companyRow + 1, 29).setValue(statusCode || 'N/A'); // Column AC: Status Code
sheet.getRange(companyRow + 1, 30).setValue(statusMessage || 'N/A'); // Column AD: Status Message
const enrichedData = [
String(result?.id) || 'N/A', // Column D: Company ID as text
new Date(), // Column E: Data Update
result?.name || 'N/A', // Column F: Company Name
result?.fqdn || 'N/A', // Column G: Company FQDN
result?.website || 'N/A', // Column H: Website
parseFoundedDate(result?.founded), // Column I: Founded
result?.companySize?.join(' - ') || 'N/A', // Column J: Company Size
(result?.specialities || []).join(', ') || 'N/A', // Column K: Specialties
(result?.industryTags || []).join(', ') || 'N/A', // Column L: Categories
(result?.intent?.detectedTopics?.map(t => t.topicName).join(', ') || 'N/A'), // Column M: Intent Topics
(result?.technologies || []).map(t => t.name).join(', ') || 'N/A', // Column N: Technologies
formatFundingData(result?.funding), // Column O: Formatted Funding
result?.revenueRange?.join(' - ') || 'N/A', // Column P: Revenue Range
result?.linkedin || 'N/A', // Column Q: LinkedIn URL
result?.crunchbase || 'N/A', // Column R: Crunchbase URL
result?.industryTags?.[0] || 'N/A', // Column S: Main Industry
result?.industryTags?.[1] || 'N/A', // Column T: Sub Industry
result?.address || result?.rawLocation || 'N/A', // Column U: Address
result?.rawLocation || 'N/A', // Column V: Full Location
result?.country || 'N/A', // Column W: Country
result?.city || 'N/A', // Column X: City
result?.state || 'N/A', // Column Y: State
result?.stateCode || 'N/A', // Column Z: State Code
result?.countryIso2 || 'N/A', // Column AA: Country ISO2
result?.description || 'N/A' // Column AB: Description
];
sheet.getRange(companyRow + 1, 4, 1, enrichedData.length).setValues([enrichedData]);
});
} catch (error) {
batch.forEach(company => {
sheet.getRange(company.index + 1, 3).setValue("Failed");
sheet.getRange(company.index + 1, 17).setValue(error.message);
sheet.getRange(company.index + 1, 29).setValue('N/A'); // Column AC
sheet.getRange(company.index + 1, 30).setValue('Error fetching data'); // Column AD
});
}
}
function formatFundingData(funding) {
if (!funding || !funding.rounds) return 'N/A';
funding.rounds.sort((a, b) => new Date(a.roundDate) - new Date(b.roundDate));
const totalRounds = funding.totalRounds || 'N/A';
const totalAmount = funding.totalRoundsAmount ? funding.totalRoundsAmount.toLocaleString('en-US') : 'N/A';
const currency = funding.currency || '';
const isIpo = funding.isIpo ? 'IPO' : 'Private';
let fundingInfo = `Total Rounds: ${totalRounds}, Total Amount: ${currency} ${totalAmount}, IPO Status: ${isIpo}\n`;
funding.rounds.forEach((round, index) => {
const roundType = round.roundType || 'Unknown Type';
const roundAmount = round.roundAmount ? round.roundAmount.toLocaleString('en-US') : 'N/A';
const roundDate = round.roundDate || 'Unknown Date';
fundingInfo += `${index + 1}. Round Type: ${roundType}, Amount: ${currency} ${roundAmount}, Date: ${roundDate}\n`;
});
return fundingInfo.trim();
}2. Customize and Run
This script has an action to fetch the API key as a property. In order to add your API key as a property, head to Project Settings, under Script Properties, add the property api_key and under the Value, paste your actual API key.
To match a company in Lusha’s database using the Company API, you need to populate either the company name (column A) or company domain (column B)
Run the script by selecting the desired option from the "Company Enrichment" menu in Google Sheets.
3. Review and Analyze Results
The script will automatically populate the sheet with enriched company information, such as industry, location, and website details. The "Data Update" column will be updated with the timestamp of the enrichment, and the status will indicate whether the enrichment was successful or failed.
Troubleshooting
If you encounter any issues:
Error Messages: Check the "Status Code" and "Message" columns for details on what went wrong during the enrichment process.
Invalid JSON Response: Ensure your API key is correctly entered and that the data formatting in your sheet matches the expected structure.
Automating with Triggers (Optional)
To automate the enrichment process, you can set up a time-driven trigger in Google Apps Script to run the script at regular intervals (e.g., daily). This way, your company data will stay up-to-date with minimal 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.