Last active
May 14, 2020 11:54
-
-
Save jnothman/d42b10ba54aaec37217f2abddfef0a95 to your computer and use it in GitHub Desktop.
populateSheetWithFolderListing.gs
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* 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); | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment