Rincian Data File

Wadah penyimpanan online Google Drive sangat membantu dalam menyimpan data digital sehingga pemiliknya dapat mengakses dimanapun asal terhubung dengan koneksi internet. Lewat Google Drive, file yang tersimpan bisa diorganisir dengan mudah menggunakan tools yang serbaguna.

Misalnya mengambil rincian atau detail data (Name, URL, Type, Date, Size, Owner) pada semua file yang terdapat dalam satu folder. Dengan begitu pengguna tidak perlu repot-repot mengecek file satu persatu hanya untuk mengetahui link atau rincian lainnya dari file tersebut. Seperti yang SyamSalabim demokan di bawah ini untuk sebuah folder anime.

Google Drive
Cara melakukannya Agan bisa memanfaatkan salah satu fitur yang disediakan Google Drive yaitu Google Sheets atau Spreadsheets. Pada dokumen Spreadsheets, terdapat fungsi Script Editor. Dengan alat itu, Agan bisa menciptakan aplikasi untuk mengelola data-data yang terdapat pada Google Drive. Salah satunya aplikasi Ambil Data yang dijelaskan pada tutorial berikut.

TUTORIAL

1. Buka akun Google Drive dan tentukan folder yang berisi semua file untuk ditampilkan rincian datanya.

Google Drive
2. Klik Kanan dalam folder tersebut dan pilih Google Sheets > Blank spreadsheet.

Google Drive
3. Setelah dokumen Spreadheets terbuka, ganti nama dokumennya jadi "AMBIL DATA". Lalu pada bagian menu pilih Tools > Script editor.

Google Drive
4. Halaman Google Script akan muncul. Klik pada judul dan ganti juga menjadi "AMBIL DATA" untuk memperjelas nama aplikasi.

Google Drive
5. Kemudian hapus kode function yang ada dan ganti dengan script di bawah berikut.

Google Script 
/*===== AMBIL DATA BY SYAMSALABIM =====*/

// Configurable variables
var folderId = '############################'; // 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.getUrl(),
        childFolder.getLastUpdated(),
        childFolder.getDescription(),
        childFolder.getSize(),
        childFolder.getOwner().getEmail(),
        childFolder.getSharingPermission(),
        childFolder.getSharingAccess()
        //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFolder.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
      ]);
      
      // 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.getUrl(),
        childFile.getLastUpdated(),
        childFile.getDescription(),
        childFile.getSize(),
        childFile.getOwner().getEmail(),
        childFile.getSharingPermission(),
        childFile.getSharingAccess()
        //, '=HYPERLINK("' + childFile.getUrl() + '", IMAGE("' + Drive.Files.get(childFile.getId()).thumbnailLink + '",1))' //The 'Drive service' is a G-Suite service (commercial service)
      ]);
    }

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

// ===========================================================================================================
// Get 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);
  }
}

// ===========================================================================================================
// Set 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.clear();
        sheet.appendRow(headerRow);
        rowStart = 2;
      } 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, headerRow.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;
}

6. Perhatikan kode yang Syams tandai merah, nantinya kode tersebut wajib diisi dengan ID Folder yang ingin ditampilkan datanya.

Google Drive
7. Kembali pada folder sebelumnya, copy paste ID yang terdapat pada URL folder. Pasang dalam kode di Google Script.

Google Drive
8. Jika sudah, klik Run > Run function > myFunction yang berada di bagian menu.

Google Drive
9. Akan muncul kotak Autorization required untuk mengijinkan aplikasi AMBIL DATA boleh mengakses file Google Drive. Klik saja Review Permissions.

Google Drive
10. Lalu muncul jendela Google untuk memastikan akun Google yang ingin menggunakan aplikasi AMBIL DATA, pilih akunnya dan klik Allow.

Google Drive
11. Langkah terakhir, kembali pada menu dan klik Run > Run function > Run untuk mengeksekusi aplikasi sekali lagi.

Google Drive
12. Sekarang Agan bisa beralih ke dokumen Google Shets sebelumnya, kalau script aplikasinya berjalan lancar maka rincian data semua file pada folder yang dipilih akan tampil dengan rapi.

Google Drive

Demikian Cara Menampilkan Rincian Data File Pada Folder di Google Drive menggunakan Google Sheets dan Google Script. Hal ini sangat berguna sehingga pengguna bisa mengetahui detail file digital dengan jumlah banyak yang tersimpan online dan mungkin ingin dibagikan ke publik, misalnya untuk memudahkan penyalinan URL file yang mau dijadikan konten unduhan pada website tertentu.

Google Drive sendiri merupakan hosting data yang tepat untuk menyimpan koleksi file sehingga bisa didownload oleh orang yang berselancar di dunia maya. Selain aman karena servernya langsung dari Google, kapasitasnya pun bisa tanpa batas dengan memakai Google Drive Unlimited.