google-apps-scriptgoogle-sheetsgoogle-apps

How to split a single spread sheet with 20 tabs into separate sheets(different files)


I have a single spreadsheet with 20 tabs but it's become very heavy to handle so I would like to split it into different sheets (files)

If it's possible, could you possibly suggest some Google Apps Scripts so that I can manage to do it?

Hopefully, a tab name becomes the name of the new file when convert.


Solution

  • Here is a script that should work. The toFolderName is blank by default, so your files will be saved to your root folder. If you put a unique folder name in, the files will be copied to that folder. I suggest to start here, it is a pretty slow script though, 20 tabs should take a little over a minute.

    function migrateSheetsToFiles() {
      var mySheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      var toFolderName = '';
      var i;
      for(i in mySheets){
        var currentSheet = mySheets[i];
        var oldData = currentSheet.getDataRange().getValues();
        var newFile = SpreadsheetApp.create(currentSheet.getName());
        var newId = newFile.getId();
        var newSheet = newFile.getSheets()[0]
    
        newSheet.getRange(1,1,oldData.length,oldData[0].length).setValues(oldData);
        newSheet.setName(newFile.getName());
    
        if(toFolderName != ''){
          var fileInDrive = DriveApp.getFileById(newId);
          fileInDrive.makeCopy(fileInDrive.getName(),DriveApp.getFoldersByName(toFolderName).next());
          fileInDrive.setTrashed(true);
        };
    
      };
    };