I am trying to copy only one column from a filtered raw file onto a working sheet. Both are sheets within the same spreadsheet.
I found this entry https://stackoverflow.com/a/52227279/17192498 which I updated for my specific sheet names. However, I do not want the entire raw file, only a specific column. How can I specify the range on the rawFile side to only be one column?
function filteredCopyTest (){
var sheet = SpreadsheetApp.getActive()
var rawFile = (sheet.getSheetByName('Raw'))
var masterRoster = (sheet.getSheetByName('Master Roster'))
var rawFileBranchFiltered = rawFile.getFilter().getRange()
rawFileBranchFiltered.copyTo(
masterRoster.getRange('AP2'),
SpreadsheetApp.CopyPasteType.PASTE_VALUES,
false);
}
Thank you in advance for the help!
I made a helper worksheet, and name it filter_data_raw
. You can hide this worksheet, and it will not affect anything in the code.
The sample code:
function filteredCopy() {
// rename the sheets with the actual name of your worksheets.
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = sheet.getSheetByName('raw');
const temp_targetSheet = sheet.getSheetByName('filter_data_raw');
const targetSheet = sheet.getSheetByName('target');
// gets the filter data
let sourceRange = sourceSheet.getFilter().getRange();
// copy and paste the data in the helper worksheet
sourceRange.copyTo(
temp_targetSheet.getRange('A1'),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
false);
// select the column that you want to copy, in the case of my dummy data
// I used the column E
let range_1 = temp_targetSheet.getRange("E:E");
// copy the column to A1, you can use AP2 base on your code
range_1.copyTo(
targetSheet.getRange('A1'),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
false);
// make sure that all the values in the sheet are up to date
SpreadsheetApp.flush()
// clear the information on the temp sheet
// to make sure everything ready for a new run
temp_targetSheet.getDataRange().clearFormat();
temp_targetSheet.getDataRange().clearContent();
}