Home > Net >  How to Import Google Drive Sub-Folder URL to Google Sheet
How to Import Google Drive Sub-Folder URL to Google Sheet

Time:09-26

I'm trying to import the Sub-folder's (Not Files) Name & URL which is in the Main Folder to Google Sheets

enter image description here

I tried working on this code but it didn't help me to achieve my task.

var SS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
    
   
    function getFolderArray(folderId){
      var folder = DriveApp.getFolderById(folderId);
      var Folders = folder.getFolders();
      var FolderList = [];
      
      //Loop though Folders and add names and urls to the array
      while (folder.hasNext()){ 
        var Folder = Folders.next();
        var FolderName = Folder.getName();    
        var FolderUrl = Folder.getUrl();
        FolderList = FolderList.concat([[FolderName, FolderUrl]]);
      }
       //See returned FolderList in a log
      //Logger.log( FolderList ) //Preview Returned Array      
      return FolderList
      
     
    }
    
      
    //Prints any 2D array to a range that starts with the selected cell
    function printArrayToSelection(twoDimArr){  
      var firstCell = SS.getActiveCell();
      var lastCell = firstCell.offset(twoDimArr.length - 1, twoDimArr[0].length - 1);
      var destinationRange = SS.getActiveSheet().getRange(
        firstCell.getA1Notation()   ':'   lastCell.getA1Notation());
      destinationRange.setValues(twoDimArr);
      
    }
    
    //Print the actual data
    function printFolderArray(){
      printArrayToSelection(getFolderArray('0B4-wws2P3gUhcExqWEI0VTlrNm8'));
    }

Can I know what went wrong?

CodePudding user response:

Try

function listOfFoldersByID() {
  var folderid = '_______your folder ID___________'; // change FolderID
  SpreadsheetApp.getActiveSheet().clear();
  SpreadsheetApp.getActiveSheet().appendRow(["parent","folder","URL"]);
  try {
    var parentFolder =DriveApp.getFolderById(folderid);
    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();
    SpreadsheetApp.getActiveSheet().appendRow([parent,childFolder.getName(),childFolder.getUrl()]);
    listSubFolders(childFolder,parent   "|"   childFolder.getName());
  }
}
  • Related