jnothman · May 14, 2020 11:54 · May 14, 2020 · May 14, 2020 · May 14, 2020
diff --git a/populateSheetWithFolderListing.gs b/populateSheetWithFolderListing.gs
@@ -6,10 +6,28 @@ This can be scheduled in Google Apps Script with a specified Drive directory and
 
 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.
 
-Authors: Vijay Raghunath, Joel Nothman
+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.
+
 */
 
 /**

diff --git a/populateSheetWithFolderListing → populateSheetWithFolderListing.gs b/populateSheetWithFolderListing → populateSheetWithFolderListing.gs
diff --git a/populateSheetWithFolderListing b/populateSheetWithFolderListing
@@ -0,0 +1,102 @@
+/* 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.
+
+Copyright 2019 the University of Sydney.
+
+Authors: Vijay Raghunath, Joel Nothman
+*/
+
+/**
+ * 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);
+}
+
No results found