Google App Script How to: Bypass Execution Timeout when collecting Folders and Subfolder Names and Hyper links

Question

I have a large number of Folders and Sub-Folders, which I want to extract the Hyperlink and the Full Folder path.

The below script works. I dont remember where I found it however it has been modified to give me what I need. However I don't know how to Bypass Google's Execution Timeout. I have a 30min timeout which helps however it's not enough.

Could anyone give me a suggestion to either seriously speed up the code and/or Bypass Google's Execution Timeout.

var SS = SpreadsheetApp.getActiveSpreadsheet();

function listFilesAndFolders(){
  var folderId = 'Insert Root Folder to Search' 
    getFolderTree(folderId, true); 
};

// Get Folder Tree
function getFolderTree(folderId, listAll) {
  try {
    // Get folder by id
    var parentFolder = DriveApp.getFolderById(folderId);
    
    // Initialise the sheet
    var data, sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Folders");
    sheet.clear();
    sheet.appendRow([ "URL", "Full Path"]);

    // Get files and folders
    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
  } catch (e) {
    Logger.log(e.toString());
  }
};

// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();
 
  // List folders inside the folder
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    data = [ 
      childFolder.getUrl(),
      parentName + "/" + childFolder.getName(),
       
    ];
    
    // Write
    sheet.appendRow(data);

    getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

I've searched online to find something that could maybe be used to incorporate the above code however so far no luck.

One option is to try an incorporate this: Google app script timeout ~ 5 minutes?

Else this: https://script.gs/bypass-script-execution-timeout-programmatically/

However I can't seem to pull it together. Any and all help would be greatly appreciated. Tx

Answer 1

Try this:

Puts all of your files and folders into a tree structure on one of your sheets. It uses PropertiesService as temporaray storage. It prints the tree structure all at once at the end. It runs about 8 minutes on my drive.

var level = 1;
function getFnF1(folder = DriveApp.getRootFolder()) {
  let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  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');//Sheet name
  sh.clearContents();
  SpreadsheetApp.flush();
  PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify({ txt: [], fwt: [], level: 0 }));
  getFnF1();
  let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  const l = tree.level + 1
  tree.txt.forEach(r => {
    if (r.length < l) {
      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'));
    }
  });
  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");
}