Copying gsheet without bound apps script project

Question

I have a google sheet with a google apps script project bound to it.

Using copy() and makeCopy() copy the gsheet with the bound script.

How can I programmatically, make copies of this spreadsheet without carrying the bound apps script project into the new copy?

Kele

You could remove the script using Apps Script API – Sneed

@Sneed i dont find the apps script API in the editor services , but does sheets api could have somethiing to help me achieive this? – Kele

developers.google.com/apps-script/api/reference/rest – Sneed

Answer 1

I believe your goal is as follows.

• You want to copy a Spreadsheet without including the container-bound script.

Issue and workaround:

In the current stage, there are the following issues.

• copy method of Class Spreadsheet and makeCopy method of Class File cannot directly achieve your expected result. The container-bound script is also copied.

• After the smart chips were added, it seems that the copy of Spreadsheet by copying the JSON object retrieved from Spreadsheet with Sheets API cannot be directly achieved.

• Although when Drive.Files.remove("### script ID of container-bound script ###") is used, the container-bound script in the copied Spreadsheet can be deleted. But, unfortunately, in the current stage, the script ID of the container-bound script cannot be directly retrieved after a Google Spreadsheet was copied. Or, it is so difficult to do it.

From the above situation, in this answer, I would like to propose a workaround. The flow for the workaround is as follows.

1. Create a new Google Spreadsheet.

2. Copy all sheets from the source Spreadsheet to the created Spreadsheet using copyTo of Class Sheet.

   • By this, the smart chips can be also copied.

3. Copy the protected ranges and sheets using Sheets API.

   • Because the protected information cannot be copied with copyTo of Class Sheet.

When this flow is reflected in a sample script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of the source Google Spreadsheet and save the script. Before you use this script, please enable Sheets API at Advanced Google services.

// 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.getActiveSpreadsheet();
  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);
}

• When this script is run, a copied Spreadsheet of Copied ### is created in the same folder of the source Spreadsheet. And, the copied Spreadsheet has no container-bound script.

• You can modify the copied Spreadsheet name by modifying Copied ${srcSpreadsheet.getName()}.

• If you want to create the copied Spreadsheet to the specific folder, please modify DriveApp.getFileById(dstSSId).moveTo(DriveApp.getFileById(srcSSId).getParents().next()); to DriveApp.getFileById(dstSSId).moveTo(DriveApp.getFolderById("###folderId###"));.

Note:

• This sample script is a simple script. I think that most data can be copied to the destination Spreadsheet. But, if there are some uncopied data, the script might be required to be modified. Please be careful about this.

• If your source Spreadsheet has no protected ranges and sheets, copyProtectedRanges_(srcSSId, dstSSId); can be removed.

References:

• copyTo(spreadsheet)
• Method: spreadsheets.get
• Method: spreadsheets.batchUpdate

Hatpin