CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Daniel Weigel
Daniel Weigel

Reputation: 1137

Make a copy of a spreadsheet while keeping protected ranges

I hope this won't make me lose points but I have been looking everywhere and I can't find an answer:

  1. I have a spreadsheet with protected ranges.
  2. I make a copy of the spreadsheet.
  3. Protected ranges are gone in the copy.

Is there a way of keeping the protected ranges in the new copy ?

If it is not possible, maybe use

var protection = spreadsheet.getRange('D1:G9').protect();

on the new copy?

But then I have no idea how to assign it to the new copy, as the script would be in the original spreadsheet and the copy of the spreadsheet has no Id before being created ...

Upvotes: -1

Views: 3279

Answers (1)

Tanaike
Tanaike

Reputation: 201643

  • When a sheet of Spreadsheet is copied to other Spreadsheet, you want to also copy the protected ranges.
    • a spreadsheet of Make a copy of a spreadsheet while keeping protected ranges means a sheet in a Spreadsheet.

When Spreadsheet is copied, the protected ranges are also copied. So I understood like above. If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Issue:

Unfortunately, in the current stage, when a sheet is copied to Spreadsheet using copyTo() of Class Sheet and the method of copyTo of Sheets API, the protected ranges are not copied. This situation is the same with the manual copy.

Workaround:

In order to copy the protected ranges, the protected ranges are required to be copied to the copied sheet. But when Spreadsheet Service is used for this situation, the script becomes a bit complicate. So in this answer, as a workaround, I used Sheets API. In this case, the object retrieved by the get method is almost the same with the request body of the method of batchRequest. So I used this.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google Services. And also, please set the variables of srcId, srcSheetName and dstId.

var srcId = "###"; // Please set source Spreadsheet ID.
var srcSheetName = "###"; // Please set source sheet name you want to copy.
var dstId = "###"; // Please set destination Spreadsheet ID.

var src = SpreadsheetApp.openById(srcId);
var dst = SpreadsheetApp.openById(dstId);
var sheetId = src.getSheetByName(srcSheetName).copyTo(dst).getSheetId();
var protectedRanges = Sheets.Spreadsheets.get(srcId, {ranges: [srcSheetName], fields: "sheets/protectedRanges"});
var requests = protectedRanges.sheets[0].protectedRanges.map(function(e) {
  e.range.sheetId = sheetId;
  return {addProtectedRange: {protectedRange: e}};
});
Sheets.Spreadsheets.batchUpdate({requests: requests}, dstId);

References:

Added:

About the following question,

how can we adapt if the sheet itself is protected except some ranges?

In this case, the property of unprotectedRanges is required to be included as follows.

Sample script

var srcId = "###"; // Please set source Spreadsheet ID.
var srcSheetName = "###"; // Please set source sheet name you want to copy.
var dstId = "###"; // Please set destination Spreadsheet ID.

var [src, dst] = [srcId, dstId].map(s => SpreadsheetApp.openById(s));
var sheetId = src.getSheetByName(srcSheetName).copyTo(dst).getSheetId();
var protectedRanges = Sheets.Spreadsheets.get(srcId, { ranges: [srcSheetName], fields: "sheets/protectedRanges" });

var requests = protectedRanges.sheets[0].protectedRanges.map(e => {
  e.range.sheetId = sheetId;
  if (e.unprotectedRanges) {
    e.unprotectedRanges.forEach(f => f.sheetId = sheetId);
  }
  return { addProtectedRange: { protectedRange: e } };
});
Sheets.Spreadsheets.batchUpdate({ requests }, dstId);

Upvotes: 8

Related Questions