Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[Google Apps Script] List all files & folders in a Google Drive folder, & write into a speadsheet
/*
* Copyright 2017 Mohsen Mesgarpour
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* -----------------------------------------------------------------------------------------------------------
*
* ListFilesFolders script: It is a Google Apps Script, which lists all files and/or folders in a
* Google Drive folder, and then writes the list into a spreadsheet in batches. The script uses a
* caching mechanism to allow output recovery after possible crash; however, it won't continue
* to recover the interrupted script and continue the search.
* If you need to reduce/remove limitation on script runtime refer to the quotas for
* Google Services: https://developers.google.com/apps-script/guides/services/quotas
*
* Functions: There are two accessible functions that you may call:
* - 'run': It lists all folders and optionally files in the specified location, then writes them into
* the selected spreadsheet.
* - 'reset': It resets the script global cache. It must be run if the script was interrupted, to
* clean out the cache and triggers. Moreover, since the outputs are cached and are written
* after the whole list is created, if you run the script after the crash it would write all
* the cached output into the sheet, then clears the cache.
*
* Configurations: The following configuration parameters must be configured before running the script.
* - 'folderId' (type: string):
* The folder ID. The folder ID is everything after the 'folders/' portion of the URL.
* - 'searchDepthMax' (type: unsigned integer):
* The maximum depth for the recursive search of folders.
* - 'listFiles' (type: boolean):
* It is a flag to indicate if the listing must include files.
* - 'cacheTimeout' (type: unsigned integer, in milliseconds):
* The maximum time that internal cache persists in memory.
* - 'lockWaitTime' (type: unsigned integer, in milliseconds):
* The maximum watiting time for the cache reader/writer to wait for the memory lock.
* - 'appendToSheet' (type: boolean):
* It is a flag for appending to the selected spreadsheet.
* - 'writeBatchSize' (type: unsigned integer):
* The batch size for writing into the spreadsheet.
*
* Algorithm: The written functions uses a recursive function to list files & folders, and it uses
* a caching mechanisem to save the outputs in cache and write at the end.
*
* -----------------------------------------------------------------------------------------------------------
* Note-1: Because Apps Script services impose daily quotas and hard limitations on some features. If
* you exceed a quota or limitation, your script will throw an exception and terminate execution.
*
* Note-2: Firstly, set your folder ID ('folderId' variable)! You may copy the folder ID from the
* browser's address field. The folder ID is everything after the 'folders/' portion of the URL
* for Google Drive folder.
* Secondly, set the 'searchDepthMax' to a reasonable number, a very large number can
* significantly delay the outputs or may cause unexpected termination.
* Thirdly, set the 'listFiles' to 'false', if you only require the folders to be listed.
* Finally, other configuration parameters are preconfigured and can be left as default.
*
* Note-3: Because, this is a script and not a certified app, you must grant it permission, to run.
* When you run it for the first time, a pop-up window will open & asks you for permission.
*
* Note-4: Files and folders must NOT be modified in the selected path, as it may corrupt the
* generated list.
*
* Note-5: If you interrupt the script you might have to wait a few seconds (maximum 6 minutes),
* until you can re-run it.
*
* Note-6: It is recommended to first run the script on a small set of files and folders.
*
* Note-7: Make sure there are no other script in the current Google Sheet with similar function or
* variable names.
*
* Note-8: Refer to version 1.0 of the script, for a simplified version of the ListFilesFolders script:
* https://gist.github.com/mesgarpour/07317e81e9ee2b3f1699/23833cef09a62a3d2cf56b4143bb3cf4dbb5b827
*
* Note-9: The "teamdrive" does not support "getSharingPermission()", therefore comment out the lines
* that uses this function.
*
* Note-10: Note that when you use business version of the Google Drive, there are still limits on how
* much time can be spent on auditing (refer to Google's quotas). For instance, the script would
* timeout after ten minutes search a folder, therefore avoid auditing very big or deep folders.
*
* -----------------------------------------------------------------------------------------------------------
*
* @version 2.4 (2020.05)
* @see https://github.com/mesgarpour
*/
// Configurable variables
var folderId = 'My folder ID'; // The folder ID (everything after the 'folders/' portion of the URL).
var searchDepthMax = 100; // Max depth for recursive search of files and folders
var listFiles = true; // flag for listing files
var cacheTimeout = 24 * 60 * 60 * 1000; // set cache time-out
var lockWaitTime = 1 * 60 * 1000; // set maximium watiting time for the cache lock
var appendToSheet = false; // flag for appending to selected spreadsheet
var writeBatchSize = 100; // the write batch size
// ===========================================================================================================
// Global variables
var cacheOutputs = 'InventoryScript_outputs';
var cacheKillFlag = 'InventoryScript_killFlag';
// ===========================================================================================================
// Reset the script cache if it is required to run from the beginning
function reset() {
SpreadsheetApp.getActiveSpreadsheet().toast('Reseting script...', 'Status', -1);
// reset triggers and delete cache variables
setKillFlag_(true, this.cacheTimeout);
deleteTriggers_(this.loopResetGapTime);
deleteCache_();
SpreadsheetApp.getActiveSpreadsheet().toast('Reset is complete!', 'Status', -1);
}
// ===========================================================================================================
// List all folders and files, then write into the current spreadsheet.
function run() {
SpreadsheetApp.getActiveSpreadsheet().toast('Executing script...', 'Status', -1);
// load cache
setKillFlag_(false, this.cacheTimeout);
var outputRows = getCache_(this.lockWaitTime);
// get list
if (outputRows === undefined || outputRows === null ||
outputRows[0] === undefined || outputRows[0] === null) {
outputRows = [];
outputRows = getChildFiles_(null, DriveApp.getFolderById(this.folderId),
listFiles, cacheTimeout, outputRows);
outputRows = getFolderTree_(outputRows, this.folderId, this.listFiles, this.cacheTimeout,
this.lockWaitTime, this.searchDepthMax);
}
// write list
writeFolderTree_(outputRows, this.appendToSheet);
SpreadsheetApp.getActiveSpreadsheet().toast('Execution is complete!', 'Status', -1);
}
// ===========================================================================================================
// Get the list of folders and files
function getFolderTree_(outputRows, folderId, listFiles, cacheTimeout, lockWaitTime, searchDepthMax) {
var parentFolder, sheet = null;
var searchDepth = -1;
try {
// Get folder by id
parentFolder = DriveApp.getFolderById(folderId);
// Initialise the spreadsheet
sheet = SpreadsheetApp.getActiveSheet();
// Get files and/or folders
outputRows = getChildFolders_(searchDepth, parentFolder.getName(), parentFolder, sheet,
listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
return outputRows;
}
// ===========================================================================================================
// Write the list of folders and files into the spreadsheet
function writeFolderTree_(outputRows, appendToSheet) {
var sheet = null;
try {
if (getKillFlag_() === false) {
// Initialise the spreadsheet
sheet = SpreadsheetApp.getActiveSheet();
// Write to the selected spreadsheet
writeOutputs_(sheet, outputRows, appendToSheet);
// reset cache
reset();
}
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}
// ===========================================================================================================
// Get the list of folders and files and their metadata using a recursive loop
function getChildFolders_(searchDepth, parentFolderName, parentFolder, sheet, listFiles, cacheTimeout,
lockWaitTime, outputRows, searchDepthMax) {
var childFolders = parentFolder.getFolders();
var childFolder = null;
searchDepth += 1;
try{
// List sub-folders inside the folder
while (childFolders.hasNext() && searchDepth < searchDepthMax && getKillFlag_() === false) {
childFolder = childFolders.next();
SpreadsheetApp.getActiveSpreadsheet().toast('Searching folder ' + childFolder.getName() +
' at depth ' + searchDepth + " ...", 'Status', -1);
// Get folder information
// Logger.log("Folder Name: " + childFolder.getName());
outputRows.push([
parentFolderName + "/" + childFolder.getName(),
childFolder.getName(),
"Folder",
childFolder.getDateCreated() ? childFolder.getDateCreated() : "NULL",
childFolder.getUrl() ? childFolder.getUrl() : "NULL",
childFolder.getLastUpdated() ? childFolder.getLastUpdated() : "NULL",
childFolder.getDescription() ? childFolder.getDescription() : "NULL",
childFolder.getSize() ? childFolder.getSize() : "NULL",
childFolder.getOwner() ? childFolder.getOwner() : "NULL",
childFolder.getOwner().getEmail() ? childFolder.getOwner().getEmail() : "NULL",
childFolder.getSharingPermission() ? childFolder.getSharingPermission() : "NULL",
childFolder.getSharingAccess() ? childFolder.getSharingAccess() : "NULL"
//, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFolder.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
// more info: https://developers.google.com/apps-script/reference/drive/folder
]);
// cache outputs
setCache_(outputRows, lockWaitTime, cacheTimeout);
// List files inside the folder
outputRows = getChildFiles_(
parentFolder, childFolder, listFiles, cacheTimeout, outputRows);
// Recursive call of the current sub-folder
outputRows = getChildFolders_(searchDepth++, parentFolderName + "/" + childFolder.getName(),
childFolder, sheet, listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);
}
} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast( 'Timed out!', 'Status', -1);
}
// cache outputs
setCache_(outputRows, lockWaitTime, cacheTimeout);
return outputRows;
}
// ===========================================================================================================
// Get the list of files in the selected folder
function getChildFiles_(parentFolder, childFolder, listFiles, cacheTimeout, outputRows) {
var childFiles = childFolder.getFiles();
var childFile = null;
var path = ""
try{
// List files inside the folder
while (listFiles && childFiles.hasNext()) {
childFile = childFiles.next();
// derive path
if (parentFolder === null){
path = childFolder.getName() + "/" + childFile.getName()
}else{
path = parentFolder.getName() + "/" + childFolder.getName() + "/" + childFile.getName()
}
// Get file information
//Logger.log("File Name: " + childFile.getName());
outputRows.push([
path,
childFile.getName(),
childFile.getName().split('.').pop(),
childFile.getDateCreated() ? childFile.getDateCreated() : "NULL",
childFile.getUrl() ? childFile.getUrl() : "NULL",
childFile.getLastUpdated() ? childFile.getDescription() : "NULL",
childFile.getDescription() ? childFile.getDescription() : "NULL",
childFile.getSize() ? childFile.getSize() : "NULL",
childFile.getOwner() ? childFolder.getOwner() : "NULL",
childFile.getOwner().getEmail() ? childFile.getOwner().getEmail() : "NULL",
childFile.getSharingPermission() ? childFile.getSharingPermission() : "NULL",
childFile.getSharingAccess() ? childFile.getSharingAccess() : "NULL"
//, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFile.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
// more info: https://developers.google.com/apps-script/reference/drive/folder
]);
}
// cache outputs
setCache_(outputRows, lockWaitTime, cacheTimeout);
} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
return outputRows;
}
// ===========================================================================================================
// Set the values from cache
function setCache_(outputRows, lockWaitTime, cacheTimeout) {
try{
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();
lock.waitLock(lockWaitTime);
cache.put(cacheOutputs, JSON.stringify(outputRows), cacheTimeout);
lock.releaseLock();
} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}
// ===========================================================================================================
// Get the values in cache
function getCache_(lockWaitTime) {
try{
var outputRows = [];
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();
lock.waitLock(lockWaitTime);
outputRows = JSON.parse(cache.get(cacheOutputs));
if (outputRows === undefined || outputRows === null ||
outputRows[0] === undefined || outputRows[0] === null) {
outputRows = JSON.parse(cache.get(cacheOutputs));
}
lock.releaseLock();
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
return outputRows;
}
// ===========================================================================================================
// Write outputs to the selected spreadsheet
function writeOutputs_(sheet, outputRows, appendToSheet) {
try{
var range, rowStart, indexStart, indexEnd = null;
var headerRow = ["Full Path", "Name", "Type", "Date", "URL", "Last Updated", "Description", "Size",
"Owner", "Sharing Permission", "Sharing Access"]; //, "Thumbnail"];
SpreadsheetApp.getActiveSpreadsheet().toast('Writing outputs...', 'Status', -1);
if (sheet !== null && outputRows.length > 0) {
if (appendToSheet === false) {
sheet.clear();
sheet.appendRow(headerRow);
rowStart = 2;
} else {
rowStart = getRowsFilled_(sheet, "A1:A") + 1;
}
indexStart = 0;
indexEnd = Math.min(writeBatchSize, outputRows.length);
while (indexStart < outputRows.length) {
range = sheet.getRange(rowStart + indexStart, 1, indexEnd - indexStart, outputRows[0].length);
range.setValues(outputRows.slice(indexStart, indexEnd));
a = outputRows.slice(indexStart, indexEnd);
indexStart = indexEnd;
indexEnd = Math.min(indexStart + writeBatchSize, outputRows.length);
}
range = sheet.getRange(getRowsFilled_(sheet, "A1:A") + 1, 1, 1, 1);
range.setValues([["End of List!"]]);
}
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}
// ===========================================================================================================
// Get number of rows filled in the selected spreadsheet
function getRowsFilled_(sheet, selectedRange) {
var selectedMatrix = sheet.getRange(selectedRange).getValues();
return selectedMatrix.filter(String).length;
}
// ===========================================================================================================
// Delete the global cache
function deleteCache_() {
try{
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();
lock.waitLock(this.lockWaitTime);
cache = CacheService.getScriptCache();
cache.remove(cacheOutputs);
lock.releaseLock();
} catch (e) {
Logger.log('Failed to delete cache! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete cache! Try again in a few minutes.');
}
}
// ===========================================================================================================
// Delete triggers
function deleteTriggers_() {
var triggers = ScriptApp.getProjectTriggers();
try{
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "run") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
} catch (e) {
Logger.log('Failed to delete triggers! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete triggers! Try again in a few minutes.');
}
}
// ===========================================================================================================
// Set kill flag
function setKillFlag_(state, cacheTimeout) {
var lock = LockService.getScriptLock();
try{
lock.waitLock(this.lockWaitTime);
cache = CacheService.getScriptCache();
cache.put(cacheKillFlag, state, cacheTimeout);
lock.releaseLock();
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
}
}
// ===========================================================================================================
// Get kill flag
function getKillFlag_() {
killFlag = false;
try {
cache = CacheService.getScriptCache();
//lock.waitLock(this.lockWaitTime);
killFlag = cache.get(cacheKillFlag) === 'true';
//lock.releaseLock();
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
}
return killFlag;
}
@Ataraxiainc1

This comment has been minimized.

Copy link

@Ataraxiainc1 Ataraxiainc1 commented Nov 11, 2016

Nice

@mmcloughlinpca

This comment has been minimized.

Copy link

@mmcloughlinpca mmcloughlinpca commented Jan 13, 2017

Hi,

There appears to be a typo - listfolers > listFolders, perhaps?

Cheers,

  • Mike
@txoof

This comment has been minimized.

Copy link

@txoof txoof commented Mar 11, 2017

@megsarpour,

Thanks for the code. It worked right out of the box! I would like to modify it to make it less thorough though.

How can I limit the recursion inside the getChildFolders function? I want to only get the first child folders within the specified folder. I don't want to recurse to the tip of the branch, just one level deep.

In the example below I would like to get a listing from MyFolder. I only want to get information for the first children from MyFolder (A, B, C, D). I want to skip all lower children. Is there a way of doing this without doing some pruning of the data as it comes in?

Thanks for your great script!

MyFolder

  • A
    • child of A 1
    • child of A 2
      • child of child of A 2
  • B
    • child of B 1
      • child of child of B 1
    • child of B 2
  • C
  • D
    • child of D 1
    • child of D 2

`
function getChildFolders(parentName, parent, data, sheet, listAll) {
var childFolders = parent.getFolders();

// List folders inside the folder
while (childFolders.hasNext()) {
var childFolder = childFolders.next();
// Logger.log("Folder Name: " + childFolder.getName());
data = [
parentName + "/" + childFolder.getName(),
childFolder.getName(),
childFolder.getDateCreated(),
childFolder.getUrl(),
childFolder.getLastUpdated(),
childFolder.getDescription(),
childFolder.getSize()
];
// Write
sheet.appendRow(data);
`

@txoof

This comment has been minimized.

Copy link

@txoof txoof commented Mar 11, 2017

I think I may have just answered my own question. I just need to remove that last recursive call at the end and that solved my problem.

Again thank you so very much for the great scripts!

@RazorII

This comment has been minimized.

Copy link

@RazorII RazorII commented Mar 26, 2017

Is it possible to get the folder list in alphabetical order?

@RazorII

This comment has been minimized.

Copy link

@RazorII RazorII commented Mar 26, 2017

Also, is it possible to have only changes inserted as rows instead of pulling in the entire folder list all over?

@dalford11

This comment has been minimized.

Copy link

@dalford11 dalford11 commented Jun 12, 2017

I'm unable to get the script to work! I'm sure I'm omitting something small, but cant figure it out - can anyone help?

The script runs without error, but generates no output.

What I've tried:

  1. I copy the script as written above into 'script editor', and within a broader function (aka myFunction). I've also tried running functions seperately via a Ui with no luck.
  2. I set the folder ID from the folder URL (copying everything after 'folder/'). It's a large folder (several Gb and 1000s of files), so have also tried setting smaller folder IDs - no change to the results above (no error, no output).
  3. I've also tried specifying the Sheet Name to write to, rather than the 'getActiveSheet' - neither change the results above (no error, no output).
@soslosam

This comment has been minimized.

Copy link

@soslosam soslosam commented Jul 4, 2017

Hello every one,
The above code is not working for me. The out is provided on the the link https://snag.gy/qCnG6V.jpg

@shieldss

This comment has been minimized.

Copy link

@shieldss shieldss commented Jul 28, 2017

This is great. Thanks for sharing. Question: is there a way to get the files in the top level folder with "listAll"? Currently, if I enter the folder ID and run "listAll", I get the child folders and child folder files of the folder ID parent folder, but no files in the parent folder ID folder. I seem to need to have everything in a folder in order to successfully get all files, which is not practical for my implementation. Thanks!

@TOMPETER1

This comment has been minimized.

Copy link

@TOMPETER1 TOMPETER1 commented Aug 8, 2017

THIS IS AMAZING!!! REALY. Thank you so much!

@mesgarpour by any chance an idea how to modify this script to add all files to one bulk folder:
I tried "addFile(child)" Function, but could not make it work.

¿Ideas anyone? (would like to avoid to write a Drive App for this).

@zeluspudding

This comment has been minimized.

Copy link

@zeluspudding zeluspudding commented Aug 17, 2017

@TOMPETER1 How did you get this to work? I can't get it to run?

@8888simon8888

This comment has been minimized.

Copy link

@8888simon8888 8888simon8888 commented Aug 19, 2017

I can run this no problem when I execute it from within the script file by choosing Run => listFolers(). But if I try to trigger it from the spreadsheet itself by adding:

function onEdit() {
  listFolers();
}

it doesn't execute.

For a script I am working on I need to be able to interact with DriveApp from the spreadsheet using an onEdit() trigger. I find that I can interact with the spreadsheet using onEdit() no problem until I make a call to DriveApp. Then any call to the sheet after that (when initated by an onEdit() trigger does not execute. But all lines of code (calls to DriveApp and calls to SpreadsheetApp) all work if I initiate the code from within the script file by choosing Run => onEdit(). Is this a bug in GAS? Have you been able to call this code using an onEdit() trigger?

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Oct 15, 2017

Sorry, I was not monitoring the comments.

I have applied the typo correction and uploaded version 2.0 with caching mechanism to speed-up the script. however, it won't attempt to recover the interrupted script and continue the search.

If you need to reduce/remove limitation on script runtime refer to the quotas for Google Services.

@mmcloughlinpca Thanks for the correction
@txoof : Thanks for your interest.
@MONKiCODE : Excellent suggestion. I leave it to the community to implement that.
@soslosam and dalford11 : It is possible that you defined a wrong folder ID;
@shieldss : You need to call folder with the folder ID, and it would list all files and folders. In version 2.0 of the script you are able to specify the depth of search as well.
@TOMPETER1 : Make use you are using file and folder id
@zeluspudding : Please refer to the documentation: https://developers.google.com/apps-script/guides/sheets/functions
@8888simon8888 : Thanks for the explanation

@PMKMUTT

This comment has been minimized.

Copy link

@PMKMUTT PMKMUTT commented Oct 17, 2017

I used the script and found "Time out!" status, could you please help me.

I have attached estimate folder size / amount of files and Time out! status following picture
image
image

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Oct 21, 2017

@PMKMUTT

If it times out, it means that the script reached its maximum runtime.

"Apps Script services impose daily quotas and hard limitations on some features. If you exceed a quota or limitation, your script will throw an exception and terminate execution.".

If it times out, you can still recover the partial list that it generated, by rerunning the script.

It is not possible to write a script without any time limitations using only free quota.

But, I can think of a few solutions:
1. Use the commercial version of the G Suit, to run it without any limitation.
2. Run the script on a smaller set of files and folders.
3. If you have business account, you can ask the administrator to regularly produce a comprehensive inventory list.
4. Use a commercial tool (e.g. zapier.com).

@PapaDocta

This comment has been minimized.

Copy link

@PapaDocta PapaDocta commented Nov 27, 2017

how can I specify in the script to look and return only pdf files?

@BIManager

This comment has been minimized.

Copy link

@BIManager BIManager commented Dec 1, 2017

@dalford11 @soslosam
I know almost nothing about Google scripts but after trying a bunch of other stuff, I spotted that line 93 says false. I changed it to true and then it worked.

@mesgarpour
Is this just an error here? Superb code and incredibly useful. Thank you.

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Dec 2, 2017

@BIManager @dalford11 @soslosam
I have corrected the bug that was avoiding to list files in the main folder

Note: "True" value for the "appendToSheet" variable only signals to the script to clear the sheet before writing it. And, "False" means that it will start from the first row that column A has no value.

Note: If you don't get any result, it means that the selected path consists of many files and folders, and the script times out. If it times out, you can still recover the partial list that it generated, by rerunning the script. It is not possible to write a script without any time limitations using only free quota.

@hansiefer

This comment has been minimized.

Copy link

@hansiefer hansiefer commented Feb 19, 2018

Hi Mohsen,
Thanks for providing this. I'm pretty new to Google Scripts and while running this I got the following error "TypeError: Cannot call method "toast" of null. (line 120, file "MVT2017TR")". Is it because I'm not following the proper steps?

  • I logged into script.google.com

  • Opened a new session (Script/Project)

  • Copy-pasted your code and modified the variables (Folder name, depth)

  • Ran and gave permission

  • I get the error that it "cannot call method "toast"" (line 120)

    SpreadsheetApp.getActiveSpreadsheet().toast('Executing script...', 'Status', -1);

If you are not initialising a sheet, how does the script know where to input the data? Do I have to create a spreadsheet in Google Drive with a specific name?

Thank you so much for any help.

Kind Regards, Hansie

Update: I tried modifying and creating a spreadsheet with a bit of your v1 code, but still getting the same error.
" // Initialise the sheet
var file, data, sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();
sheet.appendRow(["Full Path", "Name", "Date", "URL", "Last Updated", "Description", "Size"]);
"

@asafpeled

This comment has been minimized.

Copy link

@asafpeled asafpeled commented Feb 20, 2018

@hansiefer I had the same problem but I resolved it by creating a new spreadsheet, then from the menu do Tools -> Script Editor then copy and paste all the code from this project into there, save and run. (don't forget to re-enter the folder ID)

@asafpeled

This comment has been minimized.

Copy link

@asafpeled asafpeled commented Feb 21, 2018

I am scanning a team drive at our company which has thousands of files and I'm getting a lot of timeout messages. What do you recommend? Can i increase those timeout globals or will that cause unintended side effects?

UPDATE: I put a trace on the timeouts. All of them are happening in the setCache function

@brubrandao

This comment has been minimized.

Copy link

@brubrandao brubrandao commented Feb 22, 2018

Hello,

Thank you for the great code, it works pretty well.

I was thinking about how I could modify the code so that it searches for a word (string) in all my folders. The table listing would be the same. Would you have any tip or indication?

Thanks!

@asafpeled

This comment has been minimized.

Copy link

@asafpeled asafpeled commented Feb 22, 2018

@brubrando can you not just do that from the advanced search on google drive's interface?

@OlivierFB

This comment has been minimized.

Copy link

@OlivierFB OlivierFB commented Mar 14, 2018

Hi @mesgarpour,

Thank you for your work, it's amazing!
A question about it :
Is there a way to create exception folders? #
Like if the ID is 73_D8C2... or if the name is "Archive", etc.

Thanks :D
Olivier

@benjaminbradley

This comment has been minimized.

Copy link

@benjaminbradley benjaminbradley commented Mar 20, 2018

Thanks for the script! When I ran it I got "DriveUser" as every value under the Owner column.
To fix this, everywhere you see: getOwner() change it to getOwner().getEmail() (object ref here)
For example:
childFolder.getOwner().getEmail(),

There are a couple places in the script you need to update.

@gchigo

This comment has been minimized.

Copy link

@gchigo gchigo commented Mar 23, 2018

Hi @mesgarpour,
Thanks for sharing this script. It works great. How do I modify it so the output is given in a specific sheet in a specific cell? Thanks in advance!

@laurenehh

This comment has been minimized.

Copy link

@laurenehh laurenehh commented Mar 26, 2018

Is it possible for this script to have a function to update whenever a new document is added to the google drive without rerunning the entire thing?

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Apr 8, 2018

Hi,

Sorry again for late reply.

@hansiefer: @asafpeled answer is correct, thanks!

@asafpeled: refer to this answer (@mesgarpour commented on Oct 21, 2017)

@brubrandao: Firstly you have to make sure the files are compatible (https://support.google.com/drive/answer/176692). Then you may use the DriveApp file object, which has a getBlob() method that has a getDataAsString() method (https://developers.google.com/apps-script/reference/base/blob)

@OlivierFB: a quick hack is to add the following to line 206:
if (childFolder.getName() = "name of the excluded folder")
{continue;}

@benjaminbradley: Thanks for correction. The code is updated.

@gchigo: A quick hack, to achieve this:
1. replace these two line: 157 & 178, with the following:
sheet = SpreadsheetApp.getSheetByName("Name");
2. in line 344 specify the line number (default row is 2)
3. change line 343 (the default header position:: row 1, column: 1), to the following:
sheet.getRange(1,1,1,12).setValues(headerRow);

@laurenehh: To achieve that, the Push Notifications API must be added (https://developers.google.com/drive/v3/web/push). Hopefully, I will add the functionality in the next version.

Thanks again,
Mohsen

@Jobcoder

This comment has been minimized.

Copy link

@Jobcoder Jobcoder commented May 24, 2018

Hi @mesgarpour Is it possible to put the thumbnail link of each file so that we don't need to use the URL to access it? And are we obliged to run the script again when we have a new file in a folder? I read the answer to @laurenehh question but it did not update when I put a new file in the subfolders

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented May 28, 2018

Hi @Jobcoder,
I just added the thumbnail, but I commented out the code (i.e. disabled it), Because, it would need G-Suite account (business account).
Yes, you have to re-run it again. Unfortunately, the "Push Notifications API" also needs a G-Suite account.
Thanks.

@erik3355

This comment has been minimized.

Copy link

@erik3355 erik3355 commented Jun 19, 2018

@mesgarpour Great script, nice work. When I'm writing folder structure from a teamdrive however, the script won't work since it throws an exception when it tries to write the email property of the owner of the folder. The owner is null. (maybe there is no specific owner for folders in a teamdrive?) Lines 219 and 278. I just replaced the lines with an empty string to fix my the issue. I'm sorry if it is a bit cryptic, I didn't look into it much but I thought I'd let you know.

@csrafferty

This comment has been minimized.

Copy link

@csrafferty csrafferty commented Sep 24, 2018

@mesgarpour Really nice job, thank you.
Just for the record, we have a paid commercial version of Google Apps with about twenty users. If I run on a folder with up to 900 sub items, the script works great. For bigger folders, it hits the 10 minute timeout limit. Your mileage may vary but that at least gives a sense of how big a job it can take on. Not at all an issue with the script, just with Google's limits.

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Oct 21, 2018

Sorry for late reply.
@csrafferty thanks for letting us know. It is great, I did not have a chance to test it using commercial version.
@erik3355 thanks for raising the issue. Now, I have added a comment about the issue.

@rbh-se

This comment has been minimized.

Copy link

@rbh-se rbh-se commented Oct 23, 2018

When I run the skript, I get the error:
` TypeError: You can not call the method "toast" from zero. (row: 127, file: code)

it's a pity! I've been looking for someting like this.

@jpickel222

This comment has been minimized.

Copy link

@jpickel222 jpickel222 commented Oct 27, 2018

@mesgarpour
This is great. Is there a way to combine this function with the tab index function below to generate a list of the spreadsheets in a folder and all of the the tab IDs (or at least the first one) for all of the spreadsheets in a folder?

https://www.benlcollins.com/spreadsheets/index-sheet/

@jpcengele

This comment has been minimized.

Copy link

@jpcengele jpcengele commented Jan 20, 2019

Good evening .. some urgent help please .. I am getting some errors when running the "run" and the "reset" scripts as follow. can anyone help as this is time sensitive.

screen shot 2019-01-20 at 18 08 32

screen shot 2019-01-20 at 18 26 13

@jpcengele

This comment has been minimized.

Copy link

@jpcengele jpcengele commented Jan 20, 2019

it returned also

TypeError: Cannot call method "toast" of null. (line 127, file "allfiles")Dismiss

@jpcengele

This comment has been minimized.

Copy link

@jpcengele jpcengele commented Jan 20, 2019

@SaKa1979

This comment has been minimized.

Copy link

@SaKa1979 SaKa1979 commented Jan 28, 2019

@jpcengele
Afaik it needs to be run from a Google sheet. Hence 'getActiveSpreadsheet' method
So:

  1. Open an empty Google sheet
  2. Select Extra - Scripteditor
  3. In the script editor copy paste above code. Save it under an appropriate name
    Two possible methods to run the script:
    4a) Run the script from the scrip editor. Once it is finished the data will be entered into the opened Google sheet.
    4b) Back to the Google sheet, goto Extra - Macro's - Import. Your script should be listed there. Select add function.
  4. To run the script, Extra - Macro's, and select your function.
@stevenrittner

This comment has been minimized.

Copy link

@stevenrittner stevenrittner commented Feb 6, 2019

Hello all,
Curious if there is a way to structure the output so that it is not sorted by the path first and then in the order of when a file was added to a folder, but if it could be sorted globally by when a file was added to the drive. I've attached a screen shot for clarity:
image

So for instance, when I add a file to folder "TEST" it populates on the very top of the spreadsheet, this is good, I want all new files to populate at the top. When I add a file to the folder "TEST/OTHER FOLDER" it will populate at the top of that sub-folder section, which in this case is row 7, when I want it all new files to populate in row two and push the older files down.

Is this possible? I've successfully taken out the part where the script writes the folder name to the output, I have no need for that, and I've gone as far as I can with my knowledge of coding, but I can't figure out how to "sort" the output so everything will always populate in row 2 and not at the top of the child folder. PLEASE HELP! It's great appreciated!!

@DerekCaelin

This comment has been minimized.

Copy link

@DerekCaelin DerekCaelin commented Mar 6, 2019

I have a colleague at work who wanted to generate a sitemap for his drive but didn't want to trust a 3rd party vendor for security reasons. This script helped me do that for him. Thanks so much!

@zphdude

This comment has been minimized.

Copy link

@zphdude zphdude commented Mar 6, 2019

This code is AMAZING. Thank you. I am new to programming and I am learning a lot by looking through this.

@zphdude

This comment has been minimized.

Copy link

@zphdude zphdude commented Mar 6, 2019

I found a little problem with your code. If you have a child folder that has a file that is more than 2 levels deep, the file path listed is not the actual full file path.

Example:
Master/1/2/3/4/FILE

The folder will be listed properly:
Master/1/2/3/4
The file will appear as
3/4/FILE

To fix this I added a new global variable:
var fullFilePath = "";

Under function getChildFolders_() I added the global variable at the end of this code to capture the full file path.

try{
   // List sub-folders inside the folder
   while (childFolders.hasNext() && searchDepth < searchDepthMax && getKillFlag_() === false) {
     childFolder = childFolders.next();
     SpreadsheetApp.getActiveSpreadsheet().toast('Searching folder ' + childFolder.getName() +
       ' at depth ' + searchDepth + " ...", 'Status', -1);
     
     // Get folder information
     // Logger.log("Folder Name: " + childFolder.getName());
     outputRows.push([
       parentFolderName + "/" + childFolder.getName(),
       childFolder.getName(),
       "Folder",
       childFolder.getDateCreated(),
       childFolder.getUrl(),
       childFolder.getLastUpdated(),
       childFolder.getDescription(),
       childFolder.getSize(),
       childFolder.getOwner().getEmail(),
       childFolder.getSharingPermission(),
       childFolder.getSharingAccess()
       //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFolder.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
     ]);
     this.fullFilePath = parentFolderName + "/" + childFolder.getName();

Under function getChildFiles_() I replaced your 'if' original with the following:

      // derive path
      if (parentFolder === null){
        path = childFolder.getName() + "/" + childFile.getName()
      }else{
        path = this.fullFilePath + "/" + childFile.getName()
      }

Now when you run the script you get the following results:

Example:
Master/1/2/3/4/FILE

The folder listing:
Master/1/2/3/4
The file listing:
Master/1/2/3/4/FILE

@tgmorrissey

This comment has been minimized.

Copy link

@tgmorrissey tgmorrissey commented Mar 20, 2019

This is exactly what I was looking for! Thank you. I have successfully run it on my individual drive, but I am Having issues getting this to work with TeamDrive. Anyone have insight into getting it to play nice with TeamDrive

@twoplancker

This comment has been minimized.

Copy link

@twoplancker twoplancker commented Mar 28, 2019

I can also run it on my individual drive but not on Team Drive. Any help would be greatly appreciated.

@INicusor

This comment has been minimized.

Copy link

@INicusor INicusor commented Apr 17, 2019

Hello @mesgarpour ,

Is it possible to adapt the script to also show permissions per folder , subfolders and files?
Like who has access to the folder , sub folder and the files (View , edit ) .

Thank you

@einergehtnoch

This comment has been minimized.

Copy link

@einergehtnoch einergehtnoch commented Apr 19, 2019

@mesgarpour - thank you for this. great job.

@zphdude - thanks for the fix.

@LucaVJ

This comment has been minimized.

Copy link

@LucaVJ LucaVJ commented May 16, 2019

@mesgarpour - This is absolutely great, and a huge time saver... but:

I was able to successfully retrieve an exhaustive list of folders in the root folder, but I had to silence the recursive call for subfolders because my target folder is too large and deep and it was obviously timing out.

I am wondering if there's a workaround to the timing out:

Would it be possible to use an array of folderIDs when declaring the variable folderID and iterate the function to run, reset, run, reset, and append results as it carries through?

This way I could even list files, which I have omitted for the moment.

Any tips are welcome. I am stuck.

Thanks

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented May 23, 2019

@LucaVJ currently there is no known workaround, as far as I know. Your solution would probably work if the folders have very limited number of files (https://developers.google.com/apps-script/guides/services/quotas).
The only free feasible option is to synch the main folder on your local machine and generate an inventory using a bash/shell script.
Thanks again

@tracidoupenn

This comment has been minimized.

Copy link

@tracidoupenn tracidoupenn commented Jun 16, 2019

this code worked for me all year but all the sudden, I'm stuck in my tracks.

It fails to on run output (139) and child files (176). Please help!

/*

  • Copyright 2017 Mohsen Mesgarpour
  • Licensed under the Apache License, Version 2.0 (the "License");
  • you may not use this file except in compliance with the License.
  • You may obtain a copy of the License at
  • http://www.apache.org/licenses/LICENSE-2.0
    
  • Unless required by applicable law or agreed to in writing, software
  • distributed under the License is distributed on an "AS IS" BASIS,
  • WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  • See the License for the specific language governing permissions and
  • limitations under the License.

  • ListFilesFolders script: It is a Google Apps Script, which lists all files and/or folders in a
  •         Google Drive folder, and then writes the list into a spreadsheet in batches. The script uses a
    
  •         caching mechanism to allow output recovery after possible crash; however, it won't continue
    
  •         to recover the interrupted script and continue the search.
    
  •         If you need to reduce/remove limitation on script runtime refer to the quotas for
    
  •         Google Services: https://developers.google.com/apps-script/guides/services/quotas
    
  • Functions: There are two accessible functions that you may call:
    • 'run': It lists all folders and optionally files in the specified location, then writes them into
  •        the selected spreadsheet.
    
    • 'reset': It resets the script global cache. It must be run if the script was interrupted, to
  •        clean out the cache and triggers. Moreover, since the outputs are cached and are written
    
  •        after the whole list is created, if you run the script after the crash it would write all
    
  •        the cached output into the sheet, then clears the cache.
    
  • Configurations: The following configuration parameters must be configured before running the script.
    • 'folderId' (type: string):
  •      The folder ID. The folder ID is everything after the 'folders/' portion of the URL.
    
    • 'searchDepthMax' (type: unsigned integer):
  •      The maximum depth for the recursive search of folders.
    
    • 'listFiles' (type: boolean):
  •      It is a flag to indicate if the listing must include files.
    
    • 'cacheTimeout' (type: unsigned integer, in milliseconds):
  •      The maximum time that internal cache persists in memory.
    
    • 'lockWaitTime' (type: unsigned integer, in milliseconds):
  •      The maximum watiting time for the cache reader/writer to wait for the memory lock.
    
    • 'appendToSheet' (type: boolean):
  •      It is a flag for appending to the selected spreadsheet.
    
    • 'writeBatchSize' (type: unsigned integer):
  •      The batch size for writing into the spreadsheet.
    
  • Algorithm: The written functions uses a recursive function to list files & folders, and it uses
  •        a caching mechanisem to save the outputs in cache and write at the end.
    

  • Note-1: Because Apps Script services impose daily quotas and hard limitations on some features. If
  •     you exceed a quota or limitation, your script will throw an exception and terminate execution.
    
  • Note-2: Firstly, set your folder ID ('folderId' variable)! You may copy the folder ID from the
  •     browser's address field. The folder ID is everything after the 'folders/' portion of the URL
    
  •     for Google Drive folder.
    
  •     Secondly, set the 'searchDepthMax' to a reasonable number, a very large number can
    
  •     significantly delay the outputs or may cause unexpected termination.
    
  •     Thirdly, set the 'listFiles' to 'false', if you only require the folders to be listed.
    
  •     Finally, other configuration parameters are preconfigured and can be left as default.
    
  • Note-3: Because, this is a script and not a certified app, you must grant it permission, to run.
  •     When you run it for the first time, a pop-up window will open & asks you for permission.
    
  • Note-4: Files and folders must NOT be modified in the selected path, as it may corrupt the
  •     generated list.
    
  • Note-5: If you interrupt the script you might have to wait a few seconds (maximum 6 minutes),
  •     until you can re-run it.
    
  • Note-6: It is recommended to first run the script on a small set of files and folders.
  • Note-7: Make sure there are no other script in the current Google Sheet with similar function or
  •     variable names.
    
  • Note-8: Refer to version 1.0 of the script, for a simplified version of the ListFilesFolders script.
  • Note-9: The "teamdrive" does not support "getSharingPermission()", therefore comment out the lines
  •      that uses this function.
    
  • Note-10: Note that when you use business version of the Google Drive, there are still limits on how
  •      much time can be spent on auditing (refer to Google's quotas). For instance, the script would 
    
  •      timeout after ten minutes search a folder, therefore avoid auditing very big or deep folders.
    

  • @Version 2.3 (2018.10)
  • @see https://github.com/mesgarpour
    */

// Configurable variables
var folderId = '1hCPhAvgNcsjKY3lI7CglPdmMDnCumwxQ'; // The folder ID (everything after the 'folders/' portion of the URL).
var searchDepthMax = 100; // Max depth for recursive search of files and folders
var listFiles = true; // flag for listing files
var cacheTimeout = 60; // set cache time-out
var lockWaitTime = 60; // set maximium watiting time for the cache lock
var appendToSheet = false; // flag for appending to selected spreadsheet
var writeBatchSize = 100; // the write batch size

// ===========================================================================================================
// Global variables
var cacheOutputs = 'InventoryScript_outputs';
var cacheKillFlag = 'InventoryScript_killFlag';

// ===========================================================================================================
// Reset the script cache if it is required to run from the beginning
function reset() {
SpreadsheetApp.getActiveSpreadsheet().toast('Reseting script...', 'Status', -1);

// reset triggers and delete cache variables
setKillFlag_(true, this.cacheTimeout);
deleteTriggers_(this.loopResetGapTime);
deleteCache_();

SpreadsheetApp.getActiveSpreadsheet().toast('Reset is complete!', 'Status', -1);
}

// ===========================================================================================================
// List all folders and files, then write into the current spreadsheet.
function run() {
SpreadsheetApp.getActiveSpreadsheet().toast('Executing script...', 'Status', -1);

// load cache
setKillFlag_(false, this.cacheTimeout);
var outputRows = getCache_(this.lockWaitTime);

// get list
if (outputRows === undefined || outputRows === null ||
outputRows[0] === undefined || outputRows[0] === null) {
outputRows = [];

outputRows = getChildFiles_(null, DriveApp.getFolderById(this.folderId), 
                            listFiles, cacheTimeout, outputRows);

outputRows = getFolderTree_(outputRows, this.folderId, this.listFiles, this.cacheTimeout, 
                            this.lockWaitTime, this.searchDepthMax);

}

// write list
writeFolderTree_(outputRows, this.appendToSheet);

SpreadsheetApp.getActiveSpreadsheet().toast('Execution is complete!', 'Status', -1);
}

// ===========================================================================================================
// Get the list of folders and files
function getFolderTree_(outputRows, folderId, listFiles, cacheTimeout, lockWaitTime, searchDepthMax) {
var parentFolder, sheet = null;
var searchDepth = -1;

try {
// Get folder by id
parentFolder = DriveApp.getFolderById(folderId);

// Initialise the spreadsheet
sheet = SpreadsheetApp.getActiveSheet();

// Get files and/or folders
outputRows = getChildFolders_(searchDepth, parentFolder.getName(), parentFolder, sheet,
                              listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);

} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}

return outputRows;
}

// ===========================================================================================================
// Write the list of folders and files into the spreadsheet
function writeFolderTree_(outputRows, appendToSheet) {
var sheet = null;

try {
if (getKillFlag_() === false) {
// Initialise the spreadsheet
sheet = SpreadsheetApp.getActiveSheet();

  // Write to the selected spreadsheet
  writeOutputs_(sheet, outputRows, appendToSheet);
  
  // reset cache
  reset();
}

} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}

// ===========================================================================================================
// Get the list of folders and files and their metadata using a recursive loop
function getChildFolders_(searchDepth, parentFolderName, parentFolder, sheet, listFiles, cacheTimeout,
lockWaitTime, outputRows, searchDepthMax) {
var childFolders = parentFolder.getFolders();
var childFolder = null;
searchDepth += 1;

try{
// List sub-folders inside the folder
while (childFolders.hasNext() && searchDepth < searchDepthMax && getKillFlag_() === false) {
childFolder = childFolders.next();
SpreadsheetApp.getActiveSpreadsheet().toast('Searching folder ' + childFolder.getName() +
' at depth ' + searchDepth + " ...", 'Status', -1);

  // Get folder information
  // Logger.log("Folder Name: " + childFolder.getName());
  outputRows.push([
    parentFolderName + "/" + childFolder.getName(),
    childFolder.getName(),
    "Folder",
    childFolder.getDateCreated(),
    childFolder.getUrl(),
    childFolder.getLastUpdated(),
    childFolder.getDescription(),
    childFolder.getSize(),
    childFolder.getOwner().getEmail(),
    childFolder.getSharingPermission(),
    childFolder.getSharingAccess()
    //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFolder.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
  ]);
  
  // cache outputs
  setCache_(outputRows, lockWaitTime, cacheTimeout);
  
  // List files inside the folder
  outputRows = getChildFiles_(
    parentFolder, childFolder, listFiles, cacheTimeout, outputRows);
  
  // Recursive call of the current sub-folder
  outputRows = getChildFolders_(searchDepth++, parentFolderName + "/" + childFolder.getName(), 
    childFolder, sheet, listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);
}

} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast( 'Timed out!', 'Status', -1);
}

// cache outputs
setCache_(outputRows, lockWaitTime, cacheTimeout);

return outputRows;
}

// ===========================================================================================================
// Get the list of files in the selected folder
function getChildFiles_(parentFolder, childFolder, listFiles, cacheTimeout, outputRows) {
var childFiles = childFolder.getFiles();
var childFile = null;
var path = ""

try{
// List files inside the folder
while (listFiles && childFiles.hasNext()) {
childFile = childFiles.next();

  // derive path
  if (parentFolder === null){
    path = childFolder.getName() + "/" + childFile.getName()
  }else{
    path = parentFolder.getName() + "/" + childFolder.getName() + "/" + childFile.getName()
  }
  
  // Get file information
  //Logger.log("File Name: " + childFile.getName());
  outputRows.push([
    path,
    childFile.getName(),
    childFile.getName().split('.').pop(),
    childFile.getDateCreated(),
    childFile.getUrl(),
    childFile.getLastUpdated(),
    childFile.getDescription(),
    childFile.getSize(),
    childFile.getOwner().getEmail(),
    childFile.getSharingPermission(),
    childFile.getSharingAccess()
    //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFile.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
  ]);
}

// cache outputs
setCache_(outputRows, lockWaitTime, cacheTimeout);

} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
return outputRows;
}

// ===========================================================================================================
// Get the values from cache
function setCache_(outputRows, lockWaitTime, cacheTimeout) {
try{
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();

lock.waitLock(lockWaitTime);
cache.put(cacheOutputs, JSON.stringify(outputRows), cacheTimeout);
lock.releaseLock();

} catch (e) {
Logger.log('Timed out: Restarting! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}

// ===========================================================================================================
// Set the values in cache
function getCache_(lockWaitTime) {
try{
var outputRows = [];
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();

lock.waitLock(lockWaitTime);
outputRows =  JSON.parse(cache.get(cacheOutputs));
if (outputRows === undefined || outputRows === null ||
    outputRows[0] === undefined || outputRows[0] === null) {
  outputRows = JSON.parse(cache.get(cacheOutputs));
}
lock.releaseLock();

} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
return outputRows;
}

// ===========================================================================================================
// Write outputs to the selected spreadsheet
function writeOutputs_(sheet, outputRows, appendToSheet) {
try{
var range, rowStart, indexStart, indexEnd = null;
var headerRow = ["Full Path", "Name", "Type", "Date", "URL", "Last Updated", "Description", "Size",
"Owner", "Sharing Permission", "Sharing Access"]; //, "Thumbnail"];
SpreadsheetApp.getActiveSpreadsheet().toast('Writing outputs...', 'Status', -1);

if (sheet !== null && outputRows.length > 0) {
  if (appendToSheet === false) {
    sheet.clear();
    sheet.appendRow(headerRow);
    rowStart = 2;
  } else {
    rowStart = getRowsFilled_(sheet, "A1:A") + 1;
  }
  
  indexStart = 0;
  indexEnd = Math.min(writeBatchSize, outputRows.length);
  
  while (indexStart < outputRows.length) {
    range = sheet.getRange(rowStart + indexStart, 1, indexEnd - indexStart, headerRow.length);
    range.setValues(outputRows.slice(indexStart, indexEnd));
    a = outputRows.slice(indexStart, indexEnd);
    
    indexStart = indexEnd;
    indexEnd =  Math.min(indexStart + writeBatchSize, outputRows.length);
  }
  
  range = sheet.getRange(getRowsFilled_(sheet, "A1:A") + 1, 1, 1, 1);
  range.setValues([["End of List!"]]);
}

} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
}
}

// ===========================================================================================================
// Get number of rows filled in the selected spreadsheet
function getRowsFilled_(sheet, selectedRange) {
var selectedMatrix = sheet.getRange(selectedRange).getValues();
return selectedMatrix.filter(String).length;
}

// ===========================================================================================================
// Delete the global cache
function deleteCache_() {
try{
var cache = CacheService.getScriptCache();
var lock = LockService.getScriptLock();

lock.waitLock(this.lockWaitTime);
cache = CacheService.getScriptCache();
cache.remove(cacheOutputs);
lock.releaseLock();

} catch (e) {
Logger.log('Failed to delete cache! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete cache! Try again in a few minutes.');
}
}

// ===========================================================================================================
// Delete triggers
function deleteTriggers_() {
var triggers = ScriptApp.getProjectTriggers();
try{
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "run") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
} catch (e) {
Logger.log('Failed to delete triggers! ' + e.toString());
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete triggers! Try again in a few minutes.');
}
}

// ===========================================================================================================
// Set kill flag
function setKillFlag_(state, cacheTimeout) {
var lock = LockService.getScriptLock();
try{
lock.waitLock(this.lockWaitTime);
cache = CacheService.getScriptCache();
cache.put(cacheKillFlag, state, cacheTimeout);
lock.releaseLock();
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
}
}

// ===========================================================================================================
// Get kill flag
function getKillFlag_() {
killFlag = false;
try {
cache = CacheService.getScriptCache();
//lock.waitLock(this.lockWaitTime);
killFlag = cache.get(cacheKillFlag) === 'true';
//lock.releaseLock();
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
}
return killFlag;
}

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jul 17, 2019

@mesgarpour I'm encountering time out errors due to one folder containing far too many files. I do not need the files listed in this folder.
You provided direction to another user to exclude a folder with the following:

"a quick hack is to add the following to line 206:
if (childFolder.getName() = "name of the excluded folder")
{continue;}"

Since then, the code has been updated. What is the updated line in which this code can be added? Also, is there a way to modify it to exclude a child folder containing a string as opposed to the full folder name?

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Jul 17, 2019

Thanks for your comments!
@tracidoupenn : probably you reached a maximum quota (not sure which one)!

@development-dss :

  1. I do not know any work around. The best free option is to sync with your local machine, & do an inventory. Or just buy basic plan ( https://support.google.com/a/answer/4579696?hl=en )
  2. "about line 206", I meant before searching the subfolder before this "// Recursive call of the current sub-folder"
  3. a generic solution for pattern matching is regex:
@MikeEburne

This comment has been minimized.

Copy link

@MikeEburne MikeEburne commented Oct 4, 2019

This is excellent. I need to take it one step further and be able to print all the documents listed. Can anyone help...

@talshiar

This comment has been minimized.

Copy link

@talshiar talshiar commented Feb 20, 2020

I am excited for this script to function however it does not print anything. I get the error twice: Cannot read property 'getEmail' of null.

Any ideas on the issue/solution?

@DecisiveIndecisive

This comment has been minimized.

Copy link

@DecisiveIndecisive DecisiveIndecisive commented Feb 25, 2020

If you're getting a Cannot read property 'getEmail' of null. error, comment out line 225 & 284 where it says childFile.getOwner().getEmail(),
It looks like it's throwing an error when trying to get an owner of a document that has been deleted.

Hope this helps

@schroef

This comment has been minimized.

Copy link

@schroef schroef commented Feb 29, 2020

Wow this is so nice. I need to get all folders from a drive to think about restructure. FOund some online viedo, but only showed files and didnt work on shared folder. This one does work. Many thanks putting all the effort into this and sharing it.

Very much appreciated!!!

@wiktor-jurek

This comment has been minimized.

Copy link

@wiktor-jurek wiktor-jurek commented Apr 8, 2020

This doesn't work 'out of the box' with shared drives.
For shared drives, because you have to comment out a few columns of data, you have to update this line of code (line 362):
range = sheet.getRange(rowStart + indexStart, 1, indexEnd - indexStart, headerRow.length);

We set the number of columns to the amount of data that the first element contains:
range = sheet.getRange(rowStart + indexStart, 1, indexEnd - indexStart, outputRows[0].length);

@rdeloach

This comment has been minimized.

Copy link

@rdeloach rdeloach commented Apr 9, 2020

@wiktor-jurek @mesgarpour I can't get this to run on a shared folder even with Wiktor-Jurek's update to line 362 - any ideas?

@NabinPaudel

This comment has been minimized.

Copy link

@NabinPaudel NabinPaudel commented May 5, 2020

@mesgarpour this works quite well in files within my-drive, however times out in shared folder.

I managed to make it work by commenting childFolder.getOwner(), childFolder.getSharingPermission() and childFolder.getSharingAccess.
I also replaced the string to 'na' as it will create issue in row ammend.

'na', //childFolder.getOwner().getEmail(), 'na', //childFolder.getSharingPermission(), 'na', //childFolder.getSharingAccess()

try this @rdeloach.

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented May 16, 2020

Hi all
Is it possible to have the complete listing working with shared folder?
I have tried also to add the title of each column but i think my skill in appscript is to low for instance: can you help?

Thx for this work, very usefull
Rmanal

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented May 25, 2020

up

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented May 28, 2020

I fear nobody is reading

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented May 31, 2020

@Rmanal & @talshiar Sorry for my late reply. I am not monitoring it regularly.
I have updated the script to capture the error and return NULL if a field is not available (line 221-229, & 280-289).
Then, I tested on a shared drives, and it works.
Thanks to @NabinPaudel @wiktor-jurek @rdeloach @schroef & @DecisiveIndecisive

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented Jun 1, 2020

Hi
thx a lot
Nevertheless there seems to be a regression, as I don'y get anymore subfolder contents?

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented Jun 1, 2020

Additional quick question: how to apply it on "my drive" folder, as there is no shown folder ID?
Thx again for your job

@TexasCodes

This comment has been minimized.

Copy link

@TexasCodes TexasCodes commented Jun 24, 2020

Greetings all,

I am using a mix of shared and private folders and files. I updated the folderID for the parent folder.
Only the 1st-level subfolders and 1 2nd-level subfolder (SubA) are returned in the list. If I set the folderID to SubA then the file within the folder is returned; otherwise no files are returned.
There are hundreds of subfolders and files within the parent folder. I have repeatedly checked and I cannot find a difference in permissions between SubA versus all the other 2nd/3rd level subfolders. I also cannot figure out why the files are not being listed.
I receive the Status "Execution is Complete!" so timing out is not the issue. However, while scanning the folders I am receiving Time Out messages.

Any thoughts?

@Jayaprabha-gururani

This comment has been minimized.

Copy link

@Jayaprabha-gururani Jayaprabha-gururani commented Jun 29, 2020

Thanks for the script. When I run this folder size is showing null.

@lxerae

This comment has been minimized.

Copy link

@lxerae lxerae commented Jul 15, 2020

Thank you so much for this! Is there something I could add to this to only find new files/folders added to designated folder within last x hours?

@christopher-turnbull

This comment has been minimized.

Copy link

@christopher-turnbull christopher-turnbull commented Sep 22, 2020

Scratch that - looks like it's working though it's skipping a lot of files. I'm trying to run it in a shared folder so there could be a lot of permissions tweaking needed here...

@divyankjindal

This comment has been minimized.

Copy link

@divyankjindal divyankjindal commented Sep 26, 2020

Hi Mohsen
I tried your code but it is not showing the size of the folder, showing NULL

@nattard

This comment has been minimized.

Copy link

@nattard nattard commented Oct 20, 2020

Hi I have been able to run the script but I can either get a list of the folders or a list of the files not both.
Is there a way to report on both eg
folder1
folder1\file1
folder1\file2
folder2\file1

thanks

@nastu489

This comment has been minimized.

Copy link

@nastu489 nastu489 commented Oct 23, 2020

How to getlistAllfiles and Path without define the Folder ID first?

@kilmfer91

This comment has been minimized.

Copy link

@kilmfer91 kilmfer91 commented Jan 15, 2021

Hi @nattard,

The pushed array inside function getChildFolders_ has 14 values while in function getChildFiles_ each pushed array has 13 values. It is missing the getOwner() property. Once I have copy line 226 and paste it after line 286 the code worked and showed all the folder directory. Otherwise the length of array from getChildFolders_ will have one element more than the array from getChildFiles_. That difference causes the code not to work properly when function writeOutputs_ tries to set the outputRows array into the range (while loop in line 365).

Probably it could help you.

@nastu489

This comment has been minimized.

Copy link

@nastu489 nastu489 commented Jan 15, 2021

@cric-ric

This comment has been minimized.

Copy link

@cric-ric cric-ric commented Jan 18, 2021

Hi, Super script that I was looking for!

I just have an issue: it writes only the header row + one single line in the spreadsheet.
I've put breakpoints to understand:

  • the outputRows is fine, the array is filled properly with all the directories and files wanted
  • the indexStart, indexEnd, outputRows.length are properly set in the function writeOutputs_()
  • Only the first line of the array is written in the spreadsheet. No error msg.
  • I tried to change writeBatchSize to 1 and it did not change anything.
  • I changed the searchDepthMax from 100 to 2 to 1 (for test purpose): no change.
  • What I do not understand is that when debugging, I do not see it going through line 370 and after. Line 369 is the last one where the debugger stops by:

image

Thanks for any hint!

@mesgarpour

This comment has been minimized.

Copy link
Owner Author

@mesgarpour mesgarpour commented Jan 20, 2021

Sorry for late reply. Thanks for the help and support. I am not monitoring the page.
@kilmfer91 thanks for correction
@Rmanal probably you are hitting a limit. Here is URL format :https://www.googleapis.com/drive/v2/files/**folderId**
@TexasCodes you are hitting a limit. There are still limits on how much time can be spent on auditing (refer to Google's quotas)
@cric-ric probably you are hitting a limit (refer to Google's quotas)

@cric-ric

This comment has been minimized.

Copy link

@cric-ric cric-ric commented Jan 20, 2021

@cric-ric probably you are hitting a limit (refer to Google's quotas)

Hi @mesgarpour
I use a business workspace Gsuite account.
I had a look at the "Quotas for Google Services  |  Apps Script  |  Google Developers" page and I still do not understand as I do not get any quotas exception message, and no not believe I'm exceeding any quotas d this is the only script I try to run.

@nastu489

This comment has been minimized.

Copy link

@nastu489 nastu489 commented Jan 20, 2021

@cric-ric

This comment has been minimized.

Copy link

@cric-ric cric-ric commented Jan 25, 2021

Sorry for late reply. Thanks for the help and support. I am not monitoring the page.
@kilmfer91 thanks for correction
@Rmanal probably you are hitting a limit. Here is URL format :https://www.googleapis.com/drive/v2/files/**folderId**
@TexasCodes you are hitting a limit. There are still limits on how much time can be spent on auditing (refer to Google's quotas)
@cric-ric probably you are hitting a limit (refer to Google's quotas)

Hi, I fixed it. That was a mix of the getEmail() issue (that I already fixed) plus the issue related by @kilmfer91.
After those two fixes, it works like a charm.
Thanks again @mesgarpour

@Rmanal

This comment has been minimized.

Copy link

@Rmanal Rmanal commented Feb 18, 2021

Sorry for late reply. Thanks for the help and support. I am not monitoring the page.
@kilmfer91 thanks for correction
@Rmanal probably you are hitting a limit. Here is URL format :https://www.googleapis.com/drive/v2/files/**folderId**
@TexasCodes you are hitting a limit. There are still limits on how much time can be spent on auditing (refer to Google's quotas)
@cric-ric probably you are hitting a limit (refer to Google's quotas)

Hi, I fixed it. That was a mix of the getEmail() issue (that I already fixed) plus the issue related by @kilmfer91.
After those two fixes, it works like a charm.
Thanks again @mesgarpour

Hi, i have got also an issue with the "getmail" as it returns an error when applied on a shared drive: could you share the modifs you have done?
In addition how to run the script on "my drive", as i don't know how to get it as ID?
Thanks

@Worker217

This comment has been minimized.

Copy link

@Worker217 Worker217 commented Feb 18, 2021

Thanks for the script @mesgarpour. It has worked great for me so far but I noticed what I think is a typo on line 284.

childFile.getLastUpdated() ? childFile.getDescription() : "NULL",

Shouldn't it be childeFile.getLastUpdated() ? childFile.getLastUpdated() : "NULL", ?

@pietrosolaro

This comment has been minimized.

Copy link

@pietrosolaro pietrosolaro commented Mar 13, 2021

Thanks for the script @mesgarpour. It has worked great for me so far but I noticed what I think is a typo on line 284.

childFile.getLastUpdated() ? childFile.getDescription() : "NULL",

Shouldn't it be childeFile.getLastUpdated() ? childFile.getLastUpdated() : "NULL", ?

Hero, I needed that field and it wasn't working - that line was why. Thank you!

@haphazmat

This comment has been minimized.

Copy link

@haphazmat haphazmat commented Mar 26, 2021

Thank you for your script. It is very helpful!

@twMat

This comment has been minimized.

Copy link

@twMat twMat commented Mar 28, 2021

@mesgarpour - thank you very much!

As @zphdude points out and solved on March 6, 2019, there is a bug in how the file paths are rendered. Big thanks also for this!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment