Guides

Find “SSO Tax” Risks in Your SaaS Portfolio with the Torii API

Many organizations only discover late in the buying cycle that core security features (like SSO, SCIM, audit logs) sit behind higher-tier plans. This guide shows how to build a Google Sheets + Apps Script workflow that calls the Torii API to inventory your applications and flag vendors from a curated “SSO tax” reference list. You’ll get a timestamped, filterable report you can use to prioritize reviews and bring evidence to vendor conversations..

Suggest Edits

Why this matters

Security and governance hinge on predictable access controls and auditability. But in practice, teams often adopt SaaS outside of centralized IT review, and only later realize that baseline security features require costly upgrades. The result is inconsistent posture, higher risk, and awkward surprises at renewal time.

This guide gives you a lightweight, repeatable check using tools your org already has:

  • Torii API to fetch your real app inventory
  • Google Sheets as a simple, auditable canvas
  • Deterministic matching to surface likely “SSO tax” vendors quickly

What you’ll build

  • A Google Sheet with a Torii menu: “Check my apps for SSO tax”

  • A script that:

    • Prompts for your Torii API key at runtime (not stored in the sheet)
    • Calls GET /apps to fetch app id,name,vendor (optionally owners)
    • Cross-checks against your SSO Tax Vendors reference tab using clear, deterministic rules
    • Writes results to an SSO Tax Matches tab with timestamp, match status, and provenance

Prerequisites

  • A Torii API key with read access to Apps
  • A Google Workspace account with access to Apps Script
  • The reference spreadsheet (see template below)

Step 1: Prepare (or copy) the reference sheet

You can either create a new sheet using the table at https://sso.tax/ or start from our template.

📝

Tip — request the template

We’ve published a prebuilt Google Sheet that includes the reference data and headers in the correct order.
Request access to the template here.
After approval, make your own copy and use it in your environment.

If you’re building the sheet yourself, create a tab named SSO Tax Vendors with the headers in this exact order (matching the example file you provided):

Vendor | Base Pricing | SSO Pricing | % Increase | Source | Date Updated | Category
  • The script relies on these header names.
  • It uses: Vendor, % Increase, Source, Date Updated, Category.
  • It will ignore Base Pricing and SSO Pricing during matching (they’re for human context), but the columns should still be present so your sheet matches the template exactly.

Add your vendor rows under those headers. Parenthetical aliases are supported in the Vendor column (e.g., Salesforce (Sales Cloud)).

Step 2: Add the Apps Script

Open your Google Sheet and go to Extensions → Apps Script. Create a new script file and paste the code below (save when done).

// Torii → SSO.tax matcher (Deterministic simple logic)
// Menu: Torii → Check my apps for SSO tax
// API: GET https://api.toriihq.com/v1.0/apps?fields=...
// Auth: Authorization: Bearer <API_KEY>, accept: '*/*'

const TORII_BASE_URL   = 'https://api.toriihq.com/v1.0';
const APPS_ENDPOINT    = '/apps';

// Toggle to include app owner columns in the results
const INCLUDE_OWNER_COLUMNS = false;

// Fields requested from Torii
const REQUEST_FIELDS   = INCLUDE_OWNER_COLUMNS
  ? 'id,name,vendor,primaryOwner,appOwners'
  : 'id,name,vendor';

// Optional: page size control (Torii uses `size`; default is fine for most orgs)
const USE_SIZE_PARAM   = false; // set true to add &size=
const PAGE_SIZE        = 200;   // only used if USE_SIZE_PARAM === true

// Sheet/tab names (must match your spreadsheet)
const SOURCE_TAB_NAME  = 'SSO Tax Vendors';
const RESULTS_TAB_NAME = 'SSO Tax Matches';

// --- Menu ---
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Torii')
    .addItem('Check my apps for SSO tax', 'toriiCheckAppsForSsoTax')
    .addToUi();
}

