CODEWITHSUNDEEP
google-apps-script
Nemanja011
Nemanja011

Reputation: 3

Google sheet script tab color

I'm kinda new to the whole "scripting game" and this is likely a piece of cake for most of you.

Anyway, I am trying to make my script change the specific tab color based off of the value of cell A1.

If the value of A1 is 1, the sheet color needs to be set to red. If the value of A1 is 0, the sheet color needs to be set to null.

Don't mind cells 5 and 6 as they are working as intended.

Here's what I have so far, but it is not working as intended:

function onEdit(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ss1 = SpreadsheetApp.getSheetByName("1");
 var ss3 = SpreadsheetApp.getSheetByName("3");
 var first = ss.getSheetByName("1");
 var third = ss.getSheetByName("3");
 var cell1 = ss.getRange("A1");
 var cell3 = ss.getRange("A1");
 var cellContent1 = cell1.getValue();
 var cellContent3 = cell3.getValue();
 var cell5 = ss.getRange("B2");
 var cell6 = ss.getRange("C2");
 cell5.setValue(Utilities.formatDate(new Date(), 'GMT+2', 'dd.MM.yyyy'));
 cell6.setValue(Utilities.formatDate(new Date(), 'GMT+2', 'HH:mm:ss'));
  if (cellContent1 == 1) {
 first.setTabColor("ff0000");
  }
  if (cellContent1 == 0) {
 first.setTabColor(null);
  }
  if (cellContent3 == 1) {
 third.setTabColor("ff0000");
  }
  if (cellContent3 == 0) {
 third.setTabColor(null);
  }
}

Upvotes: 0

Views: 2083

Answers (2)

Cooper
Cooper

Reputation: 64032

Perhaps this will help you.

function tabColor() 
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sht = ss.getActiveSheet();
 var cell = sht.getActiveCell();
 var cellContent = cell.getValue();
 if (cellContent) 
 {
    sht.setTabColor("ff0000");
 }
 if (!cellContent) 
 {
    sht.setTabColor("ffffff");
 }
}

Upvotes: 0

Jack Brown
Jack Brown

Reputation: 5892

In your code variable cell 1 and 2 are accessing the same sheet(the active sheet). To access the value from sheet named 1 and 3 you will have modify the code like below:

function onEdit(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var first = ss.getSheetByName("1");
 var third = ss.getSheetByName("3");
 var cell1 = first.getRange("A1");
 var cell3 = third.getRange("A1");
 var cellContent1 = cell1.getValue();
 var cellContent3 = cell3.getValue();
 var cell5 = ss.getRange("B2");
 var cell6 = ss.getRange("C2");
 cell5.setValue(Utilities.formatDate(new Date(), 'GMT+2', 'dd.MM.yyyy'));
 cell6.setValue(Utilities.formatDate(new Date(), 'GMT+2', 'HH:mm:ss'));
  if (cellContent1 == 1) {
 first.setTabColor("ff0000");
  }
  if (cellContent1 == 0) {
 first.setTabColor(null);
  }
  if (cellContent3 == 1) {
 third.setTabColor("ff0000");
  }
  if (cellContent3 == 0) {
 third.setTabColor(null);
  }
}

Also note i have removed some lines that were not being used or redundant.

Hope that helps

Upvotes: 1

Related Questions