Copying Protections for Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet / protection

Gists

This is a sample script for copying the protections for Spreadsheet using Google Apps Script. When several protections of the sheet protection and the range protection are set to a Google Spreadsheet and the Spreadsheet is copied using the script and the manual copy with the browser, unfortunately, the protections of ranges are not copied. And also, the protections of sheets can be copied. But, the editor emails are not included. It seems that this is the current specification.

In this post, I would like to introduce a sample script for copying all protection conditions.

Sample script

Please add the following function to your script. And, please enable Sheets API at Advanced Google services.

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);
  Sheets.Spreadsheets.batchUpdate({ requests: requests }, dstId);
}

// Please run this script.
function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcId = ss.getId();
  const dstId = ss.copy("destSpreadsheet").getId();
  copyProtectedRanges_(srcId, dstId);
}
  • srcId and dstId are the source Spreadsheet ID including the protected condistions and the destination Spreadsheet ID, respectively.
  • This sample script copies the protected conditions for sheets and ranges. When the editor emails have been included in the protected conditions, please share the Spreadsheet with the users. This sample script doesn’t automatically share the destination Spreadsheet with he users. Please be careful this.

References

 Share!