0

I use a docs spreadsheet to manage projects. Each row represents a task and the date is contained in single cell conditionally formatted according to the date proximity.

I would like to search for that colour and copy any rows in which that coloured cell occurs to a row on a new page.

Eg. If Today = red and Tomorrow = green, I'd like to be able to pull through all the tasks that occur today to different page.

Any help here would be amazing. I appreciate that it might not be possible :<

user2141470
  • 1

2 Answers2

1

this is quite easy and a search on this forum should have bring you some examples... anyway, here is a way to get it, I chosed green to test... you could easily change/combine more colors.

function copyGreenRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var colors = sh.getDataRange().getBackgrounds();// get the colors
  var data =  sh.getDataRange().getValues(); // get corresponding data
  var datatoCopy = [];
  Logger.log(colors);
  for(var c in colors){
  var rowcolors = colors[c].toString();
    if(rowcolors.indexOf('#00ff00')!=-1){ // check if this color is in this row
      datatoCopy.push(data[c]);// if so, copy the data to an array
      }
      }
var newsheet = ss.insertSheet().getRange(1,1,datatoCopy.length,datatoCopy[0].length).setValues(datatoCopy);// bulk write to the new sheet
}

Note : to see the colors in your main sheet, look at the logger that shows all the cells color codes used in the script.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
0

Although I agree with the above post you are executing more work than you need to as data is already an array. Instead just remove them from your "data" array that you create when you used "getValues()". This should save you a bit of time as you are not creating unneeded arrays.

function copyGreenRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var colors = sh.getDataRange().getBackgrounds();// get the colors
  var data =  sh.getDataRange().getValues(); // get corresponding data
  Logger.log(colors);
  for(c=0 ; c< data.length; ++c){
  var rowcolors = colors[c].toString();
    if(rowcolors.indexOf('#00ff00') !=-1){ // check if this color is not present
     continue; //if it is  the color we want move along
      }
else
{
 data.splice(c,1); //Remove it from our array
}
    }
var newsheet = ss.insertSheet().getRange(1,1,data.length,data[0].length).setValues(data);
// bulk write to the new sheet
}
Kevrone
  • 614
  • 2
  • 11
  • 22