2020-10-28

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

  };
}


from Recent Questions - Stack Overflow https://ift.tt/31QGzAB
https://ift.tt/eA8V8J

No comments:

Post a Comment