DEV Community

Takeshi Hamaya
Takeshi Hamaya

Posted on

Building a Real-time VPN Monitoring System from Tokyo with Google Apps Script

#vpn #googleappsscript #opensource #javascript

TL;DR

I built an open-source VPN monitoring system that:

  • 🚀 Tests 15 VPN services every 6 hours (645+ measurements)
  • 📊 Calculates stability scores using statistical analysis
  • 💰 Monitors pricing with automatic alerts
  • 🚨 Detects outages using 3-sigma method
  • 🛡️ Includes free VPN leak detection tool
  • 💵 Runs on Google Apps Script's free tier ($0/month)

Live demo: https://www.blstweb.jp/network/vpn/

GitHub: https://github.com/hmy0210/vpn-stability-ranking

License: MIT

Why I Built This

Most VPN review sites have three problems:

  1. Affiliate bias: They recommend VPNs that pay higher commissions
  2. One-time tests: Single measurements don't show stability
  3. Geographic bias: Most testing is done from US/EU

I wanted transparent, continuous, unbiased data from Asia.

Architecture Overview 

Google Apps Script (Orchestrator)
  ↓
┌─────────────────────────────────────┐
│  vpn-speed-tracker.gs               │
│  - Runs every 6 hours               │
│  - Tests 15 VPNs                    │
│  - Calculates stability             │
└─────────────────────────────────────┘
  ↓
Google Spreadsheet (Data Store)
  - Speed data
  - Price history  
  - Outage logs
  - News archive
  ↓
Public Web App API
  - JSON endpoint
  - CORS enabled
  - No authentication needed
Enter fullscreen mode Exit fullscreen mode

Why Google Apps Script?

  • ✅ Free tier: 20,000 URL fetches/day
  • ✅ Built-in triggers (time-based, event-based)
  • ✅ Native Spreadsheet integration
  • ✅ Deploy as Web App in one click
  • ✅ No server maintenance

Core Feature 1: Speed Testing & Stability Scoring

The Problem with Average Speed

Average speed alone doesn't tell the full story. A VPN might average 400 Mbps but vary between 200-600 Mbps.

The Solution: Coefficient of Variation (CV)

I use CV to measure stability:

/**
 * Calculate stability score based on speed variation
 * Lower CV = More stable = Higher score
 */
function calculateStabilityScore(vpnName) {
  const sheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID)
    .getSheetByName('速度データ');

  const data = sheet.getDataRange().getValues();
  const recentSpeeds = data
    .filter(row => row[1] === vpnName)
    .slice(-10)  // Last 10 measurements
    .map(row => row[2]);  // Download speed

  if (recentSpeeds.length < 3) {
    return 50;  // Not enough data
  }

  // Calculate mean
  const avg = recentSpeeds.reduce((a, b) => a + b, 0) / recentSpeeds.length;

  // Calculate standard deviation
  const variance = recentSpeeds.reduce((sum, speed) => 
    sum + Math.pow(speed - avg, 2), 0) / recentSpeeds.length;
  const stdDev = Math.sqrt(variance);

  // Coefficient of Variation
  const cv = stdDev / avg;

  // Convert CV to 0-100 score
  // CV < 0.05 = 100 points (very stable)
  // CV > 0.3 = 0 points (unstable)
  const stabilityScore = Math.max(0, Math.min(100, 100 - (cv * 400)));

  return Math.round(stabilityScore);
}
Enter fullscreen mode Exit fullscreen mode

Results after 645+ measurements:

  • NordVPN: 95.2% stability (CV: 0.012)
  • ExpressVPN: 92.1% stability (CV: 0.020)
  • Surfshark: 89.5% stability (CV: 0.026)

Core Feature 2: Statistical Outage Detection

The Challenge

How do you automatically detect when a VPN is having issues?

The Solution: 3-Sigma Method

I use the 3-sigma rule (99.7% confidence interval):

/**
 * Detect outages using statistical analysis
 * Runs every hour via trigger
 */
function detectOutages() {
  const speedSheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID)
    .getSheetByName('速度データ');
  const outageSheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID)
    .getSheetByName('VPN障害検知（高度）');

  const data = speedSheet.getDataRange().getValues();
  const vpns = [...new Set(data.slice(1).map(row => row[1]))];

  vpns.forEach(vpnName => {
    const vpnData = data
      .filter(row => row[1] === vpnName)
      .slice(-20);  // Last 20 measurements

    if (vpnData.length < 10) return;  // Need at least 10 data points

    const speeds = vpnData.map(row => row[2]);
    const latest = speeds[speeds.length - 1];
    const historical = speeds.slice(0, -1);

    // Calculate mean and standard deviation
    const avg = historical.reduce((a, b) => a + b, 0) / historical.length;
    const stdDev = Math.sqrt(
      historical.reduce((sum, speed) => 
        sum + Math.pow(speed - avg, 2), 0) / historical.length
    );

    // 3-sigma threshold
    const threshold = avg - (3 * stdDev);

    if (latest < threshold) {
      const reason = `Speed anomaly detected: ${latest.toFixed(1)} Mbps ` +
                     `(avg: ${avg.toFixed(1)} Mbps, threshold: ${threshold.toFixed(1)} Mbps)`;

      // Check for consecutive anomalies
      const previousOutages = outageSheet.getDataRange().getValues()
        .filter(row => row[1] === vpnName)
        .slice(-5);

      const consecutiveCount = previousOutages.filter(row => 
        (new Date() - new Date(row[0])) < 3 * 60 * 60 * 1000  // Within 3 hours
      ).length + 1;

      // Log anomaly
      outageSheet.appendRow([
        new Date(),
        vpnName,
        latest,
        reason,
        consecutiveCount
      ]);

      // Alert if 3+ consecutive anomalies
      if (consecutiveCount >= 3) {
        sendAlert(`Outage detected: ${vpnName} - ${consecutiveCount} consecutive anomalies`);
      }
    }
  });
}
Enter fullscreen mode Exit fullscreen mode

