Normalize and Validate Customer Data with Google Sheets Highlights

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 →

{
  "id": "rd4RQcGPS2d0MFog",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "12_Data_Normalization_Integration_Flow",
  "tags": [],
  "nodes": [
    {
      "id": "79871069-5043-4f37-b796-cc550db4e734",
      "name": "Save Review Queue",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        384,
        224
      ],
      "parameters": {
        "columns": {
          "value": {
            "name": "={{ $json.raw_name }}",
            "email": "={{ $json.raw_email }}",
            "phone": "={{ $json.raw_phone }}",
            "gender": "={{ $json.raw_gender }}",
            "source": "={{ $json.source }}",
            "status": "={{ $json.status }}",
            "address": "={{ $json.address }}",
            "record_id": "={{ $json.id }}",
            "birth_date": "={{ $json.birth_date }}",
            "prefecture": "={{ $json.raw_prefecture }}",
            "record_key": "={{ $json.record_key }}",
            "imported_at": "={{ $json.imported_at }}",
            "postal_code": "={{ $json.postal_code }}",
            "completeness_score": "={{ $json.completeness_score }}"
          },
          "schema": [
            {
              "id": "record_id",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "record_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "phone",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "postal_code",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "postal_code",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "prefecture",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "prefecture",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "address",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "gender",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "gender",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "birth_date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "birth_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "source",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "source",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "imported_at",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "imported_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "completeness_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "completeness_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "record_key",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "record_key",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1278913705,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=1278913705",
          "cachedResultName": "review_queue"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID",
          "cachedResultName": "12_Data_Normalization_Integration_Flow"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "886a19fe-c291-4dd0-ae74-c812a5d92bb2",
      "name": "Start Manual Test",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1328,
        80
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7db42ac9-3b43-45f1-b944-d6f1819d0867",
      "name": "Read Raw Input",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1168,
        80
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "raw_input"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "12_Data_Normalization_Integration_Flow"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "2b37de59-3a23-44fd-b056-d12d0f323363",
      "name": "Map Source Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        -864,
        80
      ],
      "parameters": {
        "fields": {
          "values": [
            {
              "name": "record_id",
              "stringValue": "={{$json.id}}"
            },
            {
              "name": "birth_date",
              "stringValue": "={{ $json.birth_date }}"
            },
            {
              "name": "source",
              "stringValue": "={{ $json.source }}"
            },
            {
              "name": "created_at",
              "stringValue": "={{ $json.created_at }}"
            },
            {
              "name": "postal_code",
              "stringValue": "={{ $json.postal_code }}"
            }
          ]
        },
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "5e874637-21ae-44ca-8de3-c80cfbc78560",
      "name": "Normalize Customer Data",
      "type": "n8n-nodes-base.code",
      "position": [
        -464,
        80
      ],
      "parameters": {
        "jsCode": "return items.map(item => {\n  const d = item.json;\n\n  const normalizeText = (v) => {\n    if (v === null || v === undefined) return '';\n    return String(v).trim();\n  };\n\n  const normalizeName = (v) => {\n    const name = normalizeText(v).replace(/\\u3000/g, ' ');\n    if (!name) return '';\n\n    if (/^[A-Za-z\\s]+$/.test(name)) return name;\n\n    const parts = name.split(/\\s+/).filter(Boolean);\n    if (parts.length >= 2) {\n      return `${parts[0]}\u3001${parts.slice(1).join(' ')}`;\n    }\n    return name;\n  };\n\n  const normalizePhone = (v) => {\n    return normalizeText(v).replace(/[^0-9]/g, '');\n  };\n\n  const normalizePostalCode = (v) => {\n    return normalizeText(v).replace(/[^0-9]/g, '');\n  };\n\n  const normalizeGender = (v) => {\n    const val = normalizeText(v).toLowerCase();\n\n    if (['m', 'male', 'man', '\u7537', '\u7537\u6027'].includes(val)) return 'Male';\n    if (['f', 'female', 'woman', '\u5973', '\u5973\u6027'].includes(val)) return 'Female';\n\n    return 'NON';\n  };\n\n  const normalizeDate = (v) => {\n    const raw = normalizeText(v);\n    if (!raw) return '';\n\n    const dt = new Date(raw);\n    if (isNaN(dt.getTime())) return '';\n\n    const y = dt.getFullYear();\n    const m = String(dt.getMonth() + 1).padStart(2, '0');\n    const d2 = String(dt.getDate()).padStart(2, '0');\n\n    return `${y}/${m}/${d2}`;\n  };\n\n  const splitAddress = (prefectureValue, addressValue) => {\n    const merged = normalizeText(prefectureValue) + normalizeText(addressValue);\n\n    if (!merged) {\n      return { prefecture: '', address: '' };\n    }\n\n    const prefectures = [\n      '\u5317\u6d77\u9053',\n      '\u9752\u68ee\u770c','\u5ca9\u624b\u770c','\u5bae\u57ce\u770c','\u79cb\u7530\u770c','\u5c71\u5f62\u770c','\u798f\u5cf6\u770c',\n      '\u8328\u57ce\u770c','\u6803\u6728\u770c','\u7fa4\u99ac\u770c','\u57fc\u7389\u770c','\u5343\u8449\u770c','\u6771\u4eac\u90fd','\u795e\u5948\u5ddd\u770c',\n      '\u65b0\u6f5f\u770c','\u5bcc\u5c71\u770c','\u77f3\u5ddd\u770c','\u798f\u4e95\u770c','\u5c71\u68a8\u770c','\u9577\u91ce\u770c',\n      '\u5c90\u961c\u770c','\u9759\u5ca1\u770c','\u611b\u77e5\u770c','\u4e09\u91cd\u770c',\n      '\u6ecb\u8cc0\u770c','\u4eac\u90fd\u5e9c','\u5927\u962a\u5e9c','\u5175\u5eab\u770c','\u5948\u826f\u770c','\u548c\u6b4c\u5c71\u770c',\n      '\u9ce5\u53d6\u770c','\u5cf6\u6839\u770c','\u5ca1\u5c71\u770c','\u5e83\u5cf6\u770c','\u5c71\u53e3\u770c',\n      '\u5fb3\u5cf6\u770c','\u9999\u5ddd\u770c','\u611b\u5a9b\u770c','\u9ad8\u77e5\u770c',\n      '\u798f\u5ca1\u770c','\u4f50\u8cc0\u770c','\u9577\u5d0e\u770c','\u718a\u672c\u770c','\u5927\u5206\u770c','\u5bae\u5d0e\u770c','\u9e7f\u5150\u5cf6\u770c',\n      '\u6c96\u7e04\u770c'\n    ];\n\n    for (const p of prefectures) {\n      if (merged.startsWith(p)) {\n        return {\n          prefecture: p,\n          address: merged.slice(p.length).trim()\n        };\n      }\n    }\n\n    return {\n      prefecture: normalizeText(prefectureValue),\n      address: normalizeText(addressValue)\n    };\n  };\n\n  // \u5143\u30c7\u30fc\u30bf\n  const sourceName = d.raw_name;\n  const sourceEmail = d.raw_email;\n  const sourcePhone = d.raw_phone;\n  const sourcePrefecture = d.raw_prefecture;\n  const sourceAddress = d.address || d.city || '';\n  const sourceGender = d.raw_gender;\n  const sourceBirthDate = d.birth_date;\n  const sourceImportedAt = d.created_at;\n  const sourcePostalCode = d.postal_code;\n\n  // \u6b63\u898f\u5316\n  d.name = normalizeName(sourceName);\n  d.email = normalizeText(sourceEmail);\n  d.phone = normalizePhone(sourcePhone);\n  d.gender = normalizeGender(sourceGender);\n\n  const addr = splitAddress(sourcePrefecture, sourceAddress);\n  d.prefecture = addr.prefecture;\n  d.address = addr.address;\n\n  d.postal_code = normalizePostalCode(sourcePostalCode);\n  d.birth_date = normalizeDate(sourceBirthDate);\n  d.imported_at = normalizeDate(sourceImportedAt);\n  d.source = normalizeText(d.source);\n\n  // \u30ec\u30d3\u30e5\u30fc\u7406\u7531\n  const reasons = [];\n  if (!d.name) reasons.push('Missing name');\n  if (!d.email) reasons.push('Missing email');\n  if (!d.phone) reasons.push('Missing phone');\n  if (!d.prefecture) reasons.push('Missing prefecture');\n  if (!d.address) reasons.push('Missing address');\n  if (!d.birth_date) reasons.push('Invalid birth date');\n\n  d.review_reason = reasons.join(' / ');\n\n  // \u30b9\u30b3\u30a2: address\u3082\u5fc5\u9808\u306b\u3059\u308b\n  const completenessScore =\n    (d.name ? 17 : 0) +\n    (d.email ? 17 : 0) +\n    (d.phone ? 17 : 0) +\n    (d.prefecture ? 17 : 0) +\n    (d.address ? 16 : 0) +\n    (d.birth_date ? 16 : 0);\n\n  d.completeness_score = completenessScore;\n\n  // \u5fc5\u9808\u9805\u76ee\u304c\u5168\u90e8\u3042\u308b\u3068\u304d\u3060\u3051 normalized\n  const isValid =\n    !!d.name &&\n    !!d.email &&\n    !!d.phone &&\n    !!d.prefecture &&\n    !!d.address &&\n    !!d.birth_date;\n\n  d.status = isValid ? 'normalized' : 'needs_review';\n  d.record_key = `${d.source}_${d.imported_at}`;\n\n  return { json: d };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "45ee5477-b527-45cb-ba53-52d8c5f8825a",
      "name": "Validate Record Quality",
      "type": "n8n-nodes-base.if",
      "position": [
        -48,
        80
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "016a451b-d179-4133-a61b-fb7086beb6d3",
              "operator": {
                "type": "number",
                "operation": "gte"
              },
              "leftValue": "={{$json.completeness_score}}",
              "rightValue": 80
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "b365360f-5904-4b7f-81c2-3ac5f514c38a",
      "name": "Save Normalized Records",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        368,
        -32
      ],
      "parameters": {
        "columns": {
          "value": {
            "name": "={{ $json.raw_name }}",
            "email": "={{ $json.raw_email }}",
            "phone": "={{ $json.raw_phone }}",
            "gender": "={{ $json.raw_gender }}",
            "source": "={{$json.source}}",
            "status": "={{ $json.status }}",
            "address": "={{ $json.address }}",
            "record_id": "={{$json.record_id}}",
            "birth_date": "={{ $json.birth_date }}",
            "prefecture": "={{ $json.raw_prefecture }}",
            "record_key": "={{ $json.record_key }}",
            "imported_at": "={{ $json.imported_at }}",
            "postal_code": "={{ $json.postal_code }}",
            "review_reason": "={{ $json.review_reason }}",
            "completeness_score": "={{ $json.completeness_score }}"
          },
          "schema": [
            {
              "id": "record_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "record_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "phone",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "prefecture",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "prefecture",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "address",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "postal_code",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "postal_code",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "gender",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "gender",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "birth_date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "birth_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "source",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "source",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "imported_at",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "imported_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "completeness_score",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "completeness_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "review_reason",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "review_reason",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "record_key",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "record_key",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "locationDefine": {
            "values": {}
          }
        },
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1144211222,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=1144211222",
          "cachedResultName": "normalized_master"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "12_Data_Normalization_Integration_Flow"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4,
      "alwaysOutputData": false
    },
    {
      "id": "2003b0ee-de4f-4967-8383-d672e2a64d43",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1872,
        -128
      ],
      "parameters": {
        "width": 464,
        "height": 416,
        "content": "## Workflow Description\nThis workflow automates the process of cleaning, standardizing, and validating raw customer data collected from multiple sources.\n\nIt reads unstructured data from Google Sheets, normalizes key fields such as name, phone number, address, gender, and dates into a consistent format, and evaluates the completeness of each record using a scoring system.\n\nBased on the data quality score, the workflow automatically separates records into two categories: valid data and records that require review. Clean and validated data is stored in a master dataset, while incomplete or inconsistent records are routed to a review queue for further inspection.\n\nThis workflow demonstrates a practical data processing pipeline in n8n, combining data transformation, validation logic, and conditional routing to ensure high-quality and structured data for downstream use."
      },
      "typeVersion": 1
    },
    {
      "id": "654f5390-ff69-4465-9844-f156fa5a09a0",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1376,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 416,
        "content": "## 1. Input Block\nNodes: Start Manual Test, Read Raw Input\nThis block starts the workflow and loads raw customer data from Google Sheets. It serves as the entry point for the normalization process by collecting unstructured records from the source sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "bdd88fe1-27c2-46ef-9f79-195bf5bfc0b3",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -976,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 352,
        "height": 416,
        "content": "## 2. Field Mapping Block\nNodes: Map Source Fields\nThis block maps inconsistent source columns into a unified internal structure. It aligns different input formats into standard field names such as name, email, phone, prefecture, address, gender, birth date, and source."
      },
      "typeVersion": 1
    },
    {
      "id": "8eb70074-fc3c-40d9-8c8e-293731a18b92",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -192,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 416,
        "content": "## 4. Data Validation Block\n\nNodes: Validate Record Quality\nThis block evaluates record completeness and quality. It checks whether essential fields are present, calculates a completeness score, assigns a status, and determines whether the record is ready for the master sheet or should be sent for manual review."
      },
      "typeVersion": 1
    },
    {
      "id": "2afd38cd-0396-42aa-b24c-60518b24f0aa",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -608,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 400,
        "height": 416,
        "content": "## 3. Data Normalization Block\n\nNodes: Normalize Customer Data\nThis block cleans and standardizes each record. It formats phone numbers, normalizes names and gender values, separates address-related fields where possible, standardizes date values, and prepares the data for validation."
      },
      "typeVersion": 1
    },
    {
      "id": "bdb7b52a-fcae-467b-9401-ee5243b2526d",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        192
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 448,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## 6. Review Queue Block\n\nNodes: Save Review Queue, Build Highlight Requests, HTTP Request\nThis block stores incomplete or inconsistent records in the review queue sheet. It also highlights empty cells directly in Google Sheets, making it easier for users to identify missing information and perform manual correction quickly."
      },
      "typeVersion": 1
    },
    {
      "id": "546e5ba1-96f6-4af3-93d4-1d6c0dd6ccbc",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        -256
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 416,
        "content": "## 5. Valid Data Output Block\n\nNodes: Save Normalized Record, Code in JavaScript, HTTP Request\nThis block stores clean and validated records in the normalized master sheet. After saving, it generates highlight requests and applies color formatting to empty cells so that missing information can still be visually identified even in accepted records."
      },
      "typeVersion": 1
    },
    {
      "id": "9c8abbb5-e116-4edd-9c85-88db097b0743",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        672,
        192
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 448,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## 8. Spreadsheet-Based Quality Control Block\n\nNodes: Google Sheets output nodes and Sheets API formatting nodes\nThis block turns Google Sheets into a lightweight review dashboard. It combines structured storage with visual feedback, allowing users to manage normalized data and exception records in a practical and easy-to-understand way."
      },
      "typeVersion": 1
    },
    {
      "id": "a448ff89-e5fb-4da8-9762-3fb6ac856b30",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        -256
      ],
      "parameters": {
        "color": 7,
        "width": 496,
        "height": 416,
        "content": "## 7. Visual Review Enhancement Block\n\nNodes: Code in JavaScript, Build Highlight Requests, HTTP Request\nThis block improves spreadsheet usability by applying background colors to blank cells. Instead of highlighting an entire row, it only marks the specific empty cells that need attention, which makes the review process clearer and more efficient."
      },
      "typeVersion": 1
    },
    {
      "id": "e796bf7b-1715-4340-a113-a166a03e245f",
      "name": "Apply Normalized Sheet Cell Highlights",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        976,
        -32
      ],
      "parameters": {
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/YOUR_SPREADSHEET_ID:batchUpdate",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "requests",
              "value": "={{$json.requests}}"
            }
          ]
        },
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "007d0d76-4199-412d-bc0b-4430170974f1",
      "name": "Apply Review Queue Cell Highlights",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        992,
        224
      ],
      "parameters": {
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/YOUR_SPREADSHEET_ID:batchUpdate",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "requests",
              "value": "={{$json.requests}}"
            }
          ]
        },
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "d97be98a-b962-4584-827a-4614442269c3",
      "name": "Build Highlight Requests Normal",
      "type": "n8n-nodes-base.code",
      "position": [
        752,
        -32
      ],
      "parameters": {
        "jsCode": "const itemsIn = $input.all();\n\n// review_queue \u306e sheetId \u3092\u5165\u308c\u308b\nconst sheetId = 1144211222;\n\nconst colMap = {\n  name: 1,               // B\n  email: 2,              // C\n  phone: 3,              // D\n  postal_code: 4,        // E\n  prefecture: 5,         // F\n  address: 6,            // G\n  gender: 7,             // H\n  birth_date: 8,         // I\n  source: 9,             // J\n  imported_at: 10,       // K\n  completeness_score: 11,// L\n  status: 12,            // M\n  review_reason: 13,     // N\n  record_key: 14         // O\n};\n\n// \u7a7a\u6b04\u30c1\u30a7\u30c3\u30af\u3057\u305f\u3044\u5217\nconst targetFields = [\n  'name',\n  'email',\n  'phone',\n  'postal_code',\n  'prefecture',\n  'address',\n  'gender',\n  'birth_date',\n  'source',\n  'imported_at'\n];\n\nconst requests = [];\n\nfor (let i = 0; i < itemsIn.length; i++) {\n  const d = itemsIn[i].json;\n\n  // \u30b7\u30fc\u30c8\u306e\u5b9f\u884c\u7d50\u679c2\u884c\u76ee\u304b\u3089\u59cb\u307e\u308b\u60f3\u5b9a\n  const rowIndex = i + 1;\n\n  for (const field of targetFields) {\n    const value = d[field];\n\n    const isEmpty =\n      value === null ||\n      value === undefined ||\n      String(value).trim() === '';\n\n    if (isEmpty) {\n      const colIndex = colMap[field];\n\n      requests.push({\n        repeatCell: {\n          range: {\n            sheetId,\n            startRowIndex: rowIndex,\n            endRowIndex: rowIndex + 1,\n            startColumnIndex: colIndex,\n            endColumnIndex: colIndex + 1\n          },\n          cell: {\n            userEnteredFormat: {\n              backgroundColor: {\n                red: 1,\n                green: 0.9,\n                blue: 0.9\n              }\n            }\n          },\n          fields: 'userEnteredFormat.backgroundColor'\n        }\n      });\n    }\n  }\n}\n\nreturn [\n  {\n    json: {\n      spreadsheetId: 'YOUR_SPREADSHEET_ID',\n      requests\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "468320e5-8cc5-4ca6-9309-449da024a965",
      "name": "Build Highlight Requests Review",
      "type": "n8n-nodes-base.code",
      "position": [
        752,
        224
      ],
      "parameters": {
        "jsCode": "const itemsIn = $input.all();\n\n// review_queue \u306e sheetId \u3092\u5165\u308c\u308b\nconst sheetId = 1278913705;\n\nconst colMap = {\n  name: 1,               // B\n  email: 2,              // C\n  phone: 3,              // D\n  postal_code: 4,        // E\n  prefecture: 5,         // F\n  address: 6,            // G\n  gender: 7,             // H\n  birth_date: 8,         // I\n  source: 9,             // J\n  imported_at: 10,       // K\n  completeness_score: 11,// L\n  status: 12,            // M\n  review_reason: 13,     // N\n  record_key: 14         // O\n};\n\n// \u7a7a\u6b04\u30c1\u30a7\u30c3\u30af\u3057\u305f\u3044\u5217\nconst targetFields = [\n  'name',\n  'email',\n  'phone',\n  'postal_code',\n  'prefecture',\n  'address',\n  'gender',\n  'birth_date',\n  'source',\n  'imported_at'\n];\n\nconst requests = [];\n\nfor (let i = 0; i < itemsIn.length; i++) {\n  const d = itemsIn[i].json;\n\n  // \u30b7\u30fc\u30c8\u306e\u5b9f\u884c\u7d50\u679c2\u884c\u76ee\u304b\u3089\u59cb\u307e\u308b\u60f3\u5b9a\n  const rowIndex = i + 1;\n\n  for (const field of targetFields) {\n    const value = d[field];\n\n    const isEmpty =\n      value === null ||\n      value === undefined ||\n      String(value).trim() === '';\n\n    if (isEmpty) {\n      const colIndex = colMap[field];\n\n      requests.push({\n        repeatCell: {\n          range: {\n            sheetId,\n            startRowIndex: rowIndex,\n            endRowIndex: rowIndex + 1,\n            startColumnIndex: colIndex,\n            endColumnIndex: colIndex + 1\n          },\n          cell: {\n            userEnteredFormat: {\n              backgroundColor: {\n                red: 1,\n                green: 0.9,\n                blue: 0.9\n              }\n            }\n          },\n          fields: 'userEnteredFormat.backgroundColor'\n        }\n      });\n    }\n  }\n}\n\nreturn [\n  {\n    json: {\n      spreadsheetId: 'YOUR_SPREADSHEET_ID',\n      requests\n    }\n  }\n];"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "settings": {
    "binaryMode": "separate",
    "executionOrder": "v1"
  },
  "versionId": "7502b01d-2138-4053-af51-0e2ee4c6bd06",
  "connections": {
    "Read Raw Input": {
      "main": [
        [
          {
            "node": "Map Source Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Map Source Fields": {
      "main": [
        [
          {
            "node": "Normalize Customer Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Save Review Queue": {
      "main": [
        [
          {
            "node": "Build Highlight Requests Review",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start Manual Test": {
      "main": [
        [
          {
            "node": "Read Raw Input",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Normalize Customer Data": {
      "main": [
        [
          {
            "node": "Validate Record Quality",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Save Normalized Records": {
      "main": [
        [
          {
            "node": "Build Highlight Requests Normal",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Validate Record Quality": {
      "main": [
        [
          {
            "node": "Save Normalized Records",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Save Review Queue",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Highlight Requests Normal": {
      "main": [
        [
          {
            "node": "Apply Normalized Sheet Cell Highlights",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Highlight Requests Review": {
      "main": [
        [
          {
            "node": "Apply Review Queue Cell Highlights",
            "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.

• googleSheetsOAuth2Api

About this workflow

This workflow manually pulls raw customer records from Google Sheets, normalizes and scores key fields (name, phone, address, gender, dates), then routes results to either a normalized master sheet or a review queue and uses the Google Sheets API to highlight missing cells.…

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