When I change a cell's background color, how can I get all cells containing the same text to change the same color in all sheets?

Question

I'm working with a set-list for a cover band in which I have listed all of our songs by decade on separate sheets, including a full set list on its own sheet. When we work on new songs, I color those cells green. I would like to see those same songs highlighted green wherever they may appear on the other sheets whenever I change color (onEdit, that is).

Here's what I've got so far pieced together from other sources:

function replace() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activecell = ss.getActiveSheet().getActiveCell().getA1Notation();
var activecellvalue = ss.getActiveSheet().getActiveCell().getValue();
var activecellcolor = ss.getActiveSheet().getActiveCell().getBackground();
var allsheets = ss.getSheets();
var s = 0;
var cells = [];


   if (activecellvalue != 'Yes'|'No' && activecellcolor == '#00ff00'){
    for (var s=0; s < allsheets.length; s++) {
      var allcells = allsheets[s].getDataRange().getValues();
     for (var i=0; i < allcells.length; i++) { 
      for (var j=0; j < allcells[i].length; j++) {    
       if (allcells[i][j].toString().match(activecellvalue) == activecellvalue) {
        var newcells = allsheets[s].getRange(i+1,j+1).getA1Notation();
        cells.push(newcells);}

    }
   }
      allsheets[s].getRange(newcells).setBackground('#00ff00');
 // cells.push(newcells);
  Logger.log(newcells);
  Logger.log(allsheets);
  Logger.log(cells)
  }
 }
}

The "Yes" and "No" refer to cells containing messages in the negative or affirmative as to whether or not we have finished a song.

I have been able to get the script to color the same text in different cells in different sheets, but something still needs to be fixed which is allowing the same cell values in EVERY sheet to be highlighted, as well.

Thank you in advance for your help!

EDIT: I have a working (albeit, seemingly inefficient) code that will seek out the text of an active cell elsewhere and change its cell color:

function colorchange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activecell = ss.getActiveSheet().getActiveCell().getA1Notation();
var activecellvalue = ss.getActiveSheet().getActiveCell().getValue();
var activecellcolor = ss.getActiveSheet().getActiveCell().getBackground();
var allsheets = ss.getSheets();
var s = 0;
var name = allsheets[s].getName();
if (activecellvalue != 'Yes'|'No' && activecellcolor == '#00ff00'){
  for (var s=0; s < allsheets.length; s++) {     
     var cells = allsheets[s].getDataRange().getValues();
     for (var i=0; i < cells.length; i++) { 
      for (var j=0; j < cells[i].length; j++) { 
       if (cells[i][j].toString().match(activecellvalue) == activecellvalue) {
         var check = cells[i][j]
         var newcells = allsheets[s].getRange(i+1,j+1).getA1Notation();
         allsheets[s].getRange(newcells).setBackground('#00ff00');
         }
    }
   }
  }
}
else if (activecellvalue != 'Yes'|'No' && activecellcolor == '#ffffff'){
      for (var s=0; s < allsheets.length; s++) {     
     var cells = allsheets[s].getDataRange().getValues();
     for (var i=0; i < cells.length; i++) { 
      for (var j=0; j < cells[i].length; j++) { 
       if (cells[i][j].toString().match(activecellvalue) == activecellvalue) {
         var check = cells[i][j]
         var newcells = allsheets[s].getRange(i+1,j+1).getA1Notation();
         allsheets[s].getRange(newcells).setBackground('#ffffff');
         }
    }       
   }
  }
 }
}

The problem is getting this script to trigger on a change to background color. According to this, such a feature is not yet possible. Can anyone provide a potential solution?

UPDATE:

Working solution. Triggered by a drop-down menu selection.

function songStatus()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = ss.getDataRange();
  var curCell = sheet.getActiveCell();
  var curCol = curCell.getColumn();
  var curRow = curCell.getRow();
  var allSheets = ss.getSheets();
  var songs = [];
  var status = sheet.getRange(curRow, 3).getValue();
  var genre = sheet.getRange(curRow, 11).getValue();


if (status == "Now")  {
  sheet.getRange(curRow, 1).setBackground('#f884ff');
  sheet.getRange(curRow, 3).setBackground('#f884ff');
  var song = sheet.getRange(curRow, 1).getValue().toString();

  for (var s=7; s < allSheets.length; s++) {     
   var row = allSheets[s].getDataRange().getLastRow();
   var col = allSheets[s].getDataRange().getLastColumn();
   var cells = allSheets[s].getDataRange().getValues();

   for (var i=0; i < row; i++) { 
    for (var j=0; j < col; j++) { 
     if (cells[i][j].toString().match(song) == song) {
       allSheets[s].getRange(i+1,j+1).setBackground('#f884ff');

      }
    }
    }
    }

  }
else if (status == "Next")
  {
    sheet.getRange(curRow, 1).setBackground('#f2a2a2');
    sheet.getRange(curRow, 3).setBackground('#f2a2a2');
    var song = sheet.getRange(curRow, 1).getValue().toString();

 for (var s=7; s < allSheets.length; s++) {     
   var row = allSheets[s].getDataRange().getLastRow();
   var col = allSheets[s].getDataRange().getLastColumn();
   var cells = allSheets[s].getDataRange().getValues();
     for (var i=0; i < row; i++) { 
     for (var j=0; j < col; j++) { 
      if (cells[i][j].toString().match(song) == song) {
       allSheets[s].getRange(i+1,j+1).setBackground('#f2a2a2');

      }
    }
    }
    }
}
}

pointNclick pointNclick · Accepted Answer · 2015-11-05T23:25:33

Unfortunately, onEdit trigger only fires when an "Edit" is made by the user. However, you are right, changing the color is not considered as an edit. I would suggest a slightly different approach that would work in such a scenario. Instead of tracking your new songs by highlighting them with green and having the sheet run a script to detect if the color has been changed for that cell and then running a script to find that same song in other sheets, I'd suggest either tying your Yes/No drop-down to the onEdit function to fire the color change and the other scirpt to search for that song in other sheets, or making a new New/Old drop-down list to do that for you. Here's a small pseudo-code to do that:

function onEdit()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = ss.getDataRange();
  var curCell = sheet.getActiveCell();
  var curCol = curCell.getColumn();
  var curRow = curCell.getRow();
  var allSheets = ss.getSheets();

  //Assuming your New/Old column in E
  var status = sheet.getRange(curRow, 6).getValue();

  if (status == "New")
  {
    sheet.getRange(curRow, 1, 1, range.getLastColumn()).setBackgroundRGB(230,240,220);

    //Assuming your song name is in Column B
    var song = sheet.getRange(curRow, 3).getValue();

    for (var i=0; i<allSheets.length; i++)
    {
      var sRange = allSheets[i].getDataRange();
      var sData = sRange.getValues();
      var sRows = sRange.getLastRow();

      for (var j=0; j<sRows; j++)
      {
        if (sData[j][1] == song)
        {
          allSheets[i].getRange(j+1, 1, 1, sRange.getLastColumn()).setBackgroundRGB(230,240,220);
        }
      }
    }
  }
}

Another advantage of having a code like this is that if your sheet evolves to say, track all the songs that were Old, New, In-progress, On hold or any different statuses that you could think of, and each of these had to be tracked by a color code at a glance, it's much easier to add else-if statements to do that for you. Hope this helps!

When I change a cell's background color, how can I get all cells containing the same text to change the same color in all sheets?

1 Answers