Building a VPN Data Pipeline on Google Apps Script's Free Tier

Correction (October 2026): An earlier version of this article presented speed, stability, outage and price-change figures as results of "645+ measurements". Those figures were not real measurements and could not be verified, so I have removed them. The speed values shown on my site are editorial estimates, not measured results. The architecture and code below are unchanged and still describe how the pipeline works. I also want to disclose that the site I run (BLST VPN Navi) earns affiliate commissions from some VPN providers.

TL;DR

I built a data pipeline on Google Apps Script that:

• 🔁 Runs a batch job for 15 VPN services every 6 hours
• 📊 Computes a stability score from the stored values (coefficient of variation)
• 🚨 Flags anomalies with a 3-sigma rule
• 💰 Keeps a price history and alerts on changes
• 🛡️ Ships a client-side DNS/WebRTC leak check tool
• 💵 Runs on Google Apps Script's free tier ($0/month)

Site: https://www.blstweb.jp/network/
GitHub: https://github.com/hmy0210/vpn-stability-ranking
License: MIT

---

Why I Built This

I run a Japanese guide for three online worries — being watched, being hacked, and not being able to connect abroad — that answers each one with official sources and daily price data, then points readers to a service that solves it. I wanted the numbers on the site (prices, server counts, scores) to update automatically instead of being typed by hand, and I wanted to see how far Google Apps Script's free tier could go as a scheduler, data store and API.

---

Architecture Overview

Google Apps Script (Orchestrator)
  ↓
┌─────────────────────────────────────┐
│  vpn-speed-tracker.gs               │
│  - Runs every 6 hours               │
│  - Processes 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 Averages

An average alone doesn't tell the full story: two series with the same average can vary very differently.

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

---

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

---

Core Feature 3: Price Monitoring

VPN prices change without notice, so the pipeline keeps a daily price history:

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

---

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/leak-check/

---

Public API

All data is available via REST API:

curl https://script.google.com/macros/s/YOUR_ID/exec

Example response (illustrative values, not measurements):

{
  "status": "success",
  "timestamp": "2025-12-21T10:00:00.000Z",
  "totalVPNs": 15,
  "rankings": [
    {
      "timestamp": "2025-12-21T09:00:00.000Z",
      "vpnName": "ExampleVPN",
      "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

---

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

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

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

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

Updating prices and scores by hand doesn't scale. Scheduled jobs keep the data current.

3. Be Clear About What the Numbers Are

Say where each number comes from (official pages, estimates, or measurements) and disclose affiliate relationships. I learned this the hard way — see the correction at the top.

4. Free Tier is Powerful

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

---

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!

---

Links

• Site: https://www.blstweb.jp/network/
• GitHub: https://github.com/hmy0210/vpn-stability-ranking
• Leak Check Tool: https://www.blstweb.jp/network/vpn/leak-check/
• Project X Account:X @remoteaccessvpn
• License: MIT

---

Conclusion

Building this system taught me:

• Statistical methods (CV, 3-sigma) are easy to implement in Apps Script
• Scheduled automation keeps data current at no cost
• Be explicit about where every number comes from

All code is open source. Fork it, improve it, use it!

---

📚 Japanese Version

• Zenn: Google Apps Scriptで本番レベルのバッチ処理を実装する

---

VPN data pipeline on Google Apps Script - MIT License - GitHub

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