Best Google Sheets Phone Validation API & Email Verification Integration 2025

/**
 * Advanced Validation Workflow
 * This example shows how to create a complete validation system
 */

function validateContactData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getRange('A2:C1000'); // Adjust range as needed
  const values = dataRange.getValues();
  
  const results = [];
  
  for (let i = 0; i < values.length; i++) {
    const [name, phone, email] = values[i];
    
    if (!name && !phone && !email) break; // Stop at empty row
    
    let phoneResult = null;
    let emailResult = null;
    let status = 'pending';
    
    // Validate phone if provided
    if (phone) {
      phoneResult = VALIDATEPHONE(phone);
      if (typeof phoneResult === 'object') {
        if (!phoneResult.valid) {
          status = 'invalid_phone';
        } else if (phoneResult.spam_score > 70) {
          status = 'high_risk_phone';
        }
      }
    }
    
    // Validate email if provided
    if (email) {
      emailResult = VALIDATEEMAIL(email);
      if (typeof emailResult === 'object') {
        if (!emailResult.deliverable) {
          status = 'invalid_email';
        } else if (emailResult.disposable) {
          status = 'disposable_email';
        }
      }
    }
    
    // Set status to valid if both validations passed
    if (status === 'pending' && 
        (!phone || (phoneResult && phoneResult.valid && phoneResult.spam_score <= 70)) &&
        (!email || (emailResult && emailResult.deliverable && !emailResult.disposable))) {
      status = 'valid';
    }
    
    results.push([
      phoneResult ? phoneResult.formatted || phone : phone,
      emailResult ? emailResult.domain || '' : '',
      status,
      phoneResult ? phoneResult.spam_score || 0 : 0,
      emailResult ? emailResult.risk_score || 0 : 0
    ]);
  }
  
  // Write results to columns D-H
  if (results.length > 0) {
    const outputRange = sheet.getRange(2, 4, results.length, 5);
    outputRange.setValues(results);
    
    // Add headers if not present
    const headers = ['Formatted Phone', 'Email Domain', 'Status', 'Phone Risk', 'Email Risk'];
    sheet.getRange(1, 4, 1, 5).setValues([headers]);
  }
}

/**
 * Webhook receiver for real-time validation updates
 */
function doPost(e) {
  try {
    const data = JSON.parse(e.postData.contents);
    
    if (data.event === 'validation_update') {
      updateValidationStatus(data.phone || data.email, data.result);
    }
    
    return ContentService
      .createTextOutput(JSON.stringify({status: 'success'}))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    console.error('Webhook error:', error);
    return ContentService
      .createTextOutput(JSON.stringify({status: 'error', message: error.toString()}))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function updateValidationStatus(identifier, result) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  
  for (let i = 1; i < values.length; i++) {
    if (values[i][0] === identifier || values[i][1] === identifier) {
      sheet.getRange(i + 1, 10).setValue(JSON.stringify(result));
      break;
    }
  }
}

/**
 * Secure API Key Management
 * Store your API key securely using Script Properties
 */

function setupSecureAPIKey() {
  // Run this once to store your API key securely
  const apiKey = 'your_actual_api_key_here';
  PropertiesService.getScriptProperties().setProperty('LOOKUP_API_KEY', apiKey);
  
  // Delete the API key from this function after running it
  console.log('API key stored securely!');
}

function getSecureAPIKey() {
  return PropertiesService.getScriptProperties().getProperty('LOOKUP_API_KEY');
}

/**
 * Secure validation function with proper error handling
 */
function SECURE_VALIDATEPHONE(phoneNumber, countryCode = '') {
  try {
    // Input validation
    if (!phoneNumber || typeof phoneNumber !== 'string') {
      throw new Error('Invalid phone number input');
    }
    
    // Sanitize input
    const cleanPhone = phoneNumber.replace(/[^+ds-()]/g, '');
    
    if (cleanPhone.length < 7 || cleanPhone.length > 20) {
      throw new Error('Phone number length invalid');
    }
    
    const apiKey = getSecureAPIKey();
    if (!apiKey) {
      throw new Error('API key not configured');
    }
    
    // Make secure API request
    const url = `https://api.1lookup.com/v1/phone?phone=${encodeURIComponent(cleanPhone)}&country=${countryCode}`;
    
    const response = UrlFetchApp.fetch(url, {
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${apiKey}`,
        'Content-Type': 'application/json',
        'User-Agent': 'GoogleSheets/Secure/2025'
      },
      muteHttpExceptions: true
    });
    
    if (response.getResponseCode() !== 200) {
      throw new Error(`API Error: ${response.getResponseCode()}`);
    }
    
    const data = JSON.parse(response.getContentText());
    
    // Log successful validation (without sensitive data)
    console.log(`Phone validation successful for country: ${countryCode}`);
    
    return data.success ? data.data : { error: data.message };
    
  } catch (error) {
    // Log error securely (without exposing sensitive data)
    console.error('Validation error:', error.message);
    return { error: error.message };
  }
}

/**
 * Rate limiting implementation
 */
class RateLimiter {
  constructor(maxRequests = 60, windowMs = 60000) {
    this.maxRequests = maxRequests;
    this.windowMs = windowMs;
    this.requests = [];
  }
  
  canMakeRequest() {
    const now = Date.now();
    this.requests = this.requests.filter(time => now - time < this.windowMs);
    
    if (this.requests.length >= this.maxRequests) {
      return false;
    }
    
    this.requests.push(now);
    return true;
  }
  
  getWaitTime() {
    if (this.requests.length === 0) return 0;
    const oldestRequest = Math.min(...this.requests);
    return Math.max(0, this.windowMs - (Date.now() - oldestRequest));
  }
}

// Global rate limiter instance
const rateLimiter = new RateLimiter(60, 60000); // 60 requests per minute