Script to Copy cells to another sheet, for example, based on the Background Color of the Cell

Question

First of all I'm completly new to Google Sheets and Scripts so I'm trying to learn all by myself, so sorry if I'm asking something 'stupid'

So my problem is: I've created a Script to basically add a Background color to a cell, based in a certain condition. What I wanted to do now was identify all cells with that added Background color, select the entire row in which they are contained and copy all the information to another sheet.

I was trying to do it with an if condition, but I have no idea how to select the entire row or if the condition I'm using to get the Background color is right. Can anybody help me? Thank you so much!

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');
  var r = s.getRange('F:F');
  var v = r.getBackgrounds();
  for(var i=v.length-1;i>=0;i--)
    if(v =='B7E1CD')
      s.getCurrentCell.copyTo(spreadsheet.getRange(20,1)); //don't mind this instruction, I know it isn't right

define spreadsheet. Is it a sheet of the same spreadsheet file or of a different spreadsheet file? — soMario
Try using v[i][0]==‘#B7E1CD’ And also you need you need to use the filter method if you’re going to use that kind of a range definition as an alternative you could use the range definition that uses get last row method — Cooper
Keep in mind when you use get values or get backgrounds that you always get a two dimensional ray return to you even if it’s only a single column or a single row. I realize that’s not intuitive but it’s a reality — Cooper
Hi @Mario ! It's a sheet in the same spreadsheet file. Sorry if it wasn't clear — Filipa

soMario soMario · Accepted Answer · 2021-03-02T15:52:46

Explanation:

Your goal is to filter only the rows for which the background color in column F is '#b7e1cd' and copy these values to a different sheet.

You don't need a loop to achieve this goal, you can directly filter the rows for which the background color in F is '#b7e1cd'.
```
const new_values = values.filter((_,i)=>colors[i]=='#b7e1cd');
```
where values is defined as the full datarange of Sheet1:
```
const values = sh.getDataRange().getValues();
```

You can use copyTo in case you want to move data within the same spreadsheet file. This is why I prefer getRange/setValues because it does not have this limitation.

Solution:

The following script will paste the values starting from the cell A1 of Sheet2. If you want a different starting cell, modify 1,1 in target_sh.getRange(1,1,..) to a different cell reference.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // put the name of the source sheet
  const target_sh = ss.getSheetByName('Sheet2'); // put the name of the target sheet
  const colors = sh.getRange('F1:F').getBackgrounds().flat();
  const values = sh.getDataRange().getValues();
  const new_values = values.filter((_,i)=>colors[i]=='#b7e1cd');
  target_sh.getRange(1,1,new_values.length,new_values[0].length).setValues(new_values);
}

Updated code based on your sheet:

function iftest() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('HH 2 - Main Database'); // put the name of the source sheet
  const target_sh = ss.getSheetByName('HH 1 - New Database'); // put the name of the target sheet
  const colors = sh.getRange('F1:F').getBackgrounds().flat();
  console.log(colors);
  const values = sh.getDataRange().getValues();
  let LastRow = target_sh.getLastRow();
  const new_values = values.filter((_, i) => colors[i]=='#ff6d01');
  target_sh.getRange(LastRow+1,1,new_values.length,new_values[0].length).setValues(new_values);
}

Script to Copy cells to another sheet, for example, based on the Background Color of the Cell

1 Answers

Explanation:

Solution: