Google Script: Write folder path to google sheets in one cell instead of one row and limit folder path

Question

I would like to add data from folders to a google sheet using google sheets. I would like to have the full folder path in one cell. At the moment I am getting the folder path in one column but not in one row. Like this:

Folder 1

Subfolder 2

SubSubfolder 3

Instead of:

Folder 1 (next cell) Subfolder 1 (next cell) Subsubfolder 1

Folder 1 (next cell) Subfolder 2 (next cell) Subsubfolder 1

Another issue i have been facing is that I would like to limit the depth of the folder path to 3 , how can I integrate that in the loop? I tried using the && operator but that didn't do the trick as the loop goes on and starts counting from a different folder.

Help is highly appreciated :)

Thank you in advance.


function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var topFolder = DriveApp.getFolderById("FolderID");
  var topPath = "/";
  sheet.appendRow(["Extracted Folder", "Industry", "Client", "Projectname","Size of Folder", "Link to Folder", "Date Created", "Owner"])

  iterateSubFolders(topFolder, topPath)
}

function iterateSubFolders(callingFolder, callingPath, sheet) {

  var callingFolderName = callingFolder.getName();
  var callingFolderFullPath = callingPath + callingFolderName + "/";
  Logger.log(callingFolderFullPath);
     
  addRowToSheetForFolder(callingFolder)
  n=0
  var childSubFolders = callingFolder.getFolders();
  while ((childSubFolders.hasNext()) && (n < 3) ) {
      var nextSubFolder = childSubFolders.next();
      iterateSubFolders(nextSubFolder, callingFolderFullPath);
      n=n+1
  }
  return callingFolderFullPath;
    //This should only return the folder path with the depth = 3
    //The full folder path should appear in the google sheets in one row
}

function addRowToSheetForFolder(folder) {
  var FolderBanking = DriveApp.getFolderById("13C4cnxnzNP_HMItTiS5PpuyQgWovUuF_");
  data = [
             
             //FolderBanking, //Parent folder
             folder.getName().toString(),
             "move industry here",
             "move client to here",
             "move project name to here",
             folder.getSize(),
             folder.getUrl(),
             folder.getDateCreated(),
             //folder.getOwner().getName(), geht nur für MyDrive
             "manuell pflegen",
             folder.getDescription(),
         ];
  Logger.log("Writing row to sheet");
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(data);
  
}

user_10612011 · Answer

If I understand you correctly, you'd like to accomplish two things:

Have the full folder path for the corresponding folder in a single cell (column A) instead of the name of the nested folder.
Limit the folder search to 3 levels, so folders further down the hierarchy are not retrieved.

If that's correct, you can do the following.

Folder path in one cell:

Allow addRowToSheetForFolder to accept a second parameter corresponding to the folder path, which you have previously retrieved, and append that to the sheet.

addRowToSheetForFolder(callingFolder, callingFolderFullPath);

Limit depth to 3:

Allow iterateSubFolders to accept a depth parameter, set it to 3 when calling it in myFunction, set it to depth - 1 when calling it inside the while loop, and make the loop dependent on depth:

iterateSubFolders(nextSubFolder, callingFolderFullPath, depth - 1);

Sample output:

Code snippet:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var topFolder = DriveApp.getFolderById("FolderID");
  var topPath = "/";
  sheet.appendRow(["Extracted Folder", "Industry", "Client", "Projectname","Size of Folder", "Link to Folder", "Date Created", "Owner"])
  iterateSubFolders(topFolder, topPath, 3)
}

function iterateSubFolders(callingFolder, callingPath, depth) {
  var callingFolderName = callingFolder.getName();
  var callingFolderFullPath = callingPath + callingFolderName + "/";
  addRowToSheetForFolder(callingFolder, callingFolderFullPath);
  n=0;
  var childSubFolders = callingFolder.getFolders();
  while ((childSubFolders.hasNext()) && (n < depth) ) {
      var nextSubFolder = childSubFolders.next();
      iterateSubFolders(nextSubFolder, callingFolderFullPath, depth - 1);
  }
  return callingFolderFullPath;
}

function addRowToSheetForFolder(folder, fullFolderPath) {
  data = [
             fullFolderPath,
             "move industry here",
             "move client to here",
             "move project name to here",
             folder.getSize(),
             folder.getUrl(),
             folder.getDateCreated(),
             "manuell pflegen",
             folder.getDescription()
         ];
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(data);
}

Update:

If you want to append the data to different cells from the same row, just use split and spread when appending the data to the sheet:

data = [...fullFolderPath.split("/"), "move industry here", ...];

Also, change top path to an empty string if you don't want to skip column A:

var topPath = "";