1

I got this script to get into a folder and get the key for each file.

function listFilesInFolder(id) {

  var folder = DriveApp.getFolderById('');
  var contents = folder.getFiles();
  var file;
  var name;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Imported");
  var date;
  var size;

  sheet.clear();
  sheet.appendRow(["Name", "Data", "Size", "Id"]);


  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    date = file.getDateCreated()
    size = file.getSize()
    id = file.getId()

    data = [name, date, size, id]
    sheet.appendRow(data);
    
    //appendRow
  }
};

my problem is that taking too much time to finish going through the whole folder, it usually hits run-time limit before it finishes.

The folder contains 1000+ different files, we automatically upload files daily.

is there any way to make this script more efficient?

OPS Maheras
  • 15
  • 3
  • The script doesn't look inefficient. Perhaps you need to batch your work and or call your script more often than just once a day to keep up with the uploads. – Cooper May 22 '21 at 17:05
  • Probably this is not exactly the solution you would expect to get. More like a way around. I'd consider to install Google Drive and synchronize all the files locally. This way you will able to handle them locally via system tools, Python scripts, node.js, etc. – Yuri Khristich May 22 '21 at 17:09
  • Although I'm not sure whether this is the direct solution of your situation, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you expect, I apologize. – Tanaike May 22 '21 at 23:11

1 Answers1

1

I believe your goal as follows.

  • You want to retrieve the file list just under the specific folder using Google Apps Script.
  • You want to reduce the process cost of your current script in your question.

Modification points:

  • In your script, appendRow is used in a loop. In this case, the process cost will be high. Ref
  • And, I thought that in your situation, when Drive API is used, the process cost might be able to be a bit reduced.

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

Modified script:

Before you use this script, please enable Drive API at Advanced Google services.

function listFilesInFolder(id) {
  var folderId = "###"; // Please set the folder ID. If you want to use "id" for this, you can use var folderId = id;
  
  // 1. Retrieve file list using Drive API.
  var ar = [["Name", "Data", "Size", "Id"]];
  var pageToken = "";
  do {
    const res = Drive.Files.list({corpora: "allDrives", includeTeamDriveItems: true, supportsAllDrives: true, maxResults: 1000, pageToken: pageToken, q: `'${folderId}' in parents`});
    ar = ar.concat(res.items.map(({title, createdDate, fileSize, id}) => [title, createdDate, fileSize || 0, id]));
    pageToken = res.nextPageToken;
  } while(pageToken);

  // 2. Put the file list to Spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Imported");
  sheet.clear();
  sheet.getRange(1, 1, ar.length, ar[0].length).setValues(ar);
}

Note:

  • From your question, I couldn't understand about the detail of your situation. So in this modified script, the folders in both your Google Drive and the shared Drive can be searched.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165