Normalize and validate customer data with Google Sheets highlights

Quick overview 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. How it works 1. Starts on a manual trigger and reads raw customer data from a Google Sheets sheet named rawinput. 2. Maps incoming columns into a consistent internal structure (record ID, source, dates, and postal code) for downstream processing. 3. Normalizes customer fields (name formatting, phone and postal-code digits, gender mapping, prefecture/address splitting, and date formatting) and computes a completeness score plus a review reason. 4. Checks whether the record meets the quality threshold (completeness score 6 80) to determine if it is acceptable. 5. Appends accepted records to the normalizedmaster sheet and sends a Google Sheets batchUpdate request to highlight any empty cells. 6. Appends records needing attention to the reviewqueue sheet and sends a Google Sheets batchUpdate request to highlight empty cells for faster review. Setup 1. Create a Google Sheets OAuth2 credential in n8n and connect it to this workflow. 2. Replace YOURSPREADSHEETID in all Google Sheets and HTTP Request nodes with your target spreadsheet ID. 3. Ensure the spreadsheet contains sheets named rawinput, normalizedmaster, and reviewqueue with columns that match the fields written by the workflow (for example: name, email, phone, prefecture, address, birthdate, status, completenessscore, and reviewreason). 4. Verify the sheet tab IDs used for highlighting (1144211222 for normalizedmaster and 1278913705 for reviewqueue) match your spreadsheet, and update them if they differ. 5. (Optional) Adjust the quality threshold (currently completeness score 6 80) and normalization rules in the code to match your data requirements.