Search code examples
google-apps-scriptgoogle-sheets

How to protect sheet and set editors to same as another protected sheet


I have a script that takes a template sheet, copies it, renames it, and makes various other changes. The template sheet is protected (whole sheet), with a specific list of allowed editors. When the copy is made, the new sheet doesn't keep the protection. I am trying to expand my script to set the protection on the new sheet the same as it is on the template sheet.

What I've tried is reading the list of editors from both sheets, then looping over the list for the new sheet, and removing any that aren't also present on the template sheet. But it isn't working, no one is removed and all users with access to the spreadsheet are still able to edit the new sheet, even though the logs show every user being removed, even the ones that should stay.

My code so far:

function setProtection(templateSheet, newSheet) {
  // Protect new sheet
  var newProtection = newSheet.protect();
  var templateProtection = templateSheet.protect();
  var templateEditors = templateProtection.getEditors();
  Logger.log("templateEditors: " + templateEditors)
  var newEditors = newProtection.getEditors();
  Logger.log("newEditors: " + newEditors)
  for (const editor of newEditors) {
    Logger.log("Checking editor: " + editor)
    if (!templateEditors.includes(editor)) {
      Logger.log("Template editors does not contain " + editor + ", removing from new sheet")
      newProtection.removeEditor(editor)
    }
  }
}

Solution

  • The issue here is that newEditors and templateEditors are arrays of Objects, not primitive types. Objects only compare as equal if they have the same reference; having the same value of a parameter is not enough.

    Adjusting the code to compare the email addresses assigned to each Object rather than comparing the Objects themselves fixes the issue:

    for (const editor of newEditors) {
      Logger.log("Checking editor: " + editor)
      if (!templateEditors.some(e => e.getEmail() === editor.getEmail())) {
        Logger.log("Template editors does not contain " + editor + ", removing from new sheet")
        newProtection.removeEditor(editor)
      }
    }