List files from a google drive subfolder based on cell value

[Stuart Eade]

Hi Demigods,

After some help please.

Sheet1 has columns A and B

A1 contains header "JOB #"

A2 contains "100"

A3 contains "101"

A4 contains "102"

etc

B1 contains header "CUSTOMER"

B2 contains "CUSTOMER A"

B3 contains "CUSTOMER B"

B4 contains "CUSTOMER A"

etc

If i enter a value in B5, A5 automatically shows "103" and a script running in the background creates a folder on my google drive (in a sub directory called JOBS) called "103". It then turns cell A5 into a hyperlink, still showing "103", and enables the user to select the hyperlink to go to the "job folder" on google drive.

Clever stuff!

I have been looking for a script that will list on Sheet2, the contents of each of these folders, with the format a bit like Sheet1, i.e.

A1 contains header "JOB #"

A2 contains "100"

A3 contains "101"

A4 contains "102"

etc

B1 contains header "CUSTOMER"

B2 contains "CUSTOMER A"

B3 contains "CUSTOMER B"

B4 contains "CUSTOMER A"

etc

C1 contains header "DOCUMENTS"

C2 contains name of 1st document stored in folder 100, D2 contains 2nd document stored in folder 100, E2, contains 3rd, etc

C3 contains name of 1st document stored in folder 101, D3 etc etc

I have seen lots of scripts that will list the contents of folders vertically, but none that do it horizontally and how i describe.

I only want the name of the document, not the author or owner, or anything like that.

What would be good also, is if it displayed the name of the file, and turned it into a hyperlink to the document itself.

This couldn't run onEdit because it would be constantly updating, but if the list could update itself when a file is added perhaps?

Something for your geniuses to chew on.

Please any links to a solution like what i am suggesting and pointers would be very much appreciated.

I'm useless and writing my own script, rare flashes of brilliance every now and then (at least i think so), but other than that - rubbish. So be gentle with me.

Thanks in advance

Best regards

manc

[Stuart Eade]

So i've created a solution to my issue.

First i used the following script to create a list of files in a root directory, including the subfolder names on a sheet called 'LIST':

function getAndListFilesInFolder() {

  var arr,f,file,folderName,subFolders,id,mainFolder,name,own,sh,thisSubFolder,url;

  sh = SpreadsheetApp.getActive().getSheetByName('LIST');

  sh.getRange(1, 1, 100, 10).clear({contentsOnly: true})

  id = "SHEETID";

  arr = [["CAPITAL PROJECT", "URLs", "OWNER","FOLDER"]];

  mainFolder = DriveApp.getFolderById(id);

  subFolders = mainFolder.getFolders();

  folderName = mainFolder.getName();

  f = mainFolder.getFiles();

  while (f.hasNext()) {

    file = f.next();

    name = file.getName()

    url = file.getUrl()

    own = file.getOwner().getName()

    arr.push([name, url, own, folderName]);

  };

  while (subFolders.hasNext()) {

    thisSubFolder = subFolders.next();

    f = thisSubFolder.getFiles();

    folderName = thisSubFolder.getName();

    while (f.hasNext()) {

      file = f.next();

      name = file.getName()

      url = file.getUrl()

      own = file.getOwner().getName()

      arr.push([name, url, own,folderName]);  

    };

  };

  sh.getRange(1,1, arr.length, arr[0].length).setValues(arr);

  sh.getRange(2,1, arr.length, arr[0].length).sort(1);

}

On another sheet, I then created a query to transpose the results from 'LIST' and to keep the hyperlink integrity to the file:

=iferror(TRANSPOSE(ARRAYFORMULA(HYPERLINK(QUERY(LIST!$A$2:D,"Select B where B <> '' and D = "&$A3&" ",0),QUERY(LIST!$A$2:D,"Select A where A <> '' and D = "&$A3&" ",0)))))

Where 'B' is the URL and 'A' is the filename.

The value in A3 matches a folder name automatically created in a subfolder of the root directory.

I then copied the formula as far down as i needed it to go.

I am sure there is a better way to do it, a less CPU intensive way maybe, but this works for me for the time being.

Many thanks

manc