Replied to Creating a Catalogue in Google Sheets by Aaron Davis (readwriterespond.com)
I was recently asked to have a look at spreadsheet that listed all the guides and videos located in a support folder as a reference. This folder also included a number of folders within folders, which created a level of complexity.The current workflow involved using an Add-on File Cabinet from the ...
I recently discovered that Awesome Tables’ Filing Cabinet Add-on has been deprecated, this broke the catalogue I had created with Google Sheets.
I searched online for any further explanation on the change, but was simply sent to Awesome Tables support page.
I started exploring other options online and short of paying for API connectors, I could not really find anything. I subsequently turned to CoPilot, wondering what it might give me. Surprisingly, it gave me a basic script for everything that I needed.
function listFilesInFolders() {
var folders = [
{folderId: 'folderId1', sheetName: 'Sheet1'},
{folderId: 'folderId2', sheetName: 'Sheet2'},
// Add more folders as needed
];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
for (var i = 0; i < folders.length; i++) {
var folderId = folders[i].folderId;
var sheetName = folders[i].sheetName;
var folder = DriveApp.getFolderById(folderId);
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
}
sheet.clear();
sheet.appendRow(["Name", "Date", "Size", "URL", "Folder"]);
listFilesInFolderRecursive(folder, sheet, folder.getName());
}
}
function listFilesInFolderRecursive(folder, sheet, path) {
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
sheet.appendRow([file.getName(), file.getDateCreated(), file.getSize(), file.getUrl(), path]);
}
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var subfolder = subfolders.next();
listFilesInFolderRecursive(subfolder, sheet, path + '/' + subfolder.getName());
}
}
After a bit of back and forward, I had a new working catalogue which I provided to the team to provide feedback on.
The post 💬 Creating a Catalogue in Google Sheets first appeared on Read Write Collect.