Kevin Yu This workflow corresponds to n8n.io template #16701 — we link there as the canonical source.
This workflow follows the Google Drive → Google Sheets recipe pattern — see all workflows that pair these two integrations.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"name": "Audit Google Drive backups for stale, missing, or shrunken files and alert Slack",
"tags": [],
"nodes": [
{
"name": "Nightly Schedule",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
0,
96
],
"parameters": {
"rule": {
"interval": [
{
"field": "days",
"triggerAtHour": 3
}
]
}
},
"typeVersion": 1.2
},
{
"name": "Read SLA Table",
"type": "n8n-nodes-base.googleSheets",
"maxTries": 3,
"position": [
224,
96
],
"parameters": {
"options": {},
"resource": "sheet",
"operation": "read",
"sheetName": {
"__rl": true,
"mode": "list",
"value": ""
},
"documentId": {
"__rl": true,
"mode": "list",
"value": ""
},
"authentication": "oAuth2"
},
"credentials": {
"googleSheetsOAuth2Api": {
"name": "<your credential>"
}
},
"retryOnFail": true,
"typeVersion": 4.7,
"waitBetweenTries": 5000
},
{
"name": "List Backup Files",
"type": "n8n-nodes-base.googleDrive",
"onError": "continueRegularOutput",
"maxTries": 3,
"position": [
544,
96
],
"parameters": {
"options": {
"fields": [
"*"
]
},
"resource": "fileFolder",
"operation": "search",
"returnAll": true,
"queryString": "'PASTE_YOUR_BACKUP_FOLDER_ID_HERE' in parents and trashed = false",
"searchMethod": "query",
"authentication": "oAuth2"
},
"credentials": {
"googleDriveOAuth2Api": {
"name": "<your credential>"
}
},
"executeOnce": true,
"retryOnFail": true,
"typeVersion": 3,
"alwaysOutputData": true,
"waitBetweenTries": 5000
},
{
"name": "Compute Verdicts",
"type": "n8n-nodes-base.code",
"position": [
912,
96
],
"parameters": {
"mode": "runOnceForAllItems",
"jsCode": "// ============================================================================\n// BACKUP FRESHNESS VERDICT ENGINE\n// Compares every expected backup source (one row in the SLA table read from\n// Google Sheets) against the real files in the watched Google Drive folder\n// and assigns ONE verdict per source:\n//\n// MISSING no file at all matches this source's pattern\n// STALE newest matching file is older than its max-age window\n// SHRUNK newest matching file is smaller than allowed: below an absolute\n// byte floor, or below a percent of the trailing-median size of\n// this source's recent files (catches truncated / corrupt dumps)\n// OK fresh and full\n//\n// This node only READS metadata. It never creates, moves, or deletes a backup.\n// Almost all tuning lives in the SLA Sheet; you rarely need to edit this code.\n// ============================================================================\n\n// ---- Tunables (most config lives in the SLA Sheet, not here) --------------\nconst TRAILING_WINDOW = 5; // how many recent files define the \"normal\" size\nconst MIN_HISTORY_FOR_SHRUNK = 2; // need at least this many older files to judge shrink\nconst CADENCE_HOURS = { hourly: 2, daily: 26, weekly: 170, monthly: 750 }; // fallback window when max_age_hours is blank\n// ---------------------------------------------------------------------------\n\nconst nowMs = Date.now();\nconst runAt = new Date(nowMs).toISOString();\n\n// ---- helpers --------------------------------------------------------------\nconst str = (v) => (v === undefined || v === null) ? '' : String(v).trim();\nconst num = (v, d = 0) => { const n = Number(str(v)); return Number.isFinite(n) ? n : d; };\nconst pick = (row, ...keys) => {\n for (const k of keys) {\n if (row && row[k] !== undefined && row[k] !== null && String(row[k]).trim() !== '') return row[k];\n }\n return '';\n};\nfunction matcher(pattern) {\n const p = str(pattern);\n if (!p) return () => false;\n try { const re = new RegExp(p, 'i'); return (name) => re.test(name); }\n catch (e) { const low = p.toLowerCase(); return (name) => name.toLowerCase().includes(low); }\n}\nfunction median(nums) {\n if (!nums.length) return null;\n const s = [...nums].sort((a, b) => a - b);\n const mid = Math.floor(s.length / 2);\n return s.length % 2 ? s[mid] : (s[mid - 1] + s[mid]) / 2;\n}\nfunction fmtBytes(b) {\n if (b === null || b === undefined || !Number.isFinite(b)) return '';\n const u = ['B', 'KB', 'MB', 'GB', 'TB'];\n let i = 0, n = b;\n while (n >= 1024 && i < u.length - 1) { n /= 1024; i++; }\n return `${(i === 0) ? n : n.toFixed(n >= 10 ? 0 : 1)} ${u[i]}`;\n}\n\n// ---- 1. Read the SLA table (one row per expected source) ------------------\nconst slaRows = $('Read SLA Table').all().map((i) => i.json);\n\n// ---- 2. Read the Drive listing, keep only real (non-folder) files ---------\nconst files = $input.all().map((i) => i.json)\n .filter((f) => f && f.id && f.name) // skip error / passthrough items\n .filter((f) => f.mimeType !== 'application/vnd.google-apps.folder') // skip sub-folders\n .map((f) => {\n const sizeNum = Number(f.size); // Drive returns size as a string\n const modMs = Date.parse(f.modifiedTime || f.createdTime || '');\n return {\n name: String(f.name),\n size: Number.isFinite(sizeNum) ? sizeNum : null, // Google-native files report no size\n modMs: Number.isFinite(modMs) ? modMs : null,\n };\n });\n\n// ---- 3. Evaluate each source against its SLA row --------------------------\nconst results = [];\nfor (const row of slaRows) {\n const source = str(pick(row, 'source', 'Source'));\n if (!source) continue; // skip blank rows\n const pattern = str(pick(row, 'pattern', 'Pattern', 'prefix', 'Prefix'));\n const cadence = str(pick(row, 'cadence', 'Cadence'));\n let maxAgeH = num(pick(row, 'max_age_hours', 'maxAgeHours', 'Max Age Hours'), 0);\n if (maxAgeH <= 0) maxAgeH = CADENCE_HOURS[cadence.toLowerCase()] || 0; // fall back to cadence window\n const minSizeBytes = num(pick(row, 'min_size_bytes', 'minSizeBytes', 'Min Size Bytes'), 0);\n const minPct = num(pick(row, 'min_pct_of_median', 'minPctOfMedian', 'Min Pct Of Median'), 0);\n\n const isMatch = matcher(pattern);\n const matched = files\n .filter((f) => f.modMs !== null && isMatch(f.name))\n .sort((a, b) => a.modMs - b.modMs); // oldest -> newest\n\n let status, detail;\n let newest = null, ageH = null, trailingMedian = null, pctOfMedian = null;\n\n if (matched.length === 0) {\n status = 'MISSING';\n detail = pattern ? `no file matched \\`${pattern}\\`` : 'no pattern set for this source';\n } else {\n newest = matched[matched.length - 1];\n ageH = (nowMs - newest.modMs) / 3600000;\n if (maxAgeH > 0 && ageH > maxAgeH) {\n status = 'STALE';\n detail = `newest backup is ${ageH.toFixed(1)}h old (SLA ${maxAgeH}h)`;\n } else {\n const trailingSizes = matched.slice(0, -1).map((f) => f.size).filter((s) => Number.isFinite(s));\n const recent = trailingSizes.slice(-TRAILING_WINDOW);\n trailingMedian = recent.length >= MIN_HISTORY_FOR_SHRUNK ? median(recent) : null;\n const newestSize = newest.size;\n\n if (minSizeBytes > 0 && Number.isFinite(newestSize) && newestSize < minSizeBytes) {\n status = 'SHRUNK';\n detail = `newest ${fmtBytes(newestSize)} is below the ${fmtBytes(minSizeBytes)} floor`;\n } else if (minPct > 0 && trailingMedian && Number.isFinite(newestSize) && newestSize < (minPct / 100) * trailingMedian) {\n pctOfMedian = (newestSize / trailingMedian) * 100;\n status = 'SHRUNK';\n detail = `newest ${fmtBytes(newestSize)} is ${pctOfMedian.toFixed(0)}% of the ${fmtBytes(trailingMedian)} trailing median (floor ${minPct}%)`;\n } else {\n if (trailingMedian && Number.isFinite(newestSize)) pctOfMedian = (newestSize / trailingMedian) * 100;\n status = 'OK';\n detail = `fresh (${ageH.toFixed(1)}h old)` + (pctOfMedian !== null ? `, ${pctOfMedian.toFixed(0)}% of median size` : '');\n }\n }\n }\n\n results.push({\n source, status, detail, cadence, pattern,\n newestName: newest ? newest.name : '',\n ageHours: ageH === null ? '' : Math.round(ageH * 10) / 10,\n maxAgeHours: maxAgeH || '',\n newestSizeBytes: newest && Number.isFinite(newest.size) ? newest.size : '',\n trailingMedianBytes: trailingMedian === null ? '' : Math.round(trailingMedian),\n sizePctOfMedian: pctOfMedian === null ? '' : Math.round(pctOfMedian),\n matchedCount: matched.length,\n });\n}\n\n// ---- 4. Build the run summary and the failing-only Slack alert text -------\nconst failing = results.filter((r) => r.status !== 'OK');\nconst failCount = failing.length;\nconst okCount = results.length - failCount;\nconst icon = { MISSING: ':x:', STALE: ':hourglass_flowing_sand:', SHRUNK: ':chart_with_downwards_trend:' };\nconst lines = failing.map((r) => `\u2022 ${icon[r.status] || ':warning:'} *${r.source}* ${r.status}: ${r.detail}`);\nconst alertText =\n `:rotating_light: *Backup audit found ${failCount} issue${failCount === 1 ? '' : 's'}* (${runAt})\\n` +\n lines.join('\\n') +\n `\\n\\n${okCount}/${results.length} source${results.length === 1 ? '' : 's'} OK. Full scorecard logged to the audit sheet.`;\n\n// ---- 5. One item per source (for the scorecard); every item carries the ---\n// run-level summary so the gated Slack node can read the full alert.\nreturn results.map((r) => ({\n json: {\n runAt,\n source: r.source,\n status: r.status,\n detail: r.detail,\n cadence: r.cadence,\n pattern: r.pattern,\n newestName: r.newestName,\n ageHours: r.ageHours,\n maxAgeHours: r.maxAgeHours,\n newestSizeBytes: r.newestSizeBytes,\n trailingMedianBytes: r.trailingMedianBytes,\n sizePctOfMedian: r.sizePctOfMedian,\n matchedCount: r.matchedCount,\n failCount,\n okCount,\n hasFailures: failCount > 0,\n alertText,\n },\n}));\n",
"language": "javaScript"
},
"typeVersion": 2
},
{
"name": "Append Scorecard",
"type": "n8n-nodes-base.googleSheets",
"onError": "continueRegularOutput",
"maxTries": 3,
"position": [
1408,
224
],
"parameters": {
"columns": {
"value": {
"Detail": "={{ $json.detail }}",
"Run At": "={{ $json.runAt }}",
"Source": "={{ $json.source }}",
"Status": "={{ $json.status }}",
"Cadence": "={{ $json.cadence }}",
"Pattern": "={{ $json.pattern }}",
"Max Age (h)": "={{ $json.maxAgeHours }}",
"Newest File": "={{ $json.newestName }}",
"Matched Files": "={{ $json.matchedCount }}",
"Newest Age (h)": "={{ $json.ageHours }}",
"Size % of Median": "={{ $json.sizePctOfMedian }}",
"Newest Size (bytes)": "={{ $json.newestSizeBytes }}",
"Trailing Median (bytes)": "={{ $json.trailingMedianBytes }}"
},
"schema": [
{
"id": "Run At",
"type": "string",
"display": true,
"required": false,
"displayName": "Run At",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Source",
"type": "string",
"display": true,
"required": false,
"displayName": "Source",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Detail",
"type": "string",
"display": true,
"required": false,
"displayName": "Detail",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Cadence",
"type": "string",
"display": true,
"required": false,
"displayName": "Cadence",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Pattern",
"type": "string",
"display": true,
"required": false,
"displayName": "Pattern",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Newest File",
"type": "string",
"display": true,
"required": false,
"displayName": "Newest File",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Newest Age (h)",
"type": "string",
"display": true,
"required": false,
"displayName": "Newest Age (h)",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Max Age (h)",
"type": "string",
"display": true,
"required": false,
"displayName": "Max Age (h)",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Newest Size (bytes)",
"type": "string",
"display": true,
"required": false,
"displayName": "Newest Size (bytes)",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Trailing Median (bytes)",
"type": "string",
"display": true,
"required": false,
"displayName": "Trailing Median (bytes)",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Size % of Median",
"type": "string",
"display": true,
"required": false,
"displayName": "Size % of Median",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Matched Files",
"type": "string",
"display": true,
"required": false,
"displayName": "Matched Files",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"resource": "sheet",
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": ""
},
"documentId": {
"__rl": true,
"mode": "list",
"value": ""
},
"authentication": "oAuth2"
},
"credentials": {
"googleSheetsOAuth2Api": {
"name": "<your credential>"
}
},
"retryOnFail": true,
"typeVersion": 4.7,
"waitBetweenTries": 5000
},
{
"name": "Any Source Failing?",
"type": "n8n-nodes-base.if",
"position": [
1280,
-128
],
"parameters": {
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"operator": {
"type": "number",
"operation": "gt"
},
"leftValue": "={{ $json.failCount }}",
"rightValue": 0
}
]
}
},
"typeVersion": 2.3
},
{
"name": "Post Failing-Source Alert",
"type": "n8n-nodes-base.slack",
"onError": "continueRegularOutput",
"maxTries": 3,
"position": [
1536,
-144
],
"parameters": {
"text": "={{ $json.alertText }}",
"select": "channel",
"resource": "message",
"channelId": {
"__rl": true,
"mode": "list",
"value": ""
},
"operation": "post",
"otherOptions": {
"includeLinkToWorkflow": false
}
},
"credentials": {
"slackApi": {
"name": "<your credential>"
}
},
"executeOnce": true,
"retryOnFail": true,
"typeVersion": 2.5,
"waitBetweenTries": 5000
},
{
"name": "Overview",
"type": "n8n-nodes-base.stickyNote",
"position": [
128,
-912
],
"parameters": {
"color": 1,
"width": 1628,
"height": 560,
"content": "## Google Drive backup freshness auditor\n\nWatches one Google Drive folder that an external job already writes backups into (a pg_dump cron, Veeam, or any other tool) and checks each expected source against a per-source SLA table in a Google Sheet. It never creates a backup. It only reads file metadata and reports a verdict per source: OK, STALE, MISSING, or SHRUNK.\n\n### How it works\n1. The schedule trigger fires nightly, after the backup jobs are expected to finish.\n2. Read SLA Table pulls one row per expected source (pattern, cadence, max age, min size).\n3. List Backup Files lists the watched Drive folder with each file's name, modified time, and size.\n4. Compute Verdicts matches files to sources and assigns OK / STALE / MISSING / SHRUNK. SHRUNK compares the newest file against the trailing median size of recent files, so a truncated or corrupt dump is caught.\n5. Append Scorecard logs one dated row per source. Slack is pinged only when at least one source fails.\n\n### Setup\n1. Connect Google Drive, Google Sheets, and Slack credentials.\n2. In List Backup Files, paste your backup folder ID into the query (see the note on that node).\n3. Point Read SLA Table and Append Scorecard at your spreadsheet and tabs.\n4. Fill the SLA table (see the SLA note), pick the Slack channel, and set the schedule time."
},
"typeVersion": 1
},
{
"name": "SLA table note",
"type": "n8n-nodes-base.stickyNote",
"position": [
128,
-240
],
"parameters": {
"color": 7,
"width": 290,
"height": 504,
"content": "## SLA table (your config lives here)\nOne row per expected backup source. Columns: source, pattern (regex or filename prefix), cadence (hourly/daily/weekly/monthly), max_age_hours, min_size_bytes, min_pct_of_median. Leave a size column blank to skip that check; blank max_age_hours falls back to the cadence window."
},
"typeVersion": 1
},
{
"name": "Folder ID note",
"type": "n8n-nodes-base.stickyNote",
"position": [
432,
-160
],
"parameters": {
"color": 5,
"width": 316,
"height": 420,
"content": "## Set your folder ID here\nOpen List Backup Files and replace PASTE_YOUR_BACKUP_FOLDER_ID_HERE in the query with your backup folder ID (the part after /folders/ in the folder URL). Watch the folder the external job writes into, not a copy."
},
"typeVersion": 1
},
{
"name": "Verdict logic note",
"type": "n8n-nodes-base.stickyNote",
"position": [
768,
-160
],
"parameters": {
"color": 7,
"width": 410,
"height": 418,
"content": "## Verdict logic\nMISSING: no file matches the pattern. STALE: newest match is older than max_age_hours (or the cadence window). SHRUNK: newest file is below the byte floor, or below min_pct_of_median of the trailing-median size of recent files, which flags truncated or corrupt dumps. Otherwise OK."
},
"typeVersion": 1
},
{
"name": "Alert gate note",
"type": "n8n-nodes-base.stickyNote",
"position": [
1200,
-304
],
"parameters": {
"color": 7,
"width": 560,
"height": 330,
"content": "## Failing-only alert\nThe IF node passes only when at least one source failed, so Slack stays quiet on an all-clear night. The alert is sent once and lists every failing source with its reason."
},
"typeVersion": 1
},
{
"name": "Scorecard note",
"type": "n8n-nodes-base.stickyNote",
"position": [
1200,
32
],
"parameters": {
"color": 7,
"width": 490,
"height": 358,
"content": "## Dated scorecard\nOne row per source every run: run time, status, newest file, age, size, trailing median, and percent of median. Add these headers to row 1 of your scorecard tab. This is the audit trail."
},
"typeVersion": 1
}
],
"active": false,
"settings": {
"executionOrder": "v1"
},
"connections": {
"Read SLA Table": {
"main": [
[
{
"node": "List Backup Files",
"type": "main",
"index": 0
}
]
]
},
"Compute Verdicts": {
"main": [
[
{
"node": "Any Source Failing?",
"type": "main",
"index": 0
},
{
"node": "Append Scorecard",
"type": "main",
"index": 0
}
]
]
},
"Nightly Schedule": {
"main": [
[
{
"node": "Read SLA Table",
"type": "main",
"index": 0
}
]
]
},
"List Backup Files": {
"main": [
[
{
"node": "Compute Verdicts",
"type": "main",
"index": 0
}
]
]
},
"Any Source Failing?": {
"main": [
[
{
"node": "Post Failing-Source Alert",
"type": "main",
"index": 0
}
],
[]
]
}
}
}
Credentials you'll need
Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.
googleDriveOAuth2ApigoogleSheetsOAuth2ApislackApi
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
This workflow runs nightly to audit a Google Drive backup folder against an SLA table in Google Sheets, logging a per-source scorecard and sending a Slack alert only when backups are missing, stale, or unexpectedly small. Runs on a nightly schedule. Reads an SLA table from…
Source: https://n8n.io/workflows/16701/ — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
This workflow continuously monitors the Meta Ads Library for new creatives from a specific competitor pages, logs them into Google Sheets, and sends a concise Telegram notification with the number of
HTTP Request, Telegram, Google Sheets +1
Enhance financial oversight with this automated n8n workflow. Triggered every 5 minutes, it fetches real-time bank transactions via an API, enriches and transforms the data, and applies smart logic to
HTTP Request, Email Send, Google Sheets +1
This workflow automates competitive price intelligence using Bright Data's enterprise web scraping API. On a scheduled basis (default: daily at 9 AM), the system loops through configured competitor pr
HTTP Request, Google Sheets, Slack +1
Ensure your customer SLAs never slip with this n8n automation template. The workflow runs on a schedule, fetching open tickets from Zendesk, calculating SLA time remaining, and sending proactive alert
Zendesk, Slack, Google Sheets
> n8n, Binance API, Google Sheets, Slack, Telegram, Jira & Email
HTTP Request, Google Sheets, Slack +3