Automate Data Enrichment with Apps Script: Complete Guide to Save 240 Hours Per Year

Manually enriching hundreds of leads in Google Sheets means losing 2 to 3 hours daily on copy-pasting between tools. Google Apps Script transforms this time-consuming workflow into an automated process that runs in the background while you focus on high-value tasks.

In this tutorial, you’ll learn how to create an automation script that automatically enriches your leads with emails, phone numbers, and firmographic data—all without leaving Google Sheets.

Enrich your leads without coding

Derrick automatically enriches your data in Google Sheets. Email Finder, Phone Finder, LinkedIn Scraper, and 50+ attributes available.

Try for free →

What you’ll learn (and expected results)

By the end of this tutorial, you’ll be able to:

• Create an Apps Script that automatically enriches lead data
• Call external enrichment APIs from Google Sheets
• Automate script execution with time-based triggers
• Manage Apps Script quotas and execution limits
• Set up a logging system to track enrichment progress

Concrete result: An automated system that enriches 200 leads per day without manual intervention, saving approximately 15 hours of work per week. According to a McKinsey study, automation saves up to 240 hours per year per employee.

Prerequisites

Before starting, make sure you have:

• A Google Workspace account (free or paid)
• A Google Sheet with a lead list (minimum: first name, last name, company)
• Basic JavaScript knowledge (not required but helpful)
• Important: An enrichment tool with API access (Derrick or similar)

Estimated time: 30 to 45 minutes for complete setup

Level: Intermediate (accessible to motivated beginners)

Why automate enrichment with Apps Script?

Before diving into code, let’s understand why Apps Script is the ideal tool for enrichment automation.

The manual workflow problem

Sarah, an SDR at a SaaS startup, spends 2.5 hours every morning:

1. Copying prospect names from her Google Sheet
2. Searching for them one by one on LinkedIn
3. Finding their emails via an external tool
4. Manually copying the info back into the Sheet
5. Verifying that emails are valid

Result: Out of an 8-hour workday, Sarah loses 31% of her time on manual enrichment. According to an HBR study, this type of repetitive task costs American businesses $3 trillion annually.

The Apps Script solution

With Apps Script, Sarah:

• Launches her script in the morning with 1 click
• The script automatically enriches 200 leads
• Sarah focuses on qualification and calls
• Time saved: 2.5 hours per day = 12.5 hours per week = 52 days per year

According to a recent survey, 73% of IT leaders report that automation saves approximately 50% of time on repetitive tasks.

Step 1: Set up your Google Sheet and Apps Script editor

The first step is to properly structure your Sheet and access the code editor.

1.1 Structure your data sheet

Open your Google Sheet and organize your columns as follows:

Important: Add a header row in row 1. The script will start processing from row 2.

1.2 Access the Apps Script editor

1. In your Google Sheet, click Extensions > Apps Script
2. A new tab opens with the code editor
3. Delete the example code that appears by default
4. Rename your project: click “Untitled project” and type “Automated Enrichment”

Expected result: You now see an empty code editor with your project name at the top left.

Step 2: Create the main enrichment function

We’ll now write the core of our script: the function that enriches leads.

2.1 The base code

Copy this code into the Apps Script editor:

function enrichLeads() {
  // Step 1: Get the active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Step 2: Get all data (starting from row 2)
  const lastRow = sheet.getLastRow();
  const data = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
  
  // Step 3: Loop through each row
  for (let i = 0; i < data.length; i++) {
    const firstName = data[i][0];
    const lastName = data[i][1];
    const company = data[i][2];
    const linkedinUrl = data[i][3];
    const currentEmail = data[i][4];
    
    // Skip if already enriched
    if (currentEmail !== "") {
      continue;
    }
    
    // Step 4: Call your enrichment API
    const result = callEnrichmentAPI(firstName, lastName, company);
    
    // Step 5: Write results to the Sheet
    if (result.success) {
      sheet.getRange(i + 2, 5).setValue(result.email); // Column E
      sheet.getRange(i + 2, 6).setValue(result.phone); // Column F
      sheet.getRange(i + 2, 7).setValue(result.jobTitle); // Column G
      sheet.getRange(i + 2, 8).setValue("Enriched"); // Column H
    } else {
      sheet.getRange(i + 2, 8).setValue("Error");
    }
    
    // Pause to avoid exceeding API quotas
    Utilities.sleep(1000); // 1 second between each call
  }
  
  Logger.log("Enrichment completed: " + data.length + " rows processed");
}

What this code does:

1. Retrieves your active Google Sheet
2. Reads all data starting from row 2
3. For each row, checks if email is already filled
4. If empty, calls an enrichment API
5. Writes results (email, phone, job title) to corresponding columns
6. Adds “Enriched” or “Error” status

Expected result: The code is now in the editor, but it doesn’t do anything yet because the callEnrichmentAPI function is missing.

Step 3: Connect your enrichment API

Now we’ll create the function that actually calls your enrichment tool.

3.1 Example with Derrick (or your tool)

Add this function below the previous function:

function callEnrichmentAPI(firstName, lastName, company) {
  try {
    // API call configuration
    const apiKey = "YOUR_API_KEY"; // Replace with your real key
    const url = "https://api.your-tool.com/enrich";
    
    // Build the request
    const options = {
      method: "post",
      headers: {
        "Authorization": "Bearer " + apiKey,
        "Content-Type": "application/json"
      },
      payload: JSON.stringify({
        first_name: firstName,
        last_name: lastName,
        company: company
      })
    };
    
    // Call the API
    const response = UrlFetchApp.fetch(url, options);
    const result = JSON.parse(response.getContentText());
    
    // Return enriched data
    return {
      success: true,
      email: result.email || "",
      phone: result.phone || "",
      jobTitle: result.job_title || ""
    };
    
  } catch (error) {
    Logger.log("Enrichment error: " + error.message);
    return {
      success: false,
      email: "",
      phone: "",
      jobTitle: ""
    };
  }
}

⚠️ Important: Replace YOUR_API_KEY and the URL with the actual values from your enrichment tool.

What this code does:

1. Prepares an HTTP POST request to your API
2. Sends first name, last name, and company as JSON
3. Retrieves the API response
4. Parses JSON to extract email, phone, job title
5. Handles errors gracefully (if API fails, returns empty values)

Expected result: Your script can now communicate with your external enrichment tool.

Step 4: Test your script manually

Before automating, let’s test that everything works.

4.1 Prepare test data

In your Google Sheet, add 2-3 test rows:

4.2 Run the script

1. In the Apps Script editor, click the enrichLeads function in the dropdown menu at the top
2. Click the Run button (▶️ icon)
3. First execution: Apps Script will request permissions
   • Click “Review permissions”
   • Select your Google account
   • Click “Allow”
4. The script runs, you’ll see a spinner
5. Once completed, return to your Google Sheet

Expected result: The Email, Phone, Job Title, and Status columns are now filled for your test rows.

4.3 Check the logs

To see what happened:

1. In the Apps Script editor, click Executions (clock icon on the left)
2. You see the list of recent executions
3. Click on an execution to see detailed logs

If something doesn’t work: The logs will indicate the exact error. See the “Common errors” section below.

Step 5: Automate execution with triggers

Now that the script works, let’s automate it to run without intervention.

5.1 Create a time-based trigger

1. In the Apps Script editor, click Triggers (clock icon on the left)
2. Click + Add trigger (bottom right)
3. Configure:
   • Function to run: enrichLeads
   • Event source: Time-driven
   • Type of time-based trigger: Day timer
   • Time of day: 8am to 9am (or your preferred time)
4. Click Save

What happens now: Every day between 8am and 9am, your script will automatically run and enrich all new rows in your Sheet.

Expected result: You see your trigger in the list with “Active” status.

5.2 Advanced trigger options

You can create multiple triggers based on your needs:

Tip: For an SDR actively prospecting, a daily trigger is optimal. For a CRM database, a weekly trigger is sufficient.

Step 6: Optimize performance and manage quotas

Apps Script has execution limits. Here’s how to work around them intelligently.

6.1 Handle the 6-minute limit

Apps Script limits each execution to 6 minutes maximum. If you’re enriching 500 leads and each API call takes 2 seconds, you’ll exceed this limit.

Solution: Batch processing

Modify your main function to process only 100 rows per execution:

function enrichLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  // Limit to 100 rows per execution
  const maxRows = 100;
  let rowsProcessed = 0;
  
  for (let i = 2; i <= lastRow && rowsProcessed < maxRows; i++) {
    const email = sheet.getRange(i, 5).getValue();
    
    // Skip if already enriched
    if (email !== "") {
      continue;
    }
    
    // Enrichment...
    rowsProcessed++;
  }
  
  Logger.log("Processed: " + rowsProcessed + " rows");
}

Result: The script processes 100 rows, stops, then resumes at the next automatic execution.

6.2 Manage API quotas

Your enrichment tool likely has a quota (e.g., 1000 credits/month). Let’s add a counter:

function enrichLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const props = PropertiesService.getScriptProperties();
  
  // Get monthly counter
  let monthlyCounter = parseInt(props.getProperty('monthly_counter') || 0);
  const quotaLimit = 1000; // Your monthly limit
  
  if (monthlyCounter >= quotaLimit) {
    Logger.log("Monthly quota reached: " + monthlyCounter + "/" + quotaLimit);
    return;
  }
  
  // Enrichment...
  // After each successful enrichment:
  monthlyCounter++;
  props.setProperty('monthly_counter', monthlyCounter.toString());
}

