Skip to content

Instantly share code, notes, and snippets.

@mesgarpour
Last active March 10, 2024 00:55
Show Gist options
  • Save mesgarpour/07317e81e9ee2b3f1699 to your computer and use it in GitHub Desktop.
Save mesgarpour/07317e81e9ee2b3f1699 to your computer and use it in GitHub Desktop.
[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;
}
@Rmanal
Copy link

Rmanal commented May 28, 2020

I fear nobody is reading

@mesgarpour
Copy link
Author

@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
Copy link

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
Copy link

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
Copy link

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
Copy link

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

@lxerae
Copy link

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
Copy link

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
Copy link

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

@nattard
Copy link

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

@lutfiihsan
Copy link

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

@kilmfer91
Copy link

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.

@lutfiihsan
Copy link

lutfiihsan commented Jan 15, 2021 via email

@cric-ric
Copy link

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
Copy link
Author

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
Copy link

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.

@lutfiihsan
Copy link

lutfiihsan commented Jan 20, 2021 via email

@cric-ric
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

Thank you for your script. It is very helpful!

@twMat
Copy link

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!

@dmitcha
Copy link

dmitcha commented Jan 28, 2022

@lxerae did you ever solve for this? I’m thinking it could work by running this version once, then editing the code to append future rows to the existing list, then adding an extra step of moving processed files into a separate folder. That way, there would only ever be new files in this folder, and they get added to the existing list. Adding a sort step for the sheet would keep the list orderly. I’m using a different script right now and may edit that, but wanted to see other approaches.

@AntonioSun
Copy link

AntonioSun commented Mar 26, 2022

Thanks @Worker217 / @pietrosolaro,

@mesgarpour I believe that the childFile.getLastUpdated() ? childFile.getDescription() : "NULL", on line 284 should be fixed as childeFile.getLastUpdated() ? childFile.getLastUpdated() : "NULL",. thx!

@its-dark-in-here
Copy link

@mesgarpour - Fantastic script - really useful.

However, like @asafpeled (ages ago if you look back through comments) I am getting a lot of Timeouts with error "Argument too large: value". I am using business Gsuite so limits should not necessarily be the problem, and I believe I have finally tracked it down.

The cause as @asafpeled noted is in the SetCache routine. The Google doc says there is a limit of 100Kb (102,400 bytes) on the CacheService, and with a little additional code, I can see that for one of my Drive scans I am getting the error at trying to save 102,605 bytes to the cache!

As I am using the business Gsuite, I have decided to forego the Cache function, and commented it out, and this works for me!

@papalphacharlie
Copy link

To help keep your account secure, from May 30, 2022, ​​Google no longer supports the use of third-party apps or devices which ask you to sign in to your Google Account using only your username and password.
https://support.google.com/accounts/answer/6010255?hl=en&visit_id=637902724079718537-430457167&p=less-secure-apps&rd=1#zippy=%2Cif-less-secure-app-access-is-on-for-your-account

Has anyone tried using this script recently? Cannot find a way to enable "Less secure apps" on my personal Google account anymore.

@JgLighting
Copy link

JgLighting commented Sep 6, 2022

@mesgarpour
Is it possible to have new files in the folder appended at the end of the list ?

I only have a single level of files and no child folders in the folder I wish to run the script on.

Thanks for a great script

@GJoe2
Copy link

GJoe2 commented Sep 27, 2022

I'm having issues with multiple google accounts. Every time I use var ss = SpreadsheetApp.getActiveSpreadsheet(); It returns Cannot read property 'getSheetByName' of null. To work around it I need to use var ss = SpreadsheetApp.openById("abc1234567");

I see your code use sheet = SpreadsheetApp.getActiveSheet(); in lines 165 and 186. It would be convenient if we just select the desire spreadsheet using ID or URL in another parameter.

Also, I would like to ask how to extract the number of pages if the file is a pdf

@kmnhngyn
Copy link

kmnhngyn commented Oct 19, 2022

hi, thanks for the script! When trying to run the script on a folder with a depth of 5 and more subfolders and a lot of files, I get a time out or that the output is too large ("Restarting! Exception: Argument too large). I understand that the memory/cache gets too big. Any ideas how I can adjust the script so that it doesn't store the rows until finished, but tries to write the first subfolder, then the second, the third,... and so on? Thanks!

@aalooksth
Copy link

@lutfiihsan to get all Drive tree, just replace
var parentFolder = DriveApp.getFolderById(folderId);
with
var parentFolder = DriveApp.getRootFolder();

Do note if there are too many files, it will result in an error.

@rajesh-h
Copy link

rajesh-h commented Jul 21, 2023

@mesgarpour I know you are not actively maintaining this.

can you answer this? when i set depth to 1 listFiles= False, my expectation is it will bring all folder names within the specified folder. But I get only one folder name is this expected?

var searchDepthMax = 1; // Max depth for recursive search of files and folders
var listFiles = false;

Eg.

ParentFolder
child1
grandchild1
child2
grandchild1
child3
child4.xlsx

My expectation is it should bring
child1
child2
child3

Thanks

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