CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
GeniusPharaoh
GeniusPharaoh

Reputation: 51

Reset range color using SpreadsheetApp script

I've made this public sheet to explain my issues with alternating rows https://docs.google.com/spreadsheets/d/18PoyJLOgyPaQXl2PGtAzlTcY3c53UDzzGLFjWYQfVlU/edit#gid=0

Strangely, I now can reset cells using setBackground (null), but can't force color fill any cell with preformatting from conditional formula! I use this formula

=MOD(ROW(),2)=0

The following is a sample of my code I use to force a conditionally pre-formatted cell to red in case that a condition is met. If not, I want to clear the forced color just as I would manually click on the cell, go to

Fill Color >> Reset

I tried setBackground('None') but it didn't work. The problem with using clearFormat() is that it removes the conditional format rule I did on all of the sheet

=MOD(ROW(),2)=0

With grey color. In essence, I need the translation of me just manually resetting the fill color, not removing the formatting, which in turn removes the alignments too. If such method doesn't exist, then how to create a formatting rule with a styling color to alternate rows as was the case originally. You can find that I tried this already in the commented line.

function onEdit(e)
{
  var range = e.range;

  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getSheetName();
  var row   = range.getRow();
  var col   = range.getColumn();

  if(sName == 'AABO')
  {
    var rowValues = sheet.getRange(row + ':' + row).getValues();

    if( (rowValues[0][TASK_TYPE_COL_NUM - 1] == 'Configuration') && (rowValues[0][TOT_RW_COL_NUM - 1] > 2)
     || (rowValues[0][TASK_TYPE_COL_NUM - 1] == 'Integration')   && (rowValues[0][TOT_RW_COL_NUM - 1] > 3) )
    {
      sheet.getRange(row, TOT_RW_COL_NUM).setBackground('red');
    }
    else
    {
      sheet.getRange(row, TOT_RW_COL_NUM).setBackground('None');
//      sheet.getRange(row, TOT_RW_COL_NUM).clearFormat().setHorizontalAlignment("center").setVerticalAlignment("middle").setFormula("=MOD(ROW(),2)=0"); 
    }
  }

  SpreadsheetApp.flush();
}

Upvotes: 4

Views: 11894

Answers (1)

Android Enthusiast
Android Enthusiast

Reputation: 4950

I noticed that the value you placed in setBackground() is 'None' which is incorrect. You may set the background color of all cells in the range using a CSS color like '#ffffff' or 'white'. If you want to reset the range color, you can use null.

range.setBackground(null);

Here is a case that uses null to reset the background color: How to set background color for a row based on current date in Google Docs Spreadsheet?

Upvotes: 14

Related Questions