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 listFilesInFoldersGEN() {
var folders = [
{folderId: 'URL', sheetName: 'General'},
// 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);
}
// Save the existing data
var range = sheet.getDataRange();
var values = range.getValues();
try {
sheet.clear();
sheet.appendRow(["Name", "Date", "Size", "URL", "Folder"]);
listFilesInFolderRecursiveGEN(folder, sheet, folder.getName());
} catch (e) {
// If an error occurs, revert to the saved data
range.setValues(values);
// Log the error
var errorMessage = 'Error: ' + e.toString();
Logger.log(errorMessage);
// Send an email
var emailAddresses = ['bwillis@edu.au', 'nlapin@edu.au'];
// Enter your email address here
var subject = 'Error in Support Catalogue - General script';
var body = errorMessage;
MailApp.sendEmail(emailAddress, subject, body);
}
}
}
function listFilesInFolderRecursiveGEN(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();
listFilesInFolderRecursiveGEN(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.
David Truss
Ryan Barrett
Aaron Davis
john
Audrey Watters