[apps script] 구글 드라이브 폴더 내 파일리스트 추출

출처 : https://smartagent.blog/2018/07/16/gsuite05/

G Suite 사용법 #5 앱스 스크립트 활용 예시 모음

 

 

해당 코드를 가지고 무한 루프를 돌며 서브 폴더를 가져오게 수정함.

 

 

-----------------------------------------------------------------------------------------------------------

 

 

 

 

//구동 시작점 메소드
function get_filelist_with_subfolder_start() {
   
  // get This Folder 
  var thisFile = DriveApp.getFileById(SpreadsheetApp.getActive().getId());
  var folders = thisFile.getParents()
  var folderId;
  while (folders.hasNext()) {
    var folder = folders.next();
    folderId = folder.getId();
    break;
  }
   
  // get ID of This Folder 
  var daforder = DriveApp.getFolderById(folderId);
   
  // clear this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
 
  var srow = 1;
  // Set Header
  sheet.getRange(srow, 1).setValue("폴더");
  sheet.getRange(srow, 2).setValue("파일");
  sheet.getRange(srow, 3).setValue("만들어진 날짜");
  sheet.getRange(srow, 4).setValue("마지막으로 수정한 날짜");
  sheet.getRange(srow, 5).setValue("소유자");
  sheet.getRange(srow, 6).setValue("file type");
  sheet.getRange(srow, 7).setValue("file size (Mb)");
  sheet.getRange(srow, 8).setValue("Link");
  // Set Header Color
  var range = sheet.getRange("A1:H1");
  range.setBackground("#d9d9d9");
  // Set Format of URL link
  range = sheet.getRange("H:H");
  range.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
 
  // write file list from This Folder
  var name_thisfolder = daforder.getName();
  _write_file_from_folder(daforder, sheet, name_thisfolder);
 
  // sub folders
  var depth1_folders = daforder.getFolders();
  while(depth1_folders.hasNext()){
    var depth1_folder = depth1_folders.next();
    // Logger.log(" this: " + name_thisfolder + " sub: " + subfolder.getName());
    var name_folder = name_thisfolder + ">" + depth1_folder.getName();
    _write_file_from_folder( depth1_folder, sheet, name_folder )
    _get_filelist_with_subfolder_loop(depth1_folder, sheet, name_folder);
  } // end while depth1
 
}


function _get_filelist_with_subfolder_loop(root_depth_folder, sheet, add_path) {
      var sub_depth_folders = root_depth_folder.getFolders();
      while(sub_depth_folders.hasNext()){
        var sub_depth_folder = sub_depth_folders.next();
        var name_folder = add_path + ">" + sub_depth_folder.getName();
        _write_file_from_folder( sub_depth_folder, sheet, name_folder )
        _get_filelist_with_subfolder_loop(sub_depth_folder,sheet, name_folder);
      } // end while depth
}


//폴더 뎁스3까지만 가져오는 예제
function get_filelist_with_subfolder_ex() {
   
  // get This Folder 
  var thisFile = DriveApp.getFileById(SpreadsheetApp.getActive().getId());
  var folders = thisFile.getParents()
  var folderId;
  while (folders.hasNext()) {
    var folder = folders.next();
    folderId = folder.getId();
    break;
  }
   
  // get ID of This Folder 
  var daforder = DriveApp.getFolderById(folderId);
   
  // clear this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
 
  var srow = 1;
  // Set Header
  sheet.getRange(srow, 1).setValue("폴더");
  sheet.getRange(srow, 2).setValue("파일");
  sheet.getRange(srow, 3).setValue("만들어진 날짜");
  sheet.getRange(srow, 4).setValue("마지막으로 수정한 날짜");
  sheet.getRange(srow, 5).setValue("소유자");
  sheet.getRange(srow, 6).setValue("file type");
  sheet.getRange(srow, 7).setValue("file size (Mb)");
  sheet.getRange(srow, 8).setValue("Link");
  // Set Header Color
  var range = sheet.getRange("A1:H1");
  range.setBackground("#d9d9d9");
  // Set Format of URL link
  range = sheet.getRange("H:H");
  range.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
 
  // write file list from This Folder
  var name_thisfolder = daforder.getName();
  _write_file_from_folder(daforder, sheet, name_thisfolder);
 
  // sub folders
  var depth1_folders = daforder.getFolders();
  while(depth1_folders.hasNext()){
    var depth1_folder = depth1_folders.next();
    // Logger.log(" this: " + name_thisfolder + " sub: " + subfolder.getName());
    name_folder = name_thisfolder + ">" + depth1_folder.getName();
    _write_file_from_folder( depth1_folder, sheet, name_folder )
 
    var depth2_folders = depth1_folder.getFolders();
    while(depth2_folders.hasNext()){
      var depth2_folder = depth2_folders.next();
      name_folder = name_thisfolder + ">" + depth1_folder.getName() + ">" + depth2_folder.getName();
      _write_file_from_folder( depth2_folder, sheet, name_folder )
 
      var depth3_folders = depth2_folder.getFolders();
      while(depth3_folders.hasNext()){
        var depth3_folder = depth3_folders.next();
        name_folder = name_thisfolder + ">" + depth1_folder.getName() + ">"
          + depth2_folder.getName() + ">" + depth3_folder.getName();
        _write_file_from_folder( depth3_folder, sheet, name_folder )
      } // end while depth3
 
    } // end while depth2
 
  } // end while depth1
 
}
 
/**
 * 폴더 내 파일 리스트를 시트에 쓰기
 */
function _write_file_from_folder(ifolder, isheet, ifolder_name){
  var dafiles = ifolder.getFiles();
  
  // write folder info
  var srow = isheet.getLastRow() + 1;
  isheet.getRange(srow, 1).setValue(ifolder_name);
  isheet.getRange(srow, 3).setValue(ifolder.getDateCreated());
  isheet.getRange(srow, 4).setValue(ifolder.getLastUpdated());
  isheet.getRange(srow, 6).setValue("FOLDER");
  isheet.getRange(srow, 8).setValue(ifolder.getUrl());
   
  var range = isheet.getRange("A"+srow+":H"+srow);
  range.setBackground("#f3f3f3");
 
  // write file list 파일 리스트 쓰기
  while(dafiles.hasNext()){
    var dafile = dafiles.next();
    var file_name = dafile.getName();
     
    srow = srow + 1;
    // Write file info
    isheet.getRange(srow, 1).setValue(ifolder_name); // 폴더 이름
    isheet.getRange(srow, 2).setValue(file_name); // 파일 이름
    isheet.getRange(srow, 3).setValue(dafile.getDateCreated()); // 만들어진 날짜
    isheet.getRange(srow, 4).setValue(dafile.getLastUpdated()); // 마지막으로 수정한 날짜
    // isheet.getRange(srow, 5).setValue(dafile.getOwner().getName()); // 소유자
    isheet.getRange(srow, 6).setValue(dafile.getMimeType().replace('application/vnd.google-apps.', 'google ')); // 파일 종류
    isheet.getRange(srow, 7).setValue(dafile.getSize() / (1024 * 1024) ); // 파일 사이즈
    isheet.getRange(srow, 8).setValue(dafile.getUrl()); // 파일 url
  }
   
}