0

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

Ahmed Zaqout
  • 91
  • 1
  • 11

1 Answers1

1

This is possible via formula:

  1. IF not blank

Insert this formula in the row number column:

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

Result:

enter image description here

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.

  1. 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:

enter image description here

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:

Logan
  • 1,691
  • 1
  • 4
  • 11
  • thank you @Dan F very mush, this is a very good answer but is there any way to do the arrayformula using apps script it will be better for me – Ahmed Zaqout Apr 22 '22 at 16:36
  • I would not advise using apps script for it as it takes much longer to run. I have added a solution on the posted answer using apps script, just change the column. It works the same logic as the formula however it does not update if you add/remove rows. See the run time using the apps script compared to the arrayformula. And that's just updating one cell, imagine deleting a row and updating all rows below it, that would take so much longer. – Logan Apr 23 '22 at 04:12