Search code examples
google-apps-scriptgoogle-sheetsexceptiongoogle-drive-api

Exception: Access denied: DriveApp while running script from separate Google Account

I'm looking to create a script that makes a copy of the active spreadsheet, gives edit access to the person who created the sheet, but then sets me as the owner.

This works when I run it using the owner account, but I receive

Exception: Access denied: DriveApp

when triggered from a separate Google account.

I thought my script would add both accounts as editors, then set my personal as the owner.

Any idea why?

function makeCopy() {
  
var title = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();
  SpreadsheetApp.getActive().toast("Your Sheet is being created", "Good Luck!", 5);


  var email = Session.getActiveUser().getEmail(); //get the user's email who ran the script
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); //get the current Spreadsheet's ID
  var copyID = DriveApp.getFileById(ssID).makeCopy().setName(title).getId(); //make a copy of the spreadsheet and retrieve the new ID
  DriveApp.getFileById(copyID).addEditor(email); //add the user as an editor to the new copy
   DriveApp.getFileById(copyID).addEditor("[email protected]")
  DriveApp.getFileById(copyID).setOwner("[email protected]");




var newsheet = SpreadsheetApp.openById(copyID);



// Get the file associated with the sheet
var file = DriveApp.getFileById(newsheet.getId());

// Set the permissions to allow anyone to edit the file
file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);


//var sheet = newsheet.getSheetByName("Sheet");
//sheet.deleteRows(15,3)



// var sheetId = "1234567890abcdefghijklm";

// Open the sheet in a new tab
var url = "https://docs.google.com/spreadsheets/d/" + copyID;
var html = HtmlService.createHtmlOutput('<script>window.open("' + url + '");</script>').setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi().showModelessDialog(html, "Showtime!");


}
Solution

  • Pattern 1:

    Modification points:

    • In the current stage, it seems that the specification for transferring the file ownership has been changed. Ref I thought that this might be the reason for your current issue of Exception: Access denied:.

    When these points are reflected in your script, how about the following modification?

    Modified script:

    Before you test this script, please enable Drive API at Advanced Google services.

    function makeCopy() {
  var title = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();
  SpreadsheetApp.getActive().toast("Your Sheet is being created", "Good Luck!", 5);
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var copyID = DriveApp.getFileById(ssID).makeCopy().setName(title).getId();
  Drive.Permissions.insert({ role: "writer", type: "user", value: "[email protected]", pendingOwner: true }, copyID);
  DriveApp.getFileById(copyID).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);
  var url = "https://docs.google.com/spreadsheets/d/" + copyID;
  var html = HtmlService.createHtmlOutput('<script>window.open("' + url + '");</script>').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModelessDialog(html, "Showtime!");
}
    • When the user of another account runs this script, you (the owner) got an email for transferring the ownership of the file. When you accept it, the owner is transferred.

    References:

    Pattern 2:

    From your following reply,

    but is there a way for the permission to automatically go through? Unfortunately, I won't be able to accept the emails quick enough - I was hoping in the script it can just automatically set the owner. Any thoughts? This is amazing though.

    In this case, as a pattern 2, I would like to propose using Web Apps as I mentioned in my comment.

    Usage:

    1. Prepare Google Apps Script.

    Please copy and paste the following script to the script editor of Spreadsheet and save the script.

    function doGet(e) {
  const { spreadsheetId, email, title, key } = e.parameter;
  if (key == "samplekey") {
    var file = DriveApp.getFileById(spreadsheetId).makeCopy(DriveApp.getRootFolder()).setName(title);
    file.addEditor(email);
    file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);
    return ContentService.createTextOutput(JSON.stringify({ id: file.getId() }));
  }
  return ContentService.createTextOutput(JSON.stringify({ error: "Invalid key." }));
}

function makeCopy() {
  var webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.

  var title = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();
  SpreadsheetApp.getActive().toast("Your Sheet is being created", "Good Luck!", 5);
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var email = Session.getActiveUser().getEmail();
  var res = UrlFetchApp.fetch(`${webAppsUrl}?spreadsheetId=${ssID}&email=${email}&title=${title}&key=samplekey`);
  var obj = JSON.parse(res.getContentText());
  if (obj.error) return;
  var url = "https://docs.google.com/spreadsheets/d/" + obj.id;
  var html = HtmlService.createHtmlOutput('<script>window.open("' + url + '");</script>').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModelessDialog(html, "Showtime!");
}
    • In this sample, the copied file is put to the root folder of your Google Drive. If you want to change this, please modify the above script.

    2. Deploy Web Apps.

    The detailed information can be seen in the official document.

    Please set this using the new IDE of the script editor.

    1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
    2. Please click "Select type" -> "Web App".
    3. Please input the information about the Web App in the fields under "Deployment configuration".
    4. Please select "Me" for "Execute as".
    5. Please select "Anyone" for "Who has access".
    6. Please click "Deploy" button.
    7. When "The Web App requires you to authorize access to your data." is shown, please click "Authorize access" button. And, please authorize the scopes.
    8. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec. This URL is used for your HTML.
    9. Please copy and paste your Web Apps URL to var webAppsUrl = "https://script.google.com/macros/s/###/exec"; of the above script.
    10. Please reflect the latest script to the Web Apps.

    3. Testing.

    When other user who is not you (the owner of Spreadsheet) is run makeCopy(), the active Spreadsheet is copied to your root folder by you (the owner). By this, you are the owner of copied Spreadsheet. And, the executed user is added as a writer.

    Note:

    References: