This morning, I hit a snag while working on an app for a medical client, and I thought I’d share the experience in case it helps others in the same boat.

The Setup

We’ve structured the client's AppSheet app to work entirely from a Google Shared Drive. The shared environment is ideal — everything lives in one place, making it easy for multiple team members to access files and folders without ownership headaches.

All the automation tasks that generate files (like PDFs, reports, etc.) run smoothly. Files are being created in the correct folders, and the folder hierarchy — including dynamically created subfolders — is spot-on.

In the app settings:

The Default app folder is correctly set using the [TeamDrive] syntax, pointing to the shared drive.
The File columns are populated with the precise folder/file paths of the generated files, matching exactly where they land in Google Drive.

The Problem

Despite everything looking perfect, clicking the “Open File” action tied to the file column stubbornly refused to open the file; it would give me an error every time.

I triple-checked:

The file paths.
The shared drive structure.
The file permissions (set correctly for everyone who needs access).

Yet, nothing worked — the file simply wouldn’t open through the app's default behavior.

The Solution: A Little Google Apps Script Magic

Instead of letting this issue slow me down, I pulled out a trusty tool I’d used before.

A while back, I created a Google Apps Script that, when given a file path (as stored in a file column), would locate the actual file in Google Drive and return useful information about it. To make it even more flexible, I enhanced the script to include a return_type parameter, allowing me to control what gets returned.

Here’s how I applied it:

Script Modification:I updated the script to accept a return_type parameter, enabling the following options:
- 'file' (default): Returns the file object itself.
- 'id': Returns the file's unique Drive ID.
- 'url': Returns the direct Google Drive URL of the file.
- 'name': Returns the file name.
- 'mime': Returns the file's MIME type.
- 'size': Returns the file size.
- 'date': Returns the file’s creation date.
For this project, I set return_type to 'url', giving me the direct link to the file.(Want to see the script? Check it out here: Script Example)
Automation Adjustment: After the automation generates and saves the file, I added a step to call the script, passing in the file column value and specifying the desired return type.
Result Storage: I store the result (the direct file URL) in a dedicated URL column, right alongside the file column.

Now, users don’t click the standard "Open File" action anymore. Instead, they click a button linked to the URL column, which opens the file reliably every time — no matter if it's in a Shared Drive or not.

Performance?

The script’s execution speed is solid:

Average time: 0.67 to 0.91 seconds.
Practically no lag from a user’s perspective.

So even though the official action is having difficulties with the nuances of shared drives, this solution solves the problem and will work - as long as the script can find the file. :)

This reminds me about the best thing about AppSheet...

If you need to access the script: click here

Troubleshooting AppSheet File Column Issues in Google Shared Drives — And a Quick Workaround