Unique ID predated by predefined text

user_8706608 0
Viewed 44

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])
}
google-apps-script google-sheets javascript
1 Answers

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);
}
user_142654690
Related