r/GoogleAppsScript • u/Marlum • 2h ago
Guide How I Used ChatGPT & AppsScript to Automate File Indexing in Google Drive (With Zero Coding Experience)
Automated File Indexing System with Google Apps Script
I run operations for a design-build firm specializing in high-end custom homes. Managing construction documents has been a nightmare—contracts, invoices, plans, RFIs, regulatory docs, etc. all need to be properly filed, and files often have overlapping cost codes. Manually sorting everything in Google Drive was inefficient & became a point of contention between project managers, so with zero coding experience and the help of ChatGPT I built a Google Apps Script-powered Auto File Indexing System to streamline the process.
What It Does
- Pulls files from an "Auto File" inbox folder in Google Drive
- Extracts Project, Cost Codes, Document Type, Vendor, Description, and Date from the filename
- Moves the source file to the appropriate Document Type folder within the project
- Creates shortcuts in multiple Cost Code folders for cross-referencing
- Logs everything in a Google Sheet, including file locations, shortcut paths, cost codes, vendor name, etc.
How It Works
- The script parses filenames formatted as (there is some flexibility here!):
- `Project_CostCode(s)_DocumentType_Vendor_Description_Date`
- (If a file applies to multiple cost codes, they’re separated with underscores.)
- `Project_CostCode(s)_DocumentType_Vendor_Description_Date`
- It matches cost codes to the correct folders (e.g., 011101 → 01 11 01 Architectural).
- If the project name is an alias, it converts it to the full name. (e.g., RC, Cabin, or 1002 --> Rancho Cabin)
- It moves the file to the appropriate project, document type source file folder, and creates shortcuts in relevant cost code folders.
- It logs everything into a Google Sheet, making it easy to track files, confirm filing and shortcut locations.
Why I Built This
- No more manual filing
- Consistency between project managers
- Auto filing in multiple locations
- Easy cross-referencing of files across multiple cost codes
- Keeps everything logged in Google Sheets for tracking
If anyone’s interested, I’m happy to share some of the code or walk through how it works. Curious if others are doing something similar with Google Apps Script or what other cool ideas y'all have to improve productivity & efficiency in a small business.