Skip to content
Last updated

Automate Company Data Enrichment

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

  1. Open a new Google Sheets
  2. Go to Extensions > Apps Script
  3. 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
  setupStatusField(sheet); // Set up the status field
  
  // Count and display initial statistics
  countAndDisplayCurrentStats(sheet);

  ui.createMenu('Company Enrichment')
    .addItem('Enrich All Companies', 'runEnrichmentAllCompanies')
    .addItem('Enrich New/Failed Companies', 'runEnrichmentNewCompanies')
    .addItem('Choose Row Range to Enrich', 'chooseRowRangeToEnrich')
    .addItem('Set Max Row for All/New Enrichment', 'setMaxRowForEnrichment')
    .addToUi();
}

function chooseRowRangeToEnrich() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the total number of rows with data
  const lastRow = sheet.getLastRow();
  
  // Show input dialog for starting row
  const startResponse = ui.prompt(
    'Choose Row Range - Step 1',
    `Enter the STARTING row number (Row 3 to ${lastRow}):\n\nNote: Row 1 contains status info, Row 2 contains headers, so data rows start from Row 3.`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK and entered a value
  if (startResponse.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const startInputText = startResponse.getResponseText().trim();
  const startRowNumber = parseInt(startInputText);
  
  // Validate the start row input
  if (isNaN(startRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number for the starting row.', ui.ButtonSet.OK);
    return;
  }
  
  if (startRowNumber < 3) {
    ui.alert('Invalid Row', 'Please enter a starting row number 3 or higher (rows 1-2 are reserved for headers).', ui.ButtonSet.OK);
    return;
  }
  
  if (startRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter a starting row number between 3 and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Show input dialog for ending row
  const endResponse = ui.prompt(
    'Choose Row Range - Step 2',
    `Enter the ENDING row number (${startRowNumber} to ${lastRow}):\n\nStarting row: ${startRowNumber}\nEnter the last row you want to enrich (inclusive).`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK and entered a value
  if (endResponse.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const endInputText = endResponse.getResponseText().trim();
  const endRowNumber = parseInt(endInputText);
  
  // Validate the end row input
  if (isNaN(endRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number for the ending row.', ui.ButtonSet.OK);
    return;
  }
  
  if (endRowNumber < startRowNumber) {
    ui.alert('Invalid Range', `The ending row (${endRowNumber}) must be greater than or equal to the starting row (${startRowNumber}).`, ui.ButtonSet.OK);
    return;
  }
  
  if (endRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter an ending row number between ${startRowNumber} and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Confirm the action
  const confirmResponse = ui.alert(
    'Confirm Range Enrichment',
    `This will enrich companies from row ${startRowNumber} to row ${endRowNumber} (${endRowNumber - startRowNumber + 1} rows). Continue?`,
    ui.ButtonSet.YES_NO
  );
  
  if (confirmResponse === ui.Button.YES) {
    updateEnrichmentStatus(sheet, 'In Progress...');
    countAndDisplayCurrentStats(sheet);
    runEnrichmentFromRowRange(startRowNumber, endRowNumber);
  }
}

function setMaxRowForEnrichment() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the total number of rows with data
  const lastRow = sheet.getLastRow();
  
  // Show input dialog for max row
  const response = ui.prompt(
    'Set Maximum Row for Enrichment',
    `Enter the MAXIMUM row number to limit enrichment (Row 3 to ${lastRow}):\n\nThis will cap the "Enrich All Companies" and "Enrich New/Failed Companies" options.\nLeave empty to remove any existing limit.`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK
  if (response.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const inputText = response.getResponseText().trim();
  
  // If empty, remove the limit
  if (inputText === '') {
    PropertiesService.getScriptProperties().deleteProperty('max_enrichment_row');
    ui.alert('Limit Removed', 'Maximum row limit has been removed. All future enrichments will process all available rows.', ui.ButtonSet.OK);
    return;
  }
  
  const maxRowNumber = parseInt(inputText);
  
  // Validate the input
  if (isNaN(maxRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number or leave empty to remove the limit.', ui.ButtonSet.OK);
    return;
  }
  
  if (maxRowNumber < 3) {
    ui.alert('Invalid Row', 'Please enter a row number 3 or higher (rows 1-2 are reserved for headers).', ui.ButtonSet.OK);
    return;
  }
  
  if (maxRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter a row number between 3 and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Save the max row setting
  PropertiesService.getScriptProperties().setProperty('max_enrichment_row', maxRowNumber.toString());
  
  // Confirm the setting
  ui.alert('Limit Set', `Maximum enrichment row has been set to ${maxRowNumber}. This limit will apply to "Enrich All Companies" and "Enrich New/Failed Companies" options.`, ui.ButtonSet.OK);
}

function runEnrichmentFromRowRange(startingRow, endingRow) {
  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.');
    updateEnrichmentStatus(sheet, 'Error: API Key Missing');
    return;
  }

  let companiesToEnrich = [];
  // Convert to 0-based indices
  const startIndex = startingRow - 1;
  const endIndex = endingRow - 1;
  
  for (let i = startIndex; i <= endIndex; i++) {
    // Make sure we don't go beyond the data array
    if (i >= data.length) break;
    
    const companyName = data[i][0];
    const companyDomain = data[i][1];

    // Skip empty rows
    if (!companyName && !companyDomain) continue;

    if (companyName || companyDomain) {
      companiesToEnrich.push({
        id: (i - 1).toString(), // Adjust ID to match previous logic
        index: i,
        name: companyName,
        domain: companyDomain
      });
    }
  }

  Logger.log(`Found ${companiesToEnrich.length} companies to enrich from row ${startingRow} to ${endingRow}`);

  if (companiesToEnrich.length > 0) {
    processBatches(companiesToEnrich, sheet, apiKey);
  } else {
    updateEnrichmentStatus(sheet, 'No Companies to Enrich in Range');
  }
}

function setupStatusField(sheet) {
  // Set up the Enrichment Status field in A1 and B1
  sheet.getRange('A1').setValue('Enrichment Status:');
  sheet.getRange('A1').setFontWeight('bold');
  sheet.getRange('B1').setValue('Ready');
  sheet.getRange('B1').setFontWeight('bold');
  sheet.getRange('B1').setBackground('#f3f3f3');
  
  // Set up the Last Updated field in C1 and D1
  sheet.getRange('C1').setValue('Last Updated:');
  sheet.getRange('C1').setFontWeight('bold');
  sheet.getRange('D1').setValue('N/A');
  sheet.getRange('D1').setFontWeight('bold');
  sheet.getRange('D1').setBackground('#f3f3f3');
  
  // Set up the Enrichment Stats fields in E1 through H1
  sheet.getRange('E1').setValue('Enrichment Stats:');
  sheet.getRange('E1').setFontWeight('bold');
  
  sheet.getRange('F1').setValue('Success:0');
  sheet.getRange('F1').setFontWeight('bold');
  sheet.getRange('F1').setBackground('#f3f3f3');
  
  sheet.getRange('G1').setValue('No Data:0');
  sheet.getRange('G1').setFontWeight('bold');
  sheet.getRange('G1').setBackground('#f3f3f3');
  
  sheet.getRange('H1').setValue('Failed:0');
  sheet.getRange('H1').setFontWeight('bold');
  sheet.getRange('H1').setBackground('#f3f3f3');
}

function updateEnrichmentStatus(sheet, status) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in updateEnrichmentStatus");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    // Check if the range exists before setting values
    if (sheet.getRange('B1')) {
      sheet.getRange('B1').setValue(status);
      SpreadsheetApp.flush(); // Force the update to show immediately
    } else {
      Logger.log("Error: Could not find the B1 range");
    }
  } catch (e) {
    Logger.log("Error in updateEnrichmentStatus: " + e.message);
  }
}

// Function to update the statistics in the header row
function updateEnrichmentStats(sheet, successCount, noDataCount, failureCount) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in updateEnrichmentStats");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    // Check if the ranges exist before setting values
    if (sheet.getRange('F1') && sheet.getRange('G1') && sheet.getRange('H1')) {
      sheet.getRange('F1').setValue(`Success:${successCount}`);
      sheet.getRange('G1').setValue(`No Data:${noDataCount}`);
      sheet.getRange('H1').setValue(`Failed:${failureCount}`);
      SpreadsheetApp.flush(); // Force the update to show immediately
    } else {
      Logger.log("Error: Could not find one or more of the required ranges (F1, G1, H1)");
    }
  } catch (e) {
    Logger.log("Error in updateEnrichmentStats: " + e.message);
  }
}

function ensureHeaders(sheet) {
  const expectedHeaders = [
    "Company Name (Input)", "Company Domain (Input)", "Status", "Company ID", "Data Update", 
    "Company Name", "Company FQDN", "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"
  ];
  
  // Clear the first row except for A1-H1 which are used for status and statistics
  const firstRow = sheet.getRange(1, 9, 1, expectedHeaders.length - 8);
  firstRow.clear();
  
  // Set headers in row 2
  const headerRow = sheet.getRange(2, 1, 1, expectedHeaders.length);
  const existingHeaders = headerRow.getValues()[0];

  if (!existingHeaders.every((val, i) => val === expectedHeaders[i])) {
    headerRow.setValues([expectedHeaders]);  // Set headers
  }
  
  // Apply light grey background to row 2
  headerRow.setBackground('#f3f3f3');  // Light grey color
  
  // Make the headers bold for better visibility
  headerRow.setFontWeight('bold');
  
  // Optional: Center-align the headers
  headerRow.setHorizontalAlignment('center');
  
  sheet.setFrozenRows(2);  // Freeze rows 1 and 2
}

function runEnrichmentAllCompanies() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  updateEnrichmentStatus(sheet, 'In Progress...');
  
  // Count current statuses in the sheet first
  countAndDisplayCurrentStats(sheet);
  
  runEnrichmentCompanies(true); // Enrich all companies
}

function runEnrichmentNewCompanies() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  updateEnrichmentStatus(sheet, 'In Progress...');
  
  // Count current statuses in the sheet first
  countAndDisplayCurrentStats(sheet);
  
  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.');
    updateEnrichmentStatus(sheet, 'Error: API Key Missing');
    return;
  }

  // Check if there's a max row limit set
  const maxRowProperty = PropertiesService.getScriptProperties().getProperty('max_enrichment_row');
  const maxRowLimit = maxRowProperty ? parseInt(maxRowProperty) : null;
  const effectiveMaxRow = maxRowLimit ? Math.min(maxRowLimit, data.length - 1) : data.length - 1;

  let companiesToEnrich = [];
  // Start from row 3 (index 2) since row 2 is the header
  for (let i = 2; i <= effectiveMaxRow; i++) {
    const status = data[i][2];
    const companyName = data[i][0];
    const companyDomain = data[i][1];

    // Skip empty rows
    if (!companyName && !companyDomain) continue;

    if (all || (!status || status === "Failed")) {
      if (companyName || companyDomain) {
        companiesToEnrich.push({
          id: (i - 1).toString(), // Adjust ID to match previous logic
          index: i,
          name: companyName,
          domain: companyDomain
        });
      }
    }
  }

  const limitMessage = maxRowLimit ? ` (limited to row ${maxRowLimit})` : '';
  Logger.log(`Found ${companiesToEnrich.length} companies to enrich${limitMessage}`);

  if (companiesToEnrich.length > 0) {
    processBatches(companiesToEnrich, sheet, apiKey);
  } else {
    updateEnrichmentStatus(sheet, 'No Companies to Enrich');
  }
}

function processBatches(companies, sheet, apiKey) {
  const batchSize = 100;
  const totalBatches = Math.ceil(companies.length / batchSize);
  
  // Reset the statistics before starting by counting current status
  countAndDisplayCurrentStats(sheet);

  for (let batchIndex = 0; batchIndex < totalBatches; batchIndex++) {
    const batch = companies.slice(batchIndex * batchSize, (batchIndex + 1) * batchSize);
    const batchNumber = batchIndex + 1;
    updateEnrichmentStatus(sheet, `Processing Batch ${batchNumber}/${totalBatches}...`);
    
    // Process the batch
    processBulkCompanies(batch, sheet, apiKey);
    
    // Update statistics after each batch
    countAndDisplayCurrentStats(sheet);
  }
  
  // Do a final count to ensure accuracy
  countAndDisplayCurrentStats(sheet);
  
  updateEnrichmentStatus(sheet, 'Complete!');
  
  // Update the last updated timestamp
  const currentDateTime = new Date();
  const formattedDateTime = Utilities.formatDate(currentDateTime, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
  sheet.getRange('D1').setValue(formattedDateTime);
}

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, 16).setValue('Failed to parse response');
        sheet.getRange(company.index + 1, 28).setValue(400); // Column AB: Status Code - Bad request
        sheet.getRange(company.index + 1, 29).setValue('Bad request - Badly formatted request'); // Column AC: Status Message
        sheet.getRange(company.index + 1, 30).setValue('Failed to parse API response: ' + e.message); // Column AD: Full Message
      });
      return;
    }

    // Check if results is structured as {"data": {...}} for single company calls
    if (results.data && typeof results.data === 'object' && !Array.isArray(results.data)) {
      // Single company response structure
      const singleResults = results.data;
      // Convert to expected bulk structure
      results = {
        [batch[0].id]: singleResults
      };
    }

    batch.forEach(company => {
      const result = results[company.id];
      const companyRow = company.index;

      let statusMessage = 'Unknown error';
      let fullMessage = '';
      let individualStatusCode = statusCode; // Default to the batch status code
      let isValidResponse = false;
      
      if (result && typeof result === 'object' && Object.keys(result).length > 0) {
        // Check if there's meaningful data
        isValidResponse = (
          (result.name && result.name !== "EMPTY_DATA" && result.name !== "N/A") ||
          (result.fqdn && result.fqdn !== "N/A") ||
          (result.companySize && result.companySize.length > 0) ||
          result.employees ||
          result.industryPrimaryGroupDetails ||
          (result.industryTags && result.industryTags.length > 0)
        );
        
        // Check if we have a specific status code for this company
        if (result.status && typeof result.status === 'number') {
          individualStatusCode = result.status;
        }
        
        // Check for specific error message
        if (result.errorMessage) {
          fullMessage = result.errorMessage;
        }
      }

      // Generate status message based on individual status code
      switch (individualStatusCode) {
        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 - Badly formatted request';
          break;
        case 401:
          statusMessage = 'Unauthorised – The API key is invalid';
          break;
        case 403:
          statusMessage = 'Unauthorised – Your account is not active. Please reach out to support';
          break;
        case 404:
          statusMessage = 'Not found – The requested endpoint was not found';
          break;
        case 412:
          statusMessage = 'The request failed due to invalid syntax';
          break;
        case 429:
          statusMessage = 'Limit reached – You\'ve reached your trial limit, please contact support for upgrade';
          break;
        case 451:
          statusMessage = 'We are unable to process this contact request due to our GDPR regulations';
          break;
        case 499:
          statusMessage = 'Request failed due to request timeout';
          break;
        default:
          if (individualStatusCode >= 500) {
            statusMessage = 'Server error – There\'s a problem on Lusha\'s end';
          }
      }

      if (!isValidResponse) {
        sheet.getRange(company.index + 1, 3).setValue("Failed");
        sheet.getRange(company.index + 1, 16).setValue('No valid data returned'); // Column P: Full Message
        
        // For failed requests with no valid data, use 404 if not already set
        if (individualStatusCode === statusCode && [200, 201].includes(individualStatusCode)) {
          individualStatusCode = 404; // Not found - no data
          statusMessage = 'Not found – No data available for this company';
        }
        
        // If no specific full message, create one
        if (!fullMessage) {
          fullMessage = 'No data available for this company';
        }
      } else {
        sheet.getRange(company.index + 1, 3).setValue("Enriched");
        
        // If no specific full message for success, create one
        if (!fullMessage) {
          fullMessage = 'Successfully enriched';
        }
      }

      sheet.getRange(company.index + 1, 28).setValue(individualStatusCode); // Column AB: Status Code
      sheet.getRange(company.index + 1, 29).setValue(statusMessage); // Column AC: Status Message
      sheet.getRange(company.index + 1, 30).setValue(fullMessage); // Column AD: Full 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
        parseFoundedDate(result?.founded),                       // Column I: Founded
        result?.companySize?.join(' - ') || result?.employees || 'N/A', // Column J: Company Size
        (result?.specialities || []).join(', ') || 'N/A',        // Column K: Specialties
        result?.industryPrimaryGroupDetails?.sics?.map(s => s.description).join(', ') || 'N/A', // Column L: Categories (SIC)
        (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?.social?.linkedin?.url || result?.linkedin || 'N/A', // Column Q: LinkedIn URL
        result?.social?.crunchbase?.url || result?.crunchbase || 'N/A', // Column R: Crunchbase URL
        result?.mainIndustry || 'N/A', // Column S: Main Industry (from SIC)
        result?.subIndustry || 'N/A', // Column T: Sub Industry (from NAICS)
        result?.address || result?.location?.fullLocation || result?.rawLocation || 'N/A', // Column U: Address
        result?.location?.fullLocation || result?.rawLocation || 'N/A', // Column V: Full Location
        result?.location?.country || result?.country || 'N/A',    // Column W: Country
        result?.location?.city || result?.city || 'N/A',          // Column X: City
        result?.location?.state || result?.state || 'N/A',        // Column Y: State
        result?.location?.stateCode || result?.stateCode || 'N/A', // Column Z: State Code
        result?.location?.countryIso2 || result?.countryIso2 || 'N/A', // Column AA: Country ISO2
        result?.description || 'N/A'                             // Column AB: Description
      ];

      sheet.getRange(company.index + 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, 16).setValue(error.message);
      sheet.getRange(company.index + 1, 28).setValue(500); // Column AB: Use 5XX error for server issues
      sheet.getRange(company.index + 1, 29).setValue('Server error – There\'s a problem on Lusha\'s end'); // Column AC
      sheet.getRange(company.index + 1, 30).setValue(error.message); // Column AD: Full Message
    });
    updateEnrichmentStatus(sheet, 'Error Occurred');
  }
}

function parseFoundedDate(dateValue) {
  return typeof dateValue === 'string' ? dateValue : 'N/A';
}

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

// Function to count and display current stats from the sheet
function countAndDisplayCurrentStats(sheet) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in countAndDisplayCurrentStats");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    const data = sheet.getDataRange().getValues();
    let successCount = 0;
    let noDataCount = 0;
    let failureCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < data.length; i++) {
      // Skip empty rows
      if (!data[i][0] && !data[i][1]) continue;
      
      const status = data[i][2]; // Status is in column C (index 2)
      const companyName = data[i][5]; // Company Name is in column F (index 5)
      
      if (status === "Enriched") {
        successCount++;
      } else if (status === "Failed") {
        failureCount++; // Count all failed rows
        
        // If it's an EMPTY_DATA case, also count it as No Data
        if (companyName === "EMPTY_DATA") {
          noDataCount++;
        }
      }
    }
    
    // Update the stats display
    updateEnrichmentStats(sheet, successCount, noDataCount, failureCount);
    
    // Log for debugging
    Logger.log(`Current count - Success: ${successCount}, No Data: ${noDataCount}, Failed: ${failureCount}`);
    return { success: successCount, noData: noDataCount, failure: failureCount };
  } catch (e) {
    Logger.log("Error in countAndDisplayCurrentStats: " + e.message);
    return { success: 0, noData: 0, failure: 0 };
  }
}

function refreshStatistics() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    if (!sheet) {
      Logger.log("Error: Could not get active sheet in refreshStatistics");
      return;
    }
    
    const stats = countAndDisplayCurrentStats(sheet);
    
    // Display a toast message with the current stats
    const message = `Statistics refreshed: Success: ${stats.success}, No Data: ${stats.noData}, Failed: ${stats.failure}`;
    SpreadsheetApp.getActiveSpreadsheet().toast(message, "Statistics Updated", 5);
    
    SpreadsheetApp.flush();
  } catch (e) {
    Logger.log("Error in refreshStatistics: " + e.message);
  }
}

// Function to correct status codes for failed entries
function correctFailedStatusCodes() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const data = sheet.getDataRange().getValues();
    let fixedCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < data.length; i++) {
      // Skip empty rows
      if (!data[i][0] && !data[i][1]) continue;
      
      const status = data[i][2]; // Status is in column C (index 2)
      const companyName = data[i][5]; // Company Name is in column F (index 5)
      
      // Force update ANY rows with status "Failed" regardless of current status code
      if (status === "Failed") {
        let newCode = 404; // Default to 404 Not found
        let newMessage = 'Not found – Endpoint not found';
        let fullMessage = 'No data available for this company';
        
        // If it's EMPTY_DATA, use 404 (no data found)
        if (companyName === "EMPTY_DATA") {
          newCode = 404;
          newMessage = 'Not found – Endpoint not found';
        }
        
        // Always update the status code for any Failed entry
        sheet.getRange(i+1, 28).setValue(newCode); // Column AB: Status Code
        sheet.getRange(i+1, 29).setValue(newMessage); // Column AC: Message
        sheet.getRange(i+1, 30).setValue(fullMessage); // Column AD: Full Message
        fixedCount++;
      }
    }
    
    // Show a toast message with the results
    if (fixedCount > 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast(`Fixed status codes for ${fixedCount} failed entries`, "Status Codes Updated", 5);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast("No failed entries found to update", "Status Check Complete", 3);
    }
    
    return fixedCount;
  } catch (e) {
    Logger.log("Error in correctFailedStatusCodes: " + e.message);
    return 0;
  }
}

