Copying Google Spreadsheet by Removing Container-Bound Script Using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for copying a Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script.

When you want to copy a Google Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script, this could be achieved by using “get” and “create” methods of Sheets API before. The sample script is as follows.

const obj = Sheets.Spreadsheets.get(
  SpreadsheetApp.getActiveSpreadsheet().getId(),
  { fields: "namedRanges,properties,sheets" }
);
Sheets.Spreadsheets.create(obj);

But, in the current stage, I noticed that this cannot be used. Because, in the current stage, the smart chips are implemented. In this case, Sheets API cannot be retrieved them as an object by the current specification. So, I would like to introduce a sample script as the current workaround.

Sample script

This script uses Sheets API. So, please enable Sheets API at Advanced Google services. And, 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);
}

  • 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

 Share!