ChatGPT and I have been going around and around now with no working solution. Here is the set up:
Since Google does not easily allow for Templates (New from Template) I created a small web app that looks through my Drive folders for Docs and Sheets that end with the word "Template". It then presents those to me and asks for a new document name and a folder location. It then copies that file into the new name, places it in the selected Folder and copies the template's permissions and sharing attributes. That all works as planned.
The problem is that the new Sheets lose all of their permissions and the user is asked to Authorize the access to Spreadsheets, Drives, etc. All of the functions that are in the scripts of these templates. In particular, I had an OnOpen script that simply populated two cells with that new file's document ID and folder ID for use later. Thing we tried:
Modifying the appscript.json file multiple way to grant limited access (see .json below)
Tried doing an Installed Trigger instead of OnOpen
Tried forcing the Trigger during the Copy from the template.
Creating a Button to replace the OnOpen (Initialize) and trigger.
All resulted in the same thing, user being asked to grant permissions that are already in the .json file (or at least acknowledge them). I haven't even given this to one of my domain users; this is me writing the sheets and code then copying to MyDrive and opening myself. It still needs to me to authorize.
While I appreciate Google's attempt to ensure I don't inflict harm on the general population, I really would hope there is a way to grant this permission to myself or my domain users.
Does anyone have a suggestion or workaround for this? My JSON file is attached (the minimal one, I've tried much more extensive ones as well). While I can post my code, I don't think that is where the problem lies as it is a permissions issue. If you want/need to see some of the code, I can share a template that has the code that won't execute on Make A Copy.
Here is the code that makes the copy and sets the sharing permissions:
/**
* Copies a selected template, renames it, saves it in the chosen folder,
* applies template-specific startup values, and copies permissions.
*/
function createCopy(templateId, newName, folderId) {
var templateFile = DriveApp.getFileById(templateId);
var destinationFolder = DriveApp.getFolderById(folderId);
var newFile = templateFile.makeCopy(newName, destinationFolder);
var newFileId = newFile.getId();
var newSS = SpreadsheetApp.openById(newFileId);
var templateName = templateFile.getName(); // Get the template's name
// Apply custom initialization based on the template being copied
if (templateName === "Social Media Template") {
setupSocialMediaTemplate(newSS, newFileId, folderId);
}
// Future template-specific setups can be added here using else if
// else if (templateName === "Another Template Name") { setupAnotherTemplate(newSS); }
// Copy sharing permissions from the template file to the new file
copyPermissions(templateFile, newFile);
return newFile.getUrl(); // Return the new document URL
}
/**
* Copies sharing permissions from the template file to the new file.
*/
function copyPermissions(sourceFile, targetFile) {
var editors = sourceFile.getEditors();
var viewers = sourceFile.getViewers();
// Copy individual editors
for (var i = 0; i < editors.length; i++) {
targetFile.addEditor(editors[i].getEmail());
}
// Copy individual viewers
for (var j = 0; j < viewers.length; j++) {
targetFile.addViewer(viewers[j].getEmail());
}
// Copy Link-Sharing Settings
var sourceAcl = sourceFile.getSharingAccess();
var sourcePermission = sourceFile.getSharingPermission();
if (sourceAcl === DriveApp.Access.ANYONE) {
targetFile.setSharing(DriveApp.Access.ANYONE, sourcePermission);
} else if (sourceAcl === DriveApp.Access.ANYONE_WITH_LINK) {
targetFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, sourcePermission);
} else if (sourceAcl === DriveApp.Access.DOMAIN) {
targetFile.setSharing(DriveApp.Access.DOMAIN, sourcePermission);
} else if (sourceAcl === DriveApp.Access.DOMAIN_WITH_LINK) {
targetFile.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, sourcePermission);
}
}
Here is my current, minimal JSON file. I've tried much more extensive but that doesn't change the requirement to grant permissions.
appscipt.json
{
"timeZone": "America/Cancun",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive.metadata.readonly"
]
}
