Google Scripts - Copy rows to a new sheet when column H has a specific colour (Yellow)

Question

I've played around with this script but have still yet to get it to work.

I need a script that looks at the column F's background color in the sheet called Test.

If the cell background in Column F is yellow it will copy that entire row to a new sheet called New Data.

Please find attached the Google Spreadsheet link: https://docs.google.com/spreadsheets/d/19pMAmB94tOoV14MiSPyoPhty2dO5OudO4NupP8KhJA4/edit#gid=1703438513

Any help would be greatly appreciated!

function Extract(){ 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Test');
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getBackgrounds();
  var yellowColour = '#ffff00';
  var whiteColour = '#ffffff';


  var newSheet = ss.getSheetByName('New Sheet');

 for (var i = 0; i <= numRows - 1; i++) {

 var row = values[i];

   if (row[6] === whiteColour ) {  

   } 
     else {

       newSheet.appendRow(row);

     }

 } // End of For Loop
};

Ed Nelson Ed Nelson · Accepted Answer · 2017-08-20T14:58:09

Try this. It creates an array of rows where F is yellow. It clears the old data in 'New Data' and copies in the new array:

function Extract(){ 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Test');
  var lr=sheet.getLastRow()
  var data = sheet.getRange(2,1,lr,15).getValues();//get Test values
  var rows = sheet.getRange(2,6,lr,1).getBackgrounds();//get F background colors
  var yellowColour = '#ffff00';
  var newSheet = ss.getSheetByName('New Data');
  var lr1= newSheet.getLastRow()
  var newData=[] //new array
  for (var i = 0; i <= lr - 1; i++) {
    if (rows[i] == yellowColour ) {  //if F = yellow
       newData.push(data[i]) // add to new array
  }} 
   newSheet.getRange(2,1,lr1+1,15).clearContent()//clear old content excluding header row
   newSheet.getRange(2,1,newData.length,15).setValues(newData)//set new comtent
};

Google Scripts - Copy rows to a new sheet when column H has a specific colour (Yellow)

1 Answers