3-minute setup in any Google Sheet
- 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) inA2:A. - STEP 2 · Go to Extensions → Apps Script, replace the default
Code.gswith the script below, click Save (Ctrl+S / Cmd+S), and reload your Google Sheet tab. - 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 Mode | Endpoint(s) | Credits / Row | PAYG ($25 Pack) | Business ($199/mo) |
|---|---|---|---|---|
| Profile + Bio Email Only | GET /v1/user/info | 10 credits | $1.00 / 1K ($0.001/row) | $0.40 / 1K ($0.0004/row) |
| 10 Recent Videos Timeline | GET /v1/user/posts?count=10 | 20 credits (2/video) | $0.20 / 1K videos | $0.08 / 1K videos |
| Full Row (Profile + 10 Videos) | Both endpoints above | 30 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.