What this code does:

• Stores a monthly counter in script properties
• Stops enrichment if quota is reached
• You can manually reset the counter each 1st of the month

Expected result: Your script respects your API quota and doesn’t waste credits.

Step 7: Add advanced logging system

To precisely track what’s happening, let’s create a Logs tab in the Sheet.

7.1 Create the Logs tab

1. In your Google Sheet, create a new tab named “Logs”
2. Add headers: Date | Row | Action | Result

7.2 Logging function

Add this function to your script:

function logAction(row, action, result) {
  const logsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Logs");
  const timestamp = new Date();
  
  logsSheet.appendRow([
    timestamp,
    row,
    action,
    result
  ]);
}

7.3 Use the logging

In your enrichLeads function, add:

if (result.success) {
  // Write data...
  logAction(i + 2, "Enrichment", "Success: " + result.email);
} else {
  logAction(i + 2, "Enrichment", "Failed: " + error.message);
}

Expected result: Every action is logged in the Logs tab with timestamp, enabling precise tracking of enrichment history.

Final result: what you’ve accomplished

Congratulations! You now have a complete automated enrichment system that:

✅ Automatically enriches your leads daily without manual intervention ✅ Calls your external enrichment API (Derrick or similar) ✅ Intelligently manages quotas and execution limits ✅ Logs all actions in a tracking system ✅ Processes 100+ leads per day in the background

Concrete time savings: If you were manually enriching 200 leads per week (2.5 hours per day), you now save 12.5 hours per week, or 52 workdays per year.

According to McKinsey’s study, this type of automation can reduce operational costs by 30% and increase productivity by 0.8 to 1.4% annually.

Common errors (and how to fix them)

Here are the 5 most frequent problems encountered when automating enrichment with Apps Script.

Problem 1: “Exception: Request failed with code 403”

Symptom: The script stops with a 403 error during the API call.

Impact: No data is enriched, the script fails completely.

Solution:

1. Verify that your API key is correct and active
2. Check that your account hasn’t exceeded its monthly quota
3. Test your API key directly with a tool like Postman
4. Contact your enrichment tool’s support if the problem persists

Debug code to add:

Logger.log("API Key: " + apiKey.substring(0, 10) + "...");
Logger.log("Remaining quota: " + monthlyCounter + "/" + quotaLimit);

Problem 2: “Exception: Service invoked too many times in a short time”

Symptom: The script stops with this message after a few dozen calls.

Impact: Enrichment is incomplete, some rows aren’t processed.

Solution: Increase the delay between each API call:

// Before (too fast)
Utilities.sleep(1000); // 1 second

// After (optimal)
Utilities.sleep(2000); // 2 seconds

If you have many rows to process, prefer more frequent executions (every hour) rather than one large daily execution.

Problem 3: Script stops after 6 minutes

Symptom: The script only processes the first 150 rows then stops.

Impact: Subsequent rows are never enriched.

Solution: Implement the batch processing system (Step 6.1) to process maximum 100 rows per execution. The automatic trigger will resume where the script stopped.

Problem 4: Data writes to wrong columns

Symptom: Email appears in the Phone column, etc.

Impact: Your data is mixed and unusable.

Solution: Verify that column indexes in sheet.getRange(i + 2, X) match your columns:

• Column A = index 1
• Column B = index 2
• Column C = index 3
• etc.

Add a comment in your code to document each column:

sheet.getRange(i + 2, 5).setValue(result.email);     // Column E: Email
sheet.getRange(i + 2, 6).setValue(result.phone);     // Column F: Phone
sheet.getRange(i + 2, 7).setValue(result.jobTitle);  // Column G: Job Title

Problem 5: Trigger doesn’t run automatically

Symptom: You created a trigger but the script never runs on its own.

Impact: You must manually run the script every day, losing all automation benefits.

Solution:

1. Verify the trigger is enabled (not grayed out) in the triggers list
2. Check error notifications in your Gmail (Apps Script sends an email on failure)
3. Review execution history in Triggers > Executions
4. If the trigger doesn’t appear in history, delete it and recreate it

Going further: advanced optimizations

Once your automated enrichment system is in place, here’s how to improve it.

Enrich with multiple sources

Rather than depending on a single API, use a “waterfall” approach:

function callEnrichmentAPI(firstName, lastName, company) {
  // Attempt 1: Primary API
  let result = callAPI1(firstName, lastName, company);
  
  if (result.email !== "") {
    return result;
  }
  
  // Attempt 2: Backup API
  result = callAPI2(firstName, lastName, company);
  
  return result;
}

Advantage: Completion rate increased by 20 to 40% according to data.

Automatically validate emails

Before writing an email to your Sheet, verify it’s valid:

function isEmailValid(email) {
  const regex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return regex.test(email);
}

