I got this one that looks hairy to me, but I'm confident you guys can crack it while having fun.
The problem:
AA
, AB
, etcAC
XX001
, XX002
, etc.This is what I've come up with so far:
function generateID() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const clientSheet = ss.getSheetByName('Clients');
const dataRng = clientSheet.getRange(8, 1, clientSheet.getLastRow(), clientSheet.getLastColumn());
const values = dataRng.getValues();
const companies = values.map(e => e[0]);//Gets the company for counting
for (let a = 0; a < values.length; a++) {
let company = values[a][0];
//Counts the number of occurrences of that company in the range
var companyOccurrences = companies.reduce(function (a, b) {
return a + (b == company ? 1 : 0);
}, 0);
if (companyOccurrences > 1) {
let clientIdPrefix = values[a][2].substring(0, 2);//Gets the first 2 letters of the existing company's ID
} else {
//Generate ID prefix, incrementing on the existing ID Prefixes ('AA', 'AB', 'AC'...);
let clientIdPrefix = incrementChar(values[a][2].substring(0,1));
Logger.log('Incremented Prefixes: ' + clientIdPrefix)
}
}
}
//Increment passed letter
var alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
function incrementChar(c) {
var index = alphabet.indexOf(c)
if (index == -1) return -1 // or whatever error value you want
return alphabet[index + 1 % alphabet.length]
}
...and this is borrowing from tckmn's answer, which deals with one letter only.
This is the link to the file, should anyone want to give it a shot.
Thank you!
In your situation, how about the following modification?
// Please run this function.
function generateID() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Clients');
const dataRng = sheet.getRange(8, 1, sheet.getLastRow() - 7, 1);
const values = dataRng.getValues();
let temp = "";
let init = "";
let count = 0;
const res = values.map(([a], i) => {
count++;
if (temp != a) {
count = 1;
temp = a;
init = i == 0 ? "AA" : wrapper(init);
}
return [`${init}${count.toString().padStart(3, "0")}`];
});
console.log(res)
sheet.getRange(8, 4, res.length, 1).setValues(res);
}
//Increment
var alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
function incrementChar(c) {
var index = alphabet.indexOf(c)
if (index == -1) return -1 // or whatever error value you want
return alphabet[index + 1 % alphabet.length]
}
// I added this function.
function wrapper(str) {
const [a, b] = [...str];
const r1 = incrementChar(a);
const r2 = incrementChar(b);
return (r2 ? [a, r2] : (r1 ? [r1, "A"] : ["over"])).join("");
}
incrementChar
.console.log(res)
shows [["AA001"],["AA002"],["AA003"],["AA004"],["AA005"],["AB001"],["AB002"],["AB003"],["AC001"]]
. And this value is put to the column "D".