구글스프레드 시트 자동화 [1] 문서 인덱스 만들기

구글 스프레드시트를 활용하다 보면 드라이브에 폴더링을 깔끔하게 하더라도 부족한 부분이 생기기 마련입니다.

또한 여러 데이터를 편리하게 관리하고 싶을 때, 링크 모음집을 만들어 사용해보신 분들이 있을 겁니다.

하지만 월 매출현황이라던가 매주 혹은 매월 작성되는 문서들이 있거나, 사용자들이 꾸준히 문서를 만들어나가는 경우에는 매번 링크를 달기가 쉽지 않은 일이겠죠.

이런 경우에 매우 요긴하게 쓰일 수 있는 자동화 Appscript를 소개합니다.

앱스 크립트란?

앱 스크립트(Apps Script)는 구글 워크스페이스 플랫폼에서 가벼운 애플리케이션 개발을 위해
구글이 개발한 스크립팅 플랫폼이다. 구글 앱 스크립트는 처음에 마이크 함(Mike Harm)이 구글 시트의 개발자로서 일할 당시 부차적인 프로젝트로서 개발하였다. – 출처 위키백과

말 그대로 구글 스프레드시트를 극한까지 쓸 수 있는 환경을 제공해주는 사용자 커스터마이징 툴의 느낌이 강합니다.
매우 무섭게도 앱스 크립트 하나만으로도 거의 회사 운영 업무에 필요한 전반 (회계, 인사, 노무 등) 업무들을 대부분 자동화할 수 있습니다.

아쉽게도 오늘은 그러한 자동화를 다루진 않고, 자동화의 든든한 초석이 되어줄 문서 인덱스에 대해 소개합니다.

문서 인덱스?

목차를 만든다고 생각하시면 쉽습니다.
예를 들어, 6월 매출 현황표를 정리해놓은 폴더에 20개 지점의 매출 전표가 있다고 가정해봅시다. 이거 엑셀로 하나씩 다 보려면……
오늘은 치킨 각 이 아니라 야근각…!

거창하게 만든다면 도서관 같은 구조도 불가능하지 않습니다. 특히 당장 필요한 게 아니면 먼저 구글 드라이브의 폴더링에 대한 것부터
차근차근 이해하고 나시면 꽤 큰 데이터도 쉽게 정리할 수 있으니 너무 먼저 지레 겁먹는 일은 삼가도록 하겠습니다

구글 드라이브부터 알아가야 한다.

구글 드라이브를 써본 사람들은 어느 정도 이해가 빠르지만, 처음 접하는 이들을 위해서 간단히 소개를 하고 넘어가겠습니다
우선 구글 드라이브는 파일을 저장할 수 있는 온라인 스토리지 공간입니다. PDF부터 XLSX CSV 등 파일도 저장할 수 있으며,
구글 스프레드시트, 구글 폼, 구글 독스, 구글프레제넽이션 등 온라인 상에서 문서작성이 가능한 엄청난 장점을 지니고 있습니다.

이러한 특성 덕분에 구글 드라이브에 대해 잘 모르셨던 분들도 대략이나마 아 네이버 클라우드 같은 서비스구나 하면서 알정도의 인지도를 갖추고 있습니다.

구글스프레드 시트 자동화 [1] 문서 인덱스 만들기 - undefined - undefined - 구글 드라이브부터 알아가야 한다.

먼저 우리가 이해해야 하는 것은 ‘폴더’라는 개념입니다.

드라이브의 가장 처음 화면에서 폴더를 만들어보면 위와 같이 /drive/folders/folder_ID 값이 만들어지게 됩니다.
앞으로 만들 문서 인덱스 자동화에 가장 큰 역할을 할 녀석이 바로 저 폴더에 존재하는 고유 ID값이 되게 됩니다.

왜 중요할까요?

1. 구글 드라이브는 문서 ID 기반으로 문서를 찾는다.

2. 문서 내의 콘텐츠는 언제든 바뀔 수 있기 때문. (제목 등)

3. 하위 문서들을 불러오기 위해 폴더의 단계가 많아질수록 스크립트의 난이도가 높아지게 된다.

아마 3번이 정확히 어떤 이야긴지 감이 안 오실 텐데요. 보통은 하나의 폴더에 모든 문서를 넣어놓는 게 아닌, 분류를 통해 해당 폴더의 하위 폴더 또 그 폴더들의 하위폴더 이런 식으로 계속 무한 증식하게 됩니다. (정리를 잘하시는 분들은 특히나…)

