Increment Id From Last Row Google Apps Script

I coded something for google apps script. It is to increment ID +1 based on the last row. Everything is working so far except for the numbering of the new ID, instead of appearing

Solution 1:

Explanation / Issue:

Your code really depends on the value of the cell in column B last row:

var riskid = mysheet.getRange(rlast,2).getValue();

There are two scenarios but I believe the second applies to your issue:

1. If the value in the cell is a number (e.g. 35233) then riskid will be an integer and therefore riskid.length will return null and as a result the if condition will evaluate to false. In this case, you can either use getDisplayValue or toString() instead to get the number as string and then you can apply .length to it:

   var riskid = mysheet.getRange(rlast,2).getValue();
   

2. If the value in the cell is a string (e.g. R112) then the if condition will evaluate to true. If you do that:

   var riskidnb = riskid.substring(1,riskid.length);
   

   riskidnb will be 112 but this is still a string and therefore if you do riskidnb++ you will get NAN like the issue you have right now. In order to fix that, convert riskidnb to integer:

   var riskidnb = parseInt(riskid.substring(1,riskid.length));
   

   then you can do riskidnb++ and finally convert it back to string:

   var s = "000" + riskidnb.toString();
   

---

Answer :

function new_item() {

  // Get current spreadsheetvarapp= SpreadsheetApp;
  varss= app.getActiveSpreadsheet();
  varmysheet= ss.getActiveSheet();

  // Set date to today in update field at the top of the sheetvarnow=newDate();
  mysheet.getRange(1,4).setValue(now);
  
  // Last non-empty rowvarrlast= mysheet.getLastRow();
  Logger.log("Last row = " + rlast);
  
  // Insert Row below
  mysheet.insertRows(rlast+1);
  varr= rlast+1;
  
  // Copy format from row abovevarsourcerange= mysheet.getRange(rlast + ":" + rlast);
  vartargetrange= mysheet.getRange(r + ":" + r);
  sourcerange.copyTo(targetrange, {formatOnly:true});
  
  // Col. 2 : Risk identityvarriskid= mysheet.getRange(rlast,2).getValue();
  if (riskid.length > 3){
    // Extract number ex. 3varriskidnb= parseInt(riskid.substring(1,riskid.length));
    // Increase risk number +1
    riskidnb++
    // Convert to string "0004"vars="000" + riskidnb.toString();
    // Write risk nb i.e. "R004"
    mysheet.getRange(r,2).setValue("R-"+ s.substring(s.length-4))
  }
}

Output: