How to transfer ownership of a Google spreadsheet and to be a viewer

user_11485365 0
Viewed 752

Is it possible to hand over the owner permission to the other user and change the former owner to a viewer using apps script?

Transferring the owner permission to another user and being a viewer

I'm trying to make a script that hands over the owner to another user and changes the former owner to a viewer or prohibits the former owner from editing the Google Spreadsheet. The problem is the script is run by the former owner and the script cannot remove the edit permission of the former owner.

What I have tried

I tried setViewer(), sheet.protect(), and contentRestictions.readOnly but all of them was not a viable solution

  1. removeEditors() and setViewer()

setViewer() method to an editors has no effects, and after applying removeEditors() to the former owner(after changing the owner, of course) the script cannot execute setViewer() since it does not have permission anymore.

  1. sheet.protect()

the method gives the permission to edit the protected range for the owner and the user who runs the script. not eligible.

  1. contentRestrictions.readOnly

the restriction can be unlocked by editors. not feasible.

  • Code for contentRestrictions:

    function setReadOnly() { var ss = SpreadsheetApp.getActive(); var supervisor = 'xxxxxxxx@gmail.com'; file = DriveApp.getFileById(ss.getId()); try { Drive.Files.update({ 'writersCanShare': false, 'copyRequiresWriterPermission': true, 'contentRestrictions': [{ 'readOnly': true }] }, ss.getId()); file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.NONE); file.setOwner(supervisor); } catch (e) { console.log(e); } }

I recently found the contentRestrictions.readOnly has been implemented to Google Drive API. While I thought it would be a great way to restrict the editors from modifying the file content, I realized the users who are editors can "UNLOCK" the file with a click even it was locked by the owner.

I don't understand the use of this property if the restriction can be resolved by editors. The explanation in the docs says that we can use this field to prevent modifications to the title, uploading a new revision, and addition of comments. However, editors easily unlock the files, and the viewers cannot modify the file anyway.

google-drive-api google-apps-script google-sheets google-docs
2 Answers

I believe your current situation and your goal as follows.

  • You have a Google Spreadsheet and a script. You are the owner of them.
    • From your script, the script is the container-bound script of the Spreadsheet.
  • When you run the script, you want to transfer the owner of Spreadsheet to other user.
  • After the owner was transferred, you want to keep to have the permission for the Spreadsheet as the reader which is not the writer.

Modification points:

  • In this case, I thought that the methods of "Permissions: insert" and "Permissions: update" of Drive API (in this case, Drive API is used with Advanced Google services.) might be able to be used for achieving your goal.
  • The flow of my proposing script is as follows.
  1. Retrieve the permission ID for you.
    • In the current stage, you are the owner of the file.
  2. Transfer the owner of file (in your case, it's Spreadsheet.).
    • In the current stage, you are not the owner.3. Update your permission from writer to reader.
  3. Create a shortcut of the owner-transferred Spreadsheet to the root folder.
    • Because, when the owner is transferred, the file can be seen at the folder of "Shared with me".
    • If you are not required to create the shortcut, please remove this part.

This flow is reflected to a script, it becomes as follows.

Modified script:

Please copy and paste the following script to the script editor of the container-bound script of Google Spreadsheet. And, before you use this script, please enable Drive API at Advanced Google services.

function myFunction() {
  const email = "###"; // Please set the email. The owner of the file is transferred to this email.

  const fileId = SpreadsheetApp.getActive().getId();
  
  // 1. Retrieve the permission ID for you. In the current stage, you are the owner of the file.
  const permissionId = Drive.Permissions.list(fileId).items[0].id;

  // 2. Transfer the owner of file (in your case, it's Spreadsheet.). In the current stage, you are not the owner.
  Drive.Permissions.insert({role: "owner", type: "user", value: email}, fileId, {sendNotificationEmail: false, moveToNewOwnersRoot: true});

  // 3. Update your permission from `writer` to `reader`.
  Drive.Permissions.update({role: "reader"}, fileId, permissionId);

  // 4. Create a shortcut of the owner-transferred Spreadsheet to the root folder. Because, when the owner is transferred, the file can be seen at the folder of "Shared with me".
  Drive.Files.insert({mimeType: MimeType.SHORTCUT, shortcutDetails: {targetId: fileId}}, null);
}

Note:

  • When above script is run the owner of Spreadsheet is changed to email. And your permission becomes the reader. So in this case, you cannot see the script of Spreadsheet. Please be careful this.
  • In this case, after the owner of Spreadsheet is changed, you cannot edit the Spreadsheet and the script. So please be careful this.

References:

user_71086530

I tested my own file below, only me have access to the file.

Code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var file = DriveApp.getFileById(ssId);
  var owner = file.getOwner();

  var new_owner = 'new_owner@gmail.com';
  file.setOwner(new_owner);
  file.removeEditor(owner);    // After this line, you can't view/edit the file
  Logger.log(file.getOwner()); // Will fail
}

Things noted during testing:

  • Running removeEditor on yourself after setting another user as owner will restrict you from even viewing the file as per testing
  • Owner is immune with removeEditor (can't be removed by such)
  • Only the owner can set the ownership to other users

Please see a similar question that has an answer pointing to this, it will give you some insights regarding Google Apps Domain-Wide Delegation of Authority.

user_161186860
Related