I need help creating a script to handle daily file imports and I’m stuck on where to begin. Here’s what I’m trying to accomplish:
My Current Setup:
- Every morning a new CSV gets dropped into my Drive folder called “Daily Pacing Folder”
- Each CSV has the same column headers but different row counts
- File names change daily so I can’t rely on static naming
- I have a Google Sheet called “Sales Report” with multiple tabs
- Need to import data into the “Raw Data” tab specifically
What I Want to Automate:
I want a script that monitors my folder for new CSV files. When it finds one, it should grab all the data and put it in columns E through Y of my existing sheet. The tricky part is I can’t just overwrite everything - I need to preserve what’s already there.
My Challenge:
I’m pretty new to coding and haven’t found good examples online that match my exact situation. Most tutorials show basic imports but not this specific folder monitoring approach.
Has anyone built something similar? I’d really appreciate any guidance on the best way to approach this automation problem.
i totally agree with using triggers! it helps so much with performance. also, remember to add some logging in your script to track what’s getting imported. can be super helpful if you run into issues later on. good luck with it!
I built something very similar for my inventory tracking system last year and ran into the same challenges you’re facing. The key insight that helped me was realizing you don’t actually need real-time folder monitoring - a time-based trigger works much better in practice.
What I ended up doing was creating a script that runs every hour and checks for files modified within the last hour using the Drive API. This approach is more reliable than trying to monitor continuously. For your specific case, you’ll want to use DriveApp.getFolderByName() to access your folder, then iterate through files checking their last modified time.
The trickiest part you’ll encounter is handling the data placement without overwriting existing content. I solved this by always appending new data to the last occupied row rather than trying to insert at specific positions. You can use getLastRow() to find where your existing data ends, then use getRange() to target your specific columns E through Y.
One gotcha I learned the hard way - make sure to include error handling for when your CSV files are malformed or still being written to the folder. Nothing worse than a script failing at 3am because of a corrupted upload.