I’m working on a project where my script automatically adds data to a Google Sheets file. The problem is that over time the sheet gets really large and we need to start using a new one.
Currently we have to manually copy everything to a fresh spreadsheet. Then someone has to update the spreadsheet ID in our code. Most of my team members don’t feel comfortable editing the script themselves.
Is there any way to reference a Google Sheets file using its name rather than the ID? Or maybe there’s a different approach that would solve this issue entirely?
Set up a naming convention with dates and let the script do the work. I built something that searches for files like “ProjectData_2024_” and grabs the newest one automatically. Use DriveApp to search partial names and sort by creation date. Your team just follows the naming pattern when they create new sheets - no code changes needed. Way better than updating IDs manually and you’ll never point to the wrong sheet again.
Had the same issue. A config file beats hardcoding sheet IDs every time. I made a simple text file (or just another Google Sheet) with the current spreadsheet ID and filename. The script reads from there to grab the active sheet ID. When you need to switch sheets, team members just update the config file - no code changes needed. Plus you get a history of which sheets you used when, which helps with data tracking. Way more reliable than searching by filename since there’s zero confusion about which sheet to use.
yep, u can use DriveApp.getFilesByName(‘your sheet name’) to find sheets by name. just a heads up tho - if there’s multiple sheets with the same name, it’ll get all of em. maybe add a date or version to keep em unique!