Clean UK Financial CSVs: Apps Script Guide for Google Sheets

The UK Financial CSV Headache: Why Your Data Needs a Clean-Up Crew

If you run a small business or manage personal finances in the UK, you know the drill. You download your bank statements or sales reports as a CSV, ready to import them into Xero, FreeAgent, QuickBooks, or even just a simple spreadsheet for your bookkeeping. But then reality hits. That neatly named "transactions.csv" file is often anything but neat.

You're staring at a spreadsheet filled with inconsistent date formats, strange header names, irrelevant columns, and descriptions that look like they were written by a robot with a bad sense of humour. Trying to make sense of these messy CSVs for HMRC-ready UK financial reports can feel like trying to herd cats. It's frustrating, time-consuming, and frankly, a bit soul-destroying, especially when you're dealing with multiple accounts or sources.

The good news? You don't have to manually format every single entry. Google Sheets, combined with the power of Google Apps Script, offers a robust solution for automating your data cleaning process. This guide will walk you through how to use Apps Script to turn those chaotic bank statements and sales records into perfectly organised, ready-to-use data for your financial management. We're talking about automating repetitive tasks, improving accuracy, and freeing up precious time you could spend on, well, anything else.

Spotting the Symptoms: Common Messy UK CSV Ailments

Before we can cure the problem, we need to understand it. UK financial CSVs, whether from your bank, payment processor, or e-commerce platform, often share a few common characteristics that make apps script bookkeeping a necessity. Here are some of the usual suspects you'll encounter:

• Inconsistent Date Formats: One bank might use DD/MM/YYYY, another YYYY-MM-DD, and some even throw in time stamps for good measure. Your accounting software probably expects one specific format.
• Multiple Header Rows & Footers: Many CSVs include summary information, company details, or even blank lines before the actual transaction data begins. You only need one clear header row.
• Vague or Combined Amount Columns: Some files have a single "Amount" column where debits are negative and credits positive. Others split them into separate "Debit" and "Credit" columns. For consistent analysis, you often want a single "Value" column with correct signage.
• Irrelevant or Redundant Columns: "Transaction ID," "Card Number (Masked)," "Branch Code," "Currency Symbol" – these might be useful to the bank, but for your internal bookkeeping, they often just add clutter.
• Cryptic Descriptions: Ah, the bane of every bookkeeper. "POS PAYMENT ASDA STORES" is fairly clear, but what about "DD NATWEST BUSINESS" or "FPI TRANSFER FROM J SMITH"? These often need to be standardised or categorised.
• UK-Specific Bank Quirks: I've found that different UK banks (Lloyds, Barclays, NatWest, Monzo, Starling) each have their own peculiar CSV layouts. What works for one often needs slight tweaking for another.

Addressing these issues is at the heart of effective automate data cleaning UK. Our Apps Script will tackle these points systematically, transforming your data into a usable format for Google Sheets financial data analysis.

Getting Started: Your Google Sheets & Apps Script Setup

First things first: you'll need a Google account. If you're reading this, you probably already have one. Let's get your workspace ready for some serious apps script csv cleanup.

1. Create a New Google Sheet

Open Google Sheets and create a new blank spreadsheet. Name it something sensible, like "Financial CSV Cleaner" or "Monthly Bank Reconciliations."

2. Import Your Messy CSV

Go to File > Import > Upload and select your bank or sales CSV file. When prompted, I usually choose "Replace spreadsheet" or "Append rows to current sheet," depending on whether I have multiple tabs. Make sure to choose "Detect automatically" for the separator type, though "Comma" is usually safe for CSVs.

3. Access Google Apps Script

This is where the magic happens. In your Google Sheet, click on Extensions > Apps Script. This will open a new browser tab with the Apps Script editor. It might look a bit intimidating at first, but don't worry, we'll go through it step by step.

The Apps Script editor is essentially a coding environment for JavaScript, specifically tailored for Google Workspace applications. Think of it as your personal assistant, ready to do exactly what you tell it to do with your sheets, docs, and emails.

