I have a script that keeps the number of blank rows at desired level. New created rows should have the same formatting and copy formulas from last blank row.

The script below does that for the first time, but if you type in some new records and run it again it creates new rows but without formatting. How to fix it?

[![Here is the google sheet:][1]][1]

  function autoaddRows() {
  var addRows = 10;
  var sheet = SpreadsheetApp.getActive().getSheetByName('Plan dnia');
  var range = sheet.getRange("B2:B").getValues();
  var lastRowB = range.filter(String).length 6;
  var lastRow = sheet.getLastRow();
  var blanknrows = lastRow - lastRowB;
  var diff = addRows - blanknrows;
  if (diff > 0) {
    sheet.insertRowsAfter(lastRow, diff);
    var numberOfCol = sheet.getLastColumn() - 1;
    var range = sheet.getRange(lastRowB, 2, 1, numberOfCol);
    range.copyTo(range.offset(1, 0, diff   1, numberOfCol), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    range.clearNote();
  }
}


  [1]: https://i.stack.imgur.com/VvGjI.jpg

CodePudding user response：

In your situation, how about the following modification?

From:

range.copyTo(range.offset(1, 0, diff   1, numberOfCol), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
range.clearNote();

To:

var dst = range.offset(1, 0, addRows, numberOfCol);
range.copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
range.copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
range.clearNote();

• I thought that when the empty rows are more than 2, when diff 1 is used as the number of rows, the end of the row might be small. I thought that this might be the reason for your issue. So I modified diff 1 to addRows. And also, in order to copy the format, I added range.copyTo(range.offset(1, 0, addRows, numberOfCol), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false).

Reference:

• copyTo(destination, copyPasteType, transposed)