CODEWITHSUNDEEP
google-apps-script
cadbury4all
cadbury4all

Reputation: 9

Copying background colors using .getBackgrounds() to a range

I'm copying the background colors from a sheet into an array like this:

var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Refresh');
var lastRow = sheet.getLastRow();
var sourceStatusColorValues = sheet.getRange(1,11,lastRow,1).getBackgrounds(); //background color from column K (11)
var sourceProjectIDValues = sheet.getRange(1, 1, lastRow, 1).getValues();
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Projects');
var targetProjectIDValues = targetSheet.getRange(1, 1, targetSheet.getLastRow(),1).getValues();

Next I'm comparing the project ID from one sheet to the next and copying the background colors to an array so that I could use the .setBackgrounds() method to apply all the background colors in the right order.

//Compare the projectIDs for Target Sheet with the project IDs in Source Sheet.
//If match then add the background color to the colorValues array
var colorValues = [];

for (var i = 0; i < targetProjectIDValues.length; i++) {
  for (var j = 0; j < sourceProjectIDValues.length; j++) {
    if (targetProjectIDValues[i] == sourceProjectIDValues[j]) { 
      colorValues[i] = sourceStatusColorValues[j];
      break;
      }
    }
  }

  targetSheet.getRange(1, 11, targetSheet.getLastRow(), 1).setBackgrounds(colorValues);

The issue that I'm running into is that the values in copyValues array are all null and NOT the hexadecimal values of the background colors.

What am I missing? Could someone help? Appreciate it!!!

Upvotes: 0

Views: 373

Answers (1)

AMolina
AMolina

Reputation: 1379

Try this:

function backgrounds(){
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var sourceColors = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), 1).getBackgrounds();
  var sourceData = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), 1).getValues();

  var targetColors = [];

  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var targetData = targetSheet.getRange(1, 1, sourceSheet.getLastRow(), 1).getValues();

  for (var i = 0; i < sourceData.length; i++){
    if (sourceData[i][0] == targetData[i][0])
      targetColors.push([sourceColors[i][0]]);
    else
      targetColors.push(['#FFFFFF']);
  }
  targetSheet.getRange(1, 1, targetSheet.getLastRow()).setBackgrounds(targetColors);
}

The problem you were having is that getBackgrounds() (documentation here) and getValues() (documentation here) both return 2D arrays, and you were treating them as 1D. I tested this code with a sheet with one column of values comparing them against a second sheet, it put the corresponding value of values that matched rows.

UPDATE

I modified the code to use setBackgrounds() instead, it should be much better for working with large data sets.

Upvotes: 1

Related Questions