Search code examples
google-apps-scriptgoogle-sheetsgoogle-drive-api

List Google drive folder contents to google sheets with only new files

Looking to learn how to improve my use of loops. Currently I need to list the names and URLS from a google drive Folder to a sheet and this is the code that I have:

Existing Code

function wthFolderContents() {
  var folder_id = 'myFolderID';
   
  var folders = DriveApp.getFolderById(folder_id)
  var contents = folders.getFiles();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("SheetName");
  sheet.clearContents()
  sheet.appendRow( ['name', 'link'] );
  
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow ( [name, link] );

with this code everytime the script is run the contents are cleared and then relisted. I am looking at a way of doing this dynamically / only update the new files so the script runs more effeciently.

Ive tried the following

New Code

function wthFolderContents2() {
  var folder_id = '1vBzucZsb0SMOoHSWGtkUF-5QLQr5Fh1C';
   
  var folders = DriveApp.getFolderById(folder_id)
  var contents = folders.getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("WHTCert");
  var lastRow = sheet.getLastRow()
  var existing = sheet.getRange(1,1,lastRow,1).getValues()

      
  for(i=1;i<lastRow;i++) {
      
    var existingFilename = existing [i][0]
    Logger.log(existingFilename)

      while(contents.hasNext()) {
      var file;
      var name;
      var link; 

      file = contents.next();
      name = file.getName();
      link = file.getUrl();   
    
        if (!name == existingFilename) {
        sheet.appendRow ( [name, link] );     
  }
  }
  }

I cant get this to work, not sure what exactly where I have gone wrong. Hope someone can point me int he right direction!

Cheers

Solution

  • I believe your goal is as follows.

    • You want to reduce the process cost of your script.

    Modification points:

    • In your script, appendRow is used. In this case, the process cost will become high. Ref
    • The search for files is run in a loop. In this case, the process cost will become high.

    In your situation, it seems that you want to retrieve the file list just under the specific folder. In this case, I thought that when Drive API is used, the process cost can be reduced. In this answer, I would like to propose using Drive API in your script. When this is reflected in your script, it becomes as follows.

    When Drive API is used, all values can be retrieved. So, I thought that your 1st process might be able to be used.

    Modified script: for Drive API v2

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

    function wthFolderContents2() {
  var folder_id = '1vBzucZsb0SMOoHSWGtkUF-5QLQr5Fh1C';

  // Retrieve file list.
  var q = `'${folder_id}' in parents and trashed = false and mimeType != '${MimeType.FOLDER}'`;
  var fileList = [['name', 'link']];
  var pageToken = "";
  do {
    var obj = Drive.Files.list({ q, maxResults: 1000, pageToken, fields: "nextPageToken,items(id,title)", corpora: "allDrives", supportsAllDrives: true, includeItemsFromAllDrives: true });
    if (obj.items.length > 0) {
      fileList = [...fileList, ...obj.items.map(({ id, title }) => [title, `https://docs.google.com/presentation/d/${id}/edit?usp=drivesdk`])];
    }
    pageToken = obj.nextPageToken;
  } while (pageToken);
  
  // Put the values to Spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("WHTCert");
  sheet.clearContents();
  sheet.getRange(1, 1, fileList.length, fileList[0].length).setValues(fileList);
}
    • When this script is run, the file list (filename and URL) is retrieved from the specific folder. And, the retrieved values to the "WHTCert" sheet.

    Modified script: for Drive API v3

    In the current stage, when Drive API is enabled, v3 is enabled as the default version. So, the following script might be able to be used.

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

    function wthFolderContents2() {
  var folder_id = '1vBzucZsb0SMOoHSWGtkUF-5QLQr5Fh1C';

  // Retrieve file list.
  var q = `'${folder_id}' in parents and trashed = false and mimeType != '${MimeType.FOLDER}'`;
  var fileList = [['name', 'link']];
  var pageToken = "";
  do {
    var obj = Drive.Files.list({ q, pageSize: 1000, pageToken, fields: "nextPageToken,files(id,name)", corpora: "allDrives", supportsAllDrives: true, includeItemsFromAllDrives: true });
    if (obj.files.length > 0) {
      fileList = [...fileList, ...obj.files.map(({ id, name }) => [name, `https://docs.google.com/presentation/d/${id}/edit?usp=drivesdk`])];
    }
    pageToken = obj.nextPageToken;
  } while (pageToken);
  
  // Put the values to Spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("WHTCert");
  sheet.clearContents();
  sheet.getRange(1, 1, fileList.length, fileList[0].length).setValues(fileList);
}

    References: