Automatically generate a unique sequential ID in Google Sheets

user_12156228 0
Viewed 23650

In Google Sheets, I have a spreadsheet called Events/Incidents which staff from various branches populate. I want Column B to automatically generate a unique ID based on the year in column A and the previously populated event. Given that there could be several events on a particular day, rows in column A could have duplicate dates.

The following is an example of what I am looking for in column B:

ID

There can be no duplicates. Would really appreciate some help with either code or formula.

google-apps-script google-sheets
2 Answers

There are my thoughts https://github.com/contributorpw/google-apps-script-snippets/blob/master/snippets/spreadsheet_autoid/autoid.js

The main function gets a sheet and makes the magic

/**
 *
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
 */
function autoid_(sheet) {
  var data = sheet.getDataRange().getValues();
  if (data.length < 2) return;
  var indexId = data[0].indexOf('ID');
  var indexDate = data[0].indexOf('DATE');
  if (indexId < 0 || indexDate < 0) return;
  var id = data.reduce(
    function(p, row) {
      var year =
        row[indexDate] && row[indexDate].getTime
          ? row[indexDate].getFullYear() % 100
          : '-';
      if (!Object.prototype.hasOwnProperty.call(p.indexByGroup, year)) {
        p.indexByGroup[year] = [];
      }
      var match = ('' + row[indexId]).match(/(\d+)-(\d+)/);
      var idVal = row[indexId];
      if (match && match.length > 1) {
        idVal = match[2];
        p.indexByGroup[year].push(+idVal);
      }
      p.ids.push(idVal);
      p.years.push(year);
      return p;
    },
    { indexByGroup: {}, ids: [], years: [] }
  );

  // Logger.log(JSON.stringify(id, null, '  '));

  var newId = data
    .map(function(row, i) {
      if (row[indexId] !== '') return [row[indexId]];
      if (isNumeric(id.years[i])) {
        var lastId = Math.max.apply(
          null,
          id.indexByGroup[id.years[i]].filter(function(e) {
            return isNumeric(e);
          })
        );
        lastId = lastId === -Infinity ? 1 : lastId + 1;
        id.indexByGroup[id.years[i]].push(lastId);
        return [
          Utilities.formatString(
            '%s-%s',
            id.years[i],
            ('000000000' + lastId).slice(-3)
          )
        ];
      }
      return [''];
    })
    .slice(1);
  sheet.getRange(2, indexId + 1, newId.length).setValues(newId);
}

enter image description here

I think it can be simplified in the feature.

user_13930230

There is an easier way to generate unique values that works for me, pick a #, then do +1. Ctrl C, then Ctrl shift V to paste back and remove the formula. Now you are left with thousands of unique IDs.

This is a manual solution but you can do an entire database in a matter of seconds every once in a while.

user_144960990
Related