I need help creating a script to automatically pull data from CSV files that get uploaded to my Google Drive folder. I’m pretty new to scripting and could really use some guidance here.
Here’s what happens:
Every morning a new CSV file gets dropped into my Drive folder called “Weekly Reports”
Each CSV has the same column headers but different amounts of data
The filename changes each time
I need this data to go into my spreadsheet called “Marketing Data” on the tab “Import Sheet”
The data should go into columns F through Z specifically
I can’t replace what’s already there, just add the new stuff
Basically I want a script that watches my “Weekly Reports” folder and when it finds a new CSV file, it grabs all the content and puts it in the right place in my sheet. Has anyone done something like this before? Any help would be awesome!
yeah, the duplicates can really mess things up! checking file IDs sounds smart, i had issues too until i made sure to handle them in my script. also, trigger timing is key, i learned that the hard way too. good luck!
I did something similar last year. Set up error handling first - trust me on this. Use DriveApp.getFoldersByName() to find your “Weekly Reports” folder, then iterate through files with getFiles(). Since filenames change daily, sort by creation date to grab the newest CSV. Always create a backup before importing - just duplicate your current data to another sheet. I learned this the hard way. For mapping to columns F-Z, use getRange(row, 6, numRows, numCols) where 6 = column F. Watch out for empty cells - CSV parsing creates weird gaps sometimes. You’ll need Drive and Sheets permissions when you first run it. Test with sample files extensively before going live. Debugging data imports after they break is absolutely painful.
Built something like this for my sales team six months ago - hit a few gotchas you should watch for. Main issue is detecting new files reliably since Drive doesn’t have proper file watching like you’d expect. I ended up using a time-based trigger that runs hourly and checks for files modified in the last hour with getLastUpdated(). You’ll want to track which files you’ve already processed - I used a hidden sheet with filenames and timestamps. What caught me off guard was handling different CSV formats. Even with matching headers, data encoding or line endings can break things. Use Utilities.parseCsv() instead of splitting on commas manually. For mapping columns F through Z, watch your offset when using getRange(). Also think about what happens if someone uploads multiple files at once or if the script crashes mid-process.