Google Script: How to copy and paste range to a new sheet on a row that is not empty

Question

I'm trying to copy and paste a range from Sheet1 B4:E to a new target range, Sheet 2 B4:E to a non empty row as I also have a formula in column F of Sheet2.
The script I have currently will only paste to the next available empty row, but as there is a formula in column F, it will paste the source range at the very bottom of Sheet2. (As the script treats the formula in F as a non empty row)

Is there a way to copy a range and paste it to a specific range on another sheet whilst not overwriting any data if is re-run again?
I have written 2 scripts that would do the job if I knew how to "merge" them together.

Script 1: Copies and pastes a specific range to the next empty row of a target sheet, and does not overwrite when the script is re run.

Script 1:

function transferArchive() { 
  
//TAB 1
//Target Range - Tab 1, Range 1
 var sss = SpreadsheetApp.getActiveSpreadsheet();
 var ss = sss.getSheetByName('Front Sheet'); 
  var range = ss.getRange('B4:E');
 var data = range.getValues();

//Destination Range - Tab 1, Range 1
 var tss = SpreadsheetApp.getActiveSpreadsheet();
 var ts = tss.getSheetByName('Printed POs');
 ts.getRange(ts.getLastRow()+1, 2, data.length, data[0].length).setValues(data);
  
  
}

Script 2: Will copy and paste to a specific range on another sheet but overwrites every time I re run the script.

Script 2: 
function getdata() {
//Copies source range to specific target range, but overwrites, rather than copy down into a new row.  
  //
// Target range - Copy
      var sourcess = SpreadsheetApp.openById("1GDMpcONdRR56QbZkA17EcvetWggltAMhzeddhNEEUG0"); 
  var sourcesheet = sourcess.getSheetByName('Front Sheet');  
  var sourcerange = sourcesheet.getRange('B4:E');  
  var sourcevalues = sourcerange.getValues();
  
  // Destination range - Paste
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var destsheet = ss.getSheetByName('Printed POs');  
  var destrange = destsheet.getRange('B6:E'); 
  destrange.setValues(sourcevalues);  
  
}

JPV JPV · Accepted Answer · 2019-09-10T11:51:42

Instead of using .getLastRow() try calling a function that computes the last cell in a column and see if that works. Example:

function transferArchive() {

//TAB 1
//Target Range - Tab 1, Range 1
var sss = SpreadsheetApp.getActiveSpreadsheet();
var ss = sss.getSheetByName('Front Sheet');
var range = ss.getRange('B4:E');
var data = range.getValues();

//Destination Range - Tab 1, Range 1
var tss = SpreadsheetApp.getActiveSpreadsheet();
var ts = tss.getSheetByName('Printed POs');
ts.getRange(lastRow(ts, 1), 2, data.length, data[0].length).setValues(data);
}

function lastRow(sheet, colNum) {
var v = sheet.getRange(1, colNum, sheet.getLastRow()).getValues(),
    l = v.length,
    r;
while (l > 0) {
    if (v[l] && v[l][0].toString().length > 0) {
        r = (l + 2);
        break;
    } else {
        l--;
    }
}
return r ? r : 1;

}

Google Script: How to copy and paste range to a new sheet on a row that is not empty

2 Answers