I'm using a code that create unique ID's. The problem i'm facing is when I retreive a list of 10 rows and paste it on the column B, the code generate ID only for the first row. So I have to copy/paste row by row in order to generate the ID is there a way to modifiy the code in a way that allow to generate ID for more than a row at once ?
/* ***Custom Unique ID***
*
* onEdit of the selected new cell, Custom Unique ID:
* 1. Sorts through the ID column
* 2. Finds the largest value
* 3. Adds 1 to this value and inserts this at the next unique ID.
*
* The ID column must have at least 1 Unique ID for the program to reference.
*
* Custom Unique ID can take ID's with:
* -Leading letters
* -Leading Zeroes
* -Trailing Letters
*
* -Trailing and leading letters must be of the same length.
* -Letters cannnot be between numbers. For example, ARG00123B025AG.
* -ID must be of same length. e.g. FE100er has a length of 7 characters. Subsequent
* IDs must be of the same length. Ensure you have sufficient leading zeroes e.g. KRG0002345
*
* Custom Unique ID Can take multiple sheet and cell locations. See "var locations".
* Unique ID's won't be deleted when other cell data is changed or edited.
*/
// Location format = [sheet, ID Column, ID Column Row Start, Edit Column]
var locations = [
["Liste",3,2,2],
];
function onEdit(e){
// Set a comment on the edited cell to indicate when it was changed.
//Entry data
var range = e.range;
var col = range.getColumn();
var row = range.getRow();
// Location Data
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
function getNewID(){
function IDrange(){
var dataRange = sheet.getDataRange();
var lastRow = dataRange.getLastRow();
return sheet.getRange(IDrowStart,IDcol,lastRow-IDrowStart).getValues();
};
//Get largest Value in range
function getLastID(range){
var sorted = range.sort();
var lastIDval = sorted[sorted.length-1][0];
return lastIDval;
};
//Stores leading letters and zeroes and trailing letters
function getLettersNzeroes(id){
//Get any letters or zeroes.
var re = new RegExp("^([a-zA-Z0])$");
var letterZero = [];
for(char = 0; char < id.length; char++){
if(re.test(id[char])){
letterZero.push([char,id[char]]);// [[position, letter or zero]]
};
};
// Categorize letters and zeroes into start and end blocks
var startLetterZero = "",
endLetter = "",
len = letterZero.length - 1;
for(j = 0; j < letterZero.length; j++){
if(letterZero[j][0] === j){
startLetterZero += letterZero[j][1];
}else if(letterZero[j][1] !== "0" && letterZero[len][0] - (len - j) == letterZero[j][0]){
endLetter += letterZero[j][1];
};
};
var startNend = {"start":startLetterZero,"end":endLetter};
return startNend;
};
//Gets last id number. Adds 1 an checks to set if its new length is greater than the lastNumber.
function getNewNumber(id){
var removeZero = false;
var lastNum = parseInt(id.replace(/\D/g,''),10);//Remove letters
var newNum = (lastNum+1).toString();
if(lastNum.toString().length !== newNum.length){
var removeZero = true;
};
var newNumSet = {"num":newNum, "removeZero": removeZero};
return newNumSet
};
var lastID = getLastID(IDrange());
var lettersNzeroes = getLettersNzeroes(lastID);
var newNumber = getNewNumber(lastID);
//If the number is 9,99,999,9999 etc we need to remove a zero if it exists.
if(newNumber.removeZero === true && lettersNzeroes.start.indexOf("0") !== -1.0){
lettersNzeroes.start = lettersNzeroes.start.slice(0,-1);
};
//Rejoin everything together
var newID = lettersNzeroes.start +
newNumber.num +
lettersNzeroes.end;
return newID;
};
for(i = 0; i < locations.length; i++){
var sheetID = locations[i][0],
IDcol = locations[i][1],
IDrowStart = locations[i][2],
EditCol = locations[i][3];
var offset = IDcol - EditCol;
var cell = sheet.getActiveCell();
if(sheetID === sheet.getName()){
if(EditCol === col){
//ID Already Exists the editing cell isn't blank.
if(cell.offset(0,offset).isBlank() && cell.isBlank() === false){
var newID = getNewID();
cell.offset(0,offset).setValue(newID);
};
};
};
};
};
For achieving what you are aiming for you will need a for loop where you set each row of the column you just paste separately. e.range will return the whole range of the cells changed, it can be one or several cells.
Therefore, just setting an individual cell in the last stage (without the for loop) it will not be enough as it either will only set the first cell of the range pasted or set all of them with the same values.
To solve this change this part of your script:
var offset = IDcol - EditCol;
var cell = sheet.getActiveCell();
if(sheetID === sheet.getName()){
if(EditCol === col){
//ID Already Exists the editing cell isn't blank.
if(cell.offset(0,offset).isBlank() && cell.isBlank() === false){
var newID = getNewID();
cell.offset(0,offset).setValue(newID);
};For the following (the code has self explanatory comments):
var offset = IDcol - EditCol;
var cell = e.range;
// This is done so that we know exactly how many rows we are trying to copy/paste for
// iterating over each of these rows in our for loop
var values = cell.getValues().flat();
if(sheetID === sheet.getName()){
if(EditCol === col){
//ID Already Exists the editing cell isn't blank.
if(cell.offset(0,offset).isBlank() && cell.isBlank() === false){
// for every row we are pasting
for(i=0;i<values.length;i++){
var newID = getNewID();
// set the value of the cell in that range of rows (when i=0 this will be the first
// cell in the range you are pasting, when i=1 it will be the next cell, etc
sheet.getRange(e.range.getRow() + i, e.range.getColumn()).offset(0,offset).setValue(newID);
}
};