// --- Entry point ---
function toriiCheckAppsForSsoTax() {
  const ui = SpreadsheetApp.getUi();
  const prompt = ui.prompt(
    'Torii API Key',
    'Enter your Torii API key. It will be used only for this run.',
    ui.ButtonSet.OK_CANCEL
  );
  if (prompt.getSelectedButton() !== ui.Button.OK) {
    ui.alert('Cancelled.');
    return;
  }
  const apiKey = String(prompt.getResponseText() || '').trim();
  if (!apiKey) {
    ui.alert('No API key entered.');
    return;
  }

  try {
    const ss = SpreadsheetApp.getActive();
    const vendorsSheet = ss.getSheetByName(SOURCE_TAB_NAME);
    if (!vendorsSheet) throw new Error('Source tab "' + SOURCE_TAB_NAME + '" not found.');

    const ssoIndex = buildSsoIndex_(vendorsSheet);
    const apps = fetchAllApps_(apiKey);

    const rows = runDeterministicMatches_(apps, ssoIndex);
    writeResults_(ss, rows);

    ui.alert(
      'SSO Tax Checks complete.\n' +
      'Processed ' + apps.length + ' apps.\n' +
      'Wrote ' + rows.length + ' rows to "' + RESULTS_TAB_NAME + '".'
    );
  } catch (err) {
    SpreadsheetApp.getUi().alert('Error: ' + (err && err.message ? err.message : err));
    throw err;
  }
}

// --- Torii fetch (paginated) ---
function fetchAllApps_(apiKeyInput) {
  const apiKey = normalizeApiKey_(apiKeyInput);
  const headers = { Authorization: 'Bearer ' + apiKey, accept: '*/*' };

  let cursor = null;
  const apps = [];
  let pages = 0;

  do {
    const params = { fields: REQUEST_FIELDS };
    if (USE_SIZE_PARAM) params.size = PAGE_SIZE;
    if (cursor) params.cursor = cursor;

    const url = TORII_BASE_URL + APPS_ENDPOINT + '?' + toQueryString_(params);
    const resp = UrlFetchApp.fetch(url, { method: 'get', headers: headers, muteHttpExceptions: true });

    const code = resp.getResponseCode();
    if (code !== 200) {
      throw new Error(
        'GET /apps failed (HTTP ' + code + ')\n' +
        'URL: ' + url + '\n' +
        'Body: ' + resp.getContentText()
      );
    }

    const data = JSON.parse(resp.getContentText());
    if (data && Array.isArray(data.apps)) {
      for (const a of data.apps) {
        apps.push({
          id: a.id,
          name: a.name || '',
          vendor: a.vendor || '',
          primaryOwner: INCLUDE_OWNER_COLUMNS ? (a.primaryOwner || null) : null,
          appOwners: INCLUDE_OWNER_COLUMNS ? (Array.isArray(a.appOwners) ? a.appOwners : []) : []
        });
      }
    }
    cursor = (data && data.nextCursor) ? data.nextCursor : null;

    pages++;
    if (pages > 10000) throw new Error('Pagination guard tripped.');
  } while (cursor);

  return apps;
}

function normalizeApiKey_(raw) {
  let s = String(raw || '').trim();
  while (/^bearer\s+/i.test(s)) s = s.replace(/^bearer\s+/i, '');
  return s.trim();
}

function toQueryString_(obj) {
  return Object.keys(obj).map(k => encodeURIComponent(k) + '=' + encodeURIComponent(obj[k])).join('&');
}

// --- Build SSO index and maps ---
// Uses the following headers from the "SSO Tax Vendors" tab (others are ignored by logic):
// Vendor | % Increase | Category | Date Updated | Source
function buildSsoIndex_(sheet) {
  const rng = sheet.getDataRange();
  const values = rng.getValues();
  if (values.length < 2) throw new Error('"' + SOURCE_TAB_NAME + '" appears empty or header-only.');

  const header = values[0].map(h => String(h).trim());
  const idx = {
    vendor: headerIndex_(header, 'Vendor'),
    pct:    headerIndex_(header, '% Increase'),
    cat:    headerIndex_(header, 'Category'),
    date:   headerIndex_(header, 'Date Updated'),
    source: headerIndex_(header, 'Source')
  };

  // Build rows and precompute normalized forms and aliases
  const ssoRows = [];
  for (let r = 1; r < values.length; r++) {
    const row = values[r];
    const vendor = String(row[idx.vendor] || '').trim();
    if (!vendor) continue;

    const rec = {
      Vendor: vendor,
      pct: String(row[idx.pct] ?? '').trim(),
      Category: String(row[idx.cat] ?? '').trim(),
      DateUpdated: String(row[idx.date] ?? '').trim(),
      Source: String(row[idx.source] ?? '').trim()
    };

    // Precompute normalized keys and tokens
    rec.vendor_norm = norm_(rec.Vendor);
    rec.alias_raw = extractParen_(rec.Vendor);
    rec.alias_norm = norm_(rec.alias_raw);
    rec.vendor_concat_norm = norm_(expandParenToSpaces_(rec.Vendor));
    rec.alias_tokens = tokens_(rec.alias_raw);
    rec.vendor_concat_tokens = tokens_(expandParenToSpaces_(rec.Vendor));

    ssoRows.push(rec);
  }

  // Build maps: keep first occurrence per key
  const map_vendor = {};
  const map_alias = {};
  const map_concat = {};

  for (const rec of ssoRows) {
    if (rec.vendor_norm && !(rec.vendor_norm in map_vendor)) map_vendor[rec.vendor_norm] = rec;
    if (rec.alias_norm && !(rec.alias_norm in map_alias)) map_alias[rec.alias_norm] = rec;
    if (rec.vendor_concat_norm && !(rec.vendor_concat_norm in map_concat)) map_concat[rec.vendor_concat_norm] = rec;
  }

  return {
    rows: ssoRows,
    map_vendor: map_vendor,
    map_alias: map_alias,
    map_concat: map_concat
  };
}

function headerIndex_(header, name) {
  const target = String(name).toLowerCase();
  const idx = header.findIndex(h => String(h).trim().toLowerCase() === target);
  if (idx === -1) throw new Error('Expected column "' + name + '" not found in "' + SOURCE_TAB_NAME + '".');
  return idx;
}

// --- Deterministic matching steps ---
function runDeterministicMatches_(apps, ssoIndex) {
  const out = [];
  const nowIso = new Date().toISOString();

  for (const app of apps) {
    const appId = app.id || '';
    const appName = String(app.name || '');
    const appVendor = String(app.vendor || '');

    const nameKey = norm_(appName);
    const vendKey = norm_(appVendor);
    const comboKey = norm_(appName + ' ' + appVendor);
    const nameTokens = tokens_(appName);

    let match = null;
    let via = '';

    // 1) app.name == Vendor
    if (nameKey && ssoIndex.map_vendor[nameKey]) {
      match = ssoIndex.map_vendor[nameKey]; via = 'name==Vendor';
    }
    // 2) app.name == Alias
    else if (nameKey && ssoIndex.map_alias[nameKey]) {
      match = ssoIndex.map_alias[nameKey]; via = 'name==Alias';
    }
    // 3) app.vendor == Vendor
    else if (vendKey && ssoIndex.map_vendor[vendKey]) {
      match = ssoIndex.map_vendor[vendKey]; via = 'vendor==Vendor';
    }
    // 4) app.vendor == Alias
    else if (vendKey && ssoIndex.map_alias[vendKey]) {
      match = ssoIndex.map_alias[vendKey]; via = 'vendor==Alias';
    }
    // 5) (name + vendor) == vendor-with-parens-expanded
    else if (comboKey && ssoIndex.map_concat[comboKey]) {
      match = ssoIndex.map_concat[comboKey]; via = 'name+vendor==VendorConcat';
    }
    // 6) name tokens subset of alias OR vendor-concat tokens
    else if (nameTokens.length > 0) {
      for (const rec of ssoIndex.rows) {
        if (isSubset_(nameTokens, rec.alias_tokens) || isSubset_(nameTokens, rec.vendor_concat_tokens)) {
          match = rec; via = 'name⊆Alias/VendorConcatTokens'; break;
        }
      }
    }

    // Build output row
    const base = [
      nowIso,
      appId,
      appName,
      appVendor
    ];

    if (INCLUDE_OWNER_COLUMNS) {
      const primaryOwnerName  = safeGet_(app, 'primaryOwner.fullName') || '';
      const primaryOwnerEmail = safeGet_(app, 'primaryOwner.email') || '';
      const ownerEmails = Array.isArray(app.appOwners) ? dedupeEmails_(app.appOwners) : [];
      base.push(primaryOwnerName, primaryOwnerEmail, ownerEmails.length, ownerEmails.join('; '));
    }

    if (match) {
      base.push(
        'Matched',
        via,
        match.Vendor,
        match.pct,
        match.Category,
        match.DateUpdated,
        match.Source
      );
    } else {
      base.push(
        'No Match',
        '',
        '',
        '',
        '',
        '',
        ''
      );
    }

    out.push(base);
  }

  return out;
}

// --- Normalization helpers ---
function norm_(s) {
  if (s == null) return '';
  let x = String(s).toLowerCase();
  x = x.replace(/[()\[\]\{\}]/g, ' ');
  x = x.replace(/[^\w\s]/g, ' ');
  x = x.replace(/\s+/g, ' ').trim();
  return x;
}

function tokens_(s) {
  const n = norm_(s);
  return n ? n.split(/\s+/) : [];
}

