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