2

i am trying to run this script in google apps script. It lists all files recursively within a folder along with its size, name, url etc to spreadsheet. There is nothing wrong with the script but the problem is i am running it on a folder which has thousands of files and google script only allows few minutes of maximum runtime so each time just after few mins i get the error saying exceeded maximum execution time in google script.

Is there any workaround for this issue? i am fine even if i have to run this code somewhere outside of google apps script if that's the only way out but then again i have been told its not possible to execute this code outside of google script.

function start() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type", "Folder", "Folder Slug"]);
  var folders = DriveApp.getFolderById('FOLDER_ID');
  var folder = folders.getFolders();
  if (folder.hasNext()) {

    // 1. Retrieve the file list and put to an array.
    // 2. Sort the array by the file size.
    var list = processFolder(folder).sort((a, b) => a[2] < b[2] ? 1 : -1);

    // 3. Put the array to the Spreadsheet.
    sheet.getRange(2, 1, list.length, list[0].length).setValues(list);
  } else {
    Browser.msgBox('Folder not found!');
  }

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

  function addFilesToSheet(files, folder, list) {
    var folderName = folder.getName();
    while (files.hasNext()) {
      var file = files.next();
      list.push([
        file.getName(),
        file.getDateCreated(),
        Math.round(10 * file.getSize() / 1073741824) / 10, // Modified from file.getSize() / 1073741824,
        file.getUrl(),
        "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
        file.getDescription() || "",
        file.getMimeType(),
        folderName
      ]);
    }
  }
}

1 Answer 1

1

I have managed this problem in the past using the Continous Execution Library by Patrick Martinent:

https://gist.github.com/patt0/8395003

The basic idea is to:

  • Set up your function so it can be terminated and resumed without issue
  • Set up a time trigger to re-run the function
  • Run it until it nears the execution timeout and exit gracefully
  • Allow the trigger to restart the function
  • Repeat until done, and remove the trigger
Sign up to request clarification or add additional context in comments.

3 Comments

Hello i tried to use that library but unfortunately i couldn't succeed in doing so . Does it needs any changes ? Asking because i can see that its almost 6 years old so some stuff might be very well be depreciated
If it doesn't work anymore, you can use the same principles to schedule and manage your script
What error did you get? It's likely permissions related

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.