I have a need to create a script that copies Rows 4:8 and then whatever the last row of the sheet is, paste those rows in.

NOTE - To keep the file small, the sheet has the majority of the rows deleted on purpose. The last row may not always be the same number, so the script must know which the last row is even if there's no data in it.

EXAMPLE

If the last row is 20, upon executing the script (which I'll assign to a button), 5 additional rows will be added (which would have come from rows 4:8), extending the sheet to a total of 25 rows.

There's specific formatting in those rows so it's not as simple as just adding 5 additional rows, they must be copies of rows 4:8.

Thanks.

CodePudding user response：

function copyrow_4_8(){
        const ss = SpreadsheetApp.getActive().getActiveSheet();
        //replace the columns number with your acutal needed columns 
        const range = ss.getRange(4,1,5,4);
        const range_value = range.getValues();
        const dest_range = ss.getRange(ss.getLastRow() 1,1,range_value.length,range_value[0].length);
        //get grid id for the dest range
        const dest_range_Gid = dest_range.getGridId();
        //copy format to the dest range
        range.copyFormatToRange(dest_range_Gid,1,4,ss.getLastRow() 1,ss.getLastRow() range_value.length 1);
        //set the values to dest range.
        dest_range.setValues(range_value);
    }

1. You need to get the original range and values
2. Then you copy format and the values to the destination range.

CodePudding user response：

Copy Lines to Bottom including format

function copyrows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = ss.getRange("4:8");//select line 4 through 8
  const drg = sh.getRange(sh.getLastRow()   1 , 1);//Select destination range. You only have to specify the upper left corner of the range
  rg.copyTo(drg);
}