Google Forms

Email checker for Google Forms. Verify submitted emails with Apps Script integration.

將 EmailVerify 與 Google Forms 集成,在提交響應時自動驗證邮箱地址。

集成方法

方法	最適合	復雜度
Apps Script	自動化驗證	低
Sheets 插件	手動驗證	低
Zapier	無代碼工作流	低

方法 1：Google Apps Script

使用 Google Apps Script 创建自動化驗證工作流。

步骤 1：创建 Apps Script 項目

打開您的 Google Form
点击 ⋮ 菜單 → Script editor
这將打開 Apps Script 編輯器

步骤 2：添加驗證代碼

將默認代碼替換為：

// Configuration
const CONFIG = {
  API_KEY: 'YOUR_EMAILVERIFY_API_KEY',
  EMAIL_FIELD_INDEX: 1, // Adjust based on your form (0-indexed)
  BLOCK_DISPOSABLE: true,
  NOTIFY_ON_INVALID: true,
  NOTIFICATION_EMAIL: 'admin@yourcompany.com',
};

/**
 * Trigger function - runs when form is submitted
 */
function onFormSubmit(e) {
  const response = e.response;
  const email = getEmailFromResponse(response);

  if (!email) {
    Logger.log('No email found in response');
    return;
  }

  // Verify email
  const result = verifyEmail(email);

  // Store result
  storeVerificationResult(response, result);

  // Handle invalid emails
  if (!result.isValid) {
    handleInvalidEmail(response, email, result);
  }
}

/**
 * Extract email from form response
 */
function getEmailFromResponse(response) {
  const itemResponses = response.getItemResponses();

  // Try to find email field by type
  for (const itemResponse of itemResponses) {
    const item = itemResponse.getItem();
    const itemType = item.getType();

    // Check if it's an email validation text item
    if (itemType === FormApp.ItemType.TEXT) {
      const textItem = item.asTextItem();
      const validation = textItem.getValidation();

      // If validation requires email format
      if (validation) {
        return itemResponse.getResponse();
      }
    }
  }

  // Fallback: use configured index
  if (CONFIG.EMAIL_FIELD_INDEX < itemResponses.length) {
    return itemResponses[CONFIG.EMAIL_FIELD_INDEX].getResponse();
  }

  return null;
}

/**
 * Call EmailVerify API to verify email
 */
function verifyEmail(email) {
  const url = 'https://api.emailverify.ai/v1/verify';

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + CONFIG.API_KEY,
    },
    payload: JSON.stringify({ email: email }),
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    const isValid = data.status === 'valid' &&
      !(CONFIG.BLOCK_DISPOSABLE && data.result?.disposable);

    return {
      email: email,
      status: data.status,
      score: data.score,
      isDisposable: data.result?.disposable || false,
      isRole: data.result?.role || false,
      isValid: isValid,
      raw: data,
    };
  } catch (error) {
    Logger.log('Verification error: ' + error.message);
    return {
      email: email,
      status: 'error',
      isValid: true, // Allow on error
      error: error.message,
    };
  }
}

/**
 * Store verification result in linked spreadsheet
 */
function storeVerificationResult(response, result) {
  const form = FormApp.getActiveForm();
  const destinationId = form.getDestinationId();

  if (!destinationId) {
    Logger.log('No linked spreadsheet found');
    return;
  }

  const spreadsheet = SpreadsheetApp.openById(destinationId);
  const sheet = spreadsheet.getSheets()[0];

  // Find the response row
  const responseRow = findResponseRow(sheet, response);

  if (responseRow === -1) {
    Logger.log('Could not find response row');
    return;
  }

  // Add verification columns if they don't exist
  ensureVerificationColumns(sheet);

  // Get column indices
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const statusCol = headers.indexOf('Email Status') + 1;
  const scoreCol = headers.indexOf('Email Score') + 1;
  const disposableCol = headers.indexOf('Is Disposable') + 1;

  // Update row
  if (statusCol > 0) {
    sheet.getRange(responseRow, statusCol).setValue(result.status);
  }
  if (scoreCol > 0 && result.score !== undefined) {
    sheet.getRange(responseRow, scoreCol).setValue(result.score);
  }
  if (disposableCol > 0) {
    sheet.getRange(responseRow, disposableCol).setValue(result.isDisposable ? 'Yes' : 'No');
  }

  // Apply conditional formatting
  if (result.status === 'invalid' || result.isDisposable) {
    sheet.getRange(responseRow, 1, 1, sheet.getLastColumn())
      .setBackground('#ffcccb');
  }
}

