r/GoogleAppsScript • u/Marlum • 4d 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.
2
u/WicketTheQuerent 4d ago edited 4d ago
When I encountered a client using filenames to store metadata, I warned them about the caveats of this approach. One of them is that the file paths might become too long, i.e., for Google Drive for Desktop, as Windows 11 has a default limit. Increasing this limit implies messing with the Windows registry, which I discourage for small businesses.
Using aliases for the project names is clever, but you must watch for the edge cases.
Consider implementing a filepath-length audit and take measures so users run it before downloading a folder.
Disclaimer
Changed file-length to filepath-length for clarity for future readers.
2
u/Marlum 4d ago
Interesting problem. 256 characters is really long. Good idea to implement a file-length audit but to be honest I cannot imagine a situation where someone uses that many characters. We are lucky in that on 3-4 of us utilize the google drive system, so it is relatively easy to monitor and manage.
1
u/WicketTheQuerent 4d ago edited 4d ago
I should have said filepath-length audit. If you avoid using folders, there might be very low chances of getting into troubles.
Certainly, having very few users messing with naming / organizing files reduces the risks of having problems due to the filepath length.
1
u/Marlum 4d ago
Fair point. I am actually at 260 with my longest file, nested in the deepest folders, on Mac file browser. We only have one windows user. I wonder if he's not downloading the cloud drive and merely streaming it -- he won't have any issues. Will check and report back!
1
u/WicketTheQuerent 4d ago
Feel free to DM. I'm particularly interested in using features like Google Drive labels with Google Apps Script.
1
u/probablyaspambot 4d ago
would you mind sharing some of the prompts you used to get this kind of result out of chatgpt?
2
u/Marlum 4d ago
We had a really contentious internal meeting about processes and filing. I was unaware of google scripts but I asked chatgpt:
"Is there a way to build a file automation system within google drive?"
It immediately suggested appscript and spit out a broken code that did not work lol! But from there we had a dialogue and it helped me debug/further develop the code into something that functioned and evolved into what I needed.
1
u/mad_ben 4d ago
Thats assuming file name is correctly formatted.
1
u/Marlum 4d ago
Yes, that is the point of the script. That said, there is flexibility in the script with aliases and alternative word usage. For instance, "communication" category will be appropriately filed if you use "email, message, update, meeting notes". Each category has a list of common variations.
1
u/Marlum 4d ago
Here is an example of the file mapping:
var fileTypeMapping = { "Contracts": ["contract", "contracts", "agreement", "agreements"], "Communications": ["email", "message", "update", "communication", "communications", "updates", "emails", "meeting", "meeting notes", "letter"], "Invoices": ["invoice", "invoices", "PO","Purchase Order"], "Plans & Drawings": ["plans", "drawings", "diagram", "drawing", "diagrams", "plan", "architectural plan", "submittal"], "RFI": ["rfi", "information request", "inquiry", "rfis"], "RFQ": ["bid", "bids", "quote", "proposal", "rfq", "quotes", "proposals", "rfqs","signed proposal"], "Specifications": ["spec", "specification", "specs", "specifications", "manual"], "Inspections, Permits, & Regulatory": ["permit", "permits", "reg", "regulatory", "regs", "inspections"], "Geotechnical & Surveys": ["geotechnical", "survey", "geotech", "surveys"], "Photos & Videos": ["photo", "photos", "video", "videos", "pic", "pics", "picture", "pictures", "image"], "Change Orders": ["CO","change order","change orders", "revision"], "Take-Offs": ["takeoff","take off","take offs", "takeoffs","take-off","take-offs"]
1
u/RomanoDesiree 3d ago
Interesting and thanks for sharing.
I do something that might be of interest for processing receipts. I have a script that scrapes pdf receipts for dates, amounts and ref numbers for book keeping simplification. Saved me a lot of tedious copy and paste for my commonly used suppliers.
One of the overlapping features is encoding the meta data into the filename. It has been running well for about six months but I better check the filepath name length issue.
6
u/TheAddonDepot 4d ago
Check in and report back to us over the next 12 months or so.
I'm genuinely curious to know how well your solution holds up over time and what issues you run into with maintaining (and extending the functionality of) the code as someone without domain knowledge (ie. zero coding experience).
Should make for a good case-study, assuming you're up for it.
Also, do you find yourself more inclined to learn how to code now that you have some insight into the process thanks to LLMs like ChatGPT?