Skip to content
ShortVid API

Import TikTok Profile & Video Metrics to Google Sheets

Paste a list of @handles or profile URLs in Column A and click ShortVidAPI → Fetch Creator & Video Metrics (Skip Filled). Pulls follower count, total likes, bio link, public bio email, recent 10-video average views, engagement rate, and audience country code — with zero recalculation credit burn.

Why We Never Ship Custom Cell Formulas (=TIKTOK_USER)

Custom Google Sheets formulas automatically re-fire whenever your team reopens the workbook, sorts a filter view, or inserts a row — silently draining API credits on unchanged creators. Our Apps Script writes static values, skips rows that already have data, caches every handle for 6 hours via CacheService, and stores your API key in PropertiesService (never exposed in cells).

Get Free API Key (10,500 Credits) →

3-minute setup in any Google Sheet

  1. STEP 1 · Open a Google Sheet, click Copy Row-1 Headers (TSV) above, and paste into cell A1. Add TikTok handles (e.g. @shopify, khaby.lame) in A2:A.
  2. STEP 2 · Go to Extensions → Apps Script, replace the default Code.gs with the script below, click Save (Ctrl+S / Cmd+S), and reload your Google Sheet tab.
  3. STEP 3 · In the new ShortVidAPI top menu, click Set API Key (PropertiesService) once, then run Fetch Creator & Video Metrics (Skip Filled).

Complete Google Apps Script (`Code.gs`)

Calls GET /v1/user/info + GET /v1/user/posts?count=10 with 6-hour CacheService and automatic 404 zero-charge handling.

/**
 * ShortVid API — Google Sheets Creator & Video Metrics Batch Enricher
 * Website: https://shortvidapi.com/integrations/tiktok-to-google-sheets
 *
 * WHY NO CUSTOM CELL FORMULAS (=TIKTOK_USER)?
 * Google Sheets automatically recalculates custom cell formulas whenever a workbook
 * opens, sorts, or refreshes — silently burning API credits on unchanged rows.
 * This script uses a manual custom menu + Skip-Filled guard + 6-hour CacheService
 * + PropertiesService secret storage so you ONLY pay once per unique creator.
 *
 * Expected Sheet Columns (Row 1 = Headers, Data starts at Row 2):
 *   Col A: TikTok Handle (e.g. @shopify or khaby.lame)
 *   Col B: Display Name       Col C: Followers          Col D: Total Likes
 *   Col E: Published Videos   Col F: Verified           Col G: Bio Website Link
 *   Col H: Public Bio Email   Col I: Recent 10 Avg Views Col J: Recent 10 ER %
 *   Col K: Audience Region    Col L: Latest Video URL   Col M: Sync Status
 */

const SHORTVID_BASE_URL = "https://api.shortvidapi.com";
const CACHE_TTL_SECONDS = 21600; // 6 hours (max allowed by Apps Script CacheService)
const RECENT_VIDEOS_SAMPLE = 10; // 10 videos * 2 credits = 20 credits ($0.002 PAYG / $0.0008 Scale)

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("ShortVidAPI")
    .addItem("Fetch Creator & Video Metrics (Skip Filled)", "fetchCreatorMetricsSkipFilled")
    .addItem("Force Refresh Selected Rows", "forceRefreshSelectedRows")
    .addSeparator()
    .addItem("Set API Key (PropertiesService)", "promptAndSaveApiKey")
    .addToUi();
}

function promptAndSaveApiKey() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt(
    "ShortVid API Key",
    "Paste your tk_live_... key from https://shortvidapi.com/dashboard (stored securely in PropertiesService, never in sheet cells):",
    ui.ButtonSet.OK_CANCEL
  );
  if (res.getSelectedButton() !== ui.Button.OK) return;
  const key = res.getResponseText().trim();
  if (!key || !key.startsWith("tk_")) {
    ui.alert("Invalid key format. Expected a key starting with tk_.");
    return;
  }
  PropertiesService.getScriptProperties().setProperty("SHORTVIDAPI_KEY", key);
  ui.alert("API key saved to Script Properties.");
}

function fetchCreatorMetricsSkipFilled() {
  runSheetEnrichment({ skipFilled: true, selectedOnly: false });
}

function forceRefreshSelectedRows() {
  runSheetEnrichment({ skipFilled: false, selectedOnly: true });
}

