Automatic ID based on dropdown reference in Google Sheet-CodePudding

In my Google Sheet I have a dropdown in col D with four options:

YELLOW
BROWN
RED
BLUE

Now, I would to create a column to generate an auto ID which increases by 1 always based on the dropdown by adding the letter corresponding to the ID.

And so for YELLOW or BROWN the letter X, for RED or BLUE the letter Y.

How could I proceed with Apps Script?

CodePudding user response：

Paste this code in the script editor and save it

function onEdit(e) {
  const sh = e.source.getActiveSheet();
  const colors = ["YELLOW", "BROWN", "BLUE", "RED"];
  const ind = colors.indexOf(e.value);
  if (sh.getName()== 'Foglio1' && ind > -1 && e.range.columnStart == 4 && e.range.rowStart > 1 && !e.oldValue) {
    const idLetter = ind < 2 ? "X" : "Y";
    const idNumber = getId(sh, idLetter);
    const target = e.range.offset(0, -3);
    if(!target.getValue()) target.setValue(idNumber   idLetter);
  }
}

function getId(sh, idLetter) {
  const last = sh.getRange("A2:A").getValues().filter( r => r[0].includes(idLetter)).pop();
  return last ? Number(last[0].replace(/[^0-9]/g, ''))   1 : 1;
}

After saving, go back to the spreadsheet (tab 'Foglio1') and see if the ID appears after selecting a value in column D.