Google-sheets – Unique ID Column

google sheetsgoogle-apps-script

I have a Google Spreadsheet that I want to have a unique number created in column A when I enter information in column B. Below is the formula I currently have that works, but I am wondering if there is a way to edit it so that instead of CDI it has the date(datestamp)?

=ArrayFormula(if(len(B:B),"CDI"&text(row(A:A),"000000"),iferror(1/0)))

Desired end result would be something like:

02232015001

Best Answer

You could replace "CDI"&text(row(A:A),"000000") with 

text(month(now()),"00")&day(now())&year(now())&text(row(A:A),"000")

but this would be the timestamp of now, not of when the cell was last updated.

To capture the update time, you need a script. The script below runs every time the spreadsheet is edited, checks whether there are entries in column B without an entry in column C, and adds a timestamp-based ID to column C in this case. 

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell;
  var range = sheet.getRange("B:C");
  var values = range.getValues(); 
  for (var i=0; i<values.length; i++) {
    if (values[i][0] && !values[i][1]) {
      cell = range.getCell(i+1, 2);
      cell.setValue(formatDate()+('00'+cell.getRow()).slice(-3));
    }
  } 
}

function formatDate() {
  var month, day, d = new Date();
  month = ('0'+(d.getUTCMonth()+1)).slice(-2);
  day = ('0'+d.getUTCDate()).slice(-2);
  return d.getUTCFullYear()+month+day;
}

The result looks like this:

+-----------+-------------+
|   Data    |  Timestamp  |
+-----------+-------------+
| something | 20150629002 |
|           |             |
| more      | 20150629004 |
| and more  | 20150629005 |
+-----------+-------------+

I put the year first, because otherwise the leading zero in the month would be omitted, decreasing readability. (The alternative, formatting ID as a string, is somehow unappealing).

Related Topic