Move every tab of a certain color to a separate workbook

Question

I asked a similar question a while ago that involved moving tabs of a certain color to the end of my tabs. That question with it's answer is HERE

Now, I need to remove all of those tabs from my ever-growing workbook. I have another workbook for dead deals, but it's going to take too long to move each tab manually. Is there a similar function to the one in the answer above that will move all of the tabs of the certain color into a completely different workbook, or am I stuck doing it manually?

Here is the code I've got:

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());
      }
    }
  }
}

I really don't understand javascript (if this is javascript) enough to understand what's what. I'm guessing the bit inside Sheets.Spreadsheets.batchUpdate{} needs to be changed, but I don't know what to change it to.

Yes. You can modify that code to change it's behavior to suit your new needs. Once you've made an effort to do so and run into difficulties, you can explain the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we can try and help. This isn't a *Someone was nice enough to help me by writing code before, but now I need it changed to meet new requirements* site - we're not a free code writing service. If you're unable to attempt the changes yourself, hire a contractor to make them for you. — Ken White, Dec 04 '19 at 18:22
I'm not one to ask for help without trying first. With my limited coding knowledge, I was unable to figure out what to do with the code, which is why I asked. — Daniel Thompson, Dec 04 '19 at 19:51
Then show your code, explain the problem you're having, and ask a question related to that code. Again, we're not a free coding service. — Ken White, Dec 04 '19 at 20:59

score 1 · Answer 1 · answered Dec 04 '19 at 21:37

1

You can use SpreadsheetApp.getSheets() to get an array of sheets. And then for each sheet you can use the Sheet Class Method copyTo(Spreadsheet) and then delete the sheet. It's pretty simple.

answered Dec 04 '19 at 21:37

Cooper

59,616
6
23
54

Thanks again Cooper. I will look into the helpful info that you've given and see if I can figure it out. – Daniel Thompson Dec 04 '19 at 21:57

score 0 · Accepted Answer · answered Dec 05 '19 at 08:30

I'm not sure if there is a reason behind the use of Sheets API, but you could accomplish this without using the Advanced Service by:

(1) Defining your destination spreadsheet. For example, open your spreadsheet by id by adding this line at the top of your code (and changing the corresponding id to yours):

var dest = SpreadsheetApp.openById("your-destination-spreadsheet-id");

(2) Using copyTo(spreadsheet) to copy each sheet to your destination spreadsheet and deleteSheet(sheet). So you should change this:

        Sheets.Spreadsheets.batchUpdate(
          {
            requests:[
              {
                "updateSheetProperties": 
                {             
                  "properties":
                  {
                    "sheetId":shts[i].getSheetId(),
                    "index": shts.length
                  },
                  "fields": "index"                      
                }
              }
            ]
          }, ss.getId());

To this:

        shts[i].copyTo(dest);
        ss.deleteSheet(shts[i]);

I hope this is of any help.

score 0 · Answer 3 · answered Dec 05 '19 at 17:29

lamblichus answered my question above. For anyone who wants/needs to see the code, this is what it ended up looking like:

function moveSheetsOfAColorToDead(color) {
  var color=color || '#ff0000';
  var dest=SpreadsheetApp.openById('destination id here')
  if(color) {
    var ss=SpreadsheetApp.getActive();
    var shts=ss.getSheets();
    for(var i=0;i<shts.length;i++) {
      if(shts[i].getTabColor()==color) {            
        shts[i].copyTo(dest);
        ss.deleteSheet(shts[i]);
      }
    }
  }
}

Move every tab of a certain color to a separate workbook

3 Answers3