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

JohnGow383

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.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

T
  • Question
Golf Simulator League - Calculating Golf Handicaps for Google Sheets
2
Replies
12
Views
2K
Fluff
Fluff
D
  • Solved
How To Reference Last Value in Column (To Show In Another Cell?)
Replies
4
Views
529
Peter_SSs
Peter_SSs
B
  • Question
Formula for calculating fastest time in row for Google Sheets
Replies
3
Views
1K
Fluff
Fluff
TAPS_MikeDion
  • Solved
Selecting columns to print in Google Sheets via VBA?
Replies
2
Views
403
TAPS_MikeDion
TAPS_MikeDion
S
  • Question
Date from a row that corresponds to the first cell containing a value in another row (Google Sheets)
Replies
2
Views
337
RoryA
RoryA

Forum statistics

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

Share this page

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