CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Maason Kao
Maason Kao

Reputation: 3

Trying to setBackgroundColor based off of a keyword in Google Apps Script

I'm trying to highlight a row in a table based off of the relevant keyword. However, my attempts at setting the background color of my range of cells have not yielded any colors. I used loggers inside the if/else statement and they all worked, so I think I'm doing setBackgroundColor wrong somehow.

function statusHighlighter(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = ss.getActiveSheet();
  var sheetValues = ss.getDataRange().getValues();

  sheetValues.forEach(function(row, index) {
    var acctStatus = row[7];
    var highlightRange = sheetName.getRange(index+1, 1, 1, sheetName.getLastColumn());

    if(acctStatus === "Kept"){
      highlightRange.setBackgroundColor('##fffec4');
      Logger.log(acctStatus + " " + index);
    } else if(acctStatus === "Suspended"){
      highlightRange.setBackground('##cf4a4a');
    } else if(acctStatus === "Deleted"){
      highlightRange.setBackground('##6c75ad');
    }
  });
}

Upvotes: 0

Views: 230

Answers (1)

Marios
Marios

Reputation: 27348

Explanation / Issues:

There is one issue in your code and a couple of optimizations that I would like to suggest.

  • The hex color codes must contain only one # but you are using two ##.

  • You are iteratively using setBackground but instead you can create an array with colors inside the forEach loop and then use setBackgrounds to set the colors in one go. Have a look at best practices to see why.

  • Since you have a particular column of interest, it makes sense to consider only one column instead of the full dataRange. In the following script, acctStatusVals considers only column 8. I choose 8 because in your code you use the array index for 7 which is equivalent for column 8. Be careful on that. In my code, 8 means column H. Adjust this to your needs. To convert the 2D array into 1D since we are using a single column, you can use flat.

  • I use the following expression: Array(lastCol).fill('#6c75ad') to create an array of lastCol number of elements (last column with content) and fill it with the same value, because the goal is to color the full row with the same color value.

Optimized Solution:

Be careful with the active sheet. Make sure you select the right sheet before you run this code. I would advice you to use ss.getSheetByName('Sheet1'); but I leave it as a comment since this step is optional.

function statusHighlighter(){ 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = ss.getActiveSheet();
//  const sheetName = ss.getSheetByName('Sheet1'); // change to the name of your sheet
  const lastRow = sheetName.getLastRow();
  const lastCol = sheetName.getLastColumn();
  const acctStatusVals = sheetName.getRange(1,8,lastRow).getValues().flat();
  const colors = [];
  acctStatusVals.forEach(acctStatus=>{
   if(acctStatus === "Kept"){
      colors.push(Array(lastCol).fill('#fffec4'));
    } else if(acctStatus === "Suspended"){
      colors.push(Array(lastCol).fill('#cf4a4a'));
    } else if(acctStatus === "Deleted"){
      colors.push(Array(lastCol).fill('#6c75ad'));
    }                   
  });
  sheetName.getRange(1, 1, lastRow, lastCol).setBackgrounds(colors);
}

Sheet used for the above script:

enter image description here

Upvotes: 1

Related Questions