I'm new to google sheets and macros and need a little help please. I'm trying to copy a range of data (row) from A4:H4 on the 'Front Sheet' tab to the next empty row on the 'Data' tab.

I have recorded the macro and have tried modifying it to add in some code to put it in the last row but I'm not really sure how to do this. I'd be really grateful for any help please.

This is my recorded (basic) code:

function Copydata() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A4:H4').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data'), true);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('\'Front Sheet\'!A4:H4').copyTo(spreadsheet.getActiveRange(),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); 
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Front Sheet'), true);
spreadsheet.getRange('A7').activate();
};

I'm new to google sheets and macros and need a little help please. I'm trying to copy a range of data (row) from A4:H4 on the 'Front Sheet' tab to the next empty row on the 'Data' tab.

I have recorded the macro and have tried modifying it to add in some code to put it in the last row but I'm not really sure how to do this. I'd be really grateful for any help please.

This is my recorded (basic) code:

function Copydata() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A4:H4').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data'), true);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('\'Front Sheet\'!A4:H4').copyTo(spreadsheet.getActiveRange(),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); 
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Front Sheet'), true);
spreadsheet.getRange('A7').activate();
};

• google-sheets
• google-apps-script
• copy
• macro-recorder

1 Answer 1

Getting the Last Row of a Targeted Range

Basically, what you want to utilize here is a Method called getLastRow().

I understand that you are new with Google Apps Script, I do hope that you enjoy using the platform. Instead of correcting your code all the way, I just intended to create a new one that caters to your needs. You might want to review, active(), getRange(), and copyTo(). This function is close to what you want to achieve I do think that it will introduce unneccesary runtime which is very important when working with Google Apps Script.

I created a simple solution that already do what you are looking forward to do and you can use as a basis for your future projects.

Sample Code:

function copySheettoAnotherSheet() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Front Tab"); // Change the Sheet Name Depending on your situation
  var targetSheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); // Change the Sheet Name Depending on your situation
  var copyRangeValues = sourceSheet.getRange().getValues(); // This is the part of getting the Values from your desired range. 
  var pasteRange = targetSheet.getRange(targetSheet.getLastRow()+1,1,copyRangeValues.length,copyRangeValues[0].length); // This is getting a range, that will make sure that you are on the right size of the data you are trying to paste.
  pasteRange.setValues(copyRangeValues) // the pasting of Data
}

References:

getLastRow()