Expose Google Sheet Kpis as Prometheus Metrics for Grafana

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": "Expose Google Sheet KPIs as a Prometheus metrics endpoint for Grafana",
  "tags": [],
  "nodes": [
    {
      "id": "8ff39692-f66c-4f97-9646-214ca7b19ed9",
      "name": "When Prometheus Scrapes",
      "type": "n8n-nodes-base.webhook",
      "position": [
        0,
        368
      ],
      "parameters": {
        "path": "metrics-CHANGEME",
        "options": {},
        "responseMode": "responseNode",
        "authentication": "headerAuth"
      },
      "typeVersion": 2.1
    },
    {
      "id": "b275d69d-da8f-4814-bdf9-9d1a7567f26b",
      "name": "Set Exporter Config",
      "type": "n8n-nodes-base.set",
      "position": [
        224,
        368
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "sheet_id",
              "name": "sheet_id",
              "type": "string",
              "value": ""
            },
            {
              "id": "sheet_tab",
              "name": "sheet_tab",
              "type": "string",
              "value": "Sheet1"
            },
            {
              "id": "cache_ttl_seconds",
              "name": "cache_ttl_seconds",
              "type": "number",
              "value": 30
            },
            {
              "id": "max_series",
              "name": "max_series",
              "type": "number",
              "value": 2000
            },
            {
              "id": "exporter_prefix",
              "name": "exporter_prefix",
              "type": "string",
              "value": "sheet_exporter"
            },
            {
              "id": "serve_stale_on_error",
              "name": "serve_stale_on_error",
              "type": "boolean",
              "value": false
            },
            {
              "id": "fail_status_code",
              "name": "fail_status_code",
              "type": "number",
              "value": 200
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "59632a4e-5bd0-4cf2-a1ac-ec69e98bc895",
      "name": "Read Metrics Cache",
      "type": "n8n-nodes-base.code",
      "position": [
        496,
        368
      ],
      "parameters": {
        "jsCode": "var cfg = $('Set Exporter Config').first().json;\nvar store = $getWorkflowStaticData('global');\nvar nowMs = Date.now();\n\nvar cachedBody = typeof store.body === 'string' ? store.body : '';\nvar cachedMeta = (store.meta && typeof store.meta === 'object') ? store.meta : null;\nvar cachedAt = typeof store.cached_at === 'number' ? store.cached_at : 0;\nvar lastSuccess = typeof store.last_success === 'number' ? store.last_success : 0;\n\nvar ttl = Number(cfg.cache_ttl_seconds);\nif (!isFinite(ttl) || ttl < 0) ttl = 30;\n\nvar ageSeconds = cachedAt > 0 ? (nowMs - cachedAt) / 1000 : -1;\nvar cacheFresh = (cachedBody !== '' && ageSeconds >= 0 && ageSeconds < ttl);\n\nreturn [{ json: {\n  started_at: nowMs,\n  cache_fresh: cacheFresh,\n  cache_age_seconds: ageSeconds < 0 ? 0 : ageSeconds,\n  cached_body: cachedBody,\n  cached_meta: cachedMeta,\n  last_success: lastSuccess,\n  degraded: false,\n  cache_hit: cacheFresh,\n  body: cacheFresh ? cachedBody : '',\n  meta: cacheFresh ? cachedMeta : null\n} }];"
      },
      "typeVersion": 2
    },
    {
      "id": "b05d9f61-4845-409a-8d74-457b5533f994",
      "name": "Check Cache Freshness",
      "type": "n8n-nodes-base.if",
      "position": [
        720,
        368
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "cache-fresh",
              "operator": {
                "type": "boolean",
                "operation": "equals"
              },
              "leftValue": "={{ $json.cache_fresh }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "0b938bcd-6ebd-4365-98f0-19f3d1a7f53c",
      "name": "Read Metrics Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueErrorOutput",
      "position": [
        1008,
        384
      ],
      "parameters": {
        "options": {
          "outputFormatting": {
            "values": {
              "date": "FORMATTED_STRING",
              "general": "UNFORMATTED_VALUE"
            }
          },
          "dataLocationOnSheet": {
            "values": {
              "readRowsUntil": "firstEmptyRow",
              "rangeDefinition": "detectAutomatically"
            }
          }
        },
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Exporter Config').first().json.sheet_tab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Exporter Config').first().json.sheet_id }}"
        }
      },
      "retryOnFail": false,
      "typeVersion": 4.7,
      "alwaysOutputData": true
    },
    {
      "id": "0dfc22e3-9d5b-4ede-b0d7-4e7e0b5c0184",
      "name": "Validate Metric Rows",
      "type": "n8n-nodes-base.code",
      "position": [
        1232,
        368
      ],
      "parameters": {
        "jsCode": "var cfg = $('Set Exporter Config').first().json;\nvar items = $input.all();\n\nvar prefix = String(cfg.exporter_prefix == null ? '' : cfg.exporter_prefix).trim();\nif (prefix === '') prefix = 'sheet_exporter';\n\nvar maxSeries = Number(cfg.max_series);\nif (!isFinite(maxSeries) || maxSeries < 1) maxSeries = 2000;\n\nvar NL = String.fromCharCode(10);\nvar CR = String.fromCharCode(13);\nvar BS = String.fromCharCode(92);\nvar SEP = String.fromCharCode(0);\n\nvar nameRe = new RegExp('^[a-zA-Z_:][a-zA-Z0-9_:]*$');\nvar labelRe = new RegExp('^[a-zA-Z_][a-zA-Z0-9_]*$');\nvar allowedTypes = ['gauge', 'counter', 'untyped'];\nvar reservedNames = ['up', 'scrape_duration_seconds', 'scrape_samples_scraped', 'scrape_samples_post_metric_relabeling', 'scrape_series_added', 'scrape_body_size_bytes', 'scrape_timeout_seconds'];\n\nvar skipped = { bad_label_name: 0, bad_labels: 0, bad_name: 0, bad_type: 0, bad_value: 0, blank_value: 0, duplicate: 0, over_cap: 0, reserved_name: 0 };\nvar warnings = { scrape_label: 0, type_conflict: 0 };\nvar notes = [];\n\nfunction clean(v){\n  var s = String(v == null ? '' : v);\n  var out = '';\n  for (var i = 0; i < s.length; i++){\n    var ch = s.charAt(i);\n    if (ch === NL || ch === CR) out += ' ';\n    else out += ch;\n  }\n  if (out.length > 60) out = out.slice(0, 60);\n  return out;\n}\n\nfunction note(text){\n  if (notes.length < 20) notes.push(text);\n}\n\nfunction parseValue(raw){\n  var t = String(raw == null ? '' : raw).trim();\n  if (t === '') return { ok: false, reason: 'blank_value' };\n  var low = t.toLowerCase();\n  if (low === 'nan') return { ok: true, text: 'NaN' };\n  if (low === 'inf' || low === '+inf' || low === 'infinity' || low === '+infinity') return { ok: true, text: '+Inf' };\n  if (low === '-inf' || low === '-infinity') return { ok: true, text: '-Inf' };\n  var n = Number(t);\n  if (typeof raw !== 'boolean' && isFinite(n)) return { ok: true, text: String(n) };\n  return { ok: false, reason: 'bad_value' };\n}\n\nfunction parseLabels(raw){\n  var s = String(raw == null ? '' : raw).trim();\n  var pairs = [];\n  if (s === '') return { ok: true, labels: pairs };\n  var i = 0;\n  var n = s.length;\n  while (i < n){\n    while (i < n && s.charAt(i) === ' ') i++;\n    var key = '';\n    while (i < n && s.charAt(i) !== '=' && s.charAt(i) !== ',') { key += s.charAt(i); i++; }\n    key = key.trim();\n    if (i >= n || s.charAt(i) !== '=') return { ok: false, reason: 'bad_labels' };\n    i++;\n    var val = '';\n    if (i < n && s.charAt(i) === '\"'){\n      i++;\n      var closed = false;\n      while (i < n){\n        var c = s.charAt(i);\n        if (c === BS && i + 1 < n){\n          var nx = s.charAt(i + 1);\n          if (nx === BS){ val += BS; i += 2; }\n          else if (nx === '\"'){ val += '\"'; i += 2; }\n          else if (nx === 'n'){ val += NL; i += 2; }\n          else { val += c; i++; }\n        }\n        else if (c === '\"'){ i++; closed = true; break; }\n        else { val += c; i++; }\n      }\n      if (!closed) return { ok: false, reason: 'bad_labels' };\n      while (i < n && s.charAt(i) === ' ') i++;\n      if (i < n && s.charAt(i) !== ',') return { ok: false, reason: 'bad_labels' };\n      if (i < n) i++;\n    } else {\n      while (i < n && s.charAt(i) !== ',') { val += s.charAt(i); i++; }\n      if (i < n) i++;\n      val = val.trim();\n      if (val.length > 1 && val.charAt(0) === '\"' && val.charAt(val.length - 1) === '\"'){\n        val = val.slice(1, val.length - 1);\n      }\n    }\n    if (key === '') return { ok: false, reason: 'bad_labels' };\n    if (key.indexOf('__') === 0) return { ok: false, reason: 'bad_label_name' };\n    if (!labelRe.test(key)) return { ok: false, reason: 'bad_label_name' };\n    if (val !== '') pairs.push([key, val]);\n  }\n  pairs.sort(function(a, b){ return a[0] < b[0] ? -1 : (a[0] > b[0] ? 1 : 0); });\n  for (var d = 1; d < pairs.length; d++){\n    if (pairs[d][0] === pairs[d - 1][0]) return { ok: false, reason: 'bad_labels' };\n  }\n  return { ok: true, labels: pairs };\n}\n\nvar families = {};\nvar seen = {};\nvar series = [];\nvar rowsIn = 0;\n\nfor (var r = 0; r < items.length; r++){\n  var row = items[r].json;\n  var rowNum = (row.row_number != null && isFinite(Number(row.row_number))) ? Number(row.row_number) : (r + 2);\n  var name = String(row.metric_name == null ? '' : row.metric_name).trim();\n  if (name === '') continue;\n  rowsIn++;\n\n  if (!nameRe.test(name)){\n    skipped.bad_name++;\n    note('skipped row ' + rowNum + ': bad_name ' + clean(name));\n    continue;\n  }\n  if (reservedNames.indexOf(name) >= 0 || name === prefix || name.indexOf(prefix + '_') === 0){\n    skipped.reserved_name++;\n    note('skipped row ' + rowNum + ': reserved_name ' + clean(name));\n    continue;\n  }\n\n  var typeRaw = String(row.type == null ? '' : row.type).trim().toLowerCase();\n  if (typeRaw !== '' && allowedTypes.indexOf(typeRaw) < 0){\n    skipped.bad_type++;\n    note('skipped row ' + rowNum + ': bad_type ' + clean(typeRaw));\n    continue;\n  }\n\n  var lab = parseLabels(row.labels);\n  if (!lab.ok){\n    skipped[lab.reason]++;\n    note('skipped row ' + rowNum + ': ' + lab.reason + ' ' + clean(row.labels));\n    continue;\n  }\n\n  var val = parseValue(row.value);\n  if (!val.ok){\n    skipped[val.reason]++;\n    note('skipped row ' + rowNum + ': ' + val.reason + ' ' + clean(row.value));\n    continue;\n  }\n\n  for (var w = 0; w < lab.labels.length; w++){\n    var lk = lab.labels[w][0];\n    if (lk === 'job' || lk === 'instance'){\n      warnings.scrape_label++;\n      note('warning row ' + rowNum + ': label ' + lk + ' is renamed to exported_' + lk + ' by Prometheus');\n    }\n  }\n\n  var fkey = 'm:' + name;\n  if (!Object.prototype.hasOwnProperty.call(families, fkey)) families[fkey] = { help: '', type: '' };\n  var fam = families[fkey];\n  var help = String(row.help == null ? '' : row.help).trim();\n  if (fam.help === '' && help !== '') fam.help = help;\n  if (typeRaw !== ''){\n    if (fam.type === '') fam.type = typeRaw;\n    else if (fam.type !== typeRaw){\n      warnings.type_conflict++;\n      note('warning row ' + rowNum + ': type ' + typeRaw + ' conflicts with ' + fam.type);\n    }\n  }\n\n  var parts = [];\n  for (var q = 0; q < lab.labels.length; q++) parts.push(lab.labels[q][0] + '=' + lab.labels[q][1]);\n  var skey = 's:' + name + SEP + parts.join(',');\n  var entry = { name: name, labels: lab.labels, value: val.text, row: rowNum };\n\n  if (Object.prototype.hasOwnProperty.call(seen, skey)){\n    skipped.duplicate++;\n    note('skipped row ' + series[seen[skey]].row + ': duplicate series, row ' + rowNum + ' wins');\n    series[seen[skey]] = entry;\n  } else {\n    seen[skey] = series.length;\n    series.push(entry);\n  }\n}\n\nseries.sort(function(a, b){\n  if (a.name !== b.name) return a.name < b.name ? -1 : 1;\n  var ka = '';\n  var kb = '';\n  var x;\n  for (x = 0; x < a.labels.length; x++) ka += a.labels[x][0] + '=' + a.labels[x][1] + ',';\n  for (x = 0; x < b.labels.length; x++) kb += b.labels[x][0] + '=' + b.labels[x][1] + ',';\n  return ka < kb ? -1 : (ka > kb ? 1 : 0);\n});\n\nif (series.length > maxSeries){\n  skipped.over_cap += (series.length - maxSeries);\n  series = series.slice(0, maxSeries);\n  note('skipped: series cap ' + maxSeries + ' reached, extra series dropped');\n}\n\nreturn [{ json: {\n  series: series,\n  families: families,\n  meta: {\n    rows_in: rowsIn,\n    rows_kept: series.length,\n    series_count: series.length,\n    skipped: skipped,\n    warnings: warnings,\n    notes: notes\n  }\n} }];"
      },
      "typeVersion": 2
    },
    {
      "id": "52539103-dc12-4d84-9f97-2df08ee6dce0",
      "name": "Build Exposition Text",
      "type": "n8n-nodes-base.code",
      "position": [
        1424,
        368
      ],
      "parameters": {
        "jsCode": "var input = $input.first().json;\nvar series = input.series || [];\nvar families = input.families || {};\nvar meta = input.meta || {};\n\nvar NL = String.fromCharCode(10);\nvar CR = String.fromCharCode(13);\nvar BS = String.fromCharCode(92);\n\nfunction escLabel(v){\n  var s = String(v);\n  var out = '';\n  for (var i = 0; i < s.length; i++){\n    var ch = s.charAt(i);\n    if (ch === BS) out += BS + BS;\n    else if (ch === '\"') out += BS + '\"';\n    else if (ch === NL) out += BS + 'n';\n    else if (ch === CR) out += '';\n    else out += ch;\n  }\n  return out;\n}\n\nfunction escHelp(v){\n  var s = String(v);\n  var out = '';\n  for (var i = 0; i < s.length; i++){\n    var ch = s.charAt(i);\n    if (ch === BS) out += BS + BS;\n    else if (ch === NL) out += BS + 'n';\n    else if (ch === CR) out += '';\n    else out += ch;\n  }\n  return out;\n}\n\nvar out = [];\nvar i;\nvar skippedTotal = 0;\nvar sk = meta.skipped || {};\nfor (var key in sk){\n  if (Object.prototype.hasOwnProperty.call(sk, key)) skippedTotal += Number(sk[key]) || 0;\n}\n\nout.push('# sheet exporter read ' + (Number(meta.rows_in) || 0) + ' rows, exported ' + series.length + ' series, skipped ' + skippedTotal);\n\nvar notes = meta.notes || [];\nfor (i = 0; i < notes.length; i++) out.push('# ' + notes[i]);\n\nvar order = [];\nvar groups = {};\nfor (i = 0; i < series.length; i++){\n  var gk = 'm:' + series[i].name;\n  if (!Object.prototype.hasOwnProperty.call(groups, gk)){\n    groups[gk] = [];\n    order.push(series[i].name);\n  }\n  groups[gk].push(series[i]);\n}\norder.sort();\n\nfor (i = 0; i < order.length; i++){\n  var name = order[i];\n  var fam = Object.prototype.hasOwnProperty.call(families, 'm:' + name) ? families['m:' + name] : { help: '', type: '' };\n  if (fam.help) out.push('# HELP ' + name + ' ' + escHelp(fam.help));\n  out.push('# TYPE ' + name + ' ' + (fam.type ? fam.type : 'gauge'));\n  var list = groups['m:' + name];\n  for (var j = 0; j < list.length; j++){\n    var labels = list[j].labels;\n    var lbl = '';\n    if (labels.length){\n      var parts = [];\n      for (var k = 0; k < labels.length; k++){\n        parts.push(labels[k][0] + '=\"' + escLabel(labels[k][1]) + '\"');\n      }\n      lbl = '{' + parts.join(',') + '}';\n    }\n    out.push(name + lbl + ' ' + list[j].value);\n  }\n}\n\nvar body = out.length ? (out.join(NL) + NL) : '';\n\nreturn [{ json: { body: body, meta: meta, degraded: false, cache_hit: false } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "496cc92c-e5ae-45e6-87b7-3ba9f5edb513",
      "name": "Save Metrics Cache",
      "type": "n8n-nodes-base.code",
      "position": [
        1616,
        368
      ],
      "parameters": {
        "jsCode": "var input = $input.first().json;\nvar store = $getWorkflowStaticData('global');\n\nvar MAX_BODY_BYTES = 200000;\nvar body = String(input.body == null ? '' : input.body);\nvar meta = input.meta || {};\nvar nowMs = Date.now();\nvar nowSec = Math.floor(nowMs / 1000);\n\nif (body.length <= MAX_BODY_BYTES){\n  store.body = body;\n  store.meta = meta;\n  store.cached_at = nowMs;\n} else {\n  store.body = '';\n  store.meta = null;\n  store.cached_at = 0;\n}\nstore.last_success = nowSec;\n\nreturn [{ json: {\n  body: body,\n  meta: meta,\n  degraded: false,\n  cache_hit: false,\n  last_success: nowSec\n} }];"
      },
      "typeVersion": 2
    },
    {
      "id": "7a071dfb-5af8-4168-956c-c327da903aa2",
      "name": "Flag Sheet Read Failure",
      "type": "n8n-nodes-base.set",
      "position": [
        1392,
        592
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "degraded",
              "name": "degraded",
              "type": "boolean",
              "value": true
            },
            {
              "id": "error_message",
              "name": "error_message",
              "type": "string",
              "value": "={{ ($json.error && $json.error.message) ? $json.error.message : ($json.message ? $json.message : 'sheet read failed') }}"
            },
            {
              "id": "cached_body",
              "name": "cached_body",
              "type": "string",
              "value": "={{ $('Read Metrics Cache').first().json.cached_body }}"
            },
            {
              "id": "cached_meta",
              "name": "cached_meta",
              "type": "object",
              "value": "={{ $('Read Metrics Cache').first().json.cached_meta || {} }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "8f8318df-83be-478f-afea-2932b935fba8",
      "name": "Add Exporter Self Metrics",
      "type": "n8n-nodes-base.code",
      "position": [
        1920,
        352
      ],
      "parameters": {
        "jsCode": "var cfg = $('Set Exporter Config').first().json;\nvar ctx = $('Read Metrics Cache').first().json;\nvar input = $input.first().json;\n\nvar NL = String.fromCharCode(10);\nvar CR = String.fromCharCode(13);\n\nvar prefix = String(cfg.exporter_prefix == null ? '' : cfg.exporter_prefix).trim();\nif (prefix === '') prefix = 'sheet_exporter';\n\nvar serveStale = (cfg.serve_stale_on_error === true || String(cfg.serve_stale_on_error) === 'true');\n\nvar failCode = Number(cfg.fail_status_code);\nif (!isFinite(failCode) || failCode < 200 || failCode > 599) failCode = 200;\n\nvar degraded = (input.degraded === true);\n\nvar startedAt = Number(ctx.started_at);\nif (!isFinite(startedAt) || startedAt <= 0) startedAt = Date.now();\nvar durationSeconds = (Date.now() - startedAt) / 1000;\n\nvar body = '';\nvar meta = null;\nvar cacheHit = false;\nvar cacheAge = 0;\nvar errorText = '';\n\nif (degraded){\n  errorText = String(input.error_message == null ? 'sheet read failed' : input.error_message);\n  var scrubbed = '';\n  for (var e = 0; e < errorText.length; e++){\n    var ec = errorText.charAt(e);\n    scrubbed += (ec === NL || ec === CR) ? ' ' : ec;\n  }\n  errorText = scrubbed.length > 120 ? scrubbed.slice(0, 120) : scrubbed;\n  if (serveStale && input.cached_body){\n    body = String(input.cached_body);\n    meta = input.cached_meta || null;\n    cacheAge = Number(ctx.cache_age_seconds) || 0;\n  }\n} else {\n  body = String(input.body == null ? '' : input.body);\n  meta = input.meta || null;\n  cacheHit = (input.cache_hit === true);\n  cacheAge = cacheHit ? (Number(ctx.cache_age_seconds) || 0) : 0;\n}\n\nvar lastSuccess = Number(input.last_success);\nif (!isFinite(lastSuccess) || lastSuccess <= 0) lastSuccess = Number(ctx.last_success) || 0;\n\nvar skipped = (meta && meta.skipped) ? meta.skipped : {};\nvar warnings = (meta && meta.warnings) ? meta.warnings : {};\nvar seriesCount = (meta && isFinite(Number(meta.series_count))) ? Number(meta.series_count) : 0;\nvar rowsRead = (meta && isFinite(Number(meta.rows_in))) ? Number(meta.rows_in) : 0;\n\nvar REASONS = ['bad_label_name', 'bad_labels', 'bad_name', 'bad_type', 'bad_value', 'blank_value', 'duplicate', 'over_cap', 'reserved_name'];\nvar RULES = ['scrape_label', 'type_conflict'];\n\nvar lines = [];\nfunction family(name, help, samples){\n  lines.push('# HELP ' + name + ' ' + help);\n  lines.push('# TYPE ' + name + ' gauge');\n  for (var i = 0; i < samples.length; i++) lines.push(samples[i]);\n}\nfunction num(v){\n  var n = Number(v);\n  return isFinite(n) ? String(n) : '0';\n}\n\nif (degraded) lines.push('# sheet read failed: ' + errorText);\n\nfamily(prefix + '_up', '1 if the last sheet read succeeded, 0 if it failed', [prefix + '_up ' + (degraded ? '0' : '1')]);\nfamily(prefix + '_series', 'Number of series exported from the sheet in this response', [prefix + '_series ' + num(seriesCount)]);\nfamily(prefix + '_rows_read', 'Number of sheet rows with a metric name in this response', [prefix + '_rows_read ' + num(rowsRead)]);\n\nvar skipSamples = [];\nfor (var s = 0; s < REASONS.length; s++){\n  skipSamples.push(prefix + '_rows_skipped{reason=\"' + REASONS[s] + '\"} ' + num(skipped[REASONS[s]] || 0));\n}\nfamily(prefix + '_rows_skipped', 'Sheet rows rejected by validation, by reason', skipSamples);\n\nvar warnSamples = [];\nfor (var w = 0; w < RULES.length; w++){\n  warnSamples.push(prefix + '_warnings{rule=\"' + RULES[w] + '\"} ' + num(warnings[RULES[w]] || 0));\n}\nfamily(prefix + '_warnings', 'Non-fatal sheet problems, by rule', warnSamples);\n\nfamily(prefix + '_last_success_timestamp_seconds', 'Unix time of the last successful sheet read', [prefix + '_last_success_timestamp_seconds ' + num(lastSuccess)]);\nfamily(prefix + '_scrape_duration_seconds', 'Time spent building this response', [prefix + '_scrape_duration_seconds ' + num(durationSeconds)]);\nfamily(prefix + '_cache_age_seconds', 'Age of the cached body served, 0 on a fresh read', [prefix + '_cache_age_seconds ' + num(cacheAge)]);\nfamily(prefix + '_cache_hit', '1 if this response was served from cache', [prefix + '_cache_hit ' + (cacheHit ? '1' : '0')]);\n\nvar head = '';\nif (body !== ''){\n  head = (body.charAt(body.length - 1) === NL) ? body : (body + NL);\n}\nvar finalBody = head + lines.join(NL) + NL;\n\nreturn [{ json: { body: finalBody, http_status: (degraded ? failCode : 200) } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "24ea77b2-dc94-4e85-bc4e-9c5767e0a896",
      "name": "Return Metrics Text",
      "type": "n8n-nodes-base.respondToWebhook",
      "position": [
        2144,
        352
      ],
      "parameters": {
        "options": {
          "responseCode": "={{ $json.http_status }}",
          "responseHeaders": {
            "entries": [
              {
                "name": "Content-Type",
                "value": "text/plain; version=0.0.4; charset=utf-8"
              },
              {
                "name": "Cache-Control",
                "value": "no-store"
              }
            ]
          }
        },
        "respondWith": "text",
        "responseBody": "={{ $json.body }}"
      },
      "typeVersion": 1.5
    },
    {
      "id": "86856eca-2a8c-4b66-8a91-5fdf92a96674",
      "name": "Overview",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -800,
        -64
      ],
      "parameters": {
        "width": 690,
        "height": 1500,
        "content": "## Expose Google Sheet KPIs as a Prometheus metrics endpoint for Grafana\n\nTurns a five column Google Sheet into a Prometheus 0.0.4 text exposition endpoint, so business context like SLO targets, licence seats and manually counted backlogs lands on Grafana dashboards without a pull request, a cron writing `.prom` files, or a Pushgateway. Built for slow moving values a non-engineer owns, not for high frequency telemetry.\n\nSheet columns: `metric_name` | `help` | `type` | `value` | `labels`\nExample row: `kpi_open_tickets` | Open support tickets | `gauge` | 37 | `region=us-east`\n\n### How it works\n1. Prometheus scrapes the webhook URL with a bearer token on its own interval.\n2. The workflow checks its own cache first and answers from memory whenever the cached body is younger than `cache_ttl_seconds`, so Google is never touched on a hit.\n3. On a miss it reads the sheet and validates every row, rejecting anything malformed with a counted reason instead of guessing at it.\n4. It renders the valid families as `# HELP`, `# TYPE` and sample lines, then writes the result back into the cache.\n5. It appends its own exporter metrics to every response, so the body is never empty and a broken sheet shows up as data rather than as an absence.\n6. If the sheet read fails it still answers 200 with `sheet_exporter_up 0`, so a broken sheet stays distinguishable from a broken exporter.\n\n### Setup steps\n- [ ] Open `Set Exporter Config` and fill in `sheet_id` and `sheet_tab`.\n- [ ] Create a Header Auth credential on the webhook, header name `Authorization`, value `Bearer YOUR_TOKEN`.\n- [ ] Change the webhook path off `metrics-CHANGEME` to something unguessable.\n- [ ] Connect a Google Sheets credential on `Read Metrics Sheet`.\n- [ ] Activate the workflow, then curl the production URL and confirm the body parses.\n- [ ] Add a scrape job to `prometheus.yml` with `metrics_path` set to the webhook path and `scrape_interval` at 60s.\n- [ ] Alert on `sheet_exporter_up == 0` and on `sheet_exporter_series == 0`.\n\n### Customization\nChange `exporter_prefix` to rename every self metric. Raise or lower `cache_ttl_seconds` to trade freshness against Google Sheets read quota. Raise `max_series` for a large sheet, but keep high cardinality columns such as user id out of the labels column. Set `serve_stale_on_error` to true to keep serving the last good body through a Sheets outage, and `fail_status_code` to 503 if you would rather fail hard than follow the exporter convention."
      },
      "typeVersion": 1
    },
    {
      "id": "801f033d-d80f-4d25-9f2c-49011f3b7768",
      "name": "Section Configure Once",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -48,
        144
      ],
      "parameters": {
        "color": 7,
        "width": 416,
        "height": 420,
        "content": "## Configure once, here\n\nEvery setting a user changes lives in `Set Exporter Config`. The scrape token stays in the webhook's Header Auth credential, so it never enters the exported JSON."
      },
      "typeVersion": 1
    },
    {
      "id": "959bb6a3-3b7d-4d93-a3d4-c75089d25748",
      "name": "Section Cache First",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        432,
        144
      ],
      "parameters": {
        "color": 7,
        "width": 448,
        "height": 420,
        "content": "## Answer from cache first\n\nA fresh cache serves the scrape without touching Google, which bounds Sheets read quota no matter how often Prometheus polls. Set `cache_ttl_seconds` to 0 to disable it."
      },
      "typeVersion": 1
    },
    {
      "id": "8ae24830-9f75-439c-84c3-075adb8c47c3",
      "name": "Section Read And Validate",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        944,
        192
      ],
      "parameters": {
        "color": 7,
        "width": 848,
        "height": 600,
        "content": "## Read and validate rows\n\nA spreadsheet is a free text surface, so every row is validated and every rejection is counted by reason and named in a `#` comment. A blank cell is rejected, never turned into a real `0`."
      },
      "typeVersion": 1
    },
    {
      "id": "2cafca4e-1965-4c49-9697-ee2d7023d2fa",
      "name": "Section Never Fail A Scrape",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1856,
        160
      ],
      "parameters": {
        "color": 7,
        "width": 496,
        "height": 404,
        "content": "## Never fail a scrape\n\nEvery path ends at one response with a parseable body. A failed sheet read still answers 200 with `sheet_exporter_up 0`."
      },
      "typeVersion": 1
    },
    {
      "id": "b68d9824-f3e0-455f-bc6d-00304b77d812",
      "name": "Warning Before You Activate",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -48,
        624
      ],
      "parameters": {
        "color": 3,
        "width": 880,
        "height": 320,
        "content": "## Before you activate\n\nThis endpoint publishes business numbers on the public internet. Set the Header Auth credential and change the webhook path off `metrics-CHANGEME` first.\n\nA 15s scrape interval is 5,760 executions a day, which exhausts an n8n Cloud plan allowance in under a day. Use 60s; a human edited sheet does not change faster than that.\n\n`job` and `instance` labels in the sheet are renamed to `exported_job` and `exported_instance` by Prometheus. The exporter warns through `sheet_exporter_warnings` rather than rejecting the row."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1",
    "executionTimeout": 30,
    "saveDataErrorExecution": "all",
    "saveDataSuccessExecution": "none"
  },
  "connections": {
    "Read Metrics Cache": {
      "main": [
        [
          {
            "node": "Check Cache Freshness",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Metrics Sheet": {
      "main": [
        [
          {
            "node": "Validate Metric Rows",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Flag Sheet Read Failure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Save Metrics Cache": {
      "main": [
        [
          {
            "node": "Add Exporter Self Metrics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Exporter Config": {
      "main": [
        [
          {
            "node": "Read Metrics Cache",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Validate Metric Rows": {
      "main": [
        [
          {
            "node": "Build Exposition Text",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Exposition Text": {
      "main": [
        [
          {
            "node": "Save Metrics Cache",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Cache Freshness": {
      "main": [
        [
          {
            "node": "Add Exporter Self Metrics",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Read Metrics Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flag Sheet Read Failure": {
      "main": [
        [
          {
            "node": "Add Exporter Self Metrics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When Prometheus Scrapes": {
      "main": [
        [
          {
            "node": "Set Exporter Config",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add Exporter Self Metrics": {
      "main": [
        [
          {
            "node": "Return Metrics Text",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

This workflow exposes KPI values stored in a Google Sheet as a Prometheus text exposition endpoint via an n8n webhook, so Grafana can scrape and chart them, with caching, validation, and built-in exporter health metrics. Receives a Prometheus scrape request via an n8n webhook…

Source: https://n8n.io/workflows/17546/ — original creator credit. Request a take-down →

More Data & Sheets workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.