google apps script - Copying a spreadsheet copies all linked files as well

Question

I want to be able to only copy the spreadsheet and all it's sheets along with all defined sheet names, when I utilize the library method:

spreadSheet.copy(newSSName);

Or,

myFile.makeCopy(newNameOfFile);

Currently these methods copy all linked forms and scripts used in the forms. This is an unnecessary side effect for what I need and will result in a large mess in the Drive folder. Is there a way to do this quickly and efficiently without copying cell by cell, sheet by sheet? Or is that the only option?

Thanks.

Answer 1

How about this workaround? In this workaround, Sheets API is used for copying a Spreadsheet. In the case of copy() of Class Spreadsheet, makeCopy() of Class File and Files: copy of Drive API, the copied spreadsheet includes the bound scripts and linked forms. So I thought to use Sheets API. The flow of this workaround is as follows.

1. Retrieve object of source Spreadsheet using spreadsheets.get.
2. Create new Spreadsheet by including the retrieved object using spreadsheets.create.

By this flow, the copied Spreadsheet without including the bound scripts and linked forms can be created. The sample script is as follows. When you use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Sample script :

var fileId = "### fileId of source Spreadsheet ###"; // Please set here.
var obj = Sheets.Spreadsheets.get(fileId, {fields: "namedRanges,properties,sheets"});
Sheets.Spreadsheets.create(obj);

Note :

• When you use this script, please set fileId of source Spreadsheet.
• At spreadsheets.create of Sheets API, the Spreadsheet cannot be created in the specific folder. So the copied Spreadsheet is created to root folder. If you want to create it in the specific folder, please move it after the Spreadsheet was copied. Of course, you can do it using script.
• If you want to include developerMetadata of Spreadsheet, please add it to fields.

References :

• copy() of Class Spreadsheet
• makeCopy() of Class File
• Files: copy of Drive API
• Method: spreadsheets.get of Sheets API
• Method: spreadsheets.create of Sheets API

If this was not what you want, I'm sorry.