CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2107

Speed up the process of listing file names and links from a Google Drive folder to Google Sheets

Today when I list the names and files of a Google Drive folder in Google Sheets, I use this script:

function listFolderContents() {

  //Squads
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Import!A3:B').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

  var foldername = 'My Files';
  var folderlisting = 'listing of folder ' + foldername;

  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );     
  }  
  spreadsheet.getRange('Files!Q3:R').activate();
  spreadsheet.getRange('Import!A3:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('Import!A3:B').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});  

};

What happens is that using this method, each name and link will appear one row at a time, making it take a long time to complete the script. Imagine populating one line at a time for 300 files within a folder for example.

I would like to know what I should modify and what this script would look like so that this process would become faster, collect at once and populate the cells at once, as is done with CSV file imports for example. That populate all the cells at once.

Upvotes: 0

Views: 195

Answers (1)

Cooper
Cooper

Reputation: 64062

It's probably not a lot faster but might be a little. It's always better to access files and folders by id. Since you were assuming that you only had one folder with that name then you might just as well go find the folder id and use it.

Also activating stuff on the screen takes time and is unnecessary in apps scripts. It's used a lot in Macros but not in most scripts.

function listFolderContents() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Import');
  sh.clear({contentsOnly: true, skipFilteredRows: true});
  var folderId="1zUNj0hrJUxcjGq5gvaIifMVwpOMVyIs2";
  var folder=DriveApp.getFolderById(folderId);
  var contents=folder.getFiles();
  while(contents.hasNext()) {
    var file=contents.next();
    sh.appendRow([file.getName(), file.getUrl()]);     
  }  
  var drg1=ss.getRange('Files!Q3:R');
  ss.getRange('Import!A3:B').copyTo(drg1, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Upvotes: 1

Related Questions