Google Apps Script to fetch Twilio usage data into a spreadsheet

README.md

Twilio Usage Google Apps Script

Fetches daily Twilio usage data into a Google Sheets spreadsheet.

Features

• Pulls daily usage data for the past year
• Breaks down costs by category (SMS outbound, inbound, pumping protection, phone numbers, calls)
• Incremental updates - only fetches missing dates, skips dates already in the sheet
• Handles Twilio API pagination automatically
• Never fetches today's data (incomplete)

Setup

1. Open your Google Spreadsheet
2. Go to Extensions > Apps Script
3. Paste the contents of twilio_usage.js
4. Set up credentials:
   • Click Project Settings (gear icon)
   • Scroll to Script Properties
   • Add TWILIO_ACCOUNT_SID with your Account SID
   • Add TWILIO_AUTH_TOKEN with your Auth Token
5. Save and reload the spreadsheet
6. Use Twilio > Refresh Usage Data from the menu

Output

Creates a "Twilio Usage" sheet with columns:

Date	sms-outbound	sms-inbound	sms-pumping-protection	phonenumbers	calls	calls-inbound	calls-outbound	Total
2025-01-01	$68.29	$0.00	$27.31	$0.08	$0.00	$0.00	$0.00	$95.68

Notes

• Credentials are stored in Script Properties, not in the code
• Re-running the script only fetches new dates since the last run
• Data is sorted by date after each update

appsscript.json

{
  "timeZone": "Europe/London",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/cloud-platform",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

twilio_usage.js

function getTwilioUsage() {
  const props = PropertiesService.getScriptProperties();
  const accountSid = props.getProperty('TWILIO_ACCOUNT_SID');
  const authToken = props.getProperty('TWILIO_AUTH_TOKEN');

  if (!accountSid || !authToken) {
    throw new Error('Set TWILIO_ACCOUNT_SID and TWILIO_AUTH_TOKEN in Script Properties');
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('Twilio Usage');

  const preferredCategories = [
    'sms-outbound',
    'sms-inbound',
    'sms-pumping-protection',
    'phonenumbers',
    'calls',
    'calls-inbound',
    'calls-outbound'
  ];

  // Create sheet if needed
  if (!sheet) {
    sheet = ss.insertSheet('Twilio Usage');
    const headers = ['Date', ...preferredCategories, 'Total'];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  }

  const existingHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const currentCategories = existingHeaders.slice(1, -1);

  // Get existing dates from sheet
  const existingDates = new Set();
  if (sheet.getLastRow() > 1) {
    const dateColumn = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
    dateColumn.forEach(row => {
      if (row[0]) {
        const date = row[0] instanceof Date
          ? row[0].toISOString().split('T')[0]
          : String(row[0]);
        existingDates.add(date);
      }
    });
  }

  const formatDate = (d) => d.toISOString().split('T')[0];

  // Date range: past year up to yesterday (never today)
  const endDate = new Date();
  endDate.setDate(endDate.getDate() - 1);
  const startDate = new Date(endDate);
  startDate.setFullYear(startDate.getFullYear() - 1);

  // Build list of all dates we need
  const allDatesNeeded = [];
  for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
    const dateStr = formatDate(d);
    if (!existingDates.has(dateStr)) {
      allDatesNeeded.push(dateStr);
    }
  }

  if (allDatesNeeded.length === 0) {
    SpreadsheetApp.getUi().alert('No missing dates to fetch');
    return;
  }

  const options = {
    method: 'get',
    headers: {
      'Authorization': 'Basic ' + Utilities.base64Encode(accountSid + ':' + authToken)
    },
    muteHttpExceptions: true
  };

  let totalAdded = 0;

  // Group missing dates into contiguous chunks of up to 30 days
  let i = 0;
  while (i < allDatesNeeded.length) {
    const chunkStart = allDatesNeeded[i];
    let chunkEndIdx = i;

    // Find contiguous dates (up to 30)
    while (
      chunkEndIdx < allDatesNeeded.length - 1 &&
      chunkEndIdx - i < 29 &&
      isNextDay(allDatesNeeded[chunkEndIdx], allDatesNeeded[chunkEndIdx + 1])
    ) {
      chunkEndIdx++;
    }

    const chunkEnd = allDatesNeeded[chunkEndIdx];
    const chunkDates = new Set(allDatesNeeded.slice(i, chunkEndIdx + 1));

    // Fetch ALL pages for this chunk
    let allRecords = [];
    let nextPageUri = `/2010-04-01/Accounts/${accountSid}/Usage/Records/Daily.json?StartDate=${chunkStart}&EndDate=${chunkEnd}&PageSize=1000`;

    while (nextPageUri) {
      const url = 'https://api.twilio.com' + nextPageUri;
      const response = UrlFetchApp.fetch(url, options);

      if (response.getResponseCode() !== 200) {
        throw new Error('Twilio API error: ' + response.getContentText());
      }

      const data = JSON.parse(response.getContentText());
      allRecords = allRecords.concat(data.usage_records);
      nextPageUri = data.next_page_uri;
    }

    // Build date map for this chunk
    const dateMap = {};
    allRecords.forEach(r => {
      const date = r.start_date;
      if (!chunkDates.has(date)) return;

      if (!dateMap[date]) {
        dateMap[date] = {};
      }
      dateMap[date][r.category] = parseFloat(r.price);
    });

    // Build rows
    const dates = Object.keys(dateMap).sort();
    const rows = dates.map(date => {
      const catValues = currentCategories.map(cat => dateMap[date][cat] || 0);
      const total = catValues.reduce((sum, v) => sum + v, 0);
      return [date, ...catValues, total];
    });

    // Write this chunk
    if (rows.length > 0) {
      const lastRow = sheet.getLastRow();
      sheet.getRange(lastRow + 1, 1, rows.length, existingHeaders.length).setValues(rows);
      sheet.getRange(lastRow + 1, 2, rows.length, existingHeaders.length - 1).setNumberFormat('$#,##0.00');
      totalAdded += rows.length;
      SpreadsheetApp.flush();
    }

    i = chunkEndIdx + 1;
  }

  // Sort by date at the end
  if (totalAdded > 0) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, existingHeaders.length).sort(1);
  }

  sheet.autoResizeColumns(1, existingHeaders.length);
  SpreadsheetApp.getUi().alert(`Added ${totalAdded} new days of usage data`);
}

function isNextDay(date1, date2) {
  const d1 = new Date(date1);
  const d2 = new Date(date2);
  d1.setDate(d1.getDate() + 1);
  return d1.toISOString().split('T')[0] === date2;
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Twilio')
    .addItem('Refresh Usage Data', 'getTwilioUsage')
    .addToUi();
}