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

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
141
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

Rich (BB 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);
};
 
Upvote 0
Solution
I fixed it. It just required importing before I could add a shortcut. I had renamed another macro and you can't do that
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top