List files inside a google drive folder in spreadsheets

Question

I have a folder: https://drive.google.com/drive/folders/1_70Q4BPQrOHCQU4eUleEPuxbr0hpfhBB

This folder has some files inside. I want to create a google-appscript to list the files in a Spreadsheet, in a way it look as close as possible to this: https://docs.google.com/spreadsheets/d/11KQYtJvEi1nbw0Awp7xgitOPgc9RhkMLICBGvvJrYlU/edit#gid=0

How could I do that?

Answer 1

Solution:

The following code will do the job:

function findFilesInfo() {

  const folderId = '1_70Q4BPQrOHCQU4eUleEPuxbr0hpfhBB'
  const folder = DriveApp.getFolderById(folderId)
  const files = folder.getFiles()
  const source = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = source.getSheetByName('Página1');
  const data = [];
  
  while (files.hasNext()) {
      const childFile = files.next();
      var info = [ 
        childFile.getName(), 
        childFile.getUrl(),
        childFile.getLastUpdated(),
        Drive.Files.get(childFile.getId()).lastModifyingUser.displayName     
      ];
    
    data.push(info);
  }
  sheet.getRange(2,1,data.length,data[0].length).setValues(data);
}

Requirements:

1. Create a sheet with the name Página1 (you can adjust it of course according to your needs) that has the following structure:

2. In Resouces > Advanced Google services: enable Drive API:

3. Specify the 'folderID' of your folder here:

   const folderId = '1_70Q4BPQrOHCQU4eUleEPuxbr0hpfhBB';
   

Restrictions:

You can't use findFilesInfo() as a custom function within your google-sheet file. You can either run it directly from google script editor by clicking on the play button:

or you can create a custom button (macros) where you can click on it from your google-sheet file and execute the function.