Google sheet Auto-incrementing ID/Primarykey

Question

In this google sheet function, a primary key is automatically added to the first column on change. The problem with this function is the fact that when a new row is added the row below also gets a primary key despite the other cells in the row is blank.

How can this function be modified, so that the primary key only will be added if i.e. the first cell to the right (cell B) is not empty?

function myFunction() {

  var AUTOINC_COLUMN = 0;
  var HEADER_ROW_COUNT = 1;

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet   = spreadsheet.getSheetByName("Sheet1");
  var rows        = worksheet.getDataRange().getNumRows();
  var vals        = worksheet.getSheetValues(1, 1, rows+1, 2);

  for (var row = HEADER_ROW_COUNT; row < vals.length; row++) {
    try {
      var id = vals[row][AUTOINC_COLUMN];
      Logger.log(id);Logger.log((""+id).length ===0);
      if ((""+id).length === 0) {
        // Here the columns & rows are 1-indexed
        worksheet.getRange(row+1, AUTOINC_COLUMN+1).setValue(row);
      }
    } catch(ex) {
      // Keep calm and carry on
    }
  }
}

Answer 1

I wouldn't use a script for this, just enter this in A2 and then copy all the way down. put a 0 (or 100, wherever you want it to start) in cell A1.

=if(B2="","",A1+1)

Answer 2

Just thought I would add my answer. I would not use a formula because that would recalculate if you ever sort the sheet (not good if you are referencing primary keys elsewhere). The other code-based solution assumes that records will only be added at the end of the sheet and the last row is the highest primary key (i.e. will end up with duplicate keys if you ever resort the sheet).

This code will work no matter where you insert a record, or how you sort the data. It does assume that you will never remove the row with the highest primary key (removing a row with less than the highest primary key won't be a problem).

function auto_increment_column() {
  const AUTO_INCREMENT_SHEET = "Sheet1";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(AUTO_INCREMENT_SHEET);

  var nextIncrementValue = Math.max.apply(Math, sheet.getRange("A:A").getValues().flat().filter(e => !isNaN(parseFloat(e))))

  var rows = sheet.getRange("A:B");

  for(var i = 1; i <= rows.getNumRows(); i++) {
    var cellA = rows.getCell(i, 1);
    var cellB = rows.getCell(i, 2);

    if(cellA.getValue() === "" && cellB.getValue() !== "") {
      cellA.setValue(nextIncrementValue++);
    }
  }
}