Copying data doesn't work unless destination row is empty

Question

I've got a script that moves a row of data (well only 1 cell actually) from one sheet to another, upon a certain condition being met (in this case it's onEdit when Col17 = "Yes"). I'm selecting the data to move using:

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
s.getRange(row, 1, 1, 1).copyTo(target);

Where 's' is Active Sheet and 'targetSheet' is the Destination Sheet.

This works perfectly when my destination sheet is blank.

However, I want to fill the other columns of my destination sheet with data, and have this script operate only in Column A. As soon as I add data to any other column, my script inserts the copied value below any existing data regardless of whether or not there is data in Column A.

E.g. I have 1,2,3 in Cells A1, A2 and A3 then A,B,C,D,E in Cells B1,B2,B3,B4 and B5. I run my script. New data is inserted into A6 (the first totally empty row). I want it to go to A4 (the first empty row in Col A).

Answer 1

Issue could be because you have some data in the actual lastRow of the spreadsheet. And there is no lastRow+1 row in the spreadsheet. When ever you insert data to lastRow of a spreadsheet its always better to insert a row after the lastRow and then add the data.

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
targetSheet.insertRowAfter(targetSheet.getLastRow());
s.getRange(row, 1, 1, 1).copyTo(target);

Answer 2

I got fed up with the getLastRow function - whenever I provided it a range getLastRow would also just return me the number of rows in the range, rather than the number that contained data.

In the end I wrote a simple loop to count the cells containing data and used that instead. Probably not the best solution, but effective.

Answer 3

Well considering your data looks like

A1 B1
A2 B2
A3 B3
   B4
   B5

the expected response of .getLastRow() will be 5, because the last row to have data in it is row 5 in column B. Consider that you are working with a sheet. There is no way for the function to now that you want it to see where the last entry is on column A specifically. Instead you can just use array = getRange('A:A').getValues(), add the value to the first empty array index and use getRange('A:A').setValues(array) to update the column