The Core Cleaning Functions: Building Your Apps Script Toolkit

Let's build some functions to tackle those common CSV problems. We'll add these functions one by one into your Apps Script project. Remember to save your project frequently (File > Save project, or the floppy disk icon).

Function 1: `cleanAndStandardiseHeaders()`

This function will get rid of any extraneous rows above your actual data and then standardise your column headers. Many UK banks include summary info or company branding at the top of their CSVs. You want to get straight to the data.

Here's a simplified version. You'll need to adapt the `headerMapping` to your specific CSVs.

 function cleanAndStandardiseHeaders() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const data = sheet.getDataRange().getValues(); // Find the actual header row (often starts with "Date" or "Transaction Date") let headerRowIndex = -1; for (let i = 0; i < data.length; i++) { const row = data[i]; if (row.some(cell => typeof cell === 'string' && cell.toLowerCase().includes('date') && cell.toLowerCase().includes('transaction'))) { headerRowIndex = i; break; } if (row.some(cell => typeof cell === 'string' && cell.toLowerCase().includes('date') && cell.toLowerCase().includes('value'))) { headerRowIndex = i; break; } } if (headerRowIndex === -1) { Logger.log("Could not find a clear header row. Please check your data."); Browser.msgBox("Error", "Could not find a clear header row. Please check your data.", Browser.Buttons.OK); return; } // Remove rows above the header if (headerRowIndex > 0) { sheet.deleteRows(1, headerRowIndex); } // Get the new header row (which is now row 1) const currentHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Define your mapping for common UK bank headers to standardised ones const headerMapping = { 'Transaction Date': 'Date', 'Date': 'Date', 'Processing Date': 'Date', 'Booking Date': 'Date', 'Description': 'Description', 'Transaction Description': 'Description', 'Narrative': 'Description', 'Details': 'Description', 'Amount': 'Amount', 'Value': 'Amount', 'Debit': 'Debit', 'Credit': 'Credit', 'Money Out': 'Debit', 'Money In': 'Credit', 'Balance': 'Balance', 'Reference': 'Reference', // Add more mappings as you encounter them for your specific banks }; const standardisedHeaders = currentHeaders.map(header => { if (typeof header === 'string') { const standardised = headerMapping[header.trim()]; return standardised || header.trim(); // Keep original if no mapping found } return header; }); sheet.getRange(1, 1, 1, standardisedHeaders.length).setValues([standardisedHeaders]); Browser.msgBox("Headers Cleaned", "Headers have been standardised and rows above removed.", Browser.Buttons.OK); } 

This script first tries to locate your actual header row by looking for common keywords. Once found, it deletes any rows above it. Then, it iterates through the remaining header row and renames common, inconsistent headers (e.g., "Transaction Date", "Processing Date") to a single, standard name like "Date". It's a great first step for any messy csv to clean.

Function 2: `formatDates()`

Dates are tricky. Especially with UK data where `DD/MM/YYYY` is common, but Google Sheets might default to `MM/DD/YYYY` or just treat everything as text. This function ensures your date column is correctly formatted.

 function formatDates() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; const dateColumnIndex = headers.indexOf('Date'); // Assumes you've standardised to 'Date' if (dateColumnIndex === -1) { Browser.msgBox("Error", "Date column not found. Ensure headers are standardised first.", Browser.Buttons.OK); return; } const dateColumn = dateColumnIndex + 1; // Apps Script is 1-indexed // Get all values in the Date column, excluding the header const range = sheet.getRange(2, dateColumn, sheet.getLastRow() - 1, 1); const dates = range.getValues(); const formattedDates = dates.map(row => { let dateValue = row[0]; if (typeof dateValue === 'string') { // Try to parse common UK date formats let parts = dateValue.split('/'); // For DD/MM/YYYY if (parts.length === 3) { // Assume DD/MM/YYYY for UK banks dateValue = new Date(parts[2], parts[1] - 1, parts[0]); } else { // Let Google Sheets try to parse other common formats dateValue = new Date(dateValue); } } // Check if it's a valid date object after parsing if (dateValue instanceof Date && !isNaN(dateValue)) { // Set the timezone to ensure correct date interpretation return [Utilities.formatDate(dateValue, Session.getScriptTimeZone(), "dd/MM/yyyy")]; } return [row[0]]; // Return original if not a valid date }); range.setValues(formattedDates); Browser.msgBox("Dates Formatted", "Date column has been standardised to dd/MM/yyyy.", Browser.Buttons.OK); } 

