Copy and Paste to Last Row of Data

[Mark Atty]

Hey Everyone, So Macros are not my forte but im trying!! 

Im stuck on a copy and paste issue and I just cant work it out... 

I currently have - 

function PP() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange('D2').activate();

spreadsheet.getCurrentCell().setFormula('=today()');

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

spreadsheet.getRange('A2:D2').activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('All'), true);

spreadsheet.getRange('A1').activate();

spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();

spreadsheet.getCurrentCell().offset(1, 0).activate();

spreadsheet.getRange('Project!A2:D').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

spreadsheet.getRange('E2').activate();

spreadsheet.getCurrentCell().setFormula('=IF(and(or(B2="Qualified",B2="Soft Term",B2="Hold",B2="Disqualified",B2="Prequal",B2="Rescreened"),DAYS(Today(),D2)>=30),"No PP",if(AND(or(B2="Added CU"),DAYS(Today(),D2)>=60),"No PP",if(and(OR(B2="No Show"),DAYS(Today(),D2)>=120),"No PP",if(B2="","","PP"))))\n\n');

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getRange('E2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

var sheet = spreadsheet.getActiveSheet();

sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();

spreadsheet.getActiveRangeList().setHorizontalAlignment('center');

spreadsheet.getRange('A1').activate();

};

Basically, I want the Formula to copy down to the last entry in column E but I just cant get it to do it. So any help would be amazing!! 

Thanks 

Mark 

[Kim Nilsson]

I do that in my SPOGOU script.

Check line 232. (Line 6 below).

I use autofill after inserting the formula in the first cell. Not that a flush is necessary, as autofill is slow.

// Now it's time to create the simple passwords Based off firstNames in C and four random numbers
// Included a length check of names in C; if too short (passwords must be 8+ characters) repeat firstName
// Put the temp passwords in column E
sheet.getRange('E2').activate();
sheet.getCurrentCell().setFormula('=IFS(LEN(C2) > 3;C2 & RANDBETWEEN(1111;9999);LEN(C2) > 2;C2 & RANDBETWEEN(11111;99999);LEN(C2) > 1;C2 & C2 & RANDBETWEEN(1111;9999))');
sheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

// I NEED A DELAY HERE! The previous auto-fill command is slow
// A flush makes sure previous code is done. https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush
SpreadsheetApp.flush();

[Mark Atty]

Amazing! Thank you! 

The worst part is I already had that in the one above I just needed to be pointed in the right direction! 

Final code - 

function PP() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange('D2').activate();

spreadsheet.getCurrentCell().setFormula('=today()');

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

spreadsheet.getRange('A2:D2').activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('All'), true);

spreadsheet.getRange('A1').activate();

spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();

spreadsheet.getCurrentCell().offset(1, 0).activate();

spreadsheet.getRange('Project!A2:D').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

spreadsheet.getRange('E1').activate();

spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();

spreadsheet.getCurrentCell().offset(1, 0).activate();

spreadsheet.getCurrentCell().setFormula('=IF(and(or(B2="Qualified",B2="Soft Term",B2="Hold",B2="Disqualified",B2="Prequal",B2="Rescreened"),DAYS(Today(),D2)>=30),"No PP",if(AND(or(B2="Added CU"),DAYS(Today(),D2)>=60),"No PP",if(and(OR(B2="No Show"),DAYS(Today(),D2)>=120),"No PP",if(B2="","","PP"))))\n\n');

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

var sheet = spreadsheet.getActiveSheet();

sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();

spreadsheet.getActiveRangeList().setHorizontalAlignment('center');

spreadsheet.getRange('A1').activate();

};