세상 바라보기

구글 스프레드시트를 개인적으로든 업무적으로든 다양하게 사용중입니다.

드랍다운 기능이 생기면서 엄청 유용하게 사용하고 있는데요
리스트를 자주 추가해줘야 하는 경우 드랍다운 목록으로 관리하든 범위로 관리하든 불편하기는 매한가지입니다.

그래서
드랍다운 대상 시트에 내용 입력 시 드랍다운 목록 범위에 있으면 그대로 사용하고
목록 범위에 없으면 목록 범위에 추가해주도록 스크립트를 만들었습니다.

function onEdit(e){
  //e.range.getColumnIndex() : 수정될 시트의 컬럼 번호
  //listEdit 함수의 두번째 파라미터 column : 참조할 리스트가 있는 컬럼 번호
  var colNum = 0;
  
  //여러 컬럼 리스트를 사용해야 하는 경우 수정할 컬럼과 참조할 컬럼을 여기에 추가
  if(e.range.getColumnIndex() == 1) colNum = 1;
  if(e.range.getColumnIndex() == 3) colNum = 4;

  if(colNum != 0) listEdit(e.value, colNum);
}

function listEdit(value, column){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('list');
  var row = 2;
  var lists = [];
  while(sh.getRange(row, column).getValue() != ''){
    lists.push(sh.getRange(row, column).getValue());
    row++;
  }
  var result = false;
  for(var list of lists){
    if(value == list){
      result = true;
      break;
    }
  }
  if(!result){
    sh.getRange(row, column).setValue(value);
    row++;
  }
}

위 코드에서 몇가지 제약사항이 있는데요
1. 목록 시트명 고정 필요
2. 참조 리스트는 공백이 생기지 않게 할 것(리스트 삭제 시 아래 엤는 내용 끌어올려야함)

위 두가지만 지켜주시면 됩니다.

나중에 견본 시트 같이 올려드리겠습니다.

구글드라이브에 너무 많은 파일을 넣어놓다보니
나름 폴더 정리를 한다고 생각했는데도 찾기가 힘들때가 상당히 자주 있습니다.
그래서 구글드라이브의 파일 리스트를 만들 필요성을 느꼈는데요
개인적으로도 잘 사용하고 있지만 특히 회사에서 공유드라이브의 파일 목록을
매일 새벽에 업데이트 하도록 트리거를 걸어놓아서 매우 잘 사용하고 있습니다.

아래 스크립트를 이용하시면 될 것 같습니다.
두개의 function을 이용하고 있고
특정 폴더 아래의 파일 리스트를 추출하고자 하시는 경우
9행의 getFileList의 파라미터를
폴더명, folderID, 0 으로 입력해 주시면 됩니다.

혹시 질문 있으시다면 댓글로 달아 주시면 친절히 답해 드리겠습니다.

var maxlevel = 0;
var filelist = [];

function fileList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('FileList');
  sh.clear();
  var root = DriveApp.getRootFolder().getId();
  getFileList('root', root, 0);
  sh.getRange(1, 1).setHorizontalAlignment('center');
  
  sh.getRange(1, 1).setValue('Folder');
  sh.getRange(1, 2).setValue('FileName');
  sh.getRange(1, 3).setValue('FileUrl');
  sh.getRange(1, 4).setValue('FileMimeType');
  sh.getRange(1, 5).setValue('Description');
  var row = 2;
  for(var i in filelist){
    sh.getRange(row, 1).setValue(filelist[i].folderName);
    sh.getRange(row, 2).setValue(filelist[i].fileName);
    sh.getRange(row, 3).setValue(filelist[i].fileUrl);
    sh.getRange(row, 4).setValue(filelist[i].fileMimeType);
    sh.getRange(row, 5).setValue(filelist[i].fileDescription);
    row++;
  }
  sh.getRange(2, 1, 5).sort(1);
}

function getFileList(name, id, level){
  if(maxlevel < level) maxlevel = level;
  var folder = DriveApp.getFolderById(id); // I change the folder ID  here 
  var subfolders = folder.getFolders();
  while(subfolders.hasNext()){
    subfolder = subfolders.next();
    getFileList(name + '\\' + subfolder.getName(), subfolder.getId(), ++level);
    level--;
  }
  var files = folder.getFiles();
  while(files.hasNext()){
    file = files.next();
    filelist.push({
      folderName: name, 
      fileName: file.getName(),
      fileUrl: file.getUrl(),
      fileMimeType: file.getMimeType(),
      fileDescription: file.getDescription() == null ? '' : file.getDescription()
    });
  }
}