This script specifically tries to parse `DD/MM/YYYY` which is very common in the UK and then formats it explicitly. This helps prevent `01/04/2023` (1st April) being read as `April 1st` which can cause chaos.

Function 3: `standardiseAmounts()`

This function addresses the debit/credit conundrum. We want a single 'Amount' column where money out is negative and money in is positive, just like most accounting software expects.

 function standardiseAmounts() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; const amountColIndex = headers.indexOf('Amount'); const debitColIndex = headers.indexOf('Debit'); const creditColIndex = headers.indexOf('Credit'); if (amountColIndex === -1 && (debitColIndex === -1 || creditColIndex === -1)) { Browser.msgBox("Error", "No 'Amount' or 'Debit'/'Credit' columns found. Ensure headers are standardised.", Browser.Buttons.OK); return; } const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); const values = dataRange.getValues(); let hasDebitCredit = false; if (debitColIndex !== -1 && creditColIndex !== -1) { hasDebitCredit = true; // Add a new 'Amount' header if we're combining Debit/Credit if (amountColIndex === -1) { sheet.insertColumns(debitColIndex + 1); // Insert after Debit sheet.getRange(1, debitColIndex + 1).setValue('Amount'); // Recalculate headers as a column was inserted const newHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; const newAmountColIndex = newHeaders.indexOf('Amount'); const updatedValues = values.map(row => { const debit = parseFloat(String(row[debitColIndex]).replace(/,/g, '')) || 0; const credit = parseFloat(String(row[creditColIndex]).replace(/,/g, '')) || 0; const amount = credit - debit; // Credit is positive, Debit is negative const newRow = [...row]; newRow.splice(newAmountColIndex, 0, amount); // Insert the calculated amount return newRow; }); dataRange.offset(0, 0, updatedValues.length, updatedValues[0].length).setValues(updatedValues); } } else if (amountColIndex !== -1) { // If only 'Amount' column exists, ensure values are numbers and handle signs if needed (e.g., negative for debits) const updatedValues = values.map(row => { let amount = parseFloat(String(row[amountColIndex]).replace(/,/g, '')) || 0; return [...row.slice(0, amountColIndex), amount, ...row.slice(amountColIndex + 1)]; }); dataRange.setValues(updatedValues); } Browser.msgBox("Amounts Standardised", "Amount column standardised to single numeric column.", Browser.Buttons.OK); // Optional: Delete original Debit/Credit columns if they exist and you've combined them if (hasDebitCredit && amountColIndex === -1) { // Only if we just created the 'Amount' column // Delete Debit and Credit columns, ensure to delete from right to left to avoid index issues sheet.deleteColumn(creditColIndex > debitColIndex ? creditColIndex + 1 : debitColIndex + 1); // +1 because a column was inserted sheet.deleteColumn(creditColIndex < debitColIndex ? creditColIndex + 1 : debitColIndex + 1); // +1 because a column was inserted } } 

This script cleverly checks if you have separate "Debit" and "Credit" columns. If so, it creates a new "Amount" column, calculating `Credit - Debit` to get a single, signed figure. If you only have an "Amount" column, it just ensures the values are correctly parsed as numbers.

Function 4: `removeIrrelevantColumns()`

Clutter is the enemy of clarity. This function lets you specify which columns you want to get rid of, reducing your google sheets automation output to only the essentials.

 function removeIrrelevantColumns() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; const columnsToRemove = [ 'Reference Number', 'Transaction ID', 'Card Number (Masked)', 'Branch', 'Type', 'Notes', 'Statement Reference', 'Source', 'Running Balance' // Add any other columns you don't need ]; // Get column indices to remove (from right to left to avoid index issues) const indicesToRemove = []; for (let i = headers.length - 1; i >= 0; i--) { if (typeof headers[i] === 'string' && columnsToRemove.includes(headers[i].trim())) { indicesToRemove.push(i + 1); // Apps Script is 1-indexed } } if (indicesToRemove.length === 0) { Browser.msgBox("No Columns to Remove", "No irrelevant columns found based on your list.", Browser.Buttons.OK); return; } // Delete columns indicesToRemove.sort((a, b) => b - a); // Sort descending to delete from right indicesToRemove.forEach(colIndex => sheet.deleteColumn(colIndex)); Browser.msgBox("Columns Removed", "Irrelevant columns have been deleted.", Browser.Buttons.OK); } 

Function 5: `categoriseTransactions()` (The Powerhouse!)

This is arguably the most valuable part of automating UK financial reports. It assigns categories (e.g., "Groceries", "Utilities", "Rent") based on keywords in the description. For more complex categorisation, you might even consider using AI models like Gemini or Claude via their APIs, but for many cases, simple keyword matching is sufficient.

This function will add a new 'Category' column.

 function categoriseTransactions() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; const descriptionColIndex = headers.indexOf('Description'); if (descriptionColIndex === -1) { Browser.msgBox("Error", "Description column not found. Ensure headers are standardised.", Browser.Buttons.OK); return; } // Add a 'Category' header if it doesn't exist let categoryColIndex = headers.indexOf('Category'); if (categoryColIndex === -1) { sheet.insertColumnAfter(descriptionColIndex + 1); sheet.getRange(1, descriptionColIndex + 2).setValue('Category'); headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Re-read headers categoryColIndex = headers.indexOf('Category'); } const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); const values = dataRange.getValues(); const updatedValues = values.map(row => { const description = String(row[descriptionColIndex]).toUpperCase(); let category = row[categoryColIndex] || 'Uncategorised'; // Keep existing category if it exists if (category === 'Uncategorised') { // Only categorise if not already done // Your categorisation rules if (description.includes('ASDA') || description.includes('TESCO') || description.includes('SAINSBURYS')) { category = 'Groceries'; } else if (description.includes('O2') || description.includes('VODAFONE') || description.includes('EE')) { category = 'Phone Bill'; } else if (description.includes('E.ON') || description.includes('BRITISH GAS') || description.includes('EDF')) { category = 'Utilities - Energy'; } else if (description.includes('THAMES WATER') || description.includes('UNITED UTILITIES')) { category = 'Utilities - Water'; } else if (description.includes('VIRGIN MEDIA') || description.includes('BT BROADBAND')) { category = 'Internet/TV'; } else if (description.includes('RENT') || description.includes('LANDLORD')) { category = 'Rent'; } else if (description.includes('REVOLUT') || description.includes('MONZO') || description.includes('STARLING')) { category = 'Internal Transfer'; } else if (description.includes('SALARY') || description.includes('PAYMENT RECEIVED')) { category = 'Income'; } else if (description.includes('HMRC') || description.includes('TAX')) { category = 'Taxes'; } else if (description.includes('AMAZON') && !description.includes('PRIME')) { category = 'Online Shopping'; } else if (description.includes('DELL') || description.includes('APPLE') || description.includes('PC WORLD')) { category = 'Office Equipment'; } else if (description.includes('NINJACHAT') || description.includes('CLOUD SOFTWARE')) { // Example for software category = 'Software Subscriptions'; } // Add more rules as needed for your specific transactions } const newRow = [...row]; newRow[categoryColIndex] = category; return newRow; }); dataRange.setValues(updatedValues); Browser.msgBox("Transactions Categorised", "Transactions have been categorised based on keywords.", Browser.Buttons.OK); 
For more advanced categorisation, especially for UK freelancers, check out our guide on Mastering HMRC-Ready AI Expense Tracking for UK Freelancers.
 } 

This function introduces a new column 'Category' and then populates it based on keywords found in your transaction descriptions. The `toUpperCase()` helps with case-insensitive matching. You'll want to customise this list of `if/else if` statements to match your specific spending patterns and desired categories. This is a powerful step towards true Google Sheets financial data mastery.

Automating the Flow: Putting It All Together with a Custom Menu

Manually running each function is fine, but wouldn't it be great to have a single button? We can create a custom menu in Google Sheets that runs these scripts in order.

1. The `onOpen()` Function

This special function runs automatically every time your spreadsheet is opened. We'll use it to create our custom menu.

 function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('WealthFlow Tools') .addItem('1. Clean Headers', 'cleanAndStandardiseHeaders') .addItem('2. Format Dates', 'formatDates') .addItem('3. Standardise Amounts', 'standardiseAmounts') .addItem('4. Remove Irrelevant Columns', 'removeIrrelevantColumns') .addItem('5. Categorise Transactions', 'categoriseTransactions') .addSeparator() .addItem('Run All Cleaning Steps', 'runAllCleaningSteps') .addToUi(); } 

