how to get a row number in a column if a cell in another column has some data using google apps script?

Question

i have a sheet that have 5 columns (date , id , name , number ,row number) and i want the fifth column(row number) to be auto filled with the row number when i insert any data in the second column(id) i tried the manual way from the sheet itself not apps script but sometimes it gets deleted by mistake so i want to do it with apps script and im not sure if there is a way or not so please help me with it. here is what i mean in example:

first row got headers (date, id , name , number , row number) lets say i insert in the fifth row some data (today , 4 , ahmed , 4584231 , X)

I WANT WHEN I INSERT THE ID(4), THE ROW NUMBER GETS VALUE (5) AUTOMATICALLY BECAUSE ITS IN THE FIFTH ROW, THEN AFTER THAT LETS SAY I REMOVED ROW NUMBER 3 I WANT ALL THE VALUES IN THE ROW NUMBER COLUMN TO BE CHANGED TO THE NEW ROW IT GOT IN, I HOPE THAT WAS CLEAR FOR YOU.

thank you very mush

Answer 1

This is possible via formula:

1. IF not blank

Insert this formula in the row number column:

=if(B2<>"",row(),"")

Result:

Just drag/copy the formula down to the column.

This checks if the cell in column B is blank. If not, insert the row number.

2. ArrayFormula - this is most likely what you need to be safe from accidental deletions

Or you can also use arrayFormula so you only need to insert the formula on the first row after the header and it will be applied to the whole column:

=arrayformula(if(B2:B <> "", row(B2:B), ""))

This can also help you prevent accident deletions. It will reinsert the value on the cell when deleted, just make sure not to delete the actual arrayformula on the cell E2.

Also check that the cells below are empty or it will throw an error.

Result:

USING APPS SCRIPT

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  var row = r.getRow();
  var column = 6;

  if (r.getColumn() == 2) {
    if (!e.value){
      ss.getRange(row,column).clearContent();
    }
    else {
      ss.getRange(row,column).setValue(row);
    };
  };
};

References:

• IF Function - Not blank
• ArrayFormula