I am trying to clear (reset) the cell values on a sheet based on background color. I have found and changed the following 3 existing scripts. They all execute correctly without any errors, but none of them clear the form.

Here is a link to a mirror copy of my spreadsheet.

//////////////////////////////////

//https://stackoverflow.com/questions/70788737/clear-cell-contents-based-on-color-in-google-sheets

function ClearYellow1() {
    var colorToDelete = '#CDCDB4';
    var gs = SpreadsheetApp.getActive();
    var sn = gs.getSheetByName('Temp');
    var data = sn.getDataRange().getValues();
    for (var r = 0; r < data.length; r  ) {
        for (var c = 0; c < data[0].length; c  ) {
            var cellBgColor = sn.getRange(r   1, c   1).getBackground();
            if (cellBgColor === colorToDelete) {
                sn.getRange(r 1,c 1).clear(); //for clearing values only use
                //sn.getRange(r   1, c   1).deleteCells(SpreadsheetApp.Dimension.ROWS); //for deleting cells
                
                
            }
        }
    }
}
/////////////////////////////////////

//https://stackoverflow.com/questions/65266797/change-cell-value-based-on-the-cells-background-color-in-google-sheets

function ClearYellow2() {
  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Temp");
  const rng = sheet1.getDataRange();
  const colors = rng.getBackgrounds();
  const values = rng.getValues();
  colors.map((r,i)=>r.map((c,j)=>{
    if(c=='#CDCDB4'){
    values[i][j]="g";
    }}));
  rng.clearContent();
  rng.setValues(values);   
}
///////////////////////////////////////////

//https://stackoverflow.com/questions/68521942/google-sheets-delete-cell-with-specific-background-color

function ClearYellow3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheets()[0];
  var sheet = ss.getSheetByName("Temp");
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  var data = range.getValues()      // <--- get all the data as an array
  for (var i=0; i<bgColors.length; i  ) {
    for (var j=0; j<bgColors[i].length; j  ) {
      if (bgColors[i][j].toUpperCase() === '#CDCDB4') {
        data[i][j] = '';            // <--- change the array
      }
    }
  }
  range.setValues(data);            // <--- put the array back on the sheet
}

///////////////////////////////////////////////////////////

Any suggestions?

CodePudding user response：

Reset values based on background colors:

I set a few colors to light yellow 3 and used the debugger to see what the value was and used that value to do the comparison

function ClearYellow3() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet0");
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  var data = range.getValues()      
  for (var i=0; i<bgColors.length; i  ) {
    for (var j=0; j<bgColors[i].length; j  ) {
      if (bgColors[i][j] == "#fff2cc") {
        data[i][j] = '';   
        bgColors[i][j] = "#ffffff";//set to white         
      }
    }
  }
  range.setValues(data).setBackgrounds(bgColors);//reset values and backgrounds            
}

This worked first time without any problems

CodePudding user response：

You should pick up the color from one cell to fix the problem.

A slight modification of code

function ClearYellow3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Temp");
  var color = sheet.getRange('C2').getBackground()
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  var data = range.getValues()      
  for (var i=0; i<bgColors.length; i  ) {
    for (var j=0; j<bgColors[i].length; j  ) {
      if (bgColors[i][j] == color) {
        data[i][j] = '';   
        // bgColors[i][j] = "#ffffff";//set to white         
      }
    }
  }
  range.setValues(data).setBackgrounds(bgColors);//reset values and backgrounds            
}