Google Forms

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

Integre EmailVerify con Google Forms para verificar automáticamente direcciones de correo electrónico cuando se envían respuestas.

Métodos de Integración

Método	Ideal Para	Complejidad
Apps Script	Verificación automatizada	Baja
Complemento de Sheets	Verificación manual	Baja
Zapier	Flujo de trabajo sin código	Baja

Método 1: Google Apps Script

Cree un flujo de trabajo de verificación automatizada usando Google Apps Script.

Paso 1: Crear Proyecto de Apps Script

Abra su formulario de Google
Haga clic en el menú ⋮ → Editor de secuencia de comandos
Esto abre el editor de Apps Script

Paso 2: Agregar Código de Verificación

Reemplace el código predeterminado con:

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

Paso 3: Instalar el Activador

En el editor de Apps Script, seleccione installTrigger del menú desplegable de funciones
Haga clic en Ejecutar
Otorgue los permisos requeridos cuando se le solicite

Paso 4: Probar la Integración

Envíe una respuesta de prueba a su formulario
Revise la hoja de cálculo vinculada para ver los resultados de verificación
Vea los registros de Apps Script: Ejecuciones en la barra lateral izquierda

Método 2: Complemento de Hoja de Cálculo

Verifique correos electrónicos directamente en Google Sheets con una barra lateral personalizada.

Crear Código del Complemento

// 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 de Configuración

<!-- 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>

Método 3: Integración con Zapier

Conecte Google Forms a EmailVerify usando Zapier.

Configuración de Zap

Disparador: Google Forms → Nueva Respuesta en Hoja de Cálculo
Acción 1: Webhooks by Zapier → POST a EmailVerify
Acción 2: Google Sheets → Actualizar Fila de Hoja de Cálculo

Configuración del 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}}"
}

Actualizar Fila

Mapee el resultado de verificación de vuelta a su hoja de cálculo:

Columna: Estado de Email → {{status}}
Columna: Puntuación de Email → {{score}}
Columna: Es Desechable → {{result__disposable}}

Script de Verificación en Lote

Para verificar grandes conjuntos de datos existentes:

/**
 * 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,
    };
  }
}

Notificaciones de Correo Condicionales

Envíe diferentes correos electrónicos según los resultados de verificación:

/**
 * 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.
    `,
  });
}

Mejores Prácticas

1. Almacene la Clave de API de Forma Segura

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

2. Maneje los Límites de Tasa

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. Manejo de Errores Elegante

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

Recursos Relacionados

Integración con Typeform

Otro constructor de formularios

Integración con JotForm

Plataforma de formularios alternativa

Referencia API

Documentación completa de la API

Google Forms

Integración con Typeform

Integración con JotForm

Referencia API

On this page