0

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); }; }; }
Improve this question
7
  • Can I ask you about the issue of your current script? And, can I ask you about the detail of no avail of 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?? Commented Oct 25, 2020 at 23:57
  • Of course, thank you very much. With my current script I currently get the desired result of individual files from the tabs on an original spreadsheet. However, the number of columns and rows does not remain the same as each original tab, nor does the width and height of the columns/rows and the conditional formatting isn't copied onto the new documents. On attempting to use the code from the mentioned post, and probably due to my lack of experience, I have not been able to copy the conditional formatting. If you have any insight about this I'd most grateful. Commented Oct 26, 2020 at 0:12
  • Thank you for replying. In your situation, can I ask you about the difference between your goal and to directly copy the Google Spreadsheet including the sheets? Commented Oct 26, 2020 at 1:59
  • Of course. Once again, thank you. Source file for documents to be copied from: <docs.google.com/spreadsheets/d/…> Source Folder: <drive.google.com/drive/folders/…> Commented Oct 26, 2020 at 3:27
  • 1
    Thank you for replying. I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. I think that it will resolve your issue. Commented Oct 26, 2020 at 22:36

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you very much. I have since renamed the surplus code files with the same name. I have copied your suggested code verbatim, but unfortunately neither the conditional formatting nor other formats have been copied. Once again, thank you very much.
I do not have the same problem. Can you make a dummy copy of the spreadsheet and attach to it only the code I provided-deleting all other scripts and see if it works?
Thank you, that would be very kind of you, and it would be of tremendous help. My current concern is that I need the A1 =sheetName() result to be copied as text only in the new sheets. I'm certain that I have missed a relevant detail that produces the error. I would be very happy to use the sheet that you may prepare so that I might learn from it. Thank you.
Have you managed now to copy everything with formulas and formatting? In this case, the last step is easy -you just add to the code the two lines you had before: var newSheet = newFile.getSheets()[0] newSheet.getRange(1,1,oldData.length,oldData[0].length).setValues(oldData); to overwrite the formula with the value manually.
That's what the line newSheet.setName(newFile.getName()) is for.
|
0

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); }; } }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.