Protecting Cells of Spreadsheet that Users Copied from Your Google Drive to User's Google Drive using Google Apps Script

GasTips
Google Apps Script / Web Apps / Spreadsheet / Protect

Gists

This is the method for protecting cells of Spreadsheet that users copied from your Google Drive to user’s Google Drive using Google Apps Script.

Situation:

This method supposes the following situation.

  • You want to make users copy a Spreadsheet on your Google Drive to user’s Google Drive. - Your Spreadsheet has several protected ranges. - Your Spreadsheet is shared with the user. - User doesn’t have own folder shared with you.
  • You want to protect the ranges of Spreadsheet for the user. Namely, you want to remove the user from the editor of protected ranges.
  • You want to achieve this using Google Apps Script.

Issue:

In above situation, I think that the following points are the bottleneck.

  • When the Spreadsheet in your Google Drive is copied to user’s Google Drive, it is required to be run by the user’s account.
  • When the Spreadsheet is copied to user’s Google Drive by user, the owner of protected ranges becomes the user. So the user can edit the protected ranges. And the user cannot remove the user from the editor by the user. - In order to make the user not edit the protected ranges, it is required to change the owner of Spreadsheet, and then, it is required to remove the user from the editor of the protected ranges.
  • Here, 2 more issues occur. 1. In order to change the owner of Spreadsheet to you, it is required to be run by the user’s account. 2. In order to remove the user from the editor of the protected ranges, it is required to be run by your account which is not the user’s account.

As the result, in order to achieve above your goal using a script, the script is required to be run by the user and you.

Workaround:

In my workaround, using 2 scripts and 2 account, it achieves above goal. The point of this workaround is to using 2 Web Apps. Please think of this as just one of several workarounds. The flow of this workaround is as follows.

Flow of workaround:

In this workaround, 2 Web Apps of Web Apps “A” and Web Apps “B” are used.

  1. User accesses to Web Apps “A” that you deployed.
    • In this case, Web Apps is executed as the user.
  2. When the Web Apps “A” is run,
    1. Your Spreadsheet is copied from your Google Drive to user’s Google Drive.
    2. Owner of copied Spreadsheet is the user. So change the owner from the user to you.
    3. Access to Web Apps “B” in the script.
      • In this case, Web Apps is executed as you.
    4. When the Web Apps “B” is run,
      1. The user is removed from the editor of the protected ranges.

Usage of scripts:

Preparation:

Please create 2 projects of the standalone script type.

  • One is for Web Apps “A”.
  • Another is for Web Apps “B”.

Standalone script for Web Apps “B”:

At first, please deploy Web Apps “B”, because the URL of Web Apps “B” is used at the script of Web Apps “A”. Please copy the following script and deploy Web Apps as Execute the app as: **Me** and Who has access to the app: **Anyone, even anonymous**. About how to deploy Web Apps, please check here. And please copy the URL of this Web Apps “B” and paste it to url of the script of Web Apps “A”.

If you want to protect the protected ranges which have alrady installed in the Spreadsheet, please use the following script.

function doGet(e) {
  var id = e.parameter.id;
  if (id) {
    var owner = Session.getActiveUser().getEmail();
    var ss = SpreadsheetApp.openById(id);
    var protectedRanges = ss.getProtections(
      SpreadsheetApp.ProtectionType.RANGE
    );
    protectedRanges.forEach(function(p) {
      var removes = p.getEditors().filter(function(e) {
        return e.getEmail() != owner;
      });
      p.removeEditors(removes);
    });
  }
  return ContentService.createTextOutput("Done");
}

If you want to protect the ranges (cells) when the user copies the Spreadsheet, please use the following script.

function doGet(e) {
  var protectRange = "Sheet1!A1:B5"; // Sample protected range.

  var id = e.parameter.id;
  if (id) {
    var owner = Session.getActiveUser().getEmail();
    var ss = SpreadsheetApp.openById(id);
    var p = ss.getRange(protectRange).protect();
    var removes = p.getEditors().filter(function(e) {
      return e.getEmail() != owner;
    });
    p.removeEditors(removes);
  }
  return ContentService.createTextOutput("Done");
}

Standalone script for Web Apps “A”:

Please copy the following script and set the variables of srcSpreadsheetId and url. In this case, url is the URL retrieved at Web Apps “B”. Then, please deploy Web Apps as Execute the app as: **User accessing the web app** and Who has access to the app: **Anyone**. About how to deploy Web Apps, please check here. And please copy the URL of this Web Apps “A”. This URL is used for accessing by the user.

function doGet() {
  var srcSpreadsheetId = "###"; // Please set your source Spreadsheet ID.
  var url = "###"; // Please set the URL of Web Apps B.

  var srcSS = SpreadsheetApp.openById(srcSpreadsheetId);
  var dstSS = srcSS.copy(srcSS.getName());
  var file = DriveApp.getFileById(dstSS.getId()).setOwner(
    srcSS.getOwner().getEmail()
  );
  var res = UrlFetchApp.fetch(url + "?id=" + file.getId(), {
    muteHttpExceptions: true
  }); // Here, run the script of Web Apps "B".
  return ContentService.createTextOutput(res.getContentText());
}

Run:

When the user uses above scripts, please make the user access to the URL of Web Apps “A” using own browser. When the user accesses to Web Apps “A”, the login screen of Google is displayed. By logging in to Google, the authorization screen is shows. By authorizing it, the script of Web Apps “A” is run, and Web Apps “B” is run by the Web Apps “A”. The authorization is required to do only one time.

When “Done” is shown in user’s browser, the user can see the copied Spreadsheet at the root folder.

By this, your Spreadsheet in your Google Drive is copied to the user’s Google Drive, and the owner of Spreadsheet is modified to you, and then, the user is removed from the editor of the protected ranges. So the user get to not be able to edit the protected ranges.

How to deploy Web Apps:

  1. On the script editor, Open a dialog box by “Publish” -> “Deploy as web app”.
  2. Select “User accessing the web app” or “Me” for “Execute the app as:”.
  3. Select “Anyone” or “Anyone, even anonymous” for “Who has access to the app:”.
  4. Click “Deploy” button as new “Project version”.
  5. Automatically open a dialog box of “Authorization required”.
    1. Click “Review Permissions”.
    2. Select own account.
    3. Click “Advanced” at “This app isn’t verified”.
    4. Click “Go to ### project name ###(unsafe)”
    5. Click “Allow” button.
  6. Copy “Current web app URL:”.
    • It’s like https://script.google.com/macros/s/#####/exec.
  7. Click “OK”.

IMPORTANT:

  1. When you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. When the Web Apps is not redeployed even when the script is modified, the latest script is not used. Please be careful this.
  2. Please confirm that the Spreadsheet, that you want to make the user copy, is shared with the user.

References:

 Share!