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 using uppercase letters, removing spaces, adding a prefix or replacing separators. This makes the transformation easier to test and maintain.
Standardise with Apps Script
The script below applies exact mappings, then normalises case and separators. Write the output to a new column and flag values that do not match the expected pattern.
function standardiseProductCodes() {
const ss = SpreadsheetApp.getActive();
const values = ss.getSheetByName("Products").getRange("A2:A").getValues();
const examples = ss.getSheetByName("Examples").getRange("A2:B").getValues();
const map = {};
examples.forEach(function (r) {
const from = String(r[0] || "").trim().toLowerCase();
const to = String(r[1] || "").trim();
if (from) map[from] = to;
});
const out = values.map(function (r) {
const code = String(r[0] || "").trim();
if (!code) return ["", ""];
const key = code.toLowerCase();
if (map[key]) return [map[key], ""];
const cleaned = code.toUpperCase().replace(/[\s_\-]+/g, "-").replace(/[^A-Z0-9\-]/g, "");
const 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.