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. Create a Google Sheets Template
Go to Google Sheets and log in with your Google account.
Upload the attached Excel at the bottom of the article to Google Sheets.
Open the File Picker:
Click on the folder icon near the top-right corner labeled "Open file picker".
Upload the Excel File:
In the dialog that appears, go to the "Upload" tab.
Drag your Excel file into the box or click "Browse" to select the file from your computer.
The sheet should have columns for Company Name, Company Domain, Status, Data Update, and others as needed.
2. Add the Script to Google Sheets
Open the Google Sheets file you copied.
Go to Extensions > Apps Script.
Delete any existing code and replace it with the following script:
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Company Enrichment')
.addItem('Enrich All Companies', 'runEnrichmentAllCompanies')
.addItem('Enrich New/Failed Companies', 'runEnrichmentNewCompanies')
.addToUi();
}
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 = false;
if (result && (result.name || result.fqdn || result.website)) {
isValidResponse = true;
}
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"); // Column C: Update status to "Failed"
sheet.getRange(companyRow + 1, 17).setValue('No valid data returned'); // Column Q: Full Message
} else {
sheet.getRange(companyRow + 1, 3).setValue("Enriched"); // Column C: Set status to "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();
}3. 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.
Run the script by selecting the desired option from the "Company Enrichment" menu in Google Sheets.
4. 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.