Tag: 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.