I need help setting up automation for importing CSV files into my spreadsheet. Every morning a new CSV file gets dropped into my Google Drive folder called “Morning Reports”. Each file has the same column headers but different amounts of data rows. The filename changes daily so I can’t reference it directly.
I want to automatically detect when a new CSV appears in this folder and import its contents into my Google Sheet called “Performance Data” on the tab named “Import Zone”. The data should go into columns F through Z specifically.
The tricky part is I can’t replace existing data in that sheet since it has other important stuff. I just need the newest CSV content to populate those specific columns.
I’m pretty new to scripting so I’m not sure how to approach this. Has anyone done something similar before? Any guidance on how to check for new files and import them would be awesome.
I built something almost identical about six months ago for our daily sales reports. Google Apps Script with a time-based trigger is the way to go - just set it to run every morning and check for new files.
Here’s what worked: create a script that searches your Drive folder by date modified, then grabs the newest CSV. Use DriveApp.getFolderById() for your Morning Reports folder, then loop through files to find the latest one with getLastUpdated().
For importing, Utilities.parseCsv() converts the CSV into an array, then getRange() targets columns F through Z. That tricky part about not overwriting existing data? Just clear those specific columns before importing, or append to the bottom if that fits your workflow better.
Learn from my mistakes - add error handling for when no new file exists or the CSV format’s messed up. And make sure your trigger runs after you expect the morning upload.
The folder monitoring works great, but add a tracking system so you don’t import the same file twice. I keep a simple log with filenames and timestamps - saves tons of headaches. For the F-Z column mapping, double-check your CSV headers match your destination columns. I’ve gotten burned when the source CSV had fewer columns than expected and left gaps in my data. Always verify column count before importing. Here’s what really saved me: backup your F-Z data to a separate archive sheet before each import. If the import goes sideways, you’ve still got your previous data. For timing, run the trigger 30 minutes after the expected file drop instead of checking immediately. File uploads get delayed or interrupted all the time, so that buffer prevents missed imports.
don’t forget about multiple csvs dropping the same day. i had backup files and dupes wreck my automation. use file naming patterns or check file sizes to grab the right one. test with small data first - huge csvs will timeout and crash everything.