Google drive spreadsheet exceeded maximum excution time

Question

My script in my spreadsheet on Gdrive for insert link id of file in folders like this:

function listFolders(folder) {
     var sheet = SpreadsheetApp.getActiveSheet();
     sheet.appendRow(["Name", "FileId"]); //writes the headers
     var folder = DriveApp.getFolderById("XXXXXXXXXX")

     var subfolders = folder.getFolders();

     while (subfolders.hasNext()) {
         var name = subfolders.next();

         var files = name.getFiles();

         var cnt = 0;
         var file;

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

             data = [
                 file.getName(),
                 file.getId()
             ];
             sheet.appendRow(data);
         };
         
     }

 }

How to resolve this problem? Thanks in advance.

Answer 1

I believe your goal as follows.

• You want to retrieve the file list from the subfolders just under the specific folder using Google Apps Script.
• You want to reduce the process cost of your script.
• As the additional goal, you want to add "FolderName" to the column.

Modification points:

• In your script, appendRow is used in a loop. In this case, the process cost becomes high. Ref In your situation, I thought that when the number of files is large, the process cost will become high. In this answer, this is modified.
• It seems that folder of argument is not used.

When above points are reflected to your script, it becomes as follows.

Modified script:

function listFolders() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var folder = DriveApp.getFolderById("XXXXXXXXXX")
  var subfolders = folder.getFolders();
  var values = [["Name", "FileId", "FolderName"]]; // Modified
  while (subfolders.hasNext()) {
    var name = subfolders.next();
    var folderName = name.getName(); // Added
    var files = name.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      data = [file.getName(), file.getId(), folderName]; // Modified
      values.push(data);
    }
  }
  var cnt = values.length;
  console.log(cnt);  // You can see the total number of files at the log.
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

• In this modification, the file list is put to an array and the array including the file list is put to the Spreadsheet using setValues.

Note:

• When you want to retrieve the file list from all subfolders under the subfolders in the specific folder, you can also use the following script.

    function listFolders() {
      const getAllFolders = (id, list = [["Name", "FileId", "FolderName"]]) => { // Modified
        const fols = DriveApp.getFolderById(id).getFolders();
        let temp = [];
        while (fols.hasNext()) {
          const fol = fols.next();
          const folderName = fol.getName(); // Added
          temp.push(fol.getId());
          const files = fol.getFiles();
          while (files.hasNext()) {
            const file = files.next();
            list.push([file.getName(), file.getId(), folderName]); // Modified
          }
        }
        temp.forEach(id => getAllFolders(id, list));
        return list;
      }
  
      const id = "###"; // Please set the top folder ID.
      const values = getAllFolders(id);
      var cnt = values.length;
      console.log(cnt);  // You can see the total number of files at the log.
  
      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
    }
  

• When above modification was not the direct solution of your issue, I would like to propose to use Drive API. In this case, I think that this Google Apps Script library can be also used. https://github.com/tanaikech/FilesApp

References:

• Benchmark: Reading and Writing Spreadsheet using Google Apps Script
• setValues(values)

Added:

About your following replying,

I want to know which folder on my drive this file belonged to. const getAllFolders = (folderName,id, list = [["FolderName","Name", "FileId"]]

Unfortunately, I'm not sure whether I could correctly understand about the output values of your additional question, the following script is what you want?

Sample script:

function sample() {
  const getAllFolders = (id, list = [["FolderName", "Name", "FileId"]]) => {
    const folder = DriveApp.getFolderById(id);
    const folderName = folder.getName();
    const fols = folder.getFolders();
    let temp = [];
    while (fols.hasNext()) {
      const fol = fols.next();
      temp.push(fol.getId());
      const files = fol.getFiles();
      while (files.hasNext()) {
        const file = files.next();
        list.push([folderName, file.getName(), file.getId()]);
      }
    }
    temp.forEach(id => getAllFolders(id, list));
    return list;
  }

  const id = "###"; // Please set the top folder ID.
  const values = getAllFolders(id);
  var cnt = values.length;
  console.log(cnt);  // You can see the total number of files at the log.

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

Answer 2

This minor modification runs okay for me:

function listFolders() {
     var sh = SpreadsheetApp.getActiveSheet();
     sh.appendRow(["Name", "FileId","Count"]); 
     var folder = DriveApp.getFolderById("insert folder id")
     var subfolders = folder.getFolders();
     while (subfolders.hasNext()) {
         var fldr = subfolders.next();
         var files = fldr.getFiles();
         var cnt = 0;
         while (files.hasNext()) {
             var file = files.next();
             sh.appendRow([file.getName(),file.getId(),++cnt]);
         };
     }
 }

This was my output:

Name	FileId	Count
Slide22.png	Redacted	1
Slide21.png	Redacted	2
Slide20.png	Redacted	3
Slide19.png	Redacted	4
Slide18.png	Redacted	5
Slide17.png	Redacted	6
Slide16.png	Redacted	7
Slide15.png	Redacted	8
Slide14.png	Redacted	9
Slide13.png	Redacted	10