The method for doing this is actually part of the bmFolderFun library documented in A handier way of accessing Google Drive folders and files from Apps Script but it can be really useful for keeping your Drive clean that I thought it deserved its own article. The idea is that given a path like /data/images you should be able to easily enumnerate all files, optionally filtered by type, and optionally including any subfolders. Once you know what you have and where it is, it makes everything a lot easier to organize.
Examples
Simple non recursive list
Let’s say you want to get a list of all files in a folder on drive. We’ll start with ignoring subfolders
const a = () => {
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
console.log(ff.pileOfFiles({start:'Published Scripts/useful'}))
}
All the files in a folder
The result is already quite useful as we’ve got the filenames, paths, and Ids for both the folders and the files.
[ { folderPath: '/Published Scripts/useful/',
fileName: 'bmFolderFun',
mimeType: 'application/vnd.google-apps.script',
folderId: '0B92ExLh4POiZdkpUeXI0Q1h3VGs',
fileId: '16NWIRmwJJY_wN4erx_QZA36_ssaB2GiKDPYebj7fjBU1SpVQlo9N_RA7',
owner: 'bruce@mcpher.com' },
{ folderPath: '/Published Scripts/useful/',
fileName: 'bmTemporal',
mimeType: 'application/vnd.google-apps.script',
folderId: '0B92ExLh4POiZdkpUeXI0Q1h3VGs',
fileId: '1o_qJw1fdgF4NgRnHrsB_Jsbu3gYe1fQnPze33V9jHLqBCXHmzZaBgmGH',
owner: 'bruce@mcpher.com' },
....etc
]
simple folder pile
Mimetype filtering
Handier though if we can just look at files of a particular type
const b = () => {
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
console.log(ff.pileOfFiles({start:'Published Scripts/useful', mimeTypes: MimeType.GOOGLE_APPS_SCRIPT}))
}
filter by mimetype
Recursive list
Handier still if we can look at subfolders as well, and combine that with selective mime types. This one will find any images in the given folder or any of its subfolders
const c = () => {
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
console.log(ff.pileOfFiles({
start: '/books/going gas/admin/draft3',
mimeTypes: ['image/jpeg','image/png'],
includeSubfolders: true
}))
}
look in subfolders for images
and a little bit of the result
[{ folderPath: '/books/going gas/admin/draft3/images',
fileName: '18.3.png',
mimeType: 'image/png',
folderId: '0B92ExLh4POiZRHB1VlJTRXREaEU',
fileId: '0B92ExLh4POiZTFJEMW45Yk9xMjA',
owner: 'bruce@mcpher.com' },
{ folderPath: '/books/going gas/admin/draft3/images',
fileName: '18.2.png',
mimeType: 'image/png',
folderId: '0B92ExLh4POiZRHB1VlJTRXREaEU',
fileId: '0B92ExLh4POiZYkJ1SUVzQkh2b2c',
owner: 'bruce@mcpher.com' },
{ folderPath: '/books/going gas/admin/draft3/images',
fileName: '18.1.png',
mimeType: 'image/png',
folderId: '0B92ExLh4POiZRHB1VlJTRXREaEU',
fileId: '0B92ExLh4POiZY2JWTjdZZmhxcDg',
owner: 'bruce@mcpher.com' },
...etc ]
subfolders plus mimetype filtering
Counting
You may just want to know how many there are in all the subfolders (the answer was 81)
const d = () => {
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
console.log(ff.pileOfFiles({
includeSubfolders: true,
start: 'Published Scripts',
mimeTypes: MimeType.GOOGLE_APPS_SCRIPT
}).length)
}
counting
Counting by folder
You can manipulate the results- for example do a count by individual folder
const e = () => {
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
// get the list
const pile = ff.pileOfFiles({
includeSubfolders: true,
start: '/books/going gas/admin',
mimeTypes: [
MimeType.MICROSOFT_POWERPOINT,
MimeType.MICROSOFT_POWERPOINT_LEGACY
]
})
// dedup by folder path
const map = new Map(pile.map(({folderPath})=>([folderPath, 0])))
// count by folder path
pile.forEach (p=>map.set(p.folderPath,map.get(p.folderPath) 1))
console.log(Array.from(map).map(([folderPath, count])=>({
folderPath,
count
})))
}
count all powerpoint files in all subfolders and show count by subfolder
result
[ { folderPath: '/books/going gas/admin/beginnersvideo/material',
count: 81 },
{ folderPath: '/books/going gas/admin/video/assets/Chapter 16',
count: 1 },
{ folderPath: '/books/going gas/admin/video/material',
count: 67 },
{ folderPath: '/books/going gas/admin/video/tools',
count: 1 }
]
Writing to a sheet
All that would be more useful if we wrote it to a sheet, and of course, there’s a library to turn the whole thing into pretty much a one liner, using Handly helper for fiddler
Here’s the code to discover and write the details of all my public published scripts to a spreadsheet
const driveListToSheet = () => {
// start looking from here (can be a path or a folder object)
const start = '/Published Scripts' ///books/going gas/admin/draft3'
// whether to search in subfolders
const includeSubfolders = true
// which mimetypes to include (null for all)
const mimeTypes = MimeType.GOOGLE_APPS_SCRIPT
// force a permission dialog with this comment
// DriveApp.getRootFolder()
const ff = bmFolderFun.paths(DriveApp)
// we'll write the results out to a sheet
bmPreFiddler.PreFiddler()
// this will create a sheet if necessary
.getFiddler({
id: 'xxxxxxxxxxxxxxxx',
sheetName: 'folderfun',
createIfMissing: true
})
.setData(
// set the sheet content to the selected drive content
ff.pileOfFiles({
start,
includeSubfolders,
mimeTypes
}))
// may as well pretty it up a bit
.setHeaderFormat({
wraps: true,
backgrounds: 'tomato',
fontWeights: 'bold',
fontColor: 'white'
})
// write everything to the sheet
.dumpValues()
}
one liner to summarize drive content
Result
and here’s a sample of the sheet it created
links
bmFolderFun
github
IDE
library: 16NWIRmwJJY_wN4erx_QZA36_ssaB2GiKDPYebj7fjBU1SpVQlo9N_RA7
github: https://github.com/brucemcpherson/bmFolderFun
scrviz: https://scrviz.web.app?manifest=brucemcpherson%2FbmFolderFun%2Fappsscript.json
bmPreFiddler
library: 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR
github: https://github.com/brucemcpherson/bmPreFiddler
scrviz: https://scrviz.web.app?repo=brucemcpherson%2FbmPreFiddler
A handier way of accessing Google Drive folders and files from Apps Script