/**
 * Find the row number for a response
 */
function findResponseRow(sheet, response) {
  const timestamp = response.getTimestamp();
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    const rowTimestamp = data[i][0];
    if (rowTimestamp instanceof Date &&
        Math.abs(rowTimestamp.getTime() - timestamp.getTime()) < 5000) {
      return i + 1; // 1-indexed
    }
  }

  return -1;
}

/**
 * Add verification columns to spreadsheet
 */
function ensureVerificationColumns(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const columnsToAdd = ['Email Status', 'Email Score', 'Is Disposable'];

  for (const column of columnsToAdd) {
    if (!headers.includes(column)) {
      const newCol = sheet.getLastColumn() + 1;
      sheet.getRange(1, newCol).setValue(column);
    }
  }
}

/**
 * Handle invalid email submissions
 */
function handleInvalidEmail(response, email, result) {
  if (!CONFIG.NOTIFY_ON_INVALID) {
    return;
  }

  const form = FormApp.getActiveForm();
  const formTitle = form.getTitle();

  const subject = `[${formTitle}] Invalid Email Submission`;
  const body = `
An invalid email was submitted to your form.

Form: ${formTitle}
Email: ${email}
Status: ${result.status}
${result.isDisposable ? 'Note: Disposable email detected' : ''}
Score: ${result.score || 'N/A'}

Timestamp: ${response.getTimestamp()}
  `.trim();

  MailApp.sendEmail({
    to: CONFIG.NOTIFICATION_EMAIL,
    subject: subject,
    body: body,
  });
}

/**
 * Set up form submit trigger
 * Run this function once to install the trigger
 */
function installTrigger() {
  const form = FormApp.getActiveForm();

  // Remove existing triggers
  const triggers = ScriptApp.getUserTriggers(form);
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === 'onFormSubmit') {
      ScriptApp.deleteTrigger(trigger);
    }
  }

  // Install new trigger
  ScriptApp.newTrigger('onFormSubmit')
    .forForm(form)
    .onFormSubmit()
    .create();

  Logger.log('Trigger installed successfully');
}

/**
 * Test verification (run manually)
 */
function testVerification() {
  const result = verifyEmail('test@example.com');
  Logger.log(JSON.stringify(result, null, 2));
}

步骤 3：安裝觸發器

在 Apps Script 編輯器中，從函數下拉菜單選择 installTrigger
点击 Run
提示時授予所需权限

步骤 4：測試集成

向您的表單提交測試響應
檢查链接的表格以查看驗證結果
查看 Apps Script 日志：左侧栏中的 Executions

方法 2：表格插件

使用自定義侧边栏直接在 Google Sheets 中驗證邮箱。

创建插件代碼

// Code.gs
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('EmailVerify')
    .addItem('Verify Selected Emails', 'verifySelected')
    .addItem('Verify All Emails', 'verifyAll')
    .addItem('Settings', 'showSettings')
    .addToUi();
}

function verifySelected() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();

  const results = [];

  for (let i = 0; i < values.length; i++) {
    const email = values[i][0];

    if (!email || !isValidEmailFormat(email)) {
      results.push(['Invalid format']);
      continue;
    }

    const result = verifyEmail(email);
    results.push([result.status + (result.isDisposable ? ' (disposable)' : '')]);
  }

  // Write results to next column
  const startCol = range.getColumn() + range.getNumColumns();
  sheet.getRange(range.getRow(), startCol, results.length, 1).setValues(results);
}

function verifyAll() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ui = SpreadsheetApp.getUi();

  // Ask for email column
  const response = ui.prompt(
    'Verify All Emails',
    'Enter the column letter containing emails (e.g., B):',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  const column = response.getResponseText().toUpperCase();
  const colIndex = column.charCodeAt(0) - 64; // A=1, B=2, etc.

  const lastRow = sheet.getLastRow();
  const emailRange = sheet.getRange(2, colIndex, lastRow - 1, 1);
  const emails = emailRange.getValues().flat().filter(e => e);

  ui.alert(`Starting verification of ${emails.length} emails...`);

  // Verify in batches
  const batchSize = 50;
  let processed = 0;

  for (let i = 0; i < emails.length; i += batchSize) {
    const batch = emails.slice(i, i + batchSize);

    for (let j = 0; j < batch.length; j++) {
      const email = batch[j];
      const rowIndex = i + j + 2; // +2 for header and 0-index

      if (!isValidEmailFormat(email)) {
        sheet.getRange(rowIndex, colIndex + 1).setValue('Invalid format');
        continue;
      }

      const result = verifyEmail(email);

      // Write status
      sheet.getRange(rowIndex, colIndex + 1).setValue(result.status);

      // Write score
      if (result.score !== undefined) {
        sheet.getRange(rowIndex, colIndex + 2).setValue(result.score);
      }

      // Highlight invalid
      if (result.status === 'invalid' || result.isDisposable) {
        sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).setBackground('#ffcccb');
      }
    }

    processed += batch.length;
    SpreadsheetApp.flush();

    // Avoid rate limits
    if (i + batchSize < emails.length) {
      Utilities.sleep(1000);
    }
  }

  ui.alert(`Verification complete! Processed ${processed} emails.`);
}

function isValidEmailFormat(email) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
}

function showSettings() {
  const html = HtmlService.createHtmlOutputFromFile('Settings')
    .setWidth(400)
    .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, 'EmailVerify Settings');
}

function saveSettings(apiKey) {
  PropertiesService.getUserProperties().setProperty('EMAILVERIFY_API_KEY', apiKey);
  return { success: true };
}

function getApiKey() {
  return PropertiesService.getUserProperties().getProperty('EMAILVERIFY_API_KEY') || '';
}

设置 HTML

<!-- Settings.html -->
<!DOCTYPE html>
<html>
<head>
  <style>
    body {
      font-family: Arial, sans-serif;
      padding: 20px;
    }
    .form-group {
      margin-bottom: 15px;
    }
    label {
      display: block;
      margin-bottom: 5px;
      font-weight: bold;
    }
    input[type="text"] {
      width: 100%;
      padding: 8px;
      border: 1px solid #ddd;
      border-radius: 4px;
      box-sizing: border-box;
    }
    button {
      background-color: #4285f4;
      color: white;
      padding: 10px 20px;
      border: none;
      border-radius: 4px;
      cursor: pointer;
    }
    button:hover {
      background-color: #357abd;
    }
    .success {
      color: #28a745;
      margin-top: 10px;
    }
  </style>
</head>
<body>
  <div class="form-group">
    <label for="apiKey">EmailVerify API Key</label>
    <input type="text" id="apiKey" placeholder="bv_live_xxx" />
  </div>

  <button onclick="saveSettings()">Save Settings</button>

  <div id="message" class="success" style="display: none;"></div>

  <script>
    // Load existing settings
    google.script.run.withSuccessHandler(function(apiKey) {
      document.getElementById('apiKey').value = apiKey;
    }).getApiKey();

    function saveSettings() {
      const apiKey = document.getElementById('apiKey').value;

      google.script.run.withSuccessHandler(function(result) {
        if (result.success) {
          document.getElementById('message').textContent = 'Settings saved!';
          document.getElementById('message').style.display = 'block';
          setTimeout(function() {
            google.script.host.close();
          }, 1500);
        }
      }).saveSettings(apiKey);
    }
  </script>
</body>
</html>

方法 3：Zapier 集成

使用 Zapier 將 Google Forms 連接到 EmailVerify。

Zap 设置

觸發器：Google Forms → New Response in Spreadsheet
操作 1：Webhooks by Zapier → POST 到 EmailVerify
操作 2：Google Sheets → Update Spreadsheet Row

Webhook 配置

URL: https://api.emailverify.ai/v1/verify
Method: POST
Headers:
  Authorization: Bearer YOUR_API_KEY
  Content-Type: application/json
Data:
{
  "email": "{{email_field_value}}"
}

更新行

將驗證結果映射回您的表格：

列：Email Status → {{status}}
列：Email Score → {{score}}
列：Is Disposable → {{result__disposable}}

批量驗證脚本

用于驗證大型現有數据集：

/**
 * Bulk verify emails from a column
 * Processes in batches with progress tracking
 */
