Pulling Twilio Usage Data into Google Sheets

I needed to track daily Twilio costs in a spreadsheet. The Twilio console has usage data but no easy export, and I wanted it updating automatically.

The solution: Apps Script + Secret Manager Link to heading

I wrote an Apps Script that:

Fetches Twilio credentials from GCP Secret Manager (not hardcoded)
Pulls daily usage data via the Twilio API
Only fetches missing dates (incremental updates)
Handles pagination automatically

The full script is here: twilio-usage-appscript

Setup Link to heading

1. Link Apps Script to GCP Link to heading

In the Apps Script editor:

Project Settings (gear icon)
Under “Google Cloud Platform (GCP) Project”, click Change project
Enter your GCP project number

2. Configure OAuth scopes Link to heading

Enable “Show appsscript.json manifest file in editor” in Project Settings, then set:

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/cloud-platform",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

You need all three:

spreadsheets.currentonly - write to the sheet
cloud-platform - access Secret Manager
script.external_request - call external APIs (Twilio)

3. Set Script Properties Link to heading

In Project Settings → Script Properties, add:

GCP_PROJECT_ID - your GCP project ID
GCP_SECRET_ACCOUNT_SID_NAME - name of secret containing Twilio Account SID
GCP_SECRET_AUTH_TOKEN_NAME - name of secret containing Twilio Auth Token

4. Grant Secret Manager access Link to heading

The Apps Script runs as a service account. Grant it access to your secrets:

gcloud secrets add-iam-policy-binding TWILIO_ACCOUNT_SID \
  --member="serviceAccount:[email protected]" \
  --role="roles/secretmanager.secretAccessor"

Key bits of the script Link to heading

Fetching secrets from Secret Manager Link to heading

function getSecret(projectId, secretName) {
  const url = `https://secretmanager.googleapis.com/v1/projects/${projectId}/secrets/${secretName}/versions/latest:access`;
  const response = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() }
  });
  const data = JSON.parse(response.getContentText());
  return Utilities.newBlob(Utilities.base64Decode(data.payload.data)).getDataAsString();
}

The secret payload is base64 encoded, hence the decode step.

Incremental updates Link to heading

The script reads existing dates from column A and only fetches missing ones:

const existingDates = new Set();
const dateColumn = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
dateColumn.forEach(row => {
  if (row[0]) {
    // Handle both Date objects and strings
    const date = row[0] instanceof Date
      ? `${row[0].getFullYear()}-${String(row[0].getMonth() + 1).padStart(2, '0')}-${String(row[0].getDate()).padStart(2, '0')}`
      : String(row[0]);
    existingDates.add(date);
  }
});

Gotcha: Don’t use toISOString() for date comparison - it converts to UTC which can shift dates by a day depending on timezone.

Twilio API pagination Link to heading

The Twilio API paginates at 1000 records. With ~100 usage categories per day, you’ll hit pagination for any chunk larger than ~10 days:

let allRecords = [];
let nextPageUri = `/2010-04-01/Accounts/${accountSid}/Usage/Records/Daily.json?StartDate=${start}&EndDate=${end}&PageSize=1000`;

while (nextPageUri) {
  const response = UrlFetchApp.fetch('https://api.twilio.com' + nextPageUri, options);
  const data = JSON.parse(response.getContentText());
  allRecords = allRecords.concat(data.usage_records);
  nextPageUri = data.next_page_uri;
}

Output Link to heading

The script creates a “Twilio Usage” sheet with daily rows and category columns:

Date	sms-outbound	sms-pumping-protection	phonenumbers	Total
2025-01-15	$68.29	$27.31	$0.08	$95.68

Run it from the Twilio menu (Extensions → Twilio → Refresh Usage Data) or set up a daily trigger.