populateSheetWithFolderListing.gs

populateSheetWithFolderListing.gs

/* Populate a Google Sheets worksheet with a file listing from Google Drive

Useful to enable filename -> URL lookup in Google Sheets

This can be scheduled in Google Apps Script with a specified Drive directory and Google Sheets spreadsheet.

Developed by the Sydney Informatics Hub, a Core Research of the University of Sydney.
Please acknowledge our support when using this tool in your research.
Authors: Vijay Raghunath, Joel Nothman.

Copyright 2019 the University of Sydney.

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

*/

/**
 * Removes duplicate rows from the current sheet.
 * Code Acknowledgement - https://developers.google.com/apps-script/articles/removing_duplicates
 */
function removeDuplicates(sheet) {
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      // HACK to ensure initial ' is retained
      row[0] = "'" + row[0];
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

/**
 * To create header for the audio repo sheet.
 */
function _createHeaders(activeSpreadsheet) {
  
  var sheet1 = activeSpreadsheet.getSheets()[1];
  Logger.log(sheet1.getName()); 
  
  // Freezes the first row
  sheet1.setFrozenRows(1);
 
  // Set the values we want for headers
  var values = [["Audio File", "Drive Link"]];
 
  // Set the range of cells
  var range = sheet1.getRange("A1:B1");
 
  // Call the setValues method on range and pass in our values
  range.setValues(values);
}

/* add all audio file names and their link location to first column of the sheet */
/**
 * Add audio files and their drive path to the repo.
 */
function _addFilesToSheet(drive_path_of_audio_files, audio_repo_sheet) {
  var count = 0;
  var audio_files = DriveApp.getFolderById(drive_path_of_audio_files).searchFiles('');
  while (audio_files.hasNext()) {
    var file = audio_files.next();
    audio_repo_sheet.appendRow(["'" + file.getName(), "https://drive.google.com/open?id=" + file.getId()]);
    count++;
  }
  return count;
}

function populateSheetWithFolderListing(folder_id, workbook_id, sheet_name) {
  // Get google spread sheet by name
  var activeSpreadsheet = SpreadsheetApp.openById(workbook_id);
  Logger.log(activeSpreadsheet.getName());
  
  // Add a sheet called 'audio files repo' to the sheet, if it does not exists
  var audiorepo = activeSpreadsheet.getSheetByName(sheet_name);
  if (!audiorepo) {
    activeSpreadsheet.insertSheet(sheet_name);
    _createHeaders(activeSpreadsheet)
    audiorepo = activeSpreadsheet.getSheetByName(sheet_name);
  } else {
    Logger.log('the sheet you are after already exists for good things to happen');
  }
  
  var lastRow = audiorepo.getLastRow()
  
  nFiles = _addFilesToSheet(folder_id, audiorepo)
  
  // remove non-added rows:
  audiorepo.deleteRows(2, lastRow - 1)
  
  //removeDuplicates/(audiorepo)
  
  Logger.log('Number of files is ' + nFiles);
}