구글드라이브를 상당히 많이 사용하는 편이고 회사 업무 뿐 아니라 개인적인 일도 구글 스프레드시트를 이용해 정리하는 편입니다.
그러다보니 구글드라이브의 파일이 너무 정리되지 않더군요...
그래서, 구글드라이브 파일을 추출하고 검색하고 설명을 수정하는 하나의 시트를 만들어봤습니다.
🚨 경고.
Google Apps Script는 실행시간이 제한되어 있습니다.
특히 FileList를 가져오는 과정에서 시간 초과로 오류가 발생한다면
특정 하위폴더의 파일 목록을 별도로 추출하시는 것을 권장합니다.
이 페이지는 Google Apps Script를 사용할 줄 안다는 전제로 만들어져 있습니다.
먼저 구글 스프레드시트에서 시트를 3개 생성합니다.
1. 검색
2. FileList
3. FolderList
(시트 이름은 대소문자까지 동일해야 합니다. Google Apps Script에서 시트 이름을 이용합니다.)
그 다음 검색 시트를 다음과 같이 만들어 줍니다.
저는 B2 셀의 내용은 스마트칩으로 만들었습니다.
어떤 방식을 사용해도 상관없지만 AND, OR만 들어갈 수 있도록 제약조건을 만들어주면 좋습니다.
이 이후의 내용은 Google Apps Script에서 사용될 코드입니다.
(마지막에 전체 코드가 있으니 전체 코드를 떠 가실 분은 해당 내용을 복사하시면 됩니다.)
코드는 크게 5가지로 구성되어 있으며 전역변수 설정부분이 있습니다.
1. 전역변수 영역(소스코드는 더보기를 눌러주세요)
더보기
var maxlevel = 0;
var maxfolderlevel = 0;
var filelist = [];
var folderlist = [];
var root = DriveApp.getRootFolder().getId();
var drivepath = '<파일목록을 가져올 드라이브 주소>';
var rootName = 'filelist';여기에서 파일 목록을 가져올 드라이브 주소는 '내 드라이브' 전체 파일 목록을 가져오고 싶다면 그 윗줄에 있는 root를 그대로 사용하시면 됩니다.
그러나 특정 폴더 아래에 있는 파일만 가져오고 싶다면 다음과 같이 해당 폴더의 경로를 넣어주시면 됩니다.
위 그림의 빨간색 박스 안에 있는 경로를 넣어주시면 됩니다.
2. 파일 목록 가져오기(소스코드는 더보기를 눌러주세요)
더보기
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')
});
}
}mimetype을 뽑아내는데는 제가 갖고있는 파일 형태 목록만 넣었습니다. 추가가 필요한 파일 양식은 직접 추가해주시면 됩니다.
3. 폴더 목록 가져오기(소스코드는 더보기를 눌러주세요)
더보기
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()
});
}
4. 검색 시트에서 검색어를 넣거나 파일목록에서 설명을 수정하는 경우 발생하는 업데이트 코드입니다.(소스코드는 더보기를 눌러주세요)
더보기
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 += "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;
}
}
}
5. 이건 개인적으로 하루 안에 외부 공개된 파일이 생성되거나 수정된 경우 슬랙으로 알람을 받기 위해 만들어놓은 코드입니다. 본인의 슬랙 incomming webhook이 설정되어 있거나 다른 메신저의 incomming webhook이 설정되어 있어야 하며 다른 메신저를 사용하는 경우 payload가 변경될 수 있습니다.
(소스코드는 더보기를 클릭하세요)
더보기
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);
}
전체 코드는 다음과 같습니다.
더보기
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);
}
여기까지 왔다면 코드 생성은 마무리 되었습니다.
이제 트리거를 생성해 검색어를 넣거나 file목록, 검색결과 목록의 description 수정 시 드라이브에 적용되도록 합니다.
트리거에서 스프레드시트 수정 시 updateSheet가 실행될 수 있도록 다음과 같이 설정합니다.
이제 다시 코드편집기로 가 fileList, folderList, searchExternalMember를 한번씩 실행시켜 권한을 허용해줍니다.
저는 개인적으로 fileList, folderList function도 새벽에 실행되도록 트리거를 설정해
하루에 한번씩 업데이트 되도록 하고 있습니다.
혹시 추가적인 설명이 필요하시거나 따라 하면서 잘 안되는 부분이 있다면
댓글 남겨주시면 최대한 빨리 답변 드리도록 하겠습니다.
도저히 따라하지 못하겠다 하시는 분은 샘플을 복사해가실 수 있도록 열어드릴테니
gmail 주소를 비밀 댓글로 남겨주시기 바랍니다.
ligilo
행복한 하루 되세요~