AI-Powered B2B Lead Scoring on Autopilot via Google Sheets & Gemini

Imagine dozens of new potential clients (leads) flooding into your B2B database or registration forms every single day. When your sales team clocks in, how do they know which prospect to call first?

Does an individual registration from [e-mail] carry the same weight on your list as a corporate procurement request from [e-mail]?

Traditionally, setting up automated Lead Scoring requires bulky, rigid, rule-based CRM tools like Salesforce Pardot or Marketo that cost thousands of dollars a month. Worse yet, the keyword logic in these legacy systems is remarkably primitive. If you define a rule that says "Add 10 points if the job title includes 'Manager'", it will score an irrelevant "Office Manager" highly while completely missing high-intent enterprise buyers with titles like "Head of Operations" or "VP of Procurement."

The solution isn't sinking your engineering or ops budget into overpriced software subscriptions. Instead, you can keep the hyper-flexible Google Sheets CRM backend you already rely on and supercharge it with the semantic intent processing power of Gemini AI.

---

The Automation Loop

By deploying a scheduled hourly trigger via Google Apps Script, you can run a seamless, background enrichment workflow that transforms raw customer entries into structured sales data:

• Semantic Profile Analysis: Instead of basic keyword matching, the AI evaluates the entire row dynamically—analyzing corporate vs. free domains, company size indicators, and professional authority levels as a cohesive profile.
• 0-100 Objective Scoring: The model weights the lead directly against your company's Ideal Customer Profile (ICP) and assigns a precise numerical score.
• Human-Readable Explanations: To give your sales reps instant context before a call, the AI populates a concise, one-sentence justification explaining the exact reasoning behind the score.
• Visual Triage: High-scoring VIP rows can be automatically color-coded in green using server-side formatting rules, instantly drawing the team's eyes to high-value targets.

---

The Engineering Challenge: Enforcing the Data Contract

The real engineering challenge when integrating LLMs into web-based spreadsheets is preserving your relational schema. Left to its default behavior, a text-generation model will write conversational text, introductory fluff, and conversational dialogue directly into your pristine sheet cells, breaking downstream automation.

To build a production-ready autopilot, we utilize the structured JSON output config (responseMimeType: "application/json") natively inside the Gemini API. This enforces a strict structural contract at the API level:

function scoreIncomingLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lead Pipeline");
  const data = sheet.getDataRange().getValues();
  const apiKey = "YOUR_AI_STUDIO_KEY";
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    if (row[6] !== "") continue; // Skip if already scored in Column G

    const promptText = `Evaluate this B2B lead for enterprise hardware buying intent: 
    Company: ${row[1]}, Name: ${row[2]}, Email: ${row[3]}, Title: ${row[4]}, Notes: ${row[5]}.
    Respond STRICTLY in valid JSON format with two keys: "score" (0-100) and "reasoning" (1 sentence).`;

    const payload = {
      "contents": [{"parts": [{"text": promptText}]}],
      "generationConfig": {
        "responseMimeType": "application/json" // The enterprise secret weapon
      }
    };

    const options = {
      "method": "POST",
      "headers": { "Content-Type": "application/json" },
      "payload": JSON.stringify(payload)
    };

    try {
      const response = UrlFetchApp.fetch(url, options);
      const result = JSON.parse(JSON.parse(response.getContentText()).candidates[0].content.parts[0].text);

      // Safely commit exact types back to specific columns
      sheet.getRange(i + 1, 7).setValue(result.score);
      sheet.getRange(i + 1, 8).setValue(result.reasoning);

      if (result.score > 80) {
        sheet.getRange(i + 1, 1, 1, 8).setBackground("#e6f4ea"); // Highlight VIPs
      }
    } catch(e) {
      Logger.log("Execution failed: " + e.message);
    }
  }
}

Bridging the Operational Gap
By leveraging a serverless architecture with zero hosting costs, your data schema remains protected, your pipeline runs autonomously in the cloud, and your sales reps save hours of manual triage every week. When they log into the spreadsheet at 9:00 AM, they simply sort by the AI Score column—the most lucrative enterprise deals are already sitting right at the top, enriched, ranked, and ready to be closed.

Complete Blueprint & Source Code
If you want to copy the complete production scripts, check out the precise prompt structures, or explore more advanced webhook integration patterns for B2B e-commerce platforms, we have fully documented this setup.

👉 Read the Full Step-by-Step Guide on the MageSheet Blog.