// Force fix status codes function - more aggressive approach
function forceFixStatusCodes() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    let fixedCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < values.length; i++) {
      // Skip empty rows
      if (!values[i][0] && !values[i][1]) continue;
      
      const status = values[i][2]; // Status is in column C (index 2)
      
      if (status === "Failed") {
        // These are the specific column indices
        const statusCodeCell = sheet.getRange(i+1, 28); // AB column (index 27 + 1)
        const messageCell = sheet.getRange(i+1, 29);    // AC column (index 28 + 1)
        const fullMessageCell = sheet.getRange(i+1, 30); // AD column (index 29 + 1)
        
        // Force conversion to string "404" to avoid number formatting issues
        statusCodeCell.setValue("404");
        messageCell.setValue("Not found – The requested endpoint was not found");
        fullMessageCell.setValue("No data available for this company");
        
        fixedCount++;
      }
    }
    
    // Show a toast message with the results
    if (fixedCount > 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast(`Forcefully fixed ${fixedCount} failed entries`, "Status Codes Updated", 5);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast("No failed entries found to update", "Status Check Complete", 3);
    }
    
    return fixedCount;
  } catch (e) {
    Logger.log("Error in forceFixStatusCodes: " + e.message);
    return 0;
  }
}

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:

  1. Head to Project Settings
  2. Under Script Properties, add the property api_key
  3. Under the Value, paste your actual API key

Requirements:

  • 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.

Available Enrichment Options

The Company Enrichment menu provides several options:

  • Enrich All Companies: Enriches every row with company data
  • Enrich New/Failed Companies: Only processes rows that haven't been successfully enriched
  • Choose Row Range to Enrich: Specify exact start and end rows for targeted enrichment
  • Set Max Row for All/New Enrichment: Set a limit to cap bulk enrichment operations

Data Fields Returned

The script populates your sheet with comprehensive company information including:

Basic Information:

  • Company Name, FQDN, Founded date
  • Company Size, Employee count
  • Description, Specialties

Industry & Business:

  • Main Industry, Sub Industry, Categories
  • Technologies, Intent topics
  • Revenue Range, Funding information

Location Data:

  • Full Location, Address
  • Country, City, State, State Code
  • Country ISO2 code

Social & Links:

  • Company LinkedIn, CrunchBase
  • Website and social media links

Status Tracking:

  • Enrichment Status, Status Codes
  • Error Messages, Timestamps

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.

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

 
 
Terms
Copyright © Lusha. All rights reserved.