function runSheetEnrichment(options) {
  const ui = SpreadsheetApp.getUi();
  const apiKey = PropertiesService.getScriptProperties().getProperty("SHORTVIDAPI_KEY");
  if (!apiKey) {
    ui.alert("Missing API key. Click ShortVidAPI -> Set API Key (PropertiesService) first.");
    return;
  }

  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    ui.alert("Add at least one TikTok handle in Column A (starting at Row 2).");
    return;
  }

  let startRow = 2;
  let numRows = lastRow - 1;
  if (options.selectedOnly) {
    const sel = sheet.getActiveRange();
    if (!sel || sel.getRow() < 2) {
      ui.alert("Highlight one or more data rows (Row 2+) first.");
      return;
    }
    startRow = sel.getRow();
    numRows = sel.getNumRows();
  }

  const range = sheet.getRange(startRow, 1, numRows, 13);
  const rows = range.getValues();
  const cache = CacheService.getScriptCache();
  let updatedCount = 0;

  for (let i = 0; i < rows.length; i++) {
    const rawHandle = String(rows[i][0] || "").trim();
    if (!rawHandle) continue;

    // Skip rows where Followers (Col C = index 2) is already populated
    if (options.skipFilled && rows[i][2] !== "" && rows[i][2] !== null) {
      continue;
    }

    const handle = cleanTikTokHandle_(rawHandle);
    if (!handle) {
      rows[i][12] = "SKIPPED_INVALID_HANDLE";
      continue;
    }

    const cacheKey = "sv_v1_" + handle.toLowerCase();
    let payload = null;
    if (options.skipFilled) {
      const cachedRaw = cache.get(cacheKey);
      if (cachedRaw) {
        try { payload = JSON.parse(cachedRaw); } catch (_) { payload = null; }
      }
    }

    if (!payload) {
      payload = fetchProfileAndPosts_(handle, apiKey);
      if (payload.ok) {
        try { cache.put(cacheKey, JSON.stringify(payload), CACHE_TTL_SECONDS); } catch (_) {}
      }
      Utilities.sleep(180); // Polite pacing well within 60-600 RPM plan limits
    }

    if (!payload.ok) {
      rows[i][12] = payload.statusLabel;
      if (payload.stopBatch) {
        range.setValues(rows);
        ui.alert("Batch paused: " + payload.statusLabel);
        return;
      }
      continue;
    }

    rows[i][1] = payload.nickname;
    rows[i][2] = payload.followers;
    rows[i][3] = payload.hearts;
    rows[i][4] = payload.videos;
    rows[i][5] = payload.verified ? "Yes" : "No";
    rows[i][6] = payload.bioLink;
    rows[i][7] = payload.bioEmail;
    rows[i][8] = payload.avgViews;
    rows[i][9] = payload.engagementPct;
    rows[i][10] = payload.region;
    rows[i][11] = payload.latestVideoUrl;
    rows[i][12] = "OK (" + new Date().toISOString().slice(0, 16).replace("T", " ") + "Z)";
    updatedCount++;
  }

  range.setValues(rows);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Updated " + updatedCount + " creator row(s).",
    "ShortVidAPI",
    5
  );
}

