var maxlevel = 0;
var maxfolderlevel = 0;
var filelist = [];
var folderlist = [];
var root = DriveApp.getRootFolder().getId();
var drivepath = '<파일 목록 추출을 원하는 구글드라이브 주소>';
var rootName = 'filelist';
function fileList() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('FileList');
sh.clear();
getFileList(rootName, drivepath, 0);
sh.getRange(1, 1).setHorizontalAlignment('center');
sh.getRange(1, 1).setValue('Folder');
sh.getRange(1, 2).setValue('FileName');
sh.getRange(1, 3).setValue('File Link');
sh.getRange(1, 4).setValue('Folder Link');
sh.getRange(1, 5).setValue('FileMimeType');
sh.getRange(1, 6).setValue('Access');
sh.getRange(1, 7).setValue('Description');
sh.getRange(1, 8).setValue('공유자');
sh.getRange(1, 9).setValue('최종수정일');
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].folderUrl);
sh.getRange(row, 5).setValue(filelist[i].fileMimeType);
sh.getRange(row, 6).setValue(filelist[i].fileAccess);
sh.getRange(row, 7).setValue(filelist[i].fileDescription);
sh.getRange(row, 8).setValue(filelist[i].fileshare);
sh.getRange(row, 9).setValue(filelist[i].fileLastUpdated);
row++;
}
sh.getRange(2, 1, filelist.length, 8).sort([1, 2]);
sh.autoResizeColumns(1, 2);
sh.autoResizeColumns(5, 3);
sh.setColumnWidths(3, 2, 100);
}
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();
var sharetext = '';
if(file.getOwner().getEmail() != '<본인의 이메일 주소>') sharetext += sharetext == '' ? '소유자 : ' + file.getOwner().toString() : '\n소유자 : ' + file.getOwner().toString();
if(file.getViewers().length > 0){
for(var i in file.getViewers()){
sharetext += sharetext == '' ? '뷰어 : ' + file.getViewers()[i].getName() : '\n뷰어 : ' + file.getViewers()[i].getName();
}
}
if(file.getEditors().length > 0){
for(var i in file.getEditors()){
sharetext += sharetext == '' ? '편집자 : ' + file.getEditors()[i].getName() : '\n편집자 : ' + file.getEditors()[i].getName();
}
}
var mimetype = file.getMimeType();
var mime = "";
switch(mimetype){
case "application/vnd.google-apps.spreadsheet" :
mime = "구글스프레드시트";
break;
case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" :
mime = "엑셀";
break;
case "application/pdf" :
mime = "PDF";
break;
case "application/vnd.google-apps.document" :
mime = "구글닥스";
break;
case "application/vnd.openxmlformats-officedocument.wordprocessingml.document" :
mime = "워드";
break;
case "application/vnd.google-apps.shortcut" :
mime = "바로가기";
break;
case "application/vnd.openxmlformats-officedocument.presentationml.presentation" :
mime = "PPT";
break;
case "text/plain" :
mime = "텍스트";
break;
case "application/x-hwp" :
mime = "한글";
break;
case "application/vnd.google-apps.presentation" :
mime = "구글프레젠테이션";
break;
case "application/x-shellscript" :
mime = "쉘스크립트";
break;
}
filelist.push({
folderName: name,
fileName: file.getName(),
fileUrl: file.getUrl(),
folderUrl: folder.getUrl(),
fileMimeType: mime,
fileAccess: file.getSharingAccess().toString(),
fileDescription: file.getDescription() == null ? '' : file.getDescription(),
fileshare: sharetext,
fileLastUpdated : Utilities.formatDate(file.getLastUpdated(), 'Asia/Seoul', 'yyyy-MM-dd hh:mm')
});
}
}
function folderList(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('FolderList');
sh.clear();
getFolderList(rootName, drivepath, 0);
sh.getRange(1, 1).setHorizontalAlignment('center');
sh.getRange(1, 1).setValue('FolderName');
sh.getRange(1, 2).setValue('FolderUrl');
sh.getRange(1, 3).setValue('Access');
sh.getRange(1, 4).setValue('Description');
var row = 2;
for(var i in folderlist){
sh.getRange(row, 1).setValue(folderlist[i].folderName);
sh.getRange(row, 2).setValue(folderlist[i].folderUrl);
sh.getRange(row, 3).setValue(folderlist[i].folderAccess);
sh.getRange(row, 4).setValue(folderlist[i].folderDescription);
row++;
}
sh.getRange(2, 1, folderlist.length, 4).sort(1);
}
function getFolderList(name, id, level){
if(maxfolderlevel < level) maxfolderlevel = level;
var folder = DriveApp.getFolderById(id); // I change the folder ID here
var subfolders = folder.getFolders();
while(subfolders.hasNext()){
subfolder = subfolders.next();
getFolderList(name + '\\' + subfolder.getName(), subfolder.getId(), ++level);
level--;
}
folderlist.push({
folderName: name,
folderUrl: folder.getUrl(),
folderAccess: folder.getSharingAccess().toString(),
folderDescription: folder.getDescription() == null ? '' : folder.getDescription()
});
}
function updateSheet(e){
var sheetName = e.source.getActiveSheet().getName();
var column = e.range.getColumn();
var row = e.range.getRow();
if(sheetName == '검색' && column == 2 && (row == 1 || row == 2)) search();
if(sheetName == 'FileList' && column == 7) updateDescription(row);
if(sheetName == '검색' && column == 7) updateSearchResult(row);
}
function search(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('검색');
if(sh.getRange(1, 2).getValue() == '') return;
var searchstring = sh.getRange(1, 2).getValue().toString().split(' ');
var querystring = "SELECT * WHERE (";
var querywhere = "";
var seperate = sh.getRange(2, 2).getValue();
if(seperate == '') seperate = 'OR';
for(i = 65; i < 68; i++){
if(i == 67) i = 71;
var column = String.fromCharCode(i);
if(i > 65) querystring += ' OR (';
for(string of searchstring){
if(querywhere != '') querywhere += seperate + ' ';
// querywhere += "A contains '" + string + "' OR B contains '" + string + "' OR G contains '" + string + "'";
querywhere += "Upper(" + column + ") contains Upper('" + string + "') "
}
querystring += querywhere + ")";
querywhere = '';
}
sh.getRange(4, 1).setFormula("=QUERY(FileList!A:H, \"" + querystring + querywhere + "\", 1)");
sh.setColumnWidths(1, 2, 400);
sh.setColumnWidths(3, 4, 100);
sh.setColumnWidth(7, 400);
}
function updateDescription(row){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('FileList');
var fileUrl = sh.getRange(row, 3).getValue();
var regex = /\/d\/([a-zA-Z0-9-_]+)/;
var match = fileUrl.match(regex);
var file;
if (match && match[1]) {
file = DriveApp.getFileById(match[1]);
}
file.setDescription(sh.getRange(row, 7).getValue());
}
function updateSearchResult(row){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('검색');
var updateDescription = sh.getRange(row, 7).getValue();
sh.getRange(row, 7).setValue('');
var fileUrl = sh.getRange(row, 3).getValue();
var regex = /\/d\/([a-zA-Z0-9-_]+)/;
var match = fileUrl.match(regex);
var file;
if (match && match[1]) {
file = DriveApp.getFileById(match[1]);
}
file.setDescription(updateDescription);
var listsh = ss.getSheetByName('FileList');
for(var listrow = 2; listrow <= listsh.getLastRow(); listrow++){
if(listsh.getRange(listrow, 3).getValue() == fileUrl){
listsh.getRange(listrow, 7).setValue(updateDescription);
break;
}
}
}
function searchExternalMember(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('FileList');
var maxRow = sh.getLastRow();
var accessRange = [];
var shareExternal = [];
for(var i = 2; i <= maxRow; i++){
var nowdate = new Date();
var lastupdatedate = new Date(sh.getRange(i, 9).getValue());
var diffInDays = Math.floor((nowdate.getTime()-lastupdatedate.getTime())/(24*3600*1000));
if(diffInDays > 0) continue; //하루 이내 수정사항만 알림
if(sh.getRange(i, 6).getValue() != 'PRIVATE') accessRange.push(i);
if(sh.getRange(i, 8).getValue().trim() != '') shareExternal.push(sh.getRange(i, 2).getValue() + ' : ' + sh.getRange(i, 8).getValue().trim().split('\n').length + '명');
}
var message = '';
if(accessRange.length > 0){
message += '외부 공개 파일 : \n';
for(var row of accessRange){
message += '- ' + sh.getRange(row, 2).getValue() + ' : ' + sh.getRange(row, 6).getValue() + '\n';
}
}
message += '\n';
if(shareExternal.length > 0){
message += '공유된 파일 : \n';
for(var row of shareExternal){
message += '- ' + row + '\n';
}
}
if(message.trim() == '') return false;
var url = "<본인의 슬랙 incomming webhook 주소>";
var payload = {
"channel": "<알림받을 채널명>",
"attachments": [{
"text": message
}]
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload)
};
return UrlFetchApp.fetch(url,options);
}
