Script to copy sheet as a new spreadsheet not working well

0
votes

I have this script to copy actual sheet as a new spreadsheet named with value from I2 Cell:

function CopySheet(){
var sheet = SpreadsheetApp.getActiveSheet(); // Get current active sheet.
var sheet_name = sheet.getRange("i2").getValue(); // Get the value of cell B1, used to name the new spreadsheet.

var folder = DriveApp.getFolderById("xxxxxxxxxxxxx"); // Get the ID of the folder where you will place a copy of the spreadsheet.

var newSS = SpreadsheetApp.create(sheet_name); // create new blank spreadsheet in a root folder
var asFile = DriveApp.getFileById(newSS.getId()); // get new spreadsheet as a file

folder.addFile(asFile); // add this file to destination folder
DriveApp.getRootFolder().removeFile(asFile); // remove a file from root folder

var copiedSheet = sheet.copyTo(newSS); // copy active sheet to new spreadsheet
copiedSheet.setName(sheet_name); // rename copied sheet
newSS.deleteSheet(newSS.getSheetByName('Sheet1')); // remove "Sheet1" sheet which was created by default in new spreadsheet
}

Script beautifully copy sheet as a new spreadsheet but there's ugly "Sheet1". When script comes to line 15 : newSS.deleteSheet(newSS.getSheetByName('Sheet1')); i get error: Exception: Argument nie może być zerowy: sheet (wiersz 15, plik „Kod”) - Argue cannot be zero: sheet(row 15, file "Kod").

Please for help.

1
google-apps-script

1 Answers

0
votes

How about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

I thought that in your case, the sheet name of the default sheet might not be "Sheet1". So as a workaround, how about deleting the default sheet without using the sheet name?

The modified script is as follows. Please modify your script as follows.

Modified script:

newSS.deleteSheet(newSS.getSheetByName('Sheet1'));

newSS.deleteSheet(newSS.getSheets()[0]);

or

newSS.deleteSheet(newSS.getSheets().filter(function(s) {return s.getSheetId() == 0})[0]);

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.