Home > Back-end >  How to add rows below a cell based on the value of the cell
How to add rows below a cell based on the value of the cell

Time:12-08

I have a table where 100 rows have data. I want the macro to look at column E (say cell E2=2) and add 2 rows below E2, then go to the next non-blank cell in column E (say E5=3, because 2 rows were added below E2) and add 3 rows below E5. Then go to the next non-blank cell in column E (say E9, because 3 rows were added below E5) and add the value in E9 of empty rows below E9. So on and so for until there are no more non-empty cells in Column E.

CodePudding user response:

function nonblankInEAddRowsBelow() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  let n = 0;//added line counnter
  sh.getRange(2, 5, sh.getLastRow() - 1, 1).getValues().flat().forEach((e, i) => {
    if (e) {
      sh.insertRowsAfter(i   2   n, e);//2 is data start row
      n  = e;
    }
  })
}
  • Related