2. The `runAllCleaningSteps()` Function

This function simply calls all the other cleaning functions in the correct order.

 function runAllCleaningSteps() { try { cleanAndStandardiseHeaders(); formatDates(); standardiseAmounts(); removeIrrelevantColumns(); categoriseTransactions(); Browser.msgBox("Success", "All cleaning steps completed!", Browser.Buttons.OK); } catch (e) { Browser.msgBox("Error", "An error occurred during cleaning: " + e.message, Browser.Buttons.OK); } } 

Now, when you refresh your Google Sheet, you'll see a new menu item called "WealthFlow Tools". From there, you can run all your cleaning steps with a single click! This is true Google Sheets automation in action.

Refining Your Automation & Next Steps

Your Apps Script setup is a powerful foundation. Here are some thoughts on how to make it even better:

Testing and Iteration: Always test your scripts on a copy of your data first! Every bank CSV is slightly different, and you'll need to adjust the `headerMapping` and `columnsToRemove` arrays to fit your specific files. Don't be afraid to experiment.

Error Handling: The `try...catch` block in `runAllCleaningSteps` is a basic start. You might want more specific error messages for individual functions, perhaps even logging errors to a dedicated sheet.

Dynamic Column Detection: For an even more robust solution, you could write a function that dynamically detects column names that *should* be present (e.g., 'Date', 'Description', 'Amount') and warns you if they're missing, rather than relying solely on fixed indices. This helps with highly varied apps script csv cleanup.

Beyond Basic Cleanup: Once your data is clean, the possibilities are endless. You could:

• Generate Pivot Tables: Automatically create summary reports by category or month.
• Visualise Data: Build charts to track spending or income trends.
• Export to Accounting Software: Format the final sheet perfectly for direct upload into your preferred accounting package like Xero or FreeAgent.
• Integrate with other Sheets: Link your cleaned data to a master financial dashboard.

For those interested in how AI can further enhance your financial processes, consider exploring Essential AI Prompts for UK Small Business Bookkeeping or even automating tasks like invoice reminders with tools like NinjaChat, as discussed in How to Automate Invoice Reminders with AI and Google Sheets.

Embrace Cleaner Data, Embrace More Time

Automating the cleaning of your UK financial CSVs with Google Apps Script in Google Sheets isn't just about tidying up a spreadsheet; it's about reclaiming your time and ensuring accuracy in your financial records. No more manual copy-pasting, no more frantic searching for inconsistent dates, and significantly fewer errors in your bookkeeping. You'll gain peace of mind, make better financial decisions, and free yourself from the drudgery of data preparation. It might take a little effort to set up initially, but the long-term benefits are substantial. So, go on, give it a try – your future self will thank you for it.