GoogleAppsScript giving error in google sheet

[Ian van Zyl]

Hi there

I am trying to get the googlesheet fileID for a file that is passed as a parameter to a custom function.

In the google sheet the following formula is entered:

=getFileID("/Optimal Dashboards/Clients/OPTIMATE/Access_Control_List")

The formula gives the following error:

"PERMISSION REQUIRED: Please run this function from the script editor first to authorize Drive access.
Error: No OAuth token available"

However, when testing the script from the editor, it works 100% and returns the following fileID

18LyVhqey-HhY99gYax0tU_ok6qphEX78JJYrYZhJEtQ

The following lines are included in the appsscript.json file:

"oauthScopes": [
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/drive.metadata",
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ],
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "MYSELF"
  }
 
The service "Drive" and "Sheets" are enabled in the AppsScript editor.

The same services are defined under Google Cloud.

I have deleted, re-created and re-authorized the setups countless times and I am at a total loss as how to fix this issue.

Any assistance / guidance would greatly be appreciated.

Herewith the script that I use:

/**
 * getFileID function to get the file ID of a Google Sheet.
 * This function is designed to be deployed as a web app.
 *
 * @param {path} The file path passed to the getFileID function.
 * @return {files.next().getId()} The the file ID.
 */
// Function to get the google sheet fileID
//
function getFileID(path) {

  // Logger.log(path);
 
  // First verify we have Drive access
  try {
    const testToken = ScriptApp.getOAuthToken();
    if (!testToken) throw new Error("No OAuth token available");
   
    // Explicit test of Drive access
    const root = DriveApp.getRootFolder();
    if (!root) throw new Error("Couldn't access root folder");
  } catch (e) {
    return "PERMISSION REQUIRED: Please run this function from the script editor first to authorize Drive access.\nError: " + e.message;
  }

  try {
    // Validate input
    if (!path || typeof path !== 'string') {
      Logger.log(`Path must be a string: "${path}"`);  
      throw new Error("Path must be a text string");
    }
   
    const cleanPath = path.replace(/^\/|\/$/g, '');
    const pathParts = cleanPath.split('/').filter(Boolean);
   
    if (pathParts.length === 0) {
      Logger.log(`Empty path provided: "${pathParts}"`);
      throw new Error("Empty path provided");
    }
   
    let currentFolder = DriveApp.getRootFolder();
   
    // Navigate through each folder
    for (let i = 0; i < pathParts.length - 1; i++) {
      const folders = currentFolder.getFoldersByName(pathParts[i]);
      if (!folders.hasNext()) {
        Logger.log(`Folder not found: "${pathParts[i]}"`);  
        throw new Error(`Folder not found: "${pathParts[i]}"`);
      }
      currentFolder = folders.next();
    }
   
    // Find the file
    const fileName = pathParts[pathParts.length - 1];
    const files = currentFolder.getFilesByName(fileName);

    if (!files.hasNext()) {
      Logger.log(`File not found: "${fileName}"`);
      throw new Error(`File not found: "${fileName}"`);
    }
   
    // Logger.log(files.next().getId());

    return files.next().getId();
  } catch (e) {
    return `ERROR: ${e.message}`;
  }
}