function cleanTikTokHandle_(input) {
  const urlMatch = input.match(/tiktok\.com\/@([A-Za-z0-9._-]+)/i);
  const candidate = urlMatch ? urlMatch[1] : input.replace(/^@+/, "").split(/[/?#\s]/)[0];
  return /^[A-Za-z0-9._-]{1,64}$/.test(candidate) ? candidate : "";
}

function fetchProfileAndPosts_(handle, apiKey) {
  const headers = { "x-api-key": apiKey, "Accept": "application/json" };

  // 1) Profile & lifetime counters: 10 credits ($0.001 PAYG / $0.0004 Business Scale; 0 on 404)
  const infoResp = UrlFetchApp.fetch(
    SHORTVID_BASE_URL + "/v1/user/info?unique_id=" + encodeURIComponent(handle),
    { method: "get", headers: headers, muteHttpExceptions: true }
  );
  const code = infoResp.getResponseCode();
  if (code === 404) return { ok: false, statusLabel: "NOT_FOUND (0 credits)" };
  if (code === 401 || code === 402) {
    return { ok: false, statusLabel: "HTTP_" + code + "_CHECK_KEY_OR_CREDITS", stopBatch: true };
  }
  if (code !== 200) return { ok: false, statusLabel: "HTTP_" + code + "_RETRY_LATER" };

  const infoJson = JSON.parse(infoResp.getContentText());
  if (!infoJson || infoJson.code !== 0 || !infoJson.data || !infoJson.data.user) {
    return { ok: false, statusLabel: "EMPTY_PROFILE (0 credits)" };
  }

  const user = infoJson.data.user;
  const stats = infoJson.data.stats || {};
  const sig = String(user.signature || "");
  const emailMatch = sig.match(/[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}/);

  // 2) Recent 10 videos: 2 credits/video = 20 credits ($0.002 PAYG / $0.0008 Business Scale)
  let avgViews = 0;
  let engagementPct = 0;
  let region = "";
  let latestVideoUrl = "";

  const postsResp = UrlFetchApp.fetch(
    SHORTVID_BASE_URL + "/v1/user/posts?unique_id=" + encodeURIComponent(handle) + "&count=" + RECENT_VIDEOS_SAMPLE + "&cursor=0",
    { method: "get", headers: headers, muteHttpExceptions: true }
  );
  if (postsResp.getResponseCode() === 200) {
    const postsJson = JSON.parse(postsResp.getContentText());
    const videos = (postsJson && postsJson.data && postsJson.data.videos) || [];
    if (videos.length > 0) {
      let sumViews = 0;
      let sumInteractions = 0;
      for (let j = 0; j < videos.length; j++) {
        const v = videos[j];
        sumViews += Number(v.play_count || 0);
        sumInteractions += Number(v.digg_count || 0) + Number(v.comment_count || 0) + Number(v.share_count || 0);
      }
      avgViews = Math.round(sumViews / videos.length);
      engagementPct = sumViews > 0 ? Number(((sumInteractions / sumViews) * 100).toFixed(2)) : 0;
      region = String(videos[0].region || "");
      latestVideoUrl = String(videos[0].video_url || ("https://www.tiktok.com/@" + handle + "/video/" + videos[0].video_id));
    }
  }

  return {
    ok: true,
    nickname: user.nickname || "",
    followers: Number(stats.followerCount || 0),
    hearts: Number(stats.heartCount || 0),
    videos: Number(stats.videoCount || 0),
    verified: Boolean(user.verified),
    bioLink: (user.bioLink && user.bioLink.link) ? user.bioLink.link : "",
    bioEmail: emailMatch ? emailMatch[0].toLowerCase() : "",
    avgViews: avgViews,
    engagementPct: engagementPct,
    region: region,
    latestVideoUrl: latestVideoUrl
  };
}

Exact per-row credit math (no hidden multipliers)

Enrichment ModeEndpoint(s)Credits / RowPAYG ($25 Pack)Business ($199/mo)
Profile + Bio Email OnlyGET /v1/user/info10 credits$1.00 / 1K ($0.001/row)$0.40 / 1K ($0.0004/row)
10 Recent Videos TimelineGET /v1/user/posts?count=1020 credits (2/video)$0.20 / 1K videos$0.08 / 1K videos
Full Row (Profile + 10 Videos)Both endpoints above30 credits$3.00 / 1K creators$1.20 / 1K creators

Frequently Asked Questions

Why shouldn't I use a custom spreadsheet formula like =TIKTOK_STATS(A2) or =IMPORTDATA()?

Google Sheets automatically recalculates custom cell formulas whenever you reopen the tab, sort a column, or insert a row — silently re-calling the API and spending your credits on rows that already succeeded. Our menu-driven Apps Script writes static cell values, skips already-filled rows, and caches responses for 6 hours in CacheService.

How much does it cost to enrich 500 TikTok creators in Google Sheets?

Profile-only enrichment (GET /v1/user/info) costs 10 credits per creator ($0.50 on $25 PAYG at $1.00/1K, or $0.20 on Business Scale at $0.40/1K). Fetching both the profile AND the creator's latest 10 videos (GET /v1/user/posts?count=10 = 20 credits) costs 30 credits per creator ($1.50 PAYG / $0.60 Business Scale for 500 creators). Every new account starts with 10,500 free credits ($1.05 = 1,050 profile lookups or 350 full profile+video rows).

Where is my ShortVid API key stored inside Google Sheets?

Never put your API key in a spreadsheet cell or hardcode it in shared script text. Click ShortVidAPI -> Set API Key (PropertiesService) from the top menu to store your key in Google Apps Script's encrypted Script Properties.