google-apps-scriptfiltered

Copying only filtered data from a single column


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!


Solution

  • 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.

    enter image description here

    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();
    }