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

Question

I have a google spreadsheet with multiple sheets within it, I would like to copy each individual sheet into a new spreadsheet and to have the new spreadsheet named after text in a specific cell. I am happy to run the script multiple times so I figured to have it copy the active sheet.

i.e. What I have = Spreadsheet called "Colours" - Sheet 1="red", Sheet 2= "blue", Sheet 3= "yellow", etc.

What I want =

Spreadsheet called "Red". Spreadsheet called "blue", Spreadsheet called "yellow"

So far I have this script but its telling me "Script function not found: saveAsSpreadsheet For more information"

function copyDocument() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
var sstocopy = ss.getActiveSheet(); // Get spreadsheet with DriveApp.
var sheet = ss.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.
sstocopy.makeCopy(sheet_name,folder); // Make a copy of the spreadsheet in the destination folder.

Any help would be greatly appreciated.

Ok, realised I was being stupid and wasn't running the function correctly, which explains the script function not found error, however now I have the error "TypeError: Cannot find function makeCopy in object Sheet" — Nick Caines

Ilya Chernoskulov Ilya Chernoskulov · Accepted Answer · 2018-08-08T12:09:12

You need to open your spreadsheet as a file, not as a spreadsheet to be able to use makeCopy function.

So this line in your code is not correct:

var sstocopy = ss.getActiveSheet(); // Get spreadsheet with DriveApp.

It should be:

var sstocopy = DriveApp.getFileById(ss.getId()); // Get spreadsheet with DriveApp.

So the correct code is the following:

function copyDocument() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
    var sstocopy = DriveApp.getFileById(ss.getId()); // Get spreadsheet with DriveApp.
    var sheet = ss.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.
    sstocopy.makeCopy(sheet_name,folder); // Make a copy of the spreadsheet in the destination folder.

Answer to your comment:

For your purpose the code should be modified in this way:

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

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

1 Answers