I have a script that should enable us to have an hyperlink to a folderID identified by the script.
Here is the script:
function getParentFolderName() { var fileId = SpreadsheetApp.getActiveSpreadsheet().getId(); var file = DriveApp.getFileById(fileId); var parentFolders = file.getParents(); if (parentFolders.hasNext()) { var parentFolder = parentFolders.next(); var parentFolderName = parentFolder.getName(); Logger.log("Parent folder name: " + parentFolderName); return parentFolderName; } return null; }
function getFolderId(subFolderName) { if (!subFolderName) { Logger.log("Subfolder name is null or empty."); return null; }
var parentFolderName = getParentFolderName(); if (!parentFolderName) { Logger.log("Parent folder name is null."); return null; }
var parentFolders = DriveApp.getFoldersByName(parentFolderName); while (parentFolders.hasNext()) { var parentFolder = parentFolders.next(); var subFolders = parentFolder.getFoldersByName(subFolderName); if (subFolders.hasNext()) { var subFolder = subFolders.next(); var subFolderId = subFolder.getId(); Logger.log("Subfolder ID: " + subFolderId); return subFolderId; } }
Logger.log("Subfolder not found: " + subFolderName); return null; }
Using this formula on a cell on that spreadsheet, should return a link to that Subfolder:
=HYPERLINK("https://drive.google.com/drive/folders/" & getFolderId("Subfolder Name"), "Link Text")
However, I input the subfolder name correctly and it doesn't add any link... So I tried to have a simple formula for testing purposes:
=getFolderId("Subfolder Name")
It returns this error message: "Exception: You do not have permission to call DriveApp.getFileById. Required permissions: (https://www.googleapis.com/auth/drive.readonly || https://www.googleapis.com/auth/drive) (line 3)."
This seems to be the issue. I tried to add the scopes on the appsscript.json manifest file but it still doesn't work:
"oauthScopes": [ "https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/drive.readonly" ]
How can I fix this?