2
votes
  1. There is a sheet that is shared with writing permissions to users A and B.
  2. Column X is protected and can be edited only by user A.
  3. User B should use a script with user A permissions to edit column X.

From reading the various documentation (https://developers.google.com/apps-script/guides/services/authorization), I understand that there are two ways to run a script on a google sheet as a different user.

  1. Using the python api (https://developers.google.com/sheets/api/quickstart/python). The disadvantages: either launches from the cmdline, or Google's App Engine which is not free.
  2. Using a WebApp. My problem with that solution, and perhaps this is what I am missing, is how to edit a cell with the doGet and doPost. All the examples that I saw were using the google script to edit the cells, which slammed me back to the permissions issue.
1

1 Answers

4
votes

A possible solution: User A should deploy the web app with settings "execute as me [that is, A]", and choose access level as "anyone" [so that user B can access] or "anyone, even anonymous" to save user B an extra step and allow them to access the app programmatically. To prevent users other than B from editing, A gives B an access token, which B uses in their GET or POST requests. For example: user B accesses the app via GET request to https://..../exec?key=mykey, and the app has code like this:

function doGet(e) {
  if (e.parameter.key == "mykey") {
    var ss = SpreadsheetApp.openById("spreadsheet Id"); // can't use getActiveSpreadsheet in doGet/doPost
    // do something with the spreadsheet
  }
}

Documentation: doGet/doPost event object.