How to Dynamic Paste values after Last Row [Google Script]

Question

I am building a script to copy a row in another spreadsheet. The idea is to have one sheet with the inputs that are going to be stored in a second spreadsheet. However, I am facing some real struggle in building the dynamic paste range. This is the point I was able to reach with my present knowledge:

       function Export() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var database = SpreadsheetApp.openById("xxx");
var source = ss.getSheetByName('yyy');
var dataToCopy = source.getRange('bb').getValues();
var copyToSheet = database.getSheetByName("zzz");
var copyData = copyToSheet.getRange('bb').setValues(dataToCopy)
var Clean = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yyy').getRange('bb');
            Clean.clear({contentsOnly:true});
}

This script copy a range into a fixed range in a second spreadsheet, and it clears the values present in the source. My question is: How can I create a range that makes the script paste the data in the first blank row in the second spreadsheet?

I tried some combination of appendRow, getLastRow, insertRowAfter, but I was not able to get it done.

Thank you for your time!

Answer 1

This is what I was able to achieve with the help of a friend:

function Export2() {
     var ss = SpreadsheetApp.getActiveSpreadsheet()
      var database = SpreadsheetApp.openById("1UfKqXCMNIbjh3ge7s26SNkXyGez-bY3fvl6_3-RQKos");
     var source = ss.getSheetByName('Sheet26');
       var dataToCopy = source.getRange('A1:E1');
      var copyToSheet = database.getSheetByName("TOT");
      var lastRow = copyToSheet.getLastRow();
      for (var i = 1; i<6 ;i++){
     var Paste = copyToSheet.getRange(lastRow + 1,i).setValue(dataToCopy.getCell(1, i).getValue());
    }

    var Clean = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet26').getRange('A1:E1');
                Clean.clear({contentsOnly:true});
    }

Answer 2

Below is a script that will do the thing you want to do dynamically without the use of named ranges. It assumes that all the data on the source sheet should be copied over to the destination sheet. Let me know if you need any additional explanation beyond what is provided in the comments.

function Export() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var database = SpreadsheetApp.openById("xxx");
  var source = ss.getSheetByName('yyy');
  var dataToCopyRng = source.getDataRange(); //Gets range object of all data on source sheet
  var dataToCopy = dataToCopyRng.getValues(); //Gets the values of the source range in a 2 dimensional array
  var copyToSheet = database.getSheetByName("zzz");
  var copyData = copyToSheet.getRange(copyToSheet.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy) 
  //Explination of the above command is as follows getRange(row, column, numRows, numColumns)
  //--row is copyToSheet.getLastRow()+1 -- finds last row with content and adds one
  //--Column is 1 for Column A
  //--numRows is the length of the array (how many rows are in the array of values)
  //--numcolumns is the length of the first element of the 2 dimensional array (arrays start at zero). The length of the first element is how many columns are in the array
  //--combine the above with .getRange and you get the range object that is an exact match to the array of source data rows and columns
  //--then you simply set the values of this range with the source data
  dataToCopyRng.clear({contentsOnly:true});
}

You can learn more about 2 dimensional arrays here. Cheers!