I’m stuck and could use some help. I’m trying to set up an automatic system that pulls data from the newest spreadsheet in a shared Google Drive folder. Here’s what I need: 1. Find the most recent spreadsheet (or one with a specific name pattern) in the shared folder 2. Import a certain range or tab from that spreadsheet 3. Put the imported data into a main spreadsheet 4. Make this happen every day without me doing it manually
Is there a script or formula that can do this? I’ve been scratching my head over it for a while now. If anyone has experience with this kind of setup, I’d really appreciate some pointers or examples. Thanks in advance for any help you can offer!
I’ve actually tackled a similar challenge in my work. Here’s what worked for me:
Using Google Apps Script, you can automate this process quite effectively. First, write a script that scans the shared folder for the newest spreadsheet (or matches your naming pattern). Then, use the SpreadsheetApp class to open and read data from the identified sheet.
For the daily automation, set up a time-driven trigger in Apps Script. This will run your script at the same time each day without manual intervention.
One caveat: make sure your script has the necessary permissions to access the shared folder and target spreadsheet. Also, consider error handling for cases where the newest sheet might not have the expected structure.
It took some trial and error, but once set up, it’s been running smoothly for months. Happy to share more specifics if you need!
hey ethan, i’ve got a quick fix for u. try using zapier. it can connect to gdrive, find new sheets, and move data automatically. set it up once and it’ll run daily. no coding needed. just drag and drop actions. saved me tons of time on similar stuff
I implemented a similar system using Python and the Google Drive API. In my experience, I started by authenticating with Google Drive using service account credentials. I then queried the Drive API to locate the newest spreadsheet in the folder. After retrieving the file, I used the Sheets API to read the required data and wrote it to my main spreadsheet. Finally, I scheduled the script to execute daily via a task scheduler such as cron or Windows Task Scheduler. This approach avoids the quotas of Google Apps Script and gives better control over logging and error handling.