Google Sheets Plus Apps Script Equals Fun for the Whole Family.

I haven't written any tech pieces in a while, so as I was getting ready to write the monthly musical wrap up for February, I ran into this relatively simple problem that I had to write my own solution for. I did some googling and did not find anything particularly useful, so I imagine there will be other people trying to do this very thing. So, if that is you, looking for an easy way to pull the file names out of a specific Google Drive folder into Google sheets, today is your lucky day. It only takes a few lines of code.

Before we get started, I'd like to add some reference links. Google AppScript is very well documented here, and for anyone who has worked with JavaScript and/or JSON, it's a pretty seamless transition. And then a shout-out to the anonymous author at spreadsheet.dev who gave me a solid starting point to work with.

So what exactly is the use case? Well, every month, I like to release a blog post about, among other things, the new songs that were added to our radio broadcast. OK, great. In the past I just had a local folder on my laptop that I was copying the MP3s into as I was also adding them to the radio playlist. But, to be fair, that is a little bit old-school at this point. I am still not a fan of the Apple backup process through iCloud, and only keeping your files on a single laptop is a recipe for disaster. So last year I transitioned into using a Google Drive folder. In a new sub folder every month, new songs now go there instead and I don't have to worry about backing up the laptop. But of course, you end up needing to copy a list of those songs as text into the blog post, and that's where things get tricky. On a local folder, you can shift-click to select any number of files, and then using the clipboard copy function, just pasting that into a text editor gives you a list of the files as text. But you can't do that with Google Drive. That feature is just not available.

For longer than I would like to admit, I have just downloaded the entire contents of the Google Drive folder in question and used that same solution—copying the local files into a text editor—as a functional, if tedious, work around. As I plan to discuss a little bit in coming weeks, I am finally getting around to taking care of some of these medium to low priority issues, and last night I finally found the time to devise a solution. I continue to be pleasantly surprised with the power of Google Apps Script. I use it at work as well to do some auto updates and to generally make the spreadsheet experience just a little bit more tailored to my specific needs—for example, I send an automatic timestamp to an adjacent cell when I add a note to any project sheets, which allows me to immediately understand which note has precedence when I go to a project that hasn't been front of mind for a few days. It's a godsend. If anyone wants me to post a breakdown of that solution, just ping me on mastodon.

There are a few reasonably helpful videos that I have found, but nothing specifically accesses a single folder with just the ID. If you want to search more broadly across the root folder with just keywords, or titles, then this is not for you. But if you just need a way to easily grab a text read-out of a list of files in one folder, this is how you do it.

image for Google Sheets Plus Apps Script Equals Fun for the Whole Family.Step 1: Get the Folder IDThere are many ways to do this, but if you've got the folder open in a browser tab, just select and copy everything in the URL that is after folder/. Save this somewhere, you will need it in Step 4.

Step 2: Create Your SheetAdd a new sheet to your Google Drive account. Name it whatever you want. Make sure that you have a tab called Files.

image for Google Sheets Plus Apps Script Equals Fun for the Whole Family.Step 3: Copy the CodeFrom your new sheet, click Extensions->Apps Script.

Copy this code and put it in place of the sample function.

function getMyFilesFromDrive() { var folder = DriveApp.getFolderById("YOUR_FOLDER_ID_HERE"); var files = folder.getFiles(); var sheet = SpreadsheetApp.getActive().getSheetByName("Files"); sheet.clear(); var rows = []; rows.push(["Name"]);

while (files.hasNext()) { var file = files.next(); Logger.log(file.getName()); let fname = file.getName().toString(); rows.push([fname]); }

sheet.getRange(1,1,rows.length,1).setValues(rows);}image for Google Sheets Plus Apps Script Equals Fun for the Whole Family.Step 4: Copy the Folder IDCopy the folder ID from step 1 into the code you just pasted (replace 'YOUR_FOLDER_ID_HERE').

Step 5: Bask in the Glow of Your AwesomenessPress run and find somewhere safe to put all that money that you are.

image for Google Sheets Plus Apps Script Equals Fun for the Whole Family.You're welcome.