i use a script to take all my files from drive to a google spreadsheet with name, url...

My problem is there are a lot of files and the script run during ~30 min and exceed during time permission for apps script.

How can i speed this script please ?

function listFilesAndFolders() {
  var folderid = " "; // change FolderID
  var sh = SpreadsheetApp.getActiveSheet();
  sh.clear();
  sh.appendRow(["parent","folder", "name", "update", "Size", "URL", "ID", "description", "type"]);
  try {
    var parentFolder =DriveApp.getFolderById(folderid);
    listFiles(parentFolder,parentFolder.getName())
    listSubFolders(parentFolder,parentFolder.getName());
  } catch (e) {
    Logger.log(e.toString());
  }
}

function listSubFolders(parentFolder,parent) {
  var childFolders = parentFolder.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    Logger.log("Fold : "   childFolder.getName());
    listFiles(childFolder,parent)
    listSubFolders(childFolder,parent   "|"   childFolder.getName());
  }
}

function listFiles(fold,parent){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = [];
  var files = fold.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      parent,
      fold.getName(),
      file.getName(),
      file.getLastUpdated(),
      file.getSize(),
      file.getUrl(),
      file.getId(),
      file.getDescription(),
      file.getMimeType()
      ];
    sh.appendRow(data);
  }
}

CodePudding user response：

Rather then use Sheet.appendRow() collect each row in an array rows and when all done looping use .getRange().setValues().

function listFilesAndFolders() {
  var folderid = " "; // change FolderID
  var sh = SpreadsheetApp.getActiveSheet();
  sh.clear();
  let rows = [["parent","folder", "name", "update", "Size", "URL", "ID", "description", "type"]];
  try {
    var parentFolder =DriveApp.getFolderById(folderid);
    listFiles(parentFolder,parentFolder.getName(),  rows  )
    listSubFolders(parentFolder,parentFolder.getName());
    sh.getRange(sh.getLastRow() 1,1,rows.length,rows[0].length).setValues(rows);
  } catch (e) {
    Logger.log(e.toString());
  }
}

function listSubFolders(parentFolder,parent) {
  var childFolders = parentFolder.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    Logger.log("Fold : "   childFolder.getName());
    listFiles(childFolder,parent)
    listSubFolders(childFolder,parent   "|"   childFolder.getName());
  }
}

function listFiles(fold,parent,  rows  ){
  var sh = SpreadsheetApp.getActiveSheet();
  var files = fold.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    rows.push([ 
      parent,
      fold.getName(),
      file.getName(),
      file.getLastUpdated(),
      file.getSize(),
      file.getUrl(),
      file.getId(),
      file.getDescription(),
      file.getMimeType()
      ]);
  }
}

CodePudding user response：

This will list all of the files and folders in your drive into Sheet1

function getFnF1(folder = DriveApp.getRootFolder()) {
  let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  //Logger.log(JSON.stringify(tree));
  if (tree.level < level) {
    tree.level = level;
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  const files = folder.getFiles();
  let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = folder.getName(); } else { x = ''; } return x; }));
  tree.txt.push(row);
  row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'bold'; } else { x = 'normal'; } return x; }));
  tree.fwt.push(row);
  PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  if (files.hasNext()) {
    let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'Files:'; } else { x = ''; } return x; }));
    tree.txt.push(row);
    tree.fwt.push(['normal']);
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  while (files.hasNext()) {
    let file = files.next();
    let row = Array.from([...Array(level   1).keys()], ((x, i) => { if (i == level) { x = file.getName(); } else { x = ''; } return x; }));
    tree.txt.push(row);
    tree.fwt.push(['normal']);
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  const subfolders = folder.getFolders()
  while (subfolders.hasNext()) {
    let subfolder = subfolders.next();
    level  ;
    getFnF1(subfolder);
  }
  level--;
}

function getFilesAndFolders1() {
  const fldr = null;
  const ss = SpreadsheetApp.getActive();
  ss.toast("Entry");
  const sh = ss.getSheetByName('Sheet1');
  sh.clearContents();
  SpreadsheetApp.flush();
  PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify({ txt: [], fwt: [], level: 0 }));
  getFnF1();
  //Logger.log(PropertiesService.getScriptProperties().getProperty('FnF'));
  let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  const l = tree.level   1
  tree.txt.forEach(r => {
    if (r.length < l) {
      //Array.from(Array(l - r.length).keys()).forEach(e => r.push(''));
      r.splice(r.length, 0, ...Array(l - r.length).fill(''));
    }
  });
  tree.fwt.forEach(r => {
    if (r.length < l) {
      //Array.from(Array(l - r.length).keys()).forEach(e => r.push('normal'));
      r.splice(r.length, 0, ...Array(l - r.length).fill('normal'));
    }
  });
  //Logger.log(JSON.stringify(tree));
  sh.getRange(1, 1, tree.txt.length, tree.level   1).setValues(tree.txt);
  sh.getRange(1, 1, tree.fwt.length, tree.level   1).setFontWeights(tree.fwt);
  PropertiesService.getScriptProperties().deleteProperty('FnF');
  ss.toast("EOF");
}

It takes about 3 minutes on my drive. I keep my drive pretty clean and I don't list as many parameters as you desired.