Change sheet tab color based on blank/no blank cell-CodePudding

I have a Google worksheet with over 50 sheets. Each sheet tab is labeled with the name of the person responsible for making changes in that sheet.

I would like a script that makes all sheet tabs red until A2 is no longer blank. When A2 contains text, the sheet tab will change to green. If the text in A2 is erased making the cell blank again, the sheet tab should change back to red.

I am new to script and have tried a few snippets found on the internet but they all look for specific text in A2 to change the tab color. I need the tab color to change based on the cell being blank or nonblank.

Thank you for your help!

CodePudding user response：

Set all sheet tabs colors

function greenAndRed() {
  const ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach(sh => {
    if(sh.getRange("A2").isBlank()) {
      sh.setTabColor("#ff0000");//red
    } else {
      sh.setTabColor("#00ff00");//green
    }
  });
}

setTabColor

CodePudding user response：

I believe your goal is as follows.

In your situation, you have more than 50 sheets in a Google Spreadsheet.
When the cell "A2" of each sheet has a value, you want to set the tab color to "green".
When the cell "A2" of each sheet has no value, you want to set the tab color to "red".
You want to achieve this situation by dynamically running the script.

I thought that in your situation, when all sheets are scanned every run, the process cost will become high. So, in this answer, how about the following flow?

As the initial setting, all sheets are checked and set the tab color.
- In this case, the process cost becomes high. Because all sheets are scanned.
After the initial setting was done, only when the cell "A1" is edited for each sheet, the cell "A1" is checked and the tab color is changed using the script.

Here, 2 functions are used. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, in this sample, in order to reduce the process cost of the script, Sheets API is used. So, please enable Sheets API at Advanced Google services.

In this sample script, you can set the excluded sheets. If you want to exclude the sheets for setting the tab color, please set the sheet names you want to exclude to the variable of excludeSheetnames. If you don't want to use this, please set const excludeSheetnames = [];. By this, all sheets are used.

// First, please run this function. By this, all sheets are scanned and set the tab colors.
function forInitialSetting() {
  const excludeSheetnames = ["Sheet1"]; // If you want to exclude the sheets, please set the sheet names you want to exclude.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const sheets = ss.getSheets();
  const redColor = { red: 1, green: 0, blue: 0 }; // <-- #ff0000 (red)
  const greenColor = { red: 0, green: 1, blue: 0 }; //  <-- #00ff00 (green)
  const { ranges, requests } = sheets.reduce((o, s) => {
    const sheetName = s.getSheetName();
    if (!excludeSheetnames.includes(sheetName)) {
      o.ranges.push(`'${sheetName}'!A1`);
      o.requests.push({ updateSheetProperties: { properties: { sheetId: s.getSheetId() }, fields: "tabColor" } });
    }
    return o;
  }, { ranges: [], requests: [] });
  const v = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges }).valueRanges;
  v.forEach(({ values }, i) =>
    requests[i].updateSheetProperties.properties.tabColor = values && values[0][0] ? greenColor : redColor
  );
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

// This script is automatically run by the simple trigger when the cell "A1" is edited.
// Please don't directly run with the script editor.
function onEdit(e) {
  const excludeSheetnames = ["Sheet1"]; // If you want to exclude the sheets, please set the sheet names you want to exclude.

  const range = e.range;
  const sheet = range.getSheet();
  if (e.range.getA1Notation() != "A1" || excludeSheetnames.includes(sheet.getSheetName())) return;
  sheet.setTabColor(range.getDisplayValue() ? "#00ff00" : "#ff0000");
}

First, please run the function forInitialSetting with the script editor. By this, all sheets are scanned and set the tab colors.
After you ran forInitialSetting, when you edit the cell "A1", onEdit function is automatically run by the OnEdit trigger. By this, when the cell "A1" has a value, the tab color is set to "green". When the cell "A1" has no value, the tab color is set to "red".

Note:

For example, if you want to run forInitialSetting every editing the cell "A1", in this case, please add the following function and install OnEdit trigger to the function installedOnEdit. In that case, please remove function onEdit(e) {,,,}. By this, when the cell "A1" is edited, installedOnEdit is run.

  function installedOnEdit(e) {
    const excludeSheetnames = ["Sheet1"]; // If you want to exclude the sheets, please set the sheet names you want to exclude.

    const range = e.range;
    const sheet = range.getSheet();
    if (e.range.getA1Notation() != "A1" || excludeSheetnames.includes(sheet.getSheetName())) return;
    forInitialSetting();
  }

Set all sheet tabs colors

Sample script:

Note:

References: