Question

0

72

Views

How to increment set of 2 letters based on word occurrences in the range using GAS?

I got this one that looks hairy to me, but I'm confident you guys can crack it while having fun.

The problem:

Check of Company exists in the range
If not, get the latest ID prefix, which looks like AA, AB, etc
Generate a new prefix, which would be the following, according to item above: AC
If that company occurs more than once, then increment the ID number sufix 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 expected result:

This is the link to the file, should anyone want to give it a shot.

Thank you!

7 months ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-06-30T10:46:34.002Z

In your situation, how about the following modification?

Modified script:

// 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("");
}

In this modification, I added a wrapper function. This wrapper function uses your showing script of incrementChar.
When this script is run to your sample Spreadsheet, console.log(res) shows [["AA001"],["AA002"],["AA003"],["AA004"],["AA005"],["AB001"],["AB002"],["AB003"],["AC001"]]. And this value is put to the column "D".

Note:

This modified sample is for your provided Spreadsheet. So please be careful this.

Reference:

map()