CODEWITHSUNDEEP
apigoogle-apps-scriptgoogle-sheetsfilter
mromankim
mromankim

Reputation: 23

Apps script: Paste filtered range to the next empty row in specific column

I would like to create a code which copies a filtered column and pastes the whole column to the next available row in another sheet. The code below works however pastes the range in a loop until the end of the spreadsheet. How can I paste it just once? I could not find an answer myself. I would much appreciate your help!


function CopyFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName("MASTER"); 
var values = sheet.getDataRange().getValues();
Logger.log("VALUES "+values.length);

var criteria = SpreadsheetApp.newFilterCriteria()
.whenNumberGreaterThanOrEqualTo(1)
.build();

var range = sheet.getFilter()
.setColumnFilterCriteria(52, criteria)
.getRange();   
range.getValues();

var sheet2 = ss.getSheetByName("DL CSV OUTPUT");
sheet.getRange("AZ4:AZ").copyTo(sheet2.getRange(2,41,sheet2.getLastRow()+1), {contentsOnly:true});

sheet.getFilter().remove();
}

Upvotes: 1

Views: 80

Answers (1)

CodeBloodedChris
CodeBloodedChris

Reputation: 246

Do not use the copy method. Simply select the range you are interested in, then get a range (of same size - very important) in the target sheet and set all values at once.

EXAMPLE:

const valuesToCopy = sheet.getRange("AZ4:AZ").getValues();
sheet2.getRange(2,3,valuesToCopy.length).setValues(valuesToCopy);

And no need to worry about: {contentsOnly:true} Since the above method does not bring over any formatting.

Reference:

Upvotes: 2

Related Questions