function extractParen_(s) {
  const m = String(s || '').match(/\(([^)]+)\)/);
  return m ? m[1].trim() : '';
}

function expandParenToSpaces_(s) {
  return String(s || '').replace(/\(([^)]+)\)/g, ' $1 ');
}

function isSubset_(smallArr, bigArr) {
  if (!smallArr || !smallArr.length) return false;
  const big = new Set(bigArr || []);
  for (const t of smallArr) if (!big.has(t)) return false;
  return true;
}

function safeGet_(obj, path) {
  try {
    return path.split('.').reduce((o, k) => (o && k in o ? o[k] : null), obj);
  } catch (e) {
    return null;
  }
}

function dedupeEmails_(owners) {
  const out = new Set();
  for (const o of owners) {
    const email = (o && o.email) ? String(o.email).trim() : '';
    if (email) out.add(email.toLowerCase());
  }
  return Array.from(out).sort();
}

// --- Write results ---
function writeResults_(ss, rows) {
  let sh = ss.getSheetByName(RESULTS_TAB_NAME);
  if (!sh) sh = ss.insertSheet(RESULTS_TAB_NAME);

  const existingFilter = sh.getFilter();
  if (existingFilter) existingFilter.remove();
  sh.clearContents();

  const headerBase = [
    'Run Timestamp',
    'App ID',
    'App Name',
    'App Vendor'
  ];

  const ownerHeader = INCLUDE_OWNER_COLUMNS
    ? ['Primary Owner Name', 'Primary Owner Email', 'Owner Count', 'All Owner Emails']
    : [];

  const matchHeader = [
    'Match Status',
    'Match Via',
    'Matched Vendor (Sheet)',
    '% Increase',
    'Category',
    'Date Updated',
    'Source'
  ];

  const header = headerBase.concat(ownerHeader).concat(matchHeader);
  const all = [header, ...rows];
  sh.getRange(1, 1, all.length, header.length).setValues(all);

  sh.setFrozenRows(1);
  sh.getRange(1, 1, 1, header.length).setFontWeight('bold');

  // Keep "% Increase" as text (values may include + or ??)
  const pctCol = header.indexOf('% Increase') + 1; // 1-based
  const lastRow = sh.getLastRow();
  if (pctCol > 0 && lastRow > 1) {
    sh.getRange(2, pctCol, lastRow - 1, 1).setNumberFormat('@');
  }

  for (let c = 1; c <= header.length; c++) sh.autoResizeColumn(c);
  sh.getDataRange().createFilter();
}

Step 3: Run the check

  1. Reload the sheet to load the custom menu.
  2. Go to Torii → Check my apps for SSO tax.
  3. Paste your Torii API key when prompted.
  4. The script writes results to SSO Tax Matches and shows a completion dialog.

Step 4: Review and act

What you’ll see in SSO Tax Matches:

  • Run Timestamp — so you can compare runs over time
  • App ID / Name / Vendor — from Torii
  • Match Status — Matched or No Match
  • Match Via — which rule triggered the match (exact name, alias, vendor concat, token subset)
  • Reference columns — the vendor and notes carried from your SSO Tax Vendors tab
  • Optional owner columns — enable INCLUDE_OWNER_COLUMNS = true to add owner name/email and a rollup of all owner emails

Suggested next steps

  • Filter to the matches with the highest % Increase or with critical Category
  • Route to app owners for confirmation and mitigation plans
  • Use this ahead of renewals to strengthen negotiation or require minimum controls

Extending the solution

  • Add more Torii fields (e.g., activeUsersCount, category, tags) by updating REQUEST_FIELDS
  • Automate tickets or alerts from Apps Script (email to owners, or export CSV for your ITSM tool)
  • Enhance matching with additional alias lists or a secondary fuzzy pass if your data has many near-duplicates

Security notes

  • Use a least-privilege Torii API key and rotate regularly
  • The key is only used in-memory during the run and not stored in the sheet by default
  • Share the template link behind access controls to understand community interest and prevent abuse

Troubleshooting

  • HTTP 4xx/5xx: verify the API key and your Torii permissions; ensure your environment can reach api.toriihq.com
  • “Source tab not found”: confirm the tab name is exactly SSO Tax Vendors and headers match the order above
  • No matches: sanity-check vendor naming in your sheet and try adding aliases in parentheses for bundled products

Wrap-up

This pattern is intentionally simple: fetch your live inventory from Torii, compare against a curated reference, and produce a transparent report that anyone can filter and act on. You can reuse the same approach for other examples such as license audits.

Updated about 4 hours ago