Google Apps Script -- Error moving file: Exception: Access denied: DriveApp. But all permissions are set

Question

Beginner programmer here. Been looking for ways to optimize my small tutoring buisness, and one of the ways I'm looking for is to automatically move and sort files into student folders given a name. I am running my formula in Google Sheets titled "=moveJamboardsToStudentFolder(E2)". The function "moveJamboardsToStudentFolder" is defined in Google Apps Script. It seems that my code is working as intended (checked with logger statements) except for the following errors:

1. In Google Sheets:

Exception: You do not have permission to call DriveApp.getFoldersByName. Required permissions: (https://www.googleapis.com/auth/drive.readonly || https://www.googleapis.com/auth/drive) (line 6).

2. In Apps Script:

4:27:46 AM Info Error moving file: Aadi 1/2. Exception: Access denied: DriveApp. 4:27:47 AM Info Error moving file: 1/3 Aadi. Exception: Access denied: DriveApp. 4:27:47 AM Info Error moving file: Aadi 12/26. Exception: Access denied: DriveApp.

Here, "Aadi" was the test student name used. The 3 files above were successfully found, and so was the destination folder. The only step that fails is the actual permissions.

I've dug around and not much has helped-- most of the explanations went over my head. I tried manually inputting the "oauthScopes" in the appscripts.json file to no success amonst other checks.

Anyone know why permissions are getting in the way? All the files that are being moved are owned by me, and drive permissions were already given in a pop-up screen.

Answer 1

You cannot run a Google Apps Script that requires advanced Google Services (like accessing DriveApp) directly from a custom function in Google Sheets. Custom functions in Sheets are designed to be simple and only have access to the data in the spreadsheet where they are used. They don't have authorization to perform actions like accessing a user's Google Drive.

This is why your script runs from the Apps Script but not from Sheets.

To work around this, you can, for example:

1. Use Triggers: Instead of calling the function directly as a custom formula in the sheet, you can set up a time-driven trigger or an event-driven trigger (like on form submit or on edit) to execute your function.

2. Create a Custom Menu: Add a custom menu to your Google Sheets UI that runs your script. Users can then execute the script by clicking this menu item. This way, the script runs with the user's authorization, which can include necessary permissions for Drive access.

The exact details depend on your use case and sheets setup.