Yet another how do I copy a row from one sheet to another ?

Question

For some reason this code doesn't do the copy. What am I doing wrong please?

    var targetSheetName="My Target"
    var targetSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);      
    var sourceRange = SpreadsheetApp.getActiveSheet().getDataRange();    
    sourceRange.copyTo(targetSheet.getRange(targetSheet.getMaxRows()+1, 1));

I've also tried var sourceRange = SpreadsheetApp.getActiveSheet().getDataRange().getRow(); to assign the data, as it's unclear to me what range of data getDataRange() is getting, so I thought maybe it's getting the entire active sheet, and therefore possibly failing when it tries to copy one row. Bottom line is I tried a variety of things and nothing seems to work.

I've looked at example after example after example and all of them are so different from each other I keep running into roadblocks trying to convert what they have into what I need. The above is what I finally settled on and tweaked for my use, but I'm still stuck.

Thanks in advance.

getRange(targetSheet.getMaxRows()+1,1) will fail because it is trying to get a row that doesn't and can't exist. This is easily corrected, but first: what exactly are you wanting to copy over? (all the rows? One row? If one, which one - the last one? Are you happy to just copy values over, or do you need to copy formulae and/or formatting as well?) — AdamL
1 row to be copied - and then deleted from the source sheet. Probably more of an moveto/append I suppose. I want to copy the active row, the code is inside an onEdit(). Would be nice to copy the formatting too, but I suppose I could live without it. — Prisoner 13
By the way... I don't understand why you say "getRange(targetSheet.getMaxRows()+1,1) will fail because it is trying to get a row that doesn't and can't exist." there is always a "MaxRows" even if the sheet is empty/blank. MaxRows will then be zero. Add 1 to it and voila, we have getRange(1,1); Docs say: method getRange(row, column, optNumRows, optNumColumns) Gets a range of sheet cells. User will see an error message if the given method parameters are invalid. All parameters in this method need to be greater than 0. Well, all parameters are greater than 0 so, I'm rather befuddled. — Prisoner 13
Yes, there is always a value for getMaxRows (which can never be zero by the way, a sheet must have at least one row). That value is essentially the total number of rows in the sheet. So I assumed if you tried to get a range that was on a row after the last row in the sheet, it would fail - but I was wrong, it doesn't, it actually adds 50 more rows to accommodate the row that didn't previously exist. (which I don't think would be ideal) — AdamL

AdamL AdamL · Accepted Answer · 2012-11-16T19:57:53

1 row to be copied - and then deleted from the source sheet. Probably more of an moveto/append I suppose. I want to copy the active row

var ss = SpreadsheetApp.getActiveSpreadsheet();
var targetSheetName = "My Target";
var targetSheet = ss.getSheetByName(targetSheetName);
var sourceSheet = ss.getActiveSheet();      
var sourceRow = sourceSheet.getActiveRange().getRow();
var targetRow = targetSheet.getLastRow() + 1;
if (targetRow > targetSheet.getMaxRows()) targetSheet.insertRowAfter(targetRow - 1);
sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn()).copyTo(targetSheet.getRange(targetRow, 1));   
sourceSheet.deleteRow(sourceRow);

Yet another how do I copy a row from one sheet to another ?

1 Answers