Standardise Data in Google Sheets with Apps Script

Data standardisation makes values consistent so they can be compared, matched and reported reliably. In Google Sheets you can standardise text with formulas, automate repeatable rules with Apps Script and review exceptions before replacing original data.

Start with formulas

Common formula steps include trimming whitespace, changing case, removing unwanted characters and splitting or combining fields. Functions such as TRIM, CLEAN, LOWER, UPPER, SUBSTITUTE, REGEXREPLACE and ARRAYFORMULA can handle many cases directly in the sheet.

Automate rules with Apps Script

Use Apps Script when you need reusable rules, mapping tables or multi-step transformations. The example below reads values, applies simple cleanup and writes standardised output next to the original column.

function standardiseColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("A2:A").getValues();
  var output = values.map(function (row) {
    var value = String(row[0] || "").trim();
    value = value.replace(/\s+/g, " ");
    value = value.replace(/[^\w\s@.\-]/g, "");
    return [value];
  });
  sheet.getRange("B2:B").clearContent();
  if (output.length) sheet.getRange(2, 2, output.length, 1).setValues(output);
}

Adapt the replacements to your data type, such as company names, phone numbers, addresses or product codes. Keep original values until the output has been reviewed.

Using Flookup custom functions

For in-sheet standardisation and matching, Flookup custom functions can be a practical alternative to maintaining scripts. NORMALIZE helps clean and transform text, while FUZZYSIM, FLOOKUP, DEDUPE and ULIST support comparison and deduplication workflows. The Sheets AI Assistant can suggest formulas for your specific ranges and goal.

Next steps