How can I automatically relocate colored tabs to the end in Google Sheets?

Other Questions Google Sheets
1

I’m working with a Google Sheet that has tons of tabs. We use colors to track our workflow progress. My teammate wants all the red tabs (which mark dead deals) at the end of the tab list. Moving them manually is super time-consuming. Is there a way to do this automatically?

I tried looking for solutions but only found stuff for Excel. Google Sheets doesn’t use VBA, so those won’t work. I’m not great with coding, so I can’t write a script myself.

Here’s a simple example of what I need:

function moveRedTabsToEnd() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = sheet.getSheets();
  var redSheets = [];

  for (var i = 0; i < allSheets.length; i++) {
    if (allSheets[i].getTabColor() == '#FF0000') {
      redSheets.push(allSheets[i]);
    }
  }

  for (var j = 0; j < redSheets.length; j++) {
    sheet.moveActiveSheet(sheet.getNumSheets());
  }
}

This is just a basic idea. Can anyone help me fine-tune this or suggest a better approach? Thanks!

2

yo, ur script looks good but it might not work perfectly. have u considered using arraySort() instead? it can sort sheets based on tab color. something like this:

ss.setActiveSheet(ss.getSheets().sort((a,b) => a.getTabColor() === ‘#FF0000’ ? 1 : -1)[0]);

that should do the trick without needing a loop. hope it helps!

3

Hey there! I’ve actually tackled a similar issue before, and I can share what worked for me. Your script is on the right track, but it needs a few tweaks to function correctly.

First, you’ll want to iterate through the sheets in reverse order. This prevents issues with changing indices as you move sheets. Also, instead of using moveActiveSheet, you should use setTabColor to change the color after moving.

Here’s a modified version that should work:

function moveRedTabsToEnd() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = sheets.length - 1; i >= 0; i--) {
    if (sheets[i].getTabColor() == '#FF0000') {
      ss.setActiveSheet(sheets[i]);
      ss.moveActiveSheet(ss.getNumSheets());
    }
  }
}

You can run this function manually or set up a trigger to run it automatically. Hope this helps streamline your workflow!

4

I’ve dealt with similar organizational challenges in Google Sheets before. While the script approach is valid, there’s a simpler method that might save you some hassle. Instead of moving tabs, consider using a custom sort order.

Create a new tab called ‘Tab Order’ with two columns: ‘Tab Name’ and ‘Sort Order’. List all your tabs in the first column. In the second, use numbers to define the order (e.g., non-red tabs as 1, red tabs as 2). Then use the SORT function in your main sheet to reorganize based on this custom order.

This method is more flexible and doesn’t require constant script runs. It also allows for easy adjustments if your workflow changes. Just update the ‘Sort Order’ column, and your tabs will automatically rearrange.