Real outages detected:

  • AtlasVPN: 4 consecutive anomalies (Dec 10, 2-8am)
  • Windscribe: Speed drop from 320 → 85 Mbps (Dec 15)
  • TunnelBear: 3 consecutive failures (Dec 18)

Core Feature 3: Price Monitoring

VPN prices change without notice. I track them daily:

/**
 * Scrape VPN pricing daily
 * Uses ScraperAPI with fallback to direct scraping
 */
function scrapeAllVPNPrices() {
  const sheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID)
    .getSheetByName('VPN料金履歴');

  const vpnPricing = getVPNPricingConfig();

  vpnPricing.forEach(vpn => {
    try {
      let price = null;
      let method = 'direct';

      // Try ScraperAPI first
      if (SCRAPER_CONFIG.USE_SCRAPER_API && SCRAPER_CONFIG.API_KEY) {
        try {
          price = scrapeWithScraperAPI(vpn);
          method = 'scraperapi';
        } catch (error) {
          Logger.log(`ScraperAPI failed: ${vpn.name}`);
        }
      }

      // Fallback to direct scraping
      if (!price) {
        try {
          price = scrapeDirect(vpn);
        } catch (error) {
          price = vpn.defaultPrice;  // Use fallback price
        }
      }

      // Save to spreadsheet
      sheet.appendRow([
        new Date(),
        vpn.name,
        price,
        vpn.currency,
        method,
        !price ? true : false  // Fallback flag
      ]);

      Utilities.sleep(1000);  // Rate limiting

    } catch (error) {
      Logger.log(`Price scraping error: ${vpn.name} - ${error.message}`);
    }
  });
}
Enter fullscreen mode Exit fullscreen mode

Price changes detected in 2 weeks:

  • AtlasVPN: ¥480 → ¥520 (+8.3%)
  • HideMyAss: ¥650 → ¥580 (-10.8%)
  • Windscribe: ¥420 → ¥450 (+7.1%)

Core Feature 4: VPN Leak Detection Tool

100% client-side security diagnostics:

/**
 * DNS Leak Detection
 * Queries multiple DNS servers and compares IPs
 */
async function detectDNSLeak() {
  try {
    const responses = await Promise.all([
      fetch('https://api.ipify.org?format=json'),
      fetch('https://api.my-ip.io/ip.json')
    ]);

    const ips = await Promise.all(responses.map(r => r.json()));

    // All IPs should match if VPN is working
    if (ips.every((ip, i, arr) => ip.ip === arr[0].ip)) {
      return {
        status: 'success',
        message: 'No DNS leak detected - All servers report same IP',
        ip: ips[0].ip
      };
    } else {
      return {
        status: 'warning',
        message: 'Possible DNS leak - Different IPs detected',
        details: ips.map(ip => ip.ip).join(', ')
      };
    }
  } catch (error) {
    return {
      status: 'error',
      message: 'DNS leak check failed'
    };
  }
}

/**
 * WebRTC Leak Detection
 * Analyzes ICE candidates for real IP exposure
 */
async function detectWebRTCLeak() {
  return new Promise((resolve) => {
    const ips = [];

    const pc = new RTCPeerConnection({
      iceServers: [{ urls: 'stun:stun.l.google.com:19302' }]
    });

    pc.createDataChannel('');
    pc.createOffer().then(offer => pc.setLocalDescription(offer));

    pc.onicecandidate = (ice) => {
      if (!ice || !ice.candidate) {
        pc.close();

        if (ips.length === 0) {
          resolve({
            status: 'success',
            message: 'No WebRTC leak - Real IP not exposed'
          });
        } else {
          resolve({
            status: 'warning',
            message: `Private IPs detected (${ips.length})`,
            details: ips.join(', ')
          });
        }
        return;
      }

      // Extract IP from ICE candidate
      const ipMatch = /([0-9]{1,3}\.){3}[0-9]{1,3}/.exec(ice.candidate.candidate);
      if (ipMatch && !ips.includes(ipMatch[0])) {
        ips.push(ipMatch[0]);
      }
    };

    setTimeout(() => {
      pc.close();
      resolve({ status: 'success', message: 'No leak detected' });
    }, 3000);
  });
}
Enter fullscreen mode Exit fullscreen mode

Try it live: https://www.blstweb.jp/network/vpn/vpn-diagnosis-tool/

Public API

All data is available via REST API:

curl https://script.google.com/macros/s/YOUR_ID/exec
Enter fullscreen mode Exit fullscreen mode

Response:

{
  "status": "success",
  "timestamp": "2025-12-21T10:00:00.000Z",
  "totalVPNs": 15,
  "rankings": [
    {
      "timestamp": "2025-12-21T09:00:00.000Z",
      "vpnName": "NordVPN",
      "download": 460,
      "upload": 230,
      "ping": 15,
      "stability": 95.2,
      "reliability": 100,
      "totalScore": 97.8,
      "rank": 1
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Use cases:

  • Embed rankings in your blog
  • Research & analysis
  • Build your own VPN comparison tool

Key Findings After 645+ Measurements

1. Speed Varies by Time of Day

Speed drops 30% during peak hours (9pm-12am Tokyo time):

Time Avg Speed Variation
6-9am 412 Mbps Baseline
12-3pm 385 Mbps -6.5%
9pm-12am 289 Mbps -29.9%

Lesson: One-time tests are misleading.

2. Price ≠ Performance

MillenVPN (¥360/month) vs ExpressVPN (¥1,280/month):

  • Price difference: 3.5x
  • Speed difference: 1.27x (433 vs 340 Mbps)

Lesson: Expensive VPNs aren't always faster.

3. Weekends are More Stable

Weekend stability: 92.1% vs Weekday: 87.3%

Lesson: Server load affects performance.

Cost Breakdown

Total monthly cost: $0

Component Service Cost
Automation Google Apps Script Free
Data Storage Google Spreadsheet Free
Web App API Apps Script Web App Free
Price Scraping ScraperAPI (optional) $0-29
Twitter Posting Twitter API (optional) Free

Google Apps Script Free Tier:

  • 20,000 URL Fetch calls/day
  • 6 hours execution time/day
  • 30 MB script size limit

Perfect for this use case!

Setup Guide (5 Minutes)

1. Clone Repository 

git clone https://github.com/yourusername/vpn-stability-ranking.git
cd vpn-stability-ranking
Enter fullscreen mode Exit fullscreen mode

2. Create Google Spreadsheet

Create sheets:

  • 速度データ (Speed Data)
  • VPN料金履歴 (Price History)
  • VPN障害検知（高度） (Outage Detection)
  • VPNニュース履歴 (News History)

3. Set Up Apps Script

  1. Extensions → Apps Script
  2. Copy files from gas/ folder
  3. Edit config.gs: 
const CONFIG = {
  SPREADSHEET_ID: 'your-spreadsheet-id',
  TIMEZONE: 'Asia/Tokyo'
};
Enter fullscreen mode Exit fullscreen mode

4. Deploy as Web App

  1. Deploy → New deployment
  2. Type: Web app
  3. Execute as: Me
  4. Who has access: Anyone
  5. Deploy

5. Set Triggers

  • measureAllVPNSpeeds: Every 6 hours
  • scrapeAllVPNPrices: Daily at 9am
  • detectOutages: Every hour
  • checkPriceChanges: Daily at 10am

Done! Your system is now running.

Lessons Learned

1. Statistical Methods > Simple Averages

Using CV and 3-sigma method revealed patterns I couldn't see with averages alone.

2. Automation is Essential

Manual testing 645 times would be impossible. Automation made this dataset possible.

3. Open Source Creates Trust

No affiliate links = No bias. Users trust the data more.

4. Free Tier is Powerful

Google Apps Script's free tier is incredibly generous. Perfect for side projects.

Future Plans

Short-term (1 month):

  • [ ] Add 5 more VPN providers (total: 20)
  • [ ] Test from multiple Japanese cities
  • [ ] Add streaming service compatibility tests

Medium-term (3 months):

  • [ ] API rate limiting & authentication
  • [ ] Community features (user reviews)
  • [ ] Mobile app

Long-term (6 months):

  • [ ] Multi-region testing (Singapore, Hong Kong)
  • [ ] Machine learning for anomaly detection
  • [ ] Premium features (custom alerts)

Contributing

This is a community project! Contributions welcome:

  • 🐛 Report bugs: GitHub Issues
  • 💡 Suggest features: GitHub Discussions
  • 🔧 Submit PRs: See CONTRIBUTING.md
  • 📖 Improve docs: Help translate
  • 💬 Share feedback: Twitter @remoteaccessvpn

Good first issues available!

Links

Conclusion

Building this system taught me:

  • Statistical analysis > simple metrics
  • Automation enables impossible tasks
  • Open source builds trust
  • Free tier can be powerful

The data speaks for itself: NordVPN is the most stable, ExpressVPN has the lowest variation, and price doesn't always equal performance.

All code and data are open source. Fork it, improve it, use it!

Have questions? Comment below or open an issue on GitHub!

Top comments (0)