Google Script - How to Copy Values from 1 sheet to another sheet into last row of specific columns

[JohnGow383]

Hi.
I have two Google sheets in my spreadsheet. One named 'John' and the other 'Catch Log'. I want to run a script to copy and paste values from fixed ranges in Sheet1 (John) to the last available row in columns B, D & F in Sheet2 (Catch Log). Column A in Catch Log is populated with dates for many thousand rows and therefore when I attempt to use getLastRow it is returning the last row below the last data in Column A. I'm not sure how to use getLastRow for specific columns (as you would in VBA). To summerize criteria:
1) - Copy and paste Values of range 'D7' in Sheet 'John' to last row (or first available blank cell) in Column B.
2) - Copy and paste Values of range 'D25' in Sheet 'John' to last row (or first available blank cell) in Column D.
3) - Copy and paste Values of range 'D5' in Sheet 'John' to last row (or first available blank cell) in Column F.

I amnot familiar with Google Script and therefore any help with this will be much appreciated. Thanks

 

[JohnGow383]

Ok I kinda got this to work using getNextDataCell. It's not particularly pretty but it is working when executing from the Google Script editor. However, when I apply a shortcut to it it's not working. Any ideas?
Here is the code

function CopyDailyStats(range) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet1 = ss.getSheetByName("Catch Log");
  const sheet2 = ss.getSheetByName("John");

  const lastRow = sheet1.getRange(10, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  const lastRow2 = sheet1.getRange(10, 4).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  const lastRow3 = sheet1.getRange(10, 6).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;

  sheet1.getRange(lastRow,2).activate();
  sheet2.getRange('John!D7').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet1.getRange(lastRow2,4).activate();
  sheet2.getRange('John!D25').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet1.getRange(lastRow3,6).activate();
  sheet2.getRange('John!D5').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

 

[JohnGow383]

I fixed it. It just required importing before I could add a shortcut. I had renamed another macro and you can't do that