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

Updated: July 7, 2023

When I tested the above script again, in the current stage, I noticed that this cannot be correctly used. Because, in the current stage, the smart chips are implemented. In this case, Sheets API cannot be retrieved as an object by the current specification. So, as the current sample script, I would like to add it. I answered this to this thread. Also, this script uses Sheets API. So, please enable Sheets API at Advanced Google services.

Before you use this, please set spreadsheetId.

const spreadsheetId = "###"; // Please set your Spreadsheet ID.

// Ref: https://tanaikech.github.io/2021/03/26/copying-protections-for-spreadsheet-using-google-apps-script/
function copyProtectedRanges_(srcId, dstId) {
  const obj = Sheets.Spreadsheets.get(dstId, { fields: "sheets(properties(sheetId),protectedRanges(protectedRangeId))" }).sheets
    .reduce((o, s) => {
      o.sheetIds.push(s.properties.sheetId);
      if (s.protectedRanges && s.protectedRanges.length > 0) {
        s.protectedRanges.forEach(({ protectedRangeId }) => o.protectedRangeIds.push({ deleteProtectedRange: { protectedRangeId } }));
      }
      return o;
    }, { sheetIds: [], protectedRangeIds: [] });
  const requests = Sheets.Spreadsheets.get(srcId, { fields: "sheets/protectedRanges" }).sheets
    .reduce((ar, s, i) => {
      if (s.protectedRanges && s.protectedRanges.length > 0) {
        const temp = s.protectedRanges.map(e => {
          delete e.protectedRangeId;
          e.range.sheetId = obj.sheetIds[i];
          if (e.unprotectedRanges) {
            e.unprotectedRanges.forEach(f => f.sheetId = obj.sheetIds[i]);
          }
          return { addProtectedRange: { protectedRange: e } };
        });
        ar = ar.concat(temp);
      }
      return ar;
    }, obj.protectedRangeIds);
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, dstId);
}

// Please run this function.
function main() {
  const srcSpreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const dstSpreadsheet = SpreadsheetApp.create(`Copied ${srcSpreadsheet.getName()}`);
  const srcSSId = srcSpreadsheet.getId();
  const dstSSId = dstSpreadsheet.getId();

  DriveApp.getFileById(dstSSId).moveTo(DriveApp.getFileById(srcSSId).getParents().next());
  const temp = dstSpreadsheet.getSheets()[0].setName(Utilities.getUuid());
  srcSpreadsheet.getSheets().forEach(sheet => sheet.copyTo(dstSpreadsheet).setName(sheet.getName()));
  dstSpreadsheet.deleteSheet(temp);

  copyProtectedRanges_(srcSSId, dstSSId);
}