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

Takeshi Hamaya — Sat, 20 Dec 2025 16:49:01 +0000

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/tokyo-vpn-speed-monitor/
GitHub: https://github.com/hmy0210/vpn-stability-ranking

License: MIT

Why I Built This

Most VPN review sites have three problems:

Affiliate bias: They recommend VPNs that pay higher commissions
One-time tests: Single measurements don't show stability
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

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

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

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

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

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

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
    }
  ]
}

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

2. Create Google Spreadsheet

Create sheets:

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

3. Set Up Apps Script

Extensions → Apps Script
Copy files from gas/ folder
Edit config.gs:

const CONFIG = {
  SPREADSHEET_ID: 'your-spreadsheet-id',
  TIMEZONE: 'Asia/Tokyo'
};

4. Deploy as Web App

Deploy → New deployment
Type: Web app
Execute as: Me
Who has access: Anyone
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: X @takechiyo0210

Good first issues available!

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!

📚 Read in Other Languages & Platforms

This article is also available in:

Japanese:

Zenn (技術詳細版) - 日本語・コード多め
Qiita (技術解説) - 日本語・実装詳細
note (ストーリー版) - 日本語・読みやすい

English:

Medium (Data-driven story) - Focus on findings & insights

All versions cover the same project but with different angles!

Tokyo VPN Speed Monitor - MIT License - GitHub

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

DEV Community: Takeshi Hamaya