0

I am trying to change the tab color based on a cell value on an individual sheet. I have multiple tabs. The cell value that will determine whether or not it gets a color is a cell containing a date.

This is what I have- I can't get it to work. Any suggestions?

function onEdit(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var first = ss.getSheetByName("3005");
  var second = ss.getSheetByName("3006");
   var third = ss.getSheetByName("3007");
    var fourth = ss.getSheetByName("3008");
 var cell1 = first.getRange("c26");
  var cell2 = second.getRange("c24");
   var cell3 = third.getRange("c24");
    var cell4 = fourth.getRange("c24");
 var cellContent1 = cell1.getValue();
  if (cellContent1 == "date") {
 first.setTabColor("#66FF00");
  }
  if (cellContent1 == 0) {
 first.setTabColor(null);}
  var cellContent2 = cell2.getValue();
  if (cellContent2 == "date") {
 second.setTabColor("#66FF00");
  }
  if (cellContent2 == 0) {
 second.setTabColor(null);}
 var cellContent3 = cell3.getValue();
  if (cellContent3 == "date") {
 third.setTabColor("#66FF00");
  }
  if (cellContent3 == 0) {
 third.setTabColor(null);}
var cellContent4 = cell4.getValue();
  if (cellContent4 == "date") {
 fourth.setTabColor("#66FF00");
  }
  if (cellContent4 == 0) {
 fourth.setTabColor(null);}}

If I tell it to change color based on a number it works fine - i.e.

 function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet();
var first = ss.getSheetByName("3005"); 
var cell1 = first.getRange("i20"); 
var cellContent1 = cell1.getValue(); 
if (cellContent1 == 1) 
{ first.setTabColor("#66FF00"); } 
if (cellContent1 == 0) 
{ first.setTabColor(null);}}
8
  • This is the form I'm working with docs.google.com/spreadsheets/d/… Commented Jan 18 at 14:08
  • I posted an answer based on what I think you want to do. If you edit the correct sheet and cell, it then tests if the value in the cell is a date. If so, it sets the tab color. If not, it clears the tab color. If there are other states that are relevant (e.g. not a date and also not a zero, please clarify).
    – Blindspots
    Commented Jan 18 at 17:09
  • What is the purpose of the tab color change? I see that you have multiple SERVICE DATE fields in your sheet. Does only one matter?
    – Blindspots
    Commented Jan 18 at 17:58
  • 1
    Only the first date matters. If the tab is green, it's been used. No color= open to use Commented Jan 18 at 18:00
  • I assume that you will probably add additional tabs (order #) to the spreadsheet. Would it be better if the script dynamically retrieved the sheet names so the script doesn't need to be edited all the time? Maybe some logic like if the sheet name is a set of numbers and the edited cell is the one below service date in column C, then is it a date ...
    – Blindspots
    Commented Jan 18 at 18:04

1 Answer 1

0

Script

I edited your script to add the date-test based on what I think you want to do:

function onEdit(e) {

  const range = e.range;
  const sheet = range.getSheet();
  const name = sheet.getName();
  const color = "66FF00";
  
  const cells = [ 
    { Sheet: "3005",  A1: "C26" },
    { Sheet: "3006",  A1: "C24" },
    { Sheet: "3007",  A1: "C24" },
    { Sheet: "3008",  A1: "C24" }
  ];
  
  let i = cells.findIndex(c => c.Sheet === name);
  
  if ((i > -1) && (cells[i].A1.indexOf(range.getA1Notation()) > -1)) {

    let tabColor = isDate(range.getValue()) ? color : null;
    
    sheet.setTabColor(tabColor);  

  }

}

function isDate(v) {
 
  return v.constructor === Date;
  
}

Notes

  1. To determine if the value v from Sheets is a date, you can use a function to return TRUE if it's a date object, otherwise FALSE:
    function isDate(v) {
      return v.constructor === Date;
    }
    
  2. To check if the sheet name is in the array cells findIndex() returns either the index number associated with the sheet name or -1:
    let i = cells.findIndex(c => c.Sheet === name);
    
  3. The first two T/F tests are designed to avoid processing further code unnecessarily. If the first fails the second isn't attempted &&:
    1. Is the sheet's name listed in cells?
      (i > -1)
    2. Is the cell's address listed for that sheet in cells?
      (cells[i].A1.indexOf(range.getA1Notation()) > -1)
    3. If both conditions #1 and #2 are true then set the tabColor to:
      1. color if the edited value is a date
      2. null if the edited value is not a date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.