Normalize and Backfill Notion Database Properties with Rules and Logging

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": "Normalize and backfill Notion database properties from an editable rules table",
  "tags": [],
  "nodes": [
    {
      "name": "Every Day at 3am",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        208,
        400
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 3
            }
          ]
        }
      },
      "typeVersion": 1.3
    },
    {
      "name": "Get Database Rows",
      "type": "n8n-nodes-base.notion",
      "position": [
        384,
        400
      ],
      "parameters": {
        "simple": false,
        "options": {},
        "resource": "databasePage",
        "operation": "getAll",
        "returnAll": true,
        "databaseId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "name": "Apply Normalization Rules",
      "type": "n8n-nodes-base.code",
      "position": [
        672,
        400
      ],
      "parameters": {
        "jsCode": "// ==== CONFIG: edit this block to match your database ====\n// The Notion property names this workflow manages. Set each to your exact property name.\nconst TITLE_PROP         = 'Name';            // title property; the source for the derived Key\nconst STATUS_PROP        = 'Status';          // a select property to canonicalize and backfill\nconst KEY_PROP           = 'Key';             // rich_text: a slug derived from the title\nconst WEEK_PROP          = 'Created week';    // rich_text: an ISO year-week stamp from created time\nconst NORMALIZED_AT_PROP = 'Last normalized'; // date: stamped on any row this run changes\n\n// Backfill: the value written to STATUS_PROP when it is empty.\nconst DEFAULT_STATUS = 'To Do';\n\n// Canonical map: messy spelling (matched lowercased and trimmed) -> canonical value written as-is.\nconst CANONICAL_STATUS = {\n  'to do': 'To Do', 'todo': 'To Do', 'to-do': 'To Do', 'not started': 'To Do', 'backlog': 'To Do', 'new': 'To Do',\n  'in progress': 'In Progress', 'in-progress': 'In Progress', 'wip': 'In Progress', 'doing': 'In Progress', 'started': 'In Progress', 'ongoing': 'In Progress',\n  'done': 'Done', 'complete': 'Done', 'completed': 'Done', 'finished': 'Done', 'closed': 'Done',\n};\n\n// Derived-field switches. Turn either off to leave that property untouched.\nconst COMPUTE_KEY  = true;   // maintain KEY_PROP as a slug of the title\nconst COMPUTE_WEEK = true;   // maintain WEEK_PROP as the created-week stamp\n// ============================================================\n\nconst RUN_TS = new Date().toISOString();\n\n// Fold each canonical target onto itself so an already-correct value is recognised, not rewritten.\nconst canon = {};\nfor (const [k, v] of Object.entries(CANONICAL_STATUS)) {\n  canon[String(k).trim().toLowerCase()] = v;\n  canon[String(v).trim().toLowerCase()] = v;\n}\n\nfunction readSelect(page, name) {\n  const p = page && page.properties && page.properties[name];\n  if (!p) return '';\n  if (p.type === 'select') return (p.select && p.select.name) || '';\n  if (p.type === 'status') return (p.status && p.status.name) || '';\n  return '';\n}\nfunction readText(page, name) {\n  const p = page && page.properties && page.properties[name];\n  if (!p) return '';\n  if (p.type === 'rich_text') return (p.rich_text || []).map((x) => x.plain_text).join('');\n  if (p.type === 'title') return (p.title || []).map((x) => x.plain_text).join('');\n  return '';\n}\nfunction readTitle(page, name) {\n  const p = page && page.properties && page.properties[name];\n  if (p && p.type === 'title') return (p.title || []).map((x) => x.plain_text).join('');\n  // Fall back to whatever property is the database title, whatever it is named.\n  for (const key of Object.keys((page && page.properties) || {})) {\n    const q = page.properties[key];\n    if (q && q.type === 'title') return (q.title || []).map((x) => x.plain_text).join('');\n  }\n  return '';\n}\nfunction slugify(s) {\n  return String(s)\n    .trim()\n    .toLowerCase()\n    .replace(/[^a-z0-9]+/g, '-')\n    .replace(/^-+|-+$/g, '');\n}\nfunction isoWeek(iso) {\n  if (!iso) return '';\n  const d = new Date(iso);\n  if (isNaN(d.getTime())) return '';\n  const date = new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));\n  const dayNum = (date.getUTCDay() + 6) % 7; // Monday = 0 ... Sunday = 6\n  date.setUTCDate(date.getUTCDate() - dayNum + 3); // the Thursday of this ISO week\n  const firstThursday = new Date(Date.UTC(date.getUTCFullYear(), 0, 4));\n  const fDayNum = (firstThursday.getUTCDay() + 6) % 7;\n  firstThursday.setUTCDate(firstThursday.getUTCDate() - fDayNum + 3);\n  const week = 1 + Math.round((date.getTime() - firstThursday.getTime()) / 604800000);\n  return `${date.getUTCFullYear()}-W${String(week).padStart(2, '0')}`;\n}\n\nconst pages = items.map((i) => i.json);\n\nlet scanned = 0, normalized = 0, alreadyClean = 0;\nlet statusBackfilled = 0, statusCanonicalized = 0, keyUpdated = 0, weekUpdated = 0;\nconst updates = [];\n\nfor (const page of pages) {\n  if (!page || !page.properties) continue;\n  scanned += 1;\n\n  const changes = [];\n\n  // --- Status: backfill when empty, otherwise canonicalise known variants, leave unknowns alone ---\n  const curStatus = readSelect(page, STATUS_PROP);\n  let desiredStatus = curStatus;\n  if (curStatus === '') {\n    desiredStatus = DEFAULT_STATUS;\n  } else {\n    const mapped = canon[curStatus.trim().toLowerCase()];\n    if (mapped) desiredStatus = mapped;\n  }\n  if (desiredStatus !== curStatus) {\n    changes.push('status');\n    if (curStatus === '') statusBackfilled += 1; else statusCanonicalized += 1;\n  }\n\n  // --- Key: a slug of the title; never clobber an existing key when the title is empty ---\n  const title = readTitle(page, TITLE_PROP);\n  const curKey = readText(page, KEY_PROP);\n  let desiredKey = curKey;\n  if (COMPUTE_KEY && title !== '') {\n    const slug = slugify(title);\n    if (slug !== '' && slug !== curKey) {\n      desiredKey = slug;\n      changes.push('key');\n      keyUpdated += 1;\n    }\n  }\n\n  // --- Created week: an ISO year-week stamp from the page's created time ---\n  const curWeek = readText(page, WEEK_PROP);\n  let desiredWeek = curWeek;\n  if (COMPUTE_WEEK) {\n    const wk = isoWeek(page.created_time);\n    if (wk !== '' && wk !== curWeek) {\n      desiredWeek = wk;\n      changes.push('week');\n      weekUpdated += 1;\n    }\n  }\n\n  if (changes.length === 0) { alreadyClean += 1; continue; }\n\n  normalized += 1;\n  updates.push({ json: {\n    _type: 'update',\n    pageId: page.id,\n    title: title,\n    status: desiredStatus,\n    key: desiredKey,\n    createdWeek: desiredWeek,\n    lastNormalized: RUN_TS,\n    changed: changes,\n  } });\n}\n\nconst recap = { json: {\n  _type: 'recap',\n  scanned: scanned,\n  normalized: normalized,\n  alreadyClean: alreadyClean,\n  statusBackfilled: statusBackfilled,\n  statusCanonicalized: statusCanonicalized,\n  keyUpdated: keyUpdated,\n  weekUpdated: weekUpdated,\n  defaultStatus: DEFAULT_STATUS,\n} };\n\nreturn [...updates, recap];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Route by Item Type",
      "type": "n8n-nodes-base.switch",
      "position": [
        848,
        400
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 1,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json._type }}",
                    "rightValue": "update"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 1,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json._type }}",
                    "rightValue": "recap"
                  }
                ]
              }
            }
          ]
        },
        "options": {
          "fallbackOutput": "none"
        }
      },
      "typeVersion": 3.4
    },
    {
      "name": "Update Normalized Rows",
      "type": "n8n-nodes-base.notion",
      "onError": "continueRegularOutput",
      "position": [
        1280,
        336
      ],
      "parameters": {
        "pageId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.pageId }}"
        },
        "options": {},
        "resource": "databasePage",
        "operation": "update",
        "propertiesUi": {
          "propertyValues": [
            {
              "key": "Status|select",
              "selectValue": "={{ $json.status }}"
            },
            {
              "key": "Key|rich_text",
              "textContent": "={{ $json.key }}"
            },
            {
              "key": "Created week|rich_text",
              "textContent": "={{ $json.createdWeek }}"
            },
            {
              "key": "Last normalized|date",
              "date": "={{ $json.lastNormalized }}"
            }
          ]
        }
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "name": "Append Run to Log",
      "type": "n8n-nodes-base.notion",
      "position": [
        1280,
        512
      ],
      "parameters": {
        "blockId": {
          "__rl": true,
          "mode": "url",
          "value": ""
        },
        "blockUi": {
          "blockValues": [
            {
              "textContent": "=Normalize run {{ $now.toFormat(\"yyyy-LL-dd HH:mm\") }}: scanned {{ $json.scanned }} rows, normalized {{ $json.normalized }} ({{ $json.statusBackfilled }} status filled, {{ $json.statusCanonicalized }} status canonicalized, {{ $json.keyUpdated }} keys, {{ $json.weekUpdated }} weeks), {{ $json.alreadyClean }} already clean."
            }
          ]
        },
        "resource": "block",
        "operation": "append"
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "name": "Overview Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        48
      ],
      "parameters": {
        "width": 620,
        "height": 812,
        "content": "## Normalize and backfill Notion database properties from an editable rules table\n\n### How it works\n\n1. On a daily schedule, it reads every row from the Notion database you choose.\n2. A Code node fills a missing Status with a default, folds messy Status spellings into one canonical value, and derives a title slug and a created-week stamp.\n3. It compares each derived value to what is already stored and marks a row changed only when something actually differs.\n4. Only changed rows are updated in Notion, and each gets a Last normalized timestamp, so already-clean rows produce no write.\n5. A one-line recap of every run is appended to a log page, including runs that change nothing.\n\n### Setup steps\n\n- [ ] Connect a Notion credential and share the target database and the log page with the integration.\n- [ ] In Get Database Rows, select the database you want to normalize.\n- [ ] In Apply Normalization Rules, edit the CONFIG block: property names, DEFAULT_STATUS, and the CANONICAL_STATUS map.\n- [ ] In Update Normalized Rows, confirm the four mapped properties match your database (Status, Key, Created week, Last normalized).\n- [ ] In Append Run to Log, paste the URL of the page that should receive the recap.\n\n### Customization\n\nEdit CANONICAL_STATUS to add spellings, change DEFAULT_STATUS, or set COMPUTE_KEY and COMPUTE_WEEK to false to leave a derived field alone. Adjust Every Day at 3am to run on any schedule."
      },
      "typeVersion": 1
    },
    {
      "name": "Fetch Section Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        160,
        224
      ],
      "parameters": {
        "color": 7,
        "width": 380,
        "height": 356,
        "content": "## Fetch database rows\n\nRuns on a daily schedule and reads every row with full property data."
      },
      "typeVersion": 1
    },
    {
      "name": "Normalize Section Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        576,
        224
      ],
      "parameters": {
        "color": 7,
        "width": 484,
        "height": 356,
        "content": "## Apply normalization rules\n\nCompares each row to the rules in the Code node and emits only the rows that actually change, plus a run recap."
      },
      "typeVersion": 1
    },
    {
      "name": "Write Section Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1104,
        160
      ],
      "parameters": {
        "color": 7,
        "width": 436,
        "height": 520,
        "content": "## Write updates and log the run\n\nUpdates just the changed rows in Notion and appends a one-line recap of every run to your log page."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Every Day at 3am": {
      "main": [
        [
          {
            "node": "Get Database Rows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Database Rows": {
      "main": [
        [
          {
            "node": "Apply Normalization Rules",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route by Item Type": {
      "main": [
        [
          {
            "node": "Update Normalized Rows",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Append Run to Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Apply Normalization Rules": {
      "main": [
        [
          {
            "node": "Route by Item Type",
            "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.

• notionApi

About this workflow

This workflow runs daily to normalize and backfill properties in a Notion database, standardizing Status values, deriving a Key slug and Created week stamp, updating only changed pages, and appending a run recap to a Notion log page. Runs every day at 3am on a schedule. Fetches…

Source: https://n8n.io/workflows/16800/ — 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.