CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Ryan Richards
Ryan Richards

Reputation: 75

Google Sheets View Only Protection for User Running App Script

I am sharing a set of Google Sheet documents with other users. I am the owner of all the spreadsheets and they are housed on my Google Drive. Every user has their own dedicated sheet. Users need to be able to run scripts on their sheet that collect and manipulate data from the sheet, which means the sheet needs to be unprotected while the scripts run. However, at the end end of the script, I want to protect the sheet again so that the user running the script is unable to edit the protected cells once the script is done running.

I am able to easily remove the protections from the sheet at the beginning of the script, but I am unable to protect the sheet again via the script without the user who is running the script being listed as an editor. I have tried using the "removeEditors" function to no avail (see below for basic example for one of the protected ranges).

var userEmail = Session.getActiveUser().getEmail();

var protection = newPickSheet.getRange('A1:M3').protect();
protection.removeEditor(userEmail);

For your reference, here is the code I'm using at the beginning of the script to remove all protections:

var protections = newPickSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

for(var i = 0; i < protections.length; i++){
  protections[i].remove();
}

To summarize, I need my script to:

  1. Remove all protections
  2. Execute the main part of the script
  3. Re-add all protections so that only me, the sheet owner, but NOT the user running the script, has edit ability.

I was able to accomplish this very easily using VBA when deploying Excel Macros, but it seems this is not nearly as simple using App Scripts.

If anyone can help me figure out a solution, it would be greatly appreciated.

Upvotes: 2

Views: 3003

Answers (2)

Zahra Hnn
Zahra Hnn

Reputation: 149

I did some modifications on Tanaike's answer, because of this error: Exception: Request failed for https://script.google.com returned code 404. Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to examine full response)

Just change these lines in main():

  const options = {
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() }
  }

  var url="https://script.google.com/URL/exec";
  UrlFetchApp.fetch(url+"?key=removeprotect",options);

  // do Something: Please put the script of "Execute the main part of the script" here.

  SpreadsheetApp.flush(); // This is required to be here.
  UrlFetchApp.fetch(url+"?key=addprotect",options);

It removed the error and now my codes works as expected! To my understanding the main problem was & in url , in this case we have to use ?. Also ScriptApp.getService().getUrl() has to be changed.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201378

  • You want to run mainly 3 functions in one function.
    1. Remove the protected the range of newPickSheet.getRange('A1:M3').
    2. Run a script for editing cell in the range.
    3. Protect the range.
  • You want to run the script by clicking run button which is put on Spreadsheet. You don't want to use the event trigger.

If my understanding is correct, I think that the important point is who run each function. So how about this workaround? I think that there are several workarounds for your situation. So please think of this as just one of them.

Modification points:

  • In your situation, in order to protect a range from users, it is required to run the function for protecting the range as owner. Because the protected range is created by owner, when the protected range is removed, it is required to also run the function as owner.
    • Here, in this workaround, it supposes that the script of Execute the main part of the script includes the methods depending on user.
  • When the script is run with clicking the run button by each user, the script is run as each user. This is the important point for this situation. In order to run the part of script as owner, I used Web Apps here. By using Web Apps, the script for removing and adding the protected range can be run as owner.

By above points, the flow of this workaround is as follows. 1. Remove the protected range by owner using Web Apps. 2. Run the script of Execute the main part of the script by each user. 3. Protect the range by owner using Web Apps.

Preparation for using modified script:

Please run the following flow before you use the modified script.

  1. Copy and paste the modified script to the script editor.
    • This script supposes that you are using the container-bound script of the Spreadsheet.
    • In this modified script, I used main() for the main function. If you are using other name, please modify it.
    • Please set ##### of var newPickSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("#####");.
  2. Put the script of "Execute the main part of the script".
  3. Deploy Web Apps.
    1. On the script editor, open "Publish" -> "Deploy as Web Apps".
    2. Set "Project version" as new. Please input freely to "Describe what has changed".
    3. Set "Execute the app as:" to "Me".
    4. Set "Who has access to the app:" to "Anyone". By this, each user can access to Web Apps using own access token.
    5. Click "Deploy" or "Update" button. By this, Web Apps is deployed.
      • When you modify the script after Web Apps was deployed, please redeploy as new version. By this, the latest script is reflected to Web Apps. This is an important point for using Web Apps.

Modified script:

In this modified script, I used main() for the main function. If you are using other name, please modify it.

// This is the main function. Please set this function to the run button on Spreadsheet.
function main() {
  // DriveApp.getFiles(); // This is a dummy method for detecting a scope by the script editor.

  var url = ScriptApp.getService().getUrl() + "?access_token=" + ScriptApp.getOAuthToken();
  UrlFetchApp.fetch(url + "&key=removeprotect"); // Remove protected range


  // do Something: Please put the script of "Execute the main part of the script" here.


  SpreadsheetApp.flush(); // This is required to be here.
  UrlFetchApp.fetch(url + "&key=addprotect"); // Add protected range
}

function doGet(e) {
  var newPickSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("#####"); // Please set here.
  if (e.parameter.key == "removeprotect") {
    // Remove protected range.
    var protections = newPickSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
      protections[i].remove();
    }
  } else {
    // Add protected range.
    var ownersEmail = Session.getActiveUser().getEmail();
    var protection = newPickSheet.getRange('A1:M3').protect();
    var editors = protection.getEditors();
    for (var i = 0; i < editors.length; i++) {
      var email = editors[i].getEmail();
      if (email != ownersEmail) protection.removeEditor(email);
    }
  }
  return ContentService.createTextOutput("ok");
}

Note:

  • In this modified script, the range of "A1:M3" in the sheet of newPickSheet. newPickSheet is declared as var newPickSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("#####"). If you want to modify this, please modify to your situation.
  • In my environment, when user clicks the run button soon, after user opens the shared Spreadsheet, there was sometimes the case that the function of the button cannot be found. In this case, please wait for completely loading the Spreadsheet.
  • This is a simple sample script. So please modify this to your situation.

References:

Upvotes: 4

Related Questions