-3

I have a Google Sheet with over 100 tabs (worksheets). I color code my tabs based on where they are in my workflow (Yellow-I'm collecting data, Green-ready for my partner to research, Blue-my partner is researching, Red-dead deal). We add multiple tabs on a daily basis, and the list of tabs is growing rapidly. My partner wants all of the dead deals (red tabs) to be at the end of the list. Dragging the tabs one by one to the end takes way too long. I need a script (or any other working method) to take every tab that is colored red and move them to the end of the list of tabs.

So far the only option I've found was a VBA code for Excel. As far as I can tell, VBA doesn't work with Google Sheets, because it is a code that only Microsoft uses SMH.

I don't know enough about jquery or scripting to write my own code, and I haven't been able to find an answer by Googling, so I don't have any test results. Also, my sheet has sensitive data, so I can't provide it as test data, but the question should be easy (for someone who knows how to script) to reproduce without need of a test sheet. Just make a blank Google Sheet with 3 or so tabs, color the 1st one red, then work on a script that will move that red tab to the end.

Daniel Thompson
  • 11
  • 2

2 Answers2

0

Pseudocode:

You need a loop to go through each tab in the spreadsheet
Inside that loop you want to check the color (with "getTabColor()")
If the color is red, move them to the end (or hide, them, I'd personally hide them, with "hideSheet()")

I'm not sure how sustainable this is, there is a limit to how many tabs you can have in a spreadsheet (I believe it is 160).

J. G.
  • 1,922
  • 1
  • 11
  • 21
0

Moving sheets/tabs of a color to end of list of tabs using Google Sheets API version 4

function moveSheetsOfAColorToEnd(color) {
  var color=color || '#ff0000';
  if(color) {
    var ss=SpreadsheetApp.getActive();
    var shts=ss.getSheets();
    for(var i=0;i<shts.length;i++) {
      if(shts[i].getTabColor()==color) {            
        Sheets.Spreadsheets.batchUpdate(
          {
            requests:[
              {
                "updateSheetProperties": 
                {             
                  "properties":
                  {
                    "sheetId":shts[i].getSheetId(),
                    "index": shts.length
                  },
                  "fields": "index"                      
                }
              }
            ]
          }, ss.getId());
      }
    }
  }
}

You will need to Enable Sheets API v4 in Resources Menu and also in the Google Cloud Platform.

Google Sheets API v4 Reference

This question helped me a lot.

shts.length is the length of the array returned from Spreadsheet.getSheets(). Since the sheets index begins at one setting a given sheet index to the length of that array will always move that sheet to the end.

Here's another way to do the same thing:

function moveSheetsOfAColorToEnd1(color) {
  var color=color || '#ff0000';
  if(color) {
    var ss=SpreadsheetApp.getActive();
    var shts=ss.getSheets();
    for(var i=0;i<shts.length;i++) {
      if(shts[i].getTabColor()==color) {
        var shtsPropReq=Sheets.newUpdateSheetPropertiesRequest();
        shtsPropReq.properties=Sheets.newSheetProperties();
        shtsPropReq.properties.sheetId=shts[i].getSheetId();
        shtsPropReq.properties.index=shts.length;
        shtsPropReq.fields="index";
        var requests=[Sheets.newRequest()];
        requests[0].updateSheetProperties=shtsPropReq;
        var batchUpdateRequest=Sheets.newBatchUpdateSpreadsheetRequest();
        batchUpdateRequest.requests=requests;
        Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, ss.getId());
      }
    }
  }
}

This method follows the same pattern as the Apps Script example found on this page.

There are some additional examples found here

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Can you explain what you mean by, "You will need to Enable Sheets API v4 in Resources Menu and also in the Google Cloud Platform."? When I click on the resources menu, and click Advanced Google Services, a list of APIs comes up, but I don't see one named "Sheets API v4". I tried following the instructions found here: https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services But I still didn't find an API with that name in the list. – Daniel Thompson Apr 11 '19 at 16:14
  • My apologies, I must have overlooked it. There is one named "Google Sheets API v4". I've enabled it, and I'm going to test the script. I will let you know how it goes. – Daniel Thompson Apr 11 '19 at 16:17
  • Thanks Cooper! I tested the script after enabling Google Scripts API v4, and it worked like a charm. (I'm new to stackoverflow, so I'm not 100% sure how to mark your answer as the correct answer. I clicked the checkmark next to your comment, so hopefully that's it.) – Daniel Thompson Apr 11 '19 at 16:22
  • I believe you can also do this with [`moveActiveSheet`](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#moveActiveSheet(Integer)) Also, I think you can queue requests and `batchUpdate` outside the loop. – TheMaster Apr 13 '19 at 10:01
  • You’re right I should’ve done the batch update outside of the loop. This was the first time I used this API. – Cooper Apr 13 '19 at 14:26