CODEWITHSUNDEEP
google-apps-scriptformatconditional-formatting
Marcelo Meza
Marcelo Meza

Reputation: 73

Copy and apply conditional formatting while creating separate spreadsheets from multiple tabs

I'm using the code in the following post (How to split a single spread sheet with 20 tabs into separate sheets（different files）) to create separate documents from a single spreadsheet that has multiple tabs/sheets.

This has been relatively successful, but I have not been able to copy the formatting, neither the cell width and height nor the two conditional formatting rules in cell C2. To this aim I used the code in this post:

Google script to copy sheet in spreadsheet to new spreadsheet and name new spreadsheet after specific cell

Unfortunately, that code only copies values and not formulas.

I've attempted to use code from this port, to no avail:

Script: How to copy and reapply conditional formatting rules to a range on edit?

Is it possible to copy and apply conditional formatting while creating separate spreadsheets from multiple tabs?

This is mu current code:

function migrateSheetsToFiles() {
  var mySheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var toFolderName = 'ScreenerUserFolder';
  var i;
  for(i in mySheets){
    var currentSheet = mySheets[i];
    var oldData = currentSheet.getDataRange().getValues();
    var oldDataFormula = currentSheet.getRange("A2").getFormula();
    var oldDataFormatting = currentSheet.getRange("C2").getFormula();    
    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.getRange(2,1,oldData.length,oldData[0].length).setFormula(oldDataFormula);
    newSheet.deleteRow(4).deleteColumn(3).deleteRow(3).deleteColumn(2);
    newSheet.setName(newFile.getName());
    if(toFolderName != ''){
      var fileInDrive = DriveApp.getFileById(newId);
      fileInDrive.makeCopy(fileInDrive.getName(),DriveApp.getFoldersByName(toFolderName).next());
      fileInDrive.setTrashed(true);
    };

  };
}

Upvotes: 0

Views: 194

Answers (2)

Marcelo Meza
Marcelo Meza

Reputation: 73

Thank you for your contributions. Thanks to this I have proposed code and modified accordingly.

function migrateSheetsToFiles() {
  var mySheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var toFolderName = 'ScreenerUserFolder';
  var i;
  for(i in mySheets){
    var currentSheet = mySheets[i];
    var newFile = SpreadsheetApp.create(currentSheet.getName());
        var newId = newFile.getId();
           currentSheet.copyTo(newFile);
    var source = newFile.getSheetByName("Sheet1");
        newFile.deleteSheet(source);
    if(toFolderName != ''){
    var files = DriveApp.getFolderById("1rooBctI9qRbcpQSIR79S76xMSJchSmK4");
        files.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
      files.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.VIEW);
      var oldData = currentSheet.getRange('a1').getValues();
        newFile.getRange('a1').setValues(oldData);
  var targetFolder = DriveApp.getFolderById("1rooBctI9qRbcpQSIR79S76xMSJchSmK4");
    var file = DriveApp.getFileById(newId);
        file.moveTo(targetFolder);  
};
}
}

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26836

If you want to copy the sheets with all formulas and formatting - use copyTo()

Sample:

function migrateSheetsToFiles() {
  var mySheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
//  var toFolderName = 'ScreenerUserFolder';
  var i;
  for(i in mySheets){
    var currentSheet = mySheets[i];
    var newFile = SpreadsheetApp.create(currentSheet.getName());
    currentSheet.copyTo(newFile);
    var newId = newFile.getId();    
    if(toFolderName != ''){
      ...
    }
  };
}

ATTENTION

Your source file contains within the same Apps Script project several code files with a function called migrateSheetsToFiles().

Mind that you can NOT have more than one function of the same name within one Apps Script project - even if the funcitons are located in different .gs files.

I assume this is the reason that why the modifications you performed in your code did not go through.

Upvotes: 2

Related Questions