Google Sheets & Apps Script

Plan a Dynamic Document Upload Workflow in Google Sheets

❮ Google Sheets & Apps Script Copy the Prompt ❯

What is a dynamic document upload system?

A Settings-driven Apps Script workflow can add an Upload Document menu to Google Sheets. After a user selects a spreadsheet row and document type, a custom upload dialog can send a file to a configured Google Drive folder and write a View File hyperlink in the selected row.

Document types, target sheets, destination columns and folder IDs are managed in a Settings sheet, so the menu can be rebuilt from configuration instead of hard-coding each upload type.

Implementation note: this guide explains the workflow and includes a copyable prompt, but not a ready-to-run script. Review generated code, authorize only the required services, and test with a non-sensitive sample file before using real records.

Settings sheet: configure columns A–D

Create a sheet named Settings. Use the first row for headings and add one configuration per row below it. The script should read these values to determine what each menu option does.

Column	Heading	What it controls	Example
A	Upload Name	Label shown in the Upload Document menu.	Supplier Invoice
B	Sheet Name	Target worksheet that contains the selected record.	Import Expenses
C	Destination Column	Column where the file link should be written. Use the column letter expected by your script.	B
D	Drive ID	ID of the Google Drive folder that should receive the uploaded file.	FOLDER_ID_FOR_EXPENSES

For example, a Settings row might contain Supplier Invoice | Import Expenses | B | FOLDER_ID_FOR_EXPENSES. Use placeholder IDs in examples; put real folder IDs only in your own configuration after reviewing the generated code.

Keep the values accurate: the sheet name must match the tab name, the destination column must be valid for that sheet, and the account running the script needs permission to add files to the destination folder.

When the spreadsheet opens, the Apps Script menu builder reads the configured upload names in column A and creates corresponding items under Upload Document. Each menu item is associated with its settings row so the script can look up the matching sheet, column, and Drive ID when you choose it.

Because this menu is built from Settings, adding a new valid row can add another upload option without writing a new handler for that document type. The script still needs its upload and permission logic configured correctly.

Use the menu's Refresh Menu option to run the rebuildUploadMenu function and rebuild the menu from the latest Settings values. This is useful after adding, renaming, or removing an upload type. If your project does not yet include this menu item, add it to the menu-building code and ensure the function name matches exactly.

Typical user flow

1. Add or update a row in Settings.
2. In the spreadsheet, choose Upload Document → Refresh Menu.
3. Choose Upload Document → [your configured Upload Name].

Upload a file from a selected row

Open the worksheet named in the selected Settings entry.
Select the row for the record that should receive the document link. The script can use the active row to connect the upload to that record.
Choose Upload Document, then select the relevant document type, such as Supplier Invoice.
In the file picker or upload dialog provided by the script, select the document. The script uses the configured Drive ID to save the file to its destination folder.
After a successful upload, the script writes a View File hyperlink into the configured column on the selected row.

Click View File to open the uploaded document in Google Drive. Whether another person can open it depends on the file's sharing permissions; uploading a file does not automatically make it public.

Common setup checks

Confirm the Settings tab is named exactly Settings and the values are in columns A–D.
Check that the selected row is on the sheet named in column B.
Verify column C identifies the intended link destination.
Use a Drive folder ID in column D, not the entire folder URL, if that is what the script expects.
Run the script's authorization flow and grant only the permissions needed for Sheets and Drive access.
Refresh the menu after changing configuration, then try an upload with a test row and a non-sensitive file.

Copyable ChatGPT prompt

Copy the prompt below into ChatGPT and provide your spreadsheet details. Review and test any generated Apps Script before using it with important files.

I want to create a Dynamic Document Upload System in Google Sheets using Google Apps Script.

My spreadsheet has a sheet named "Settings" with these columns:
Column A = Upload Name
Column B = Sheet Name
Column C = Destination Column for the View File link
Column D = Drive ID (the destination Google Drive folder ID)

Example configuration rows:
Supplier Invoice | Import Expenses | B | FOLDER_ID_FOR_EXPENSES
BOL | Import Expenses | F | FOLDER_ID_FOR_EXPENSES
CHA | Import Purchase | J | FOLDER_ID_FOR_PURCHASES

Please create a complete, maintainable Google Apps Script solution with these requirements:
1. Create a custom Google Sheets menu named "Upload Document".
2. Build the upload type menu items dynamically from the Upload Name values in column A of Settings. Do not hard-code the names.
3. Include a "Refresh Menu" menu item that runs a function named rebuildUploadMenu and rebuilds the menu from the current Settings sheet.
4. When I select a row in the configured Sheet Name and choose an upload type, use that Settings row to identify the destination sheet, destination column, and Drive folder ID.
5. Let me select a file, upload it into the configured Google Drive folder, and write a clickable "View File" hyperlink in the configured column of the selected row.
6. Validate missing settings, invalid sheet or column names, missing folder access, and the absence of a selected data row. Show clear error messages and avoid overwriting unrelated cells.
7. Explain required Google Apps Script services, authorization steps, deployment or dialog setup if needed, and any sharing permissions affecting access to uploaded files.
8. Provide the complete code in clearly labeled sections and explain how to install and test it safely.

Ask me for any missing spreadsheet details needed to produce code that matches my exact sheet names, columns, and upload workflow.

Frequently asked questions

Can I add more document types later?

Yes. Add a valid configuration row to Settings and use Upload Document → Refresh Menu. The menu builder can then read the new upload name dynamically.

Where does the uploaded document go?

It goes to the Google Drive folder identified by the Drive ID in column D of the matching Settings row, assuming the script-running account has access.

Does the View File link make a file public?

No. The link points to the uploaded file, but Google Drive sharing permissions still determine who can open it.

Summary: configure each document type in Settings columns A–D, refresh the dynamic menu, select the target row, upload the file to Drive, and use the generated View File hyperlink to open it.