CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1883984
user1883984

Reputation: 87

Generating a static unique ID that isn't linked to the row number

I'm pretty terrible at coding, but have been trying to piece together a script to generate a static unique ID for placement within a google sheet.

What I would like to to happen is when any data is entered in column D, the equivalent row in column A generates a unique ID that is incremental from the last ID. However the unique ID must be static in that is not linked to the row number in any way. The unique ID needs also needs to increment in the order that data is entered in column D, which won't necessarily be entered in sequence with row numbers (i.e. ID2 may be in row 16, with ID3 in row 6).

I've located and adapted two scripts which I feel are nearly achieving what needs to be done, but have reached a limit of my understanding to finalise. Can anyone help explain how I would adapt the OnEdit function to include the setValue command from the OnForm function please?

function OnForm(){
var sh = SpreadsheetApp.getActiveSheet()
var startcell = sh.getRange('A1').getValue();
if(! startcell){sh.getRange('A1').setValue(1);return};
var colValues = sh.getRange('A1:A').getValues();
var max=0;// define the max variable to a minimal value
  for(var r in colValues){ // iterate the array
    var vv=colValues[r][0].toString().replace(/[^0-9]/g,'');
    if(Number(vv)>max){max=vv};
    }
    max++ ; // increment to be 1 above max value
sh.getRange(sh.getLastRow()+1, 1).setValue(Utilities.formatString('IG%05d',max));
}


function onEdit(e) {
var ss = e.source.getActiveSheet();
var watchedCols = [4]
if (watchedCols.indexOf(e.range.columnStart) === -1) return;
ss.getRange(e.range.rowStart, 2)
    .setValue('unique ID here')
}

Upvotes: 2

Views: 4930

Answers (1)

VOL
VOL

Reputation: 309

If I understood your problem correctly, you should loop through the values in the ID column and search for the biggest value. After looping through the column and having the biggest value stored in a variable, just increment that value by one and set it as the new ID.

So run this loop before you set the value of the cell:

var drang = ss.getDataRange();
var ids = ss.getRange(1, 2, drang.getLastRow(), 1).getValues();
var biggest = 0;

for(var i = 0; i < ids.length; i++){
  var id = ids[i][0];
  if(id > biggest){
    biggest = id;
  }
}

And then when you set the value:

ss.getRange(e.range.rowStart, 2)
.setValue(biggest+1)

Upvotes: 3

Related Questions