List all files in google drive without exceeding maximum execution time

Question

I am trying to list the name of more than 10000 images in google drive into a spreed sheet . I have a running script but it stops every 360 seconds giving error "exceeding maximum execution time " for which i have found another script , but as a laymen i am not able to use both the scripts together . Please help or suggest any other idea . Thankyou in Advance .

for 1) Extracing List all files in google drive Here

function SDesigns(folderName) {

   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "URL", "Download", "Date", "Size", "Description", "Image"]);


//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById("FOLDER_ID");
    var contents = folder.getFiles();

    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file = contents.next();
        cnt++;

           data = [
                file.getName(),
                file.getUrl(), 
                "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),

             /* file.getDateCreated(),
                file.getSize(),                              
                file.getDescription(),
                "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",  */
            ]; 

            sheet.appendRow(data);



    };
 };

2) for Exceeding the Maximum Execution Time Limit i found a post Here

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

}

The question should be updated to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. — Linda Lawton - DaImTo, Jan 22 '20 at 11:53
Don't use`appendRow` to do bulk writes, ever. Use `Range#setValues`. Please review the official Apps Script best practices for a tutorial on how to use this method. — tehhowch, Jan 22 '20 at 12:01

ziganotschka · Accepted Answer · 2020-01-22T12:41:43.980

Calls to any external services including `spreadsheetApp` make your code slow

To make your script more efficient, I recommend to not append a new row within each for loop iteration, but instead push the data into a two-dimensional array and insert it into your sheet only once, after exiting the loop.

Rewrite your script as following:

function SDesigns() {
   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "URL", "Download", "Date", "Size", "Description", "Image"]);
//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById("FOLDER_ID");
    var contents = folder.getFiles();
    var cnt = 0;
    var file;
    var array = [];
    while (contents.hasNext()) {
        var file = contents.next();
        cnt++;
        data = [
                file.getName(),
                file.getUrl(), 
                "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),

             /* file.getDateCreated(),
                file.getSize(),                              
                file.getDescription(),
                "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",  */
            ]; 
        array.push(data);
    };
  sheet.getRange((sheet.getLastRow()+1), 1, array.length, array[0].length).setValues(array); 
 };

This will make your code approximately 3 times faster and should take away the need to work around the maximum runtime.

For me, for a folder containing 110 files, the script execution time was 3.13 s. Extrapolating to 10000 images the execution time should not exceed 285 s.

Merging your script with the second script you found would be rather tricky. The reason is that DriveApp does not retrieve files in an established order. Thus, if you stop listing files and want resume in the next script execution, there is no guarantee that you won't obtain duplicates.

You can always store a continuation token for the iteration: https://developers.google.com/apps-script/reference/drive/file-iterator.html#getContinuationToken() — tehhowch, Jan 22 '20 at 12:46
<3 Thankyou soo much for this . Your answer means a lot to me . Regards — sanjay maalde, Jan 23 '20 at 04:57

List all files in google drive without exceeding maximum execution time

1 Answers1

Calls to any external services including spreadsheetApp make your code slow

Calls to any external services including `spreadsheetApp` make your code slow