function bulkVerifyEmails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ui = SpreadsheetApp.getUi();

  // Configuration
  const EMAIL_COL = 2;      // Column B (1-indexed)
  const STATUS_COL = 3;     // Column C for status
  const SCORE_COL = 4;      // Column D for score
  const START_ROW = 2;      // Skip header
  const BATCH_SIZE = 100;

  const lastRow = sheet.getLastRow();
  const totalEmails = lastRow - START_ROW + 1;

  const proceed = ui.alert(
    'Bulk Verification',
    `This will verify ${totalEmails} emails. Continue?`,
    ui.ButtonSet.YES_NO
  );

  if (proceed !== ui.Button.YES) {
    return;
  }

  // Get all emails
  const emailRange = sheet.getRange(START_ROW, EMAIL_COL, totalEmails, 1);
  const emails = emailRange.getValues().flat();

  // Get API key
  const apiKey = PropertiesService.getUserProperties().getProperty('EMAILVERIFY_API_KEY');
  if (!apiKey) {
    ui.alert('Please configure your API key in Settings first.');
    return;
  }

  // Process in batches
  let processed = 0;
  const stats = { valid: 0, invalid: 0, disposable: 0, errors: 0 };

  for (let i = 0; i < emails.length; i++) {
    const email = emails[i];
    const row = START_ROW + i;

    // Skip if already verified
    const existingStatus = sheet.getRange(row, STATUS_COL).getValue();
    if (existingStatus) {
      processed++;
      continue;
    }

    if (!email || !isValidEmailFormat(email)) {
      sheet.getRange(row, STATUS_COL).setValue('invalid_format');
      stats.errors++;
      processed++;
      continue;
    }

    // Verify
    const result = verifyEmailWithKey(email, apiKey);

    // Write results
    sheet.getRange(row, STATUS_COL).setValue(result.status);
    if (result.score !== undefined) {
      sheet.getRange(row, SCORE_COL).setValue(result.score);
    }

    // Update stats
    if (result.status === 'valid') stats.valid++;
    else if (result.status === 'invalid') stats.invalid++;
    if (result.isDisposable) stats.disposable++;
    if (result.error) stats.errors++;

    // Highlight invalid rows
    if (result.status === 'invalid' || result.isDisposable) {
      sheet.getRange(row, 1, 1, sheet.getLastColumn()).setBackground('#ffcccb');
    }

    processed++;

    // Progress update every 50 emails
    if (processed % 50 === 0) {
      SpreadsheetApp.flush();
      Logger.log(`Progress: ${processed}/${totalEmails}`);
    }

    // Rate limiting
    Utilities.sleep(100);
  }

  // Final stats
  const message = `
Verification Complete!

Total: ${processed}
Valid: ${stats.valid}
Invalid: ${stats.invalid}
Disposable: ${stats.disposable}
Errors: ${stats.errors}
  `.trim();

  ui.alert('Results', message, ui.ButtonSet.OK);
}

function verifyEmailWithKey(email, apiKey) {
  const url = 'https://api.emailverify.ai/v1/verify';

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey,
    },
    payload: JSON.stringify({ email: email }),
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    return {
      status: data.status,
      score: data.score,
      isDisposable: data.result?.disposable || false,
    };
  } catch (error) {
    return {
      status: 'error',
      error: error.message,
    };
  }
}

條件邮件通知

根据驗證結果發送不同的邮件：

/**
 * Send confirmation email only to verified addresses
 */
function sendConditionalConfirmation(e) {
  const response = e.response;
  const email = getEmailFromResponse(response);

  if (!email) return;

  // Verify email first
  const result = verifyEmail(email);

  if (!result.isValid) {
    // Send admin notification instead
    notifyAdminOfInvalidSubmission(response, email, result);
    return;
  }

  // Send confirmation to valid email
  const formTitle = FormApp.getActiveForm().getTitle();

  MailApp.sendEmail({
    to: email,
    subject: `Thank you for your submission to ${formTitle}`,
    htmlBody: `
      <h2>Thank you!</h2>
      <p>We've received your submission and will be in touch soon.</p>
      <p>Best regards,<br>The Team</p>
    `,
  });
}

function notifyAdminOfInvalidSubmission(response, email, result) {
  const formTitle = FormApp.getActiveForm().getTitle();

  MailApp.sendEmail({
    to: CONFIG.NOTIFICATION_EMAIL,
    subject: `[Alert] Invalid email submission - ${formTitle}`,
    body: `
A submission with an invalid email was received.

Email: ${email}
Status: ${result.status}
Disposable: ${result.isDisposable}

Please review the submission in your spreadsheet.
    `,
  });
}

最佳實践

1. 安全存儲 API 密钥

// Use Properties Service instead of hardcoding
const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');

2. 處理速率限制

function verifyWithRetry(email, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    const result = verifyEmail(email);

    if (result.status !== 'error' || !result.error.includes('rate')) {
      return result;
    }

    // Wait and retry
    Utilities.sleep(2000 * (i + 1));
  }

  return { status: 'error', error: 'Max retries exceeded' };
}

3. 優雅的錯誤處理

// Never block form submissions on API errors
if (result.error) {
  Logger.log('Verification failed, allowing submission');
  return; // Don't block the submission
}