0

Trying to get a list of folders (not sub folders or files) in a Gsuite shared drive to populate a Google Sheet worksheet, but the script below fails after first time and will not overwrite the list when I re-run.

I have tried quite a few variations, but my problem is: 1. I would love to have a 3rd column produced showing the html 2. Script works once, but when I refresh or place a trigger on the sheet it no longer works or updates.

Here is my current code:

function listFilesInFolder(folderName) {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(["Name", "File-Id"]);
//change the folder ID below to reflect your folder's ID (look in the            URL when you're in your folder)
var folder = DriveApp.getFolderById("0ADTcDD3ZSaa5Uk9PVA");
var contents = folder.getFolders()

var cnt = 0;
var file;

while (contents.hasNext()) {
    var file = contents.next();
    cnt++;

       data = [
            file.getName(),
            file.getId(),
        ];

        sheet.appendRow(data);
    };
};

Any suggestions on how to: 1. Get the folder list to update each hour on the Google sheet? 2. Fix error where the folder list is not updating/overwriting? 3. Add the html of each folder into a third column? 4. Have the list produced in alphabetical order?

Many thanks in advance if you have made it this far!

Michael Nicholls
  • 13
  • 1
  • 3

1 Answers1

0
function listFoldersInAFolder() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clearContents();
  sh.appendRow(["Name", "Folder-URL"]);
  var fA=[];
  var folders=DriveApp.getFolderById("folder id").getFolders();//Enter folder id
  while (folders.hasNext()) {
    var folder= folders.next();
    fA.push([folder.getName(),folder.getUrl()]);
  }
  sh.getRange(sh.getLastRow()+1,1,fA.length,2).setValues(fA);
  sh.getRange(2,1,sh.getLastRow()-1,2).sort({column:1,ascending:true});
}

If you want to get all folders and sub folders you will have to recurse them. Here's an example of that: https://stackoverflow.com/a/55248127/7215091

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you Cooper - it works really well! Excellent! One final item - I want to change the folder.getId() to generate a hyperlink to the folder instead (in Gsuite shared drive). Alternatively, I can append "https://drive.google.com/drive/folders/" text and the folder ID to generate the hyperlink. Any suggestions on getting the hyperlink generated? – Michael Nicholls Jan 15 '20 at 04:27
  • I could change it to get URL – Cooper Jan 15 '20 at 05:32