// In your enrichment function:
if (result.email && isEmailValid(result.email)) {
  sheet.getRange(i + 2, 5).setValue(result.email);
}

For even more robust validation, use a tool like Derrick’s Email Verifier which checks actual deliverability.

Enrich only specific rows

Add a “To Enrich” column with a checkbox:

const toEnrich = data[i][8]; // Column I: checkbox

if (toEnrich === false) {
  continue; // Skip this row
}

Advantage: You precisely control which leads to enrich, saving API credits.

Create automatic reports

Send yourself a weekly summary email:

function sendWeeklyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Logs");
  const logs = sheet.getDataRange().getValues();
  
  let successes = 0;
  let failures = 0;
  
  logs.forEach(log => {
    if (log[3].includes("Success")) successes++;
    if (log[3].includes("Failed")) failures++;
  });
  
  const message = `Weekly enrichment report:
  
  ✅ Successful enrichments: ${successes}
  ❌ Failures: ${failures}
  📊 Success rate: ${((successes / (successes + failures)) * 100).toFixed(1)}%`;
  
  GmailApp.sendEmail(
    "your.email@example.com",
    "Weekly enrichment report",
    message
  );
}

Set up a weekly trigger for this function.

No-code alternative: Derrick for Google Sheets

If writing Apps Script code seems too technical, Derrick offers a native solution for Google Sheets.

Derrick advantages:

• ✅ No code required: Native Google Sheets formulas
• ✅ 50+ enrichment attributes per contact and company
• ✅ Email Finder + Phone Finder + LinkedIn Scraper integrated
• ✅ Ask Claude / Ask OpenAI for AI-powered enrichment
• ✅ Rollover credits: unused credits are kept

How it works:

1. Install the Derrick extension from Google Workspace Marketplace
2. In your Sheet, use formulas: =DERRICK_EMAIL("FirstName", "LastName", "Company")
3. Enrichment happens automatically in real-time

Difference with Apps Script: Derrick is simpler to use (no code), while Apps Script offers more flexibility for complex custom workflows.

For Sales Ops teams managing large lead volumes (500+ per day), Derrick saves an average of 20 hours of setup and maintenance per month compared to a homemade Apps Script solution.

Conclusion: automate to focus on what matters

You now know how to automate lead enrichment with Apps Script. This system saves you 15 hours per week that you can reinvest in qualification, personalization, and closing.

Steps recap:

1. Structure your Google Sheet with the right columns
2. Create the main enrichment function
3. Connect your external enrichment API
4. Test manually with a few leads
5. Create a trigger to automate execution
6. Optimize to manage quotas and limits
7. Add a logging system to track results

Next step: Launch your first script today. Start with 10 test rows, verify everything works, then enable complete automation.

According to a recent study, 73% of IT leaders report that automating repetitive tasks saves approximately 50% of their teams’ time. Don’t stay in the 27% still losing hours on manual copy-pasting.

Enrich without coding with Derrick

Install Derrick and enrich your leads with 1 click. Email Finder, Phone Finder, LinkedIn Scraper, and Ask Claude natively integrated in Google Sheets.

Try for free →

FAQ

How many leads can I automatically enrich per day with Apps Script?

With a daily trigger and 2-second delay between each API call, you can enrich approximately 150 to 200 leads per day (respecting the 6-minute execution limit). For higher volumes, configure multiple triggers per day or use a native tool like Derrick that doesn’t have these limitations.

Is Apps Script free for data enrichment?

Yes, Apps Script is 100% free for Google Workspace users. However, you’ll need to pay for your external enrichment tool (email finder API, phone finder, etc.). Expect between $29 and $99 per month depending on the tool chosen and your credit volume.

How do I avoid exceeding my enrichment tool’s API quotas?

Implement a counter in script properties that tracks your monthly consumption. Add a condition that stops the script if quota is reached. Also configure an email alert system when you approach 80% of your monthly quota.

Can I automate enrichment for multiple Google Sheets simultaneously?

Yes, create a script in a standalone Apps Script project rather than one linked to a specific Sheet. Use SpreadsheetApp.openById("SHEET_ID") to target different Sheets. However, watch execution limits: prefer staggered triggers to avoid overload.

What happens if my script crashes mid-execution?

Thanks to the if (currentEmail !== "") check, the script automatically skips already enriched rows. At the next automatic execution, it will resume where it stopped. This is why adding an “Enriched” status in a dedicated column is crucial.

Can Apps Script enrich data from LinkedIn Sales Navigator?

Apps Script cannot directly scrape LinkedIn Sales Navigator as it would require complex authentication. However, you can manually export your list from Sales Navigator as CSV, import it into your Google Sheet, then launch automatic enrichment with Apps Script. Alternatively, use Derrick which imports Sales Navigator with 1 click.