How do I copy both the values and background color of a filtered range in Google Sheets App Script?

Jorge O Published at Dev

Jorge O

The goal of this script is to basically filter out a specific range based on cell value and copy it onto another sheet, carrying over both the cell value and the cell background colors. I have a script that filters by cell value, and copies the filtered values onto another sheet, which is what I've included in this post, but I can't figure out how to change this to also carry over the cell color for this range.

A copy of the sheet I'm working with https://docs.google.com/spreadsheets/d/1rCXkqNLN2EI97Lk5OTOjxFHloehO7lnIXdioQMhEeks/edit?usp=sharing

const ss = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheet = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName("places");
const rng = sheet.getRange(2,1,sheet.getLastRow(),33).getValues();

function filterAlternatives(){

  let Criteria1 = "Chicago";
  let Criteria2 = "Alternatives";
  let Criteria3 = "Approved";
  let Criteria4 = "Field Check";

  let FData = rng.filter(function(e){
    return e[7] == Criteria1
    && e[19] == Criteria2
    && (e[15] == Criteria3 || e[15] == Criteria4)
  })

  let newSheet = ss.insertSheet(Criteria2);
  newSheet.getRange(2,1,FData.length,33)
    .setValues(FData)
    .setHorizontalAlignment("left")
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
  
  let headers = sheet.getRange("A1:AG1").getValues();
  newSheet.getRange("A1:AG1")
    .setValues(headers)
    .setHorizontalAlignment("left")
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
  
  newSheet.sort(29,false);
  newSheet.setRowHeightsForced(1,FData.length+1,23);
  newSheet.setTabColor("ff0000");
  newSheet.setColumnWidths(1,33,100);
  newSheet.setColumnWidth(1,60);
  newSheet.setColumnWidth(2,300);
  newSheet.setColumnWidth(3,50);
  newSheet.setColumnWidth(4,200);
  newSheet.setColumnWidth(5,50);
  newSheet.setColumnWidth(6,50);
  newSheet.setColumnWidth(13,175);
  newSheet.setColumnWidth(14,175);
  newSheet.setColumnWidth(22,50);
  newSheet.setFrozenRows(1);
  newSheet.setFrozenColumns(2);
}

TheWizEd

I haven't tested it but I believe it should work.

Change your filter to also collect the background colors. Then add the backgrounds to the newsheet.

let backGrounds =[];
let FData = rng.filter(function(e,i){
  if( ( e[7] == Criteria1 )
  &&  ( e[19] == Criteria2 )
  &&  ( ( e[15] == Criteria3 ) || ( e[15] == Criteria4 ) ) {
    backGrounds.push(sheet.getRange(2+i,1,1,33).getBackgrounds()[0]);
    return true;
  }
  return false;
});

newSheet.getRange(2,1,FData.length,33)
  .setValues(FData)
  .setBackgrounds(backGrounds)
  .setHorizontalAlignment("left")
  .setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);

Collected from the Internet

Please contact [email protected] to delete if infringement.