So I am a noob in coding but managed to adjust the code a bit to do what I need it to do and that is to list the files inside a folder and its subfolder files.
The issue is that the files total is about 50k or more and keeps increasing each day XD so now most of the time I get a "Exceeded maximum execution time" and sometimes's I don't. Inside the script, there is a flush function so it should reset the timer if I am correct?
I run the script every day I do not know what to do to fix this? I think the best would be to have a function that will check if it is already listed and if not updated to skip it to speed up the script but again I just do not know atm ware to start.
If someone could help me to fix the " Exceeded maximum execution time" I would be extremely grateful.
this is the script:
function ListarTodo() {
/* Adapted from Code written by @Andres Duarte in this link:
https://stackoverflow.com/questions/59045664/how-to-list-also-files-inside-subfolders-in-google-drive/63182864#63182864
*/
// List all files and sub-folders in a single folder on Google Drive
// declare the folder name
var foldername = 'Beelden';
// declare this sheet
var sheet = SpreadsheetApp.getActiveSheet();
// clear any existing contents
sheet.clear();
// append a header row
sheet.appendRow(["Folder","Name", "Last Updated", "Size MB", "URL"]);
// getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
// folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
var folders = DriveApp.getFoldersByName(foldername);
var foldersnext = folders.next();
var lintotal = 2;
// Initiate recursive function
lintotal = SubCarpetas(foldersnext, foldername, lintotal);
}
function SubCarpetas(folder, path, cantlineas) {
cantlineas = ListarArchivos(folder, path, cantlineas);
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var mysubfolders = subfolders.next();
var mysubfolderName = mysubfolders.getName();
var newpath = "";
newpath = path + "/" + mysubfolderName;
cantlineas = SubCarpetas(mysubfolders, newpath, cantlineas);
}
return(cantlineas)
}
// list files in this folder
// myfiles is a File Iterator
function ListarArchivos(mifoldersnext, mipath, milintotal) {
var datos = []; //temporary array that we are going to use to record on the sheet
var files = []; //array with all the files that we find in the folder that we are evaluating
var file = []; //array that we use to dump the data of each file before saving it
var total = 0;
var sheet = SpreadsheetApp.getActiveSheet();
var myfiles = mifoldersnext.getFiles();
// We create an array with the data of each file and save the total number of files
while (myfiles.hasNext()) {
files.push(myfiles.next());
total++;
}
// we sort the array by file names alphabetically // sorts the files array by file names alphabetically
files = files.sort(function(a, b){
var aName = a.getName().toUpperCase();
var bName = b.getName().toUpperCase();
return aName.localeCompare(bName);
});
////
var vuelta = 0;
var bulk = 500; // We define the number of lines to record each time, in the GoogleDoc spreadsheet
var linea = milintotal; // we define in which line we are going to save in the spreadsheet
for (var i = 0; i < files.length; i++) { // we go through the array of files and format the information we need for our spreadsheet
file = files[i];
var fname = file.getName(); //file name
var fdate = file.getLastUpdated(); // date and time last modified
var fsize = file.getSize()/1024/1024; // file size, we pass it from byte to Kbyte and then to Mb
fsize = +fsize.toFixed(2); // we format it to two decimal places
var furl = file.getUrl(); //File URL
datos[vuelta] = [mipath+" ("+total+")", fname, fdate, fsize, furl]; // we put everything inside a temporary array
vuelta++;
if (vuelta == bulk) {// when it reaches the defined quantity, save this array with 10 lines and empty it
linea = milintotal;
// Logger.log("linea = "+linea); //DEBUG
// Logger.log("vuelta = "+vuelta); //DEBUG
// Logger.log("total = "+total); //DEBUG
// Logger.log("lintotal = "+milintotal); //DEBUG
// Logger.log("registros en datos = "+datos.length); //DEBUG
// Logger.log("data = "+datos); //DEBUG
sheet.getRange(linea, 1, bulk,5).setValues(datos); // we save the data of the temporary array in the sheet
SpreadsheetApp.flush(); // we force the data to appear on the sheet - without this the data does not appear until finished (it generates a lot of impatience)
milintotal = milintotal + vuelta;
datos = []; // empty the temporary array
vuelta = 0;
}
}
if (datos.length>0) {// When exiting the loop we record what is left in the data array
linea = milintotal;
// Logger.log("linea = "+linea); //DEBUG
// Logger.log("vuelta = "+vuelta); //DEBUG
// Logger.log("total = "+total); //DEBUG
// Logger.log("lintotal = "+milintotal); //DEBUG
// Logger.log("records in data = "+ data.length); //DEBUG
// Logger.log("data = "+datos); //DEBUG
sheet.getRange(linea, 1, datos.length,5).setValues(datos);
SpreadsheetApp.flush(); //ansiolítico
milintotal = milintotal + datos.length;
datos = [];
vuelta = 0;
}
return (milintotal)
}Thank you all
UPDATE!
So after looking at multiple forumes i found that the best thing atm i can do is make a break for this script by using the following script:
function runMe() {
var startTime= (new Date()).getTime();
//do some work here
var scriptProperties = PropertiesService.getScriptProperties();
var startRow= scriptProperties.getProperty('start_row');
for(var ii = startRow; ii <= size; ii++) {
var currTime = (new Date()).getTime();
if(currTime - startTime >= MAX_RUNNING_TIME) {
scriptProperties.setProperty("start_row", ii);
ScriptApp.newTrigger("runMe")
.timeBased()
.at(new Date(currTime+REASONABLE_TIME_TO_WAIT))
.create();
break;
} else {
doSomeWork();
}
}
//do some more work here
}But after trying I do not understand where to divide this to make it work T-T any ideas?
