I can generate a list of all Sheets names by using the following script:

function SHEETNAMES(option = 0) {

  var sheetNames = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  if (option === 0) {
    for (var i = 0; i < sheets.length; i  ) sheetNames.push([sheets[i].getName()]);
  } else if (option === -1) {
    for (var i = 0; i < sheets.length; i  ) if (currSheet != sheets[i].getName()) { sheetNames.push([sheets[i].getName()]) };
  } else if (option === 1) {
    sheetNames.push(currSheet);
  } else {
    throw "Option argument must be -1, 0, or 1";
  }
  return sheetNames
}

Each tab sheet I have has 2 colors, red (#ff0000) or green (#00ff00). How if I want to do a color filter, if I only want to show a list of sheet tab names that are only green (#00ff00) for example.

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the sheet names by checking the tab color using Google Apps Script.
• You want to give the color as the HEX value.

In this case, how about the following sample script?

Sample script:

function SHEETNAMES(tabColor) {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  const sheetNames = sheets.reduce((ar, s) => {
    const colorObj = s.getTabColorObject();
    if ((tabColor && colorObj.getColorType() == SpreadsheetApp.ColorType.RGB && colorObj.asRgbColor().asHexString() == tabColor) || (!tabColor && colorObj.getColorType() == SpreadsheetApp.ColorType.UNSUPPORTED)) {
      ar.push(s.getSheetName());
    }
    return ar;
  }, []);
  return sheetNames.length > 0 ? sheetNames : [""];
}

• In this case, when SHEETNAMES("#ff0000") is used, the sheet names with the tab color of #ff0000 are returned.
• When SHEETNAMES() is used, the sheet names with no tab color are returned.

Note:

• It seems that getTabColor() can be used. But, it seems that this has already been deprecated. Please be careful about this. Ref

Reference:

• getTabColorObject()

CodePudding user response：

Get Tabnames by color

function gettabnamesbycolor(hexcolor="#ffffff") {
  const ss = SpreadsheetApp.getActive();
  const tcs = ss.getSheets().map(sh => {
    let cobj = sh.getTabColorObject();
    let type = cobj.getColorType().name();
    if(type == "RGB" && cobj.asRgbColor().asHexString() == hexcolor) {
      return sh.getName();
    } else if (type == "UNSUPPORTED" && hexcolor == "#ffffff") {
      return sh.getName();
    }
  }).filter(e => e);
  //Logger.log(JSON.stringify(tcs));
  return tcs.join(',');
}