Product codes and SKUs can drift over time as suppliers, systems and teams use different separators, prefixes or casing. Apps Script can apply consistent formatting rules and mapping tables in Google Sheets so codes are easier to compare, match and report.
Collect examples and rules
Start with a short list of original and preferred codes in an Examples sheet. Note the rule behind each change, such as uppercase letters, remove spaces, add a prefix or replace separators. This makes the transformation easier to test and maintain.
Standardise with Apps Script
The script below applies exact mappings, then normalises case and removes common separators. Write the output to a new column and flag values that do not match the expected pattern.
function standardiseProductCodes() {
var ss = SpreadsheetApp.getActive();
var values = ss.getSheetByName("Products").getRange("A2:A").getValues();
var examples = ss.getSheetByName("Examples").getRange("A2:B").getValues();
var map = {};
examples.forEach(function (r) {
var from = String(r[0] || "").trim().toLowerCase();
var to = String(r[1] || "").trim();
if (from) map[from] = to;
});
var out = values.map(function (r) {
var code = String(r[0] || "").trim();
if (!code) return ["", ""];
var key = code.toLowerCase();
if (map[key]) return [map[key], ""];
var cleaned = code.toUpperCase().replace(/[\s_\-]+/g, "-").replace(/[^A-Z0-9\-]/g, "");
var status = /^[A-Z0-9\-]+$/.test(cleaned) ? "" : "Review";
return [cleaned, status];
});
ss.getSheetByName("Products").getRange("B2:C").clearContent();
if (out.length) ss.getSheetByName("Products").getRange(2, 2, out.length, 2).setValues(out);
}Adapt the pattern to your catalogue rules. For example, you may need fixed-length segments, category prefixes or checks against a master list.
Using Flookup custom functions
When codes need cleaning and matching, Flookup custom functions can complement or replace parts of a script. NORMALIZE can help clean text, while FUZZYSIM and FLOOKUP can compare or match similar values. The Sheets AI Assistant can suggest formulas for your specific structure.