Unique ID predated by predefined text-CodePudding

I have a spreadsheet that I need a unique id where the first character is a letter, the six next characters are the date and the 2 last a unique number between 0-99 where format need to be in 2 characters. I tried to increment by 1 the row count but the issue is that if someone delete a row the next rows to be inputted will have false and duplicated ID.

thanks in advance

function test(){


  var ss = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXXXXX').getSheetByName('Sheet1');
  
  var range = SpreadsheetApp.getActiveSheet().getLastRow() 1;
  var formatDate = Utilities.formatDate(new Date(), "GMT 8", "ddMMyy");
  var id = "T" formatDate range;


  ss.appendRow([id])
}

CodePudding user response：

You can make a variable-counter and save it to script properties. https://developers.google.com/apps-script/guides/properties

At every run you can get the counter, increase it and add to your next ID. And save the last value of the counter back to the properties.

Something like this:

function generate_ID() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var counter = scriptProperties.getProperty('counter') || 0;

  var formatDate = Utilities.formatDate(new Date(), "GMT 8", "ddMMyy");
  var zero = (counter < 10) ? '0' : '';
  var id = "T"   formatDate   zero   counter  ;
  SpreadsheetApp.getActiveSheet().appendRow([id])
  
  if (counter > 99) counter = 0;
  scriptProperties.setProperty('counter', counter);
}