이렇게 되면 특정 문서를 찾기 위해 몇 개씩 폴더를 열어봐야 하는 일이 생기기도 하죠?
이럴 땐 폴더들끼리 구분만 잘해줘도 중간 이상은 가게 됩니다.

적용 예제

먼저 스프레드시트가 필요합니다. 없으신 분들은 새로 만들어 주세요. 이후 메뉴 탭에서 확장 프로그램 – Apps Script로 들어와 주세요.

충분히 혼란스러울 수 있지만 우리는 침착하게

function myFunction() {

}

요 부분을 지워주고 아래 스크립트를 복사 붙여 넣기 해주세요.

var folderId = SpreadsheetApp.getActiveSpreadsheet().getRange('A1').getValues(); // The folder ID (everything after the 'folders/' portion of the URL).
var searchDepthMax = 100; // Max depth for recursive search of files and folders
var listFiles = true; // flag for listing files
var cacheTimeout = 24 * 60 * 60 * 1000; // set cache time-out
var lockWaitTime = 1 * 60 * 1000; // set maximium watiting time for the cache lock
var appendToSheet = false; // flag for appending to selected spreadsheet
var writeBatchSize = 100; // the write batch size


// ===========================================================================================================
// Global variables
var cacheOutputs = 'InventoryScript_outputs';
var cacheKillFlag = 'InventoryScript_killFlag';


// ===========================================================================================================
// Reset the script cache if it is required to run from the beginning
function reset() {
  SpreadsheetApp.getActiveSpreadsheet().toast('Reseting script...', 'Status', -1);
  
  // reset triggers and delete cache variables
  setKillFlag_(true, this.cacheTimeout);
  deleteTriggers_(this.loopResetGapTime);
  deleteCache_();
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Reset is complete!', 'Status', -1);
}


// ===========================================================================================================
// List all folders and files, then write into the current spreadsheet.
function run() {
  SpreadsheetApp.getActiveSpreadsheet().toast('Executing script...', 'Status', -1);
  
  // load cache
  setKillFlag_(false, this.cacheTimeout);
  var outputRows = getCache_(this.lockWaitTime);
  
  // get list
  if (outputRows === undefined || outputRows === null ||
      outputRows[0] === undefined || outputRows[0] === null) {
    outputRows = [];
    
    outputRows = getChildFiles_(null, DriveApp.getFolderById(this.folderId), 
                                listFiles, cacheTimeout, outputRows);
    
    outputRows = getFolderTree_(outputRows, this.folderId, this.listFiles, this.cacheTimeout, 
                                this.lockWaitTime, this.searchDepthMax);
  }
  
  
  // write list
  writeFolderTree_(outputRows, this.appendToSheet);
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Execution is complete!', 'Status', -1);
}


// ===========================================================================================================
// Get the list of folders and files
function getFolderTree_(outputRows, folderId, listFiles, cacheTimeout, lockWaitTime, searchDepthMax) {
  var parentFolder, sheet = null;
  var searchDepth = -1;
  
  try {
    // Get folder by id
    parentFolder = DriveApp.getFolderById(folderId);
    
    // Initialise the spreadsheet
    sheet = SpreadsheetApp.getActiveSheet();
    
    // Get files and/or folders
    outputRows = getChildFolders_(searchDepth, parentFolder.getName(), parentFolder, sheet,
                                  listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
  
  return outputRows;
}


// ===========================================================================================================
// Write the list of folders and files into the spreadsheet
function writeFolderTree_(outputRows, appendToSheet) {
  var sheet = null;
  
  try {
    if (getKillFlag_() === false) {
      // Initialise the spreadsheet
      sheet = SpreadsheetApp.getActiveSheet();
      
      // Write to the selected spreadsheet
      writeOutputs_(sheet, outputRows, appendToSheet);
      
      // reset cache
      reset();
    }
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
}


// ===========================================================================================================
// Get the list of folders and files and their metadata using a recursive loop
function getChildFolders_(searchDepth, parentFolderName, parentFolder, sheet, listFiles, cacheTimeout,
                          lockWaitTime, outputRows, searchDepthMax) {
  var childFolders = parentFolder.getFolders();
  var childFolder = null;
  searchDepth += 1;
  
  try{
    // List sub-folders inside the folder
    while (childFolders.hasNext() && searchDepth < searchDepthMax && getKillFlag_() === false) {
      childFolder = childFolders.next();
      SpreadsheetApp.getActiveSpreadsheet().toast('Searching folder ' + childFolder.getName() +
        ' at depth ' + searchDepth + " ...", 'Status', -1);
      
      // Get folder information
      // Logger.log("Folder Name: " + childFolder.getName());
      outputRows.push([
        parentFolderName + "/" + childFolder.getName(),
        childFolder.getName(),
        "Folder",
        childFolder.getDateCreated() ? childFolder.getDateCreated() : "NULL",
        childFolder.getUrl() ? childFolder.getUrl() : "NULL",
        childFolder.getLastUpdated() ? childFolder.getLastUpdated() : "NULL",
        childFolder.getDescription() ? childFolder.getDescription() : "NULL",
        childFolder.getSize() ? childFolder.getSize() : "NULL",
        childFolder.getOwner() ? childFolder.getOwner() : "NULL", 
        childFolder.getOwner().getEmail() ? childFolder.getOwner().getEmail() : "NULL",
        childFolder.getSharingPermission() ? childFolder.getSharingPermission() : "NULL",
        childFolder.getSharingAccess() ? childFolder.getSharingAccess() : "NULL"
        //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFolder.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
        // more info: https://developers.google.com/apps-script/reference/drive/folder 
      ]);
      
      // cache outputs
      setCache_(outputRows, lockWaitTime, cacheTimeout);
      
      // List files inside the folder
      outputRows = getChildFiles_(
        parentFolder, childFolder, listFiles, cacheTimeout, outputRows);
      
      // Recursive call of the current sub-folder
      outputRows = getChildFolders_(searchDepth++, parentFolderName + "/" + childFolder.getName(), 
        childFolder, sheet, listFiles, cacheTimeout, lockWaitTime, outputRows, searchDepthMax);
    }
  } catch (e) {
    Logger.log('Timed out: Restarting! ' + e.toString());
    SpreadsheetApp.getActiveSpreadsheet().toast( 'Timed out!', 'Status', -1);
  }
  
  // cache outputs
  setCache_(outputRows, lockWaitTime, cacheTimeout);
  
  return outputRows;
}


// ===========================================================================================================
// Get the list of files in the selected folder
function getChildFiles_(parentFolder, childFolder, listFiles, cacheTimeout, outputRows) {
  var childFiles = childFolder.getFiles();
  var childFile = null;
  var path = ""
  
  try{
    // List files inside the folder
    while (listFiles && childFiles.hasNext()) {
      childFile = childFiles.next();
      
      // derive path
      if (parentFolder === null){
        path = childFolder.getName() + "/" + childFile.getName()
      }else{
        path = parentFolder.getName() + "/" + childFolder.getName() + "/" + childFile.getName()
      }
      
      // Get file information
      //Logger.log("File Name: " + childFile.getName());
      outputRows.push([
        path,
        childFile.getName(),
        childFile.getName().split('.').pop(),
        childFile.getDateCreated() ? childFile.getDateCreated() : "NULL",
        childFile.getUrl() ? childFile.getUrl() : "NULL",
        childFile.getLastUpdated() ? childFile.getDescription() : "NULL",
        childFile.getDescription() ? childFile.getDescription() : "NULL",
        childFile.getSize() ? childFile.getSize() : "NULL", 
        childFile.getOwner() ? childFolder.getOwner() : "NULL",
        childFile.getOwner().getEmail() ? childFile.getOwner().getEmail() : "NULL",
        childFile.getSharingPermission() ? childFile.getSharingPermission() : "NULL",
        childFile.getSharingAccess() ? childFile.getSharingAccess() : "NULL" 
        //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFile.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
        // more info: https://developers.google.com/apps-script/reference/drive/folder 
      ]);
    }

    // cache outputs
    setCache_(outputRows, lockWaitTime, cacheTimeout);
  } catch (e) {
    Logger.log('Timed out: Restarting! ' + e.toString());
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
  return outputRows;
}


// ===========================================================================================================
// Set the values from cache
function setCache_(outputRows, lockWaitTime, cacheTimeout) {
  try{
    var cache = CacheService.getScriptCache();
    var lock = LockService.getScriptLock();
    
    lock.waitLock(lockWaitTime);
    cache.put(cacheOutputs, JSON.stringify(outputRows), cacheTimeout);
    lock.releaseLock();
  } catch (e) {
    Logger.log('Timed out: Restarting! ' + e.toString());
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
}


// ===========================================================================================================
// Get the values in cache
function getCache_(lockWaitTime) {
  try{
    var outputRows = [];
    var cache = CacheService.getScriptCache();
    var lock = LockService.getScriptLock();
    
    lock.waitLock(lockWaitTime);
    outputRows =  JSON.parse(cache.get(cacheOutputs));
    if (outputRows === undefined || outputRows === null ||
        outputRows[0] === undefined || outputRows[0] === null) {
      outputRows = JSON.parse(cache.get(cacheOutputs));
    }
    lock.releaseLock();
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
  return outputRows;
}


// ===========================================================================================================
// Write outputs to the selected spreadsheet
function writeOutputs_(sheet, outputRows, appendToSheet) {
  try{
    var range, rowStart, indexStart, indexEnd = null;
    var headerRow = ["Full Path", "Name", "Type", "Date", "URL", "Last Updated", "Description", "Size",
                     "Owner", "Sharing Permission", "Sharing Access"]; //, "Thumbnail"];
    SpreadsheetApp.getActiveSpreadsheet().toast('Writing outputs...', 'Status', -1);
    
    if (sheet !== null && outputRows.length > 0) {
      if (appendToSheet === false) {
        sheet.getRange('A3:L').clear();
        sheet.appendRow(headerRow);
        rowStart = 4;
      } else {
        rowStart = getRowsFilled_(sheet, "A1:A") + 1;
      }
      
      indexStart = 0;
      indexEnd = Math.min(writeBatchSize, outputRows.length);
      
      while (indexStart < outputRows.length) {
        range = sheet.getRange(rowStart + indexStart, 1, indexEnd - indexStart, outputRows[0].length);
        range.setValues(outputRows.slice(indexStart, indexEnd));
        a = outputRows.slice(indexStart, indexEnd);
        
        indexStart = indexEnd;
        indexEnd =  Math.min(indexStart + writeBatchSize, outputRows.length);
      }
      
      range = sheet.getRange(getRowsFilled_(sheet, "A1:A") + 1, 1, 1, 1);
      range.setValues([["End of List!"]]);
    }
    
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Timed out!', 'Status', -1);
  }
}


// ===========================================================================================================
// Get number of rows filled in the selected spreadsheet
function getRowsFilled_(sheet, selectedRange) {
  var selectedMatrix = sheet.getRange(selectedRange).getValues();
  return selectedMatrix.filter(String).length;
}


// ===========================================================================================================
// Delete the global cache
function deleteCache_() {
  try{
    var cache = CacheService.getScriptCache();
    var lock = LockService.getScriptLock();
    
    lock.waitLock(this.lockWaitTime);
    cache = CacheService.getScriptCache();
    cache.remove(cacheOutputs);
    lock.releaseLock();
  } catch (e) {
    Logger.log('Failed to delete cache! ' + e.toString());
    SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete cache! Try again in a few minutes.');
  }
}


// ===========================================================================================================
// Delete triggers
function deleteTriggers_() {
  var triggers = ScriptApp.getProjectTriggers();
  try{
    for (var i = 0; i < triggers.length; i++) {
      if (triggers[i].getHandlerFunction() === "run") {
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
  } catch (e) {
    Logger.log('Failed to delete triggers! ' + e.toString());
    SpreadsheetApp.getActiveSpreadsheet().toast('Failed to delete triggers! Try again in a few minutes.');
  }
}


// ===========================================================================================================
// Set kill flag
function setKillFlag_(state, cacheTimeout) {
  var lock = LockService.getScriptLock();
  try{
    lock.waitLock(this.lockWaitTime);
    cache = CacheService.getScriptCache();
    cache.put(cacheKillFlag, state, cacheTimeout);
    lock.releaseLock();
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
  }
}


// ===========================================================================================================
// Get kill flag
function getKillFlag_() {
  killFlag = false;
  try {
    cache = CacheService.getScriptCache();
    //lock.waitLock(this.lockWaitTime);
    killFlag = cache.get(cacheKillFlag) === 'true';
    //lock.releaseLock();
  } catch (e) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Failed to set kill flag! Try again in a few minutes.');
  }
  return killFlag;
}