How to list all files recursively from google drive folder to spreadsheet?

Question

i am trying to the list all the files recursively from a google drive folder to a spreadsheet and sort the file listing by size ( Largest sized file should be on top ) . i am facing issues with the script

start function is giving the error - ReferenceError: *****folder_id is not defined (Line 16)

function start() {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type", "Folder", "Folder Slug"]);

    var folder = DriveApp.getFoldersById(FOLDER_ID);

    if (folder.hasNext()) {
        processFolder(folder);
    } else {
        Browser.msgBox('Folder not found!');
    }


    function processFolder(folder) {
        while (folder.hasNext()) {
            var f = folder.next();
            var contents = f.getFiles();
            addFilesToSheet(contents, f);
            var subFolder = f.getFolders();
            processFolder(subFolder);
        }
    }

    function addFilesToSheet(files, folder) {
        var data;
        var folderName = folder.getName();
        while (files.hasNext()) {
            var file = files.next();
            Logger.log(file.getName());

            sheet.appendRow([
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl(),
      "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
      file.getDescription(),
      file.getMimeType(),
      folderName
    ]);
        }
    }

    
}

@Cooper thanks for the input . On another note , does drive api has any option to get the size in terms of GB ( not bytes ) file.getSize() by default outputs size in bytes — Sachin, Jun 15 '21 at 16:44
i haven't been able to find it yet , i guess will ask a separate question for it then — Sachin, Jun 15 '21 at 17:47
This is the response for a get: https://developers.google.com/drive/api/v2/reference/files — Cooper, Jun 15 '21 at 18:05

Kin Siang · Accepted Answer · 2021-06-15T14:49:54.580

1

To check whether a specific file type exist in Gdrive, you cannot use If statement but file.hasnext() function, below is the method to check only spreadsheet type and return the property as per your expectation, do take note that it will be meaningless action to get the file size of spreadsheet since it will be 0 byte due to store in Google database:

function fileType() {
  var folder = DriveApp.getFolderById('xxx');
  var files = folder.getFiles();
  while(files.hasNext()){
    var file = files.next();
    Logger.log(file.getUrl());
    Logger.log(file.getName())
  }
}

I have performed the testing, the entire code work fine, except this part, by amended I will have all the log and sheet data updating :)

From

var folder = DriveApp.getFoldersById(FOLDER_ID);

    if (folder.hasNext()) {
        processFolder(folder);
    } else {
        Browser.msgBox('Folder not found!');
    }

To:

var folders = DriveApp.getFolderById('xxx');
    var folder = folders.getFolders();
    if (folder.hasNext()) {
        processFolder(folder);
    } else {
        Browser.msgBox('Folder not found!');
    }

edited Jun 15 '21 at 14:49

answered Jun 15 '21 at 11:00

Kin Siang

2,644
2
4
8

Yes, it is easier to do with all files type, check my edited answer by using `getfiles` – Kin Siang Jun 15 '21 at 12:26
But this doesn't work recursively , its only listing the files if they are directly present in the root of folderID passed to script – Sachin Jun 15 '21 at 13:21
Are you trying to obtain entire file from all your folder?? – Kin Siang Jun 15 '21 at 13:24
You should use `.addUi` if you want to add custom menu https://developers.google.com/apps-script/guides/menus, and `FOLDERID` is not a valid address, you should select specific folder for the one you want to check ie. `1xasddxxxx`, for the remaining part , I think it has many unnecessary call....and you need to workout on it based on the most simple method to get all file details and amend it... – Kin Siang Jun 15 '21 at 14:16
I am obviously entering my folder id in its place , i have replaced it with (FOLDER_ID) just for the purpose of this question – Sachin Jun 15 '21 at 14:25
Check my edited answer, now it solve, accept if helping :) – Kin Siang Jun 15 '21 at 14:50

How to list all files recursively from google drive folder to spreadsheet?

1 Answers1