Copy a range in active sheet, create new spreadsheet with custom name, paste range

Question

I recently wrote my first Google Apps script that makes a copy of a spreadsheet (including all tabs within that spreadsheet) and places it into a specific folder in the user's Drive. The copy is renamed based on a cell value in the original spreadsheet. Here is my script, for reference:

function copyDocument() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
var id = ss.getId(); // Get current active spreadsheet ID.
var sstocopy = DriveApp.getFileById(id); // Get spreadsheet with DriveApp.
var sheet = ss.getActiveSheet(); // Get current active sheet.
var sheet_name = sheet.getRange("B1").getValue(); // Get the value of cell B1, used to name the new spreadsheet.
var folder_name = sheet.getRange("C23").getValue(); // Get the target folder ID.
var folder = DriveApp.getFolderById(folder_name); // 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.
}

This script works, but I have been asked to modify it because the script I wrote is copying over unnecessary tabs and data that is causing confusion to users.

The new script should make a copy of a specific range in a specific sheet, create a new spreadsheet, and paste that range into it. It should also name itself after a cell value in the range.

However, the only method I have come across that specifically copies a sheet into a new spreadsheet is copyTo(spreadsheet). However, the Google Apps Script Guide specifies that "the copied sheet will be named 'Copy of [original name]'" by default.

I want to be able to rename the copied sheet after a specific cell. My question is, can I use copyTo(spreadsheet) and give the new spreadsheet a custom name, based on a cell?

Thanks!

This doesn't look right var folder_name = sheet.getRange("C23").getValue(); // Get the target folder ID. var folder = DriveApp.getFolderById(folder_name); Seems like you using a name for the folder id and that's not going to work. — Cooper
Seems like makeCopy(name,destination) is just what you want. — Cooper
makeCopy(name,destination) is ideal because you can actually use it to specify the name of the copied file. However, I do not want to copy the entire spreadsheet. I only want to copy a range of cells, create a new spreadsheet, and rename it what I want. The method I found, copyTo(spreadsheet) accomplishes copying a range to a new spreadsheet, but does not allow me to rename the new file. — Mary Kidd
Well, I guess you will have to create your own function then — Cooper

Karl_S Karl_S · Accepted Answer · 2017-08-25T18:30:12

You will need to get the data from the specific sheet to copy out to a variable of using:

var sourceSheet = ss.getSheetByName("Sheet1");
var sourceData = sourceSheet.getDataRange().getValues();
var originalRangeNotation = sourceSheet.getDataRange().getA1Notation();

Then you need to create a new, empty file and make a sheet with the same name

var ssNew = SpreadsheetApp.create("My New File Name");
ssNew.insertSheet('My New Sheet');

Then add the contents from the saved data to the new file. Since the insertSheet makes the new sheet the active one, we use:

var sheetNew = ssNew.getActiveSheet();
var rangeNew = sheet.getRange(originalRangeNotation);
range.setValues(sourceData);

Copy a range in active sheet, create new spreadsheet with custom name, paste range

2 Answers