VOIP 1 and VOIP 2 Performance Dashboard

What this is

Calls were tracked in two separate VoIP systems, each with its own report format. I connected both to a single Google Sheets workbook through their APIs using Google Apps Script, so call activity could be tracked in one place and records pulled without exporting from each system. This page is a web replica of that workbook: the same sheets, the same KPI formulas, and the same records.

Pipeline

How it stays current

• Hourly trigger. A time-driven trigger runs the refresh even when nobody has the sheet open. A menu item turns it on or off.
• Overlap window. Each run re-reads from one day before the latest stored call and stops five minutes before "now", so late-arriving and still-in-progress calls are picked up on a later run.
• Locking. A script lock stops a manual refresh and a scheduled one from running at the same time.
• Logging. Every run writes the range requested, records fetched, records added, and duplicates skipped to the Refresh Log, including failures and the provider's error message.
• History. Older calls were loaded once from each provider's CSV export, then checked against expected counts before the API refresh took over.
• Credentials. API keys and logins live in Script Properties, not in the code or the sheet.

Shared schema

Excerpt: append only new records

function appendUniqueApiRows_(dataSheet, rows, existingKeys) {
  const uniqueRows = [];
  let duplicates = 0;

  rows.forEach(row => {
    const key = String(row[1] || "");          // "provider|call id"

    if (!key || existingKeys.has(key)) {
      duplicates++;
      return;
    }

    existingKeys.add(key);
    uniqueRows.push(row);
  });

  if (uniqueRows.length) appendRows_(dataSheet, uniqueRows);
  return { added: uniqueRows.length, duplicates };
}

Excerpt: hourly automatic refresh

function installAutomaticRefresh() {
  const removed = removeAutomaticRefreshTriggers_();   // never stack duplicate triggers

  ScriptApp.newTrigger("refreshAllCDRs")
    .timeBased()
    .everyHours(1)
    .create();

  logRefresh_("Automatic Refresh", "Enabled", 0,
    `Hourly trigger installed (${removed} old trigger(s) replaced).`);
}