Home > front end >  How do I duplicate a Google Sheet with Apps Script that doesn't copy over the attached script f
How do I duplicate a Google Sheet with Apps Script that doesn't copy over the attached script f

Time:12-16

I can duplicate the activespreadsheet to a new file on my Google Drive with App Script ok. However, it copies the attached scripts. How can I duplicate the spreadsheet without it bringing in all the Apps Scripts?

I can duplicate the spreadsheet ok. But when I open the newly duplicated file, it has all my scripts attached. And the Menus. I just want to duplicate the spreadsheet only.

CodePudding user response:

Create a new blank spreadsheet and use Sheet.copyTo() to copy each sheet in the source spreadsheet into the new spreadsheet in turn.

CodePudding user response:

Copy a spreadsheet without scripts:

function copySpreadsheetWithoutScripts() {
  const ss = SpreadsheetApp.getActive();
  const dss = SpreadsheetApp.create(ss.getName());
  dss.getSheets()[0].setName("~~~");
  ss.getSheets().forEach(sh => {
    sh.copyTo(dss).setName(sh.getName());
  });
  dss.deleteSheet(dss.getSheetByName("~~~"));
}

If you wish to copy it to a destination folder

function copySpreadsheetWithoutScripts() {
  const df = DriveApp.getFolderById(gobj.globals.folder1id);
  const ss = SpreadsheetApp.getActive();
  const dss = SpreadsheetApp.create(ss.getName());
  dss.getSheets()[0].setName("~~~");
  ss.getSheets().forEach(sh => {
    sh.copyTo(dss).setName(sh.getName());
  });
  dss.deleteSheet(dss.getSheetByName("~~~"));
  Drive.Files.update({"parents": [{"id": df.getId()}]}, dss.getId())
}

Note the latter version requires SpreadsheetApp.getActive() project to be a GCP project.

  • Related