Skip to content

Instantly share code, notes, and snippets.

@phillypb

phillypb/Copy or move file into folder Tool.gs

Created August 15, 2024 13:25
Show Gist options
  • Save phillypb/ebcd53a9b934a8a0b633d0e3ed320698 to your computer and use it in GitHub Desktop.
Save phillypb/ebcd53a9b934a8a0b633d0e3ed320698 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Copy or move file into folder Tool.gs
/**
* A number of global variables that are re-used throughout this script.
*/
// 5 minute maximum runtime
var maxRuntime = 5 * 60 * 1000;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var timeZone = ss.getSpreadsheetTimeZone();
var logSheet = ss.getSheetByName('Log');
var statusColumnNo = 6;
/**
* @OnlyCurrentDoc
*
* Creates Menu item.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Admin')
.addItem('Select a folder of files', 'pickerPopup')
.addItem('Select destination folder', 'destPickerPopup')
.addSeparator()
.addItem('Get file names', 'getFileNames')
.addItem('Get folder names', 'getFolderNames')
.addSeparator()
.addItem('Run the tool', 'runTool')
.addToUi();
};
function getFileID(id) {
pickerFileId(id);
};
function getDestFolderID(id) {
destPickerFolderId(id);
};
function getFileNames() {
fileNames();
};
function getFolderNames() {
folderNames();
};
function runTool() {
main();
};
/**
* It performs all of the functions for the Copy or move file into folder system.
*/
function main() {
try {
logEvent("Starting 'main' Function.");
// get start time so can manage duration of script and avoid timeout
var startTime = new Date().getTime();
// create variable for capturing results
var errorFlag = false;
var metricsSent = false;
// get Files Sheet
var filesSheet = ss.getSheetByName('Files');
logEvent("Successfully got the 'Files' Sheet.");
// get Column and call Function to retrieve last row number, then subtract 2 for Headings
var columnToCheck = filesSheet.getRange("A:A").getValues();
var lastRowNo = getLastRowSpecial(columnToCheck) - 2;
// check no error with previous Function
if (lastRowNo !== false) {
// proceed as no error
// get the data in the Files sheet
var lastCol = filesSheet.getLastColumn() - 1;
var data = filesSheet.getRange(3, 1, lastRowNo, lastCol).getValues();
var dataLength = data.length;
// create counter for number of rows actioned
var rowsActionedCounter = 0;
// loop through spreadsheet data ********************************
for (var i = 0; i < dataLength; i++) {
// create flag to determine if a file has been actioned
var aFileHasBeenActioned = false;
// log row number
var rowNo = i + 3;
logEvent("Current row number is: " + rowNo);
// get columns to see if skipping row
var fileIDUrl = data[i][0];
var folderIDUrl = data[i][2];
var operationChoice = data[i][4];
var status = data[i][5];
if ((fileIDUrl != "") && (folderIDUrl != "") && (operationChoice != "") && (status == "")) {
// proceed with current row
// check file reachable for user running script ************************
try {
// get the file
var fileID = fileIDUrl.match(/[-\w]{19,}/);
var file = DriveApp.getFileById(fileID);
var fileName = file.getName();
logEvent("Successfully got file: " + fileName);
} catch (error) {
logEvent("Unable to get Drive file: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Google Drive file error";
var popupMessage = "Please check you have provided the correct file ID/URL.<br/><br/>Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop
break;
};
// check file reachable for user running script ************************
// check folder reachable for user running script ************************
try {
// get the folder
var folderID = folderIDUrl.match(/[-\w]{19,}/);
var destFolder = DriveApp.getFolderById(folderID);
var folderName = destFolder.getName();
logEvent("Successfully got folder: " + folderName);
} catch (error) {
logEvent("Unable to get Drive folder: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Google Drive folder error";
var popupMessage = "Please check you have provided the correct folder ID/URL.<br/><br/>Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop
break;
};
// check folder reachable for user running script ************************
// check Operation value
if (operationChoice == "Copy") {
logEvent("Operation is Copy.");
// make a copy of the file and get its Url
var newFileUrl = file.makeCopy(fileName, destFolder).getUrl();
logEvent("Created new file with URL: " + newFileUrl);
// update Flag so know file has been actioned
aFileHasBeenActioned = true;
} else if (operationChoice == "Move") {
logEvent("Operation is Move.");
// move file
file.moveTo(destFolder);
logEvent("File has been moved.");
// update Flag so know file has been actioned
aFileHasBeenActioned = true;
} else {
logEvent("Operation is Unknown.");
// set error flag and break out of loop
errorFlag = true;
break;
};
if (aFileHasBeenActioned === true) {
// update Status Column
filesSheet.getRange(rowNo, statusColumnNo).setValue("Completed");
// increment rows actioned counter by 1
rowsActionedCounter++;
} else {
// no folder has been updated
};
} else {
logEvent("Skipping row.");
};
// perform runtime check ************************************************
// get current time
var endTime = new Date().getTime();
// find elapsed time by subtracting from start time
var elapsedTime = endTime - startTime;
// check against maximum runtime
var timeLimitExceeded = elapsedTime >= maxRuntime;
// check status
if (timeLimitExceeded) {
// runtime has been met/exceeded
logEvent('Runtime has been met/exceeded.');
// run Function to launch HTML popup
var popupTitle = "Time limit reached";
var popupMessage = "The maximum Google runtime has been reached but there are still files to action. To continue please close this dialogue box and select to run the tool again from the Menu.";
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop to prevent script from continuing
break;
} else {
// runtime has not been met/exceeded, script can continue looping through rows
};
// perform runtime check ************************************************
};
// loop through spreadsheet data ********************************
} else {
// error occured, do nothing as popup already displayed
errorFlag = true;
};
// determine final popup message
if (errorFlag === false) {
// no errors have occured
// run Function to launch HTML popup
var popupTitle = "Successfully completed";
var popupMessage = rowsActionedCounter + " files have been actioned." + "<br/><br/>" + "<a href='https://www.pbainbridge.co.uk/' target='_blank'>Developed by The Gift of Script.</a>";
htmlPopup(popupTitle, popupMessage);
} else {
// errors have occured, do nothing as popup box already being displayed
};
logEvent("Completed 'main' Function.");
} catch (error) {
logEvent("Error with 'main' Function: " + error.stack);
// convert 'catch' message to string and perform JavaScript 'match' for keywords
var errorString = error.toString();
var matching = errorString.match(/Access denied: DriveApp/gi);
// test if 'match' is true to display specific message
if (matching) {
// run Function to launch HTML popup
var popupTitle = "Error with 'main' Function";
var popupMessage = "You do not appear to have permission to move the file.<br/><br/>Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
} else {
// run Function to launch HTML popup
var popupTitle = "Error with 'main' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
};
};
};
/**
* Function to get last row number from specified column.
* Used as future additions to the Google Sheet may include tickboxes which affect 'getRange()'.
*/
function getLastRowSpecial(columnToCheck) {
try {
logEvent("Started 'getLastRowSpecial' Function.");
// reset variables before using in loop below
var rowNum = 0;
var blank = false;
// loop through the array and check the value in the cell ****************************
for (var row = 0; row < columnToCheck.length; row++) {
// check if cell value is empty AND 'blank' variable is not false
var rowValue = columnToCheck[row][0];
if ((rowValue == "") && (!blank)) {
// if true then set row number variable to value of loop and flag it's true
rowNum = row;
blank = true;
}
else if (rowValue != "") {
// if the cell value is not empty (contains data) then flag it's not blank
blank = false;
}
}
// loop through the array and check the value in the cell ****************************
logEvent("Last Row is: " + rowNum);
logEvent("Completed 'getLastRowSpecial' Function.");
return rowNum;
} catch (error) {
logEvent("Error with 'getLastRowSpecial' Function: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Error with 'getLastRowSpecial' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
// return error flag
return false;
};
};
/**
* Display a dialog box to prompt user if they wish to open Google Picker.
*/
function pickerPopup() {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'Open Folder Picker',
'Select this option if you have a single Google Drive folder full of files (no sub-folders). Only click once to select your folder (do not double-click it). It make take up to 10 seconds to load.',
ui.ButtonSet.YES_NO
);
// Process the user's response.
if (result == ui.Button.YES) {
// User clicked "Yes".
showPicker();
} else {
// User clicked "No" or X in the title bar.
};
};
function showPicker() {
var html = HtmlService.createHtmlOutputFromFile('4) Picker.html')
.setWidth(650)
.setHeight(500)
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi().showModalDialog(html, 'Select Folder');
};
function getOAuthToken() {
DriveApp.getRootFolder();
return ScriptApp.getOAuthToken();
};
<!DOCTYPE html>
<html>
<head>
<!-- Add the standard Google Style Sheet. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script type="text/javascript">
var DIALOG_DIMENSIONS = {
width: 650,
height: 500
};
var pickerApiLoaded = false;
function onApiLoad() {
gapi.load('picker', {
'callback': function() {
pickerApiLoaded = true;
}
});
google.script.run.withSuccessHandler(createPicker).withFailureHandler(showError).getOAuthToken();
}
function createPicker(token) {
if (pickerApiLoaded && token) {
var docsView = new google.picker.DocsView()
.setParent('root')
.setIncludeFolders(true)
.setMode(google.picker.DocsViewMode.LIST)
.setMimeTypes('application/vnd.google-apps.folder')
.setSelectFolderEnabled(true);
var picker = new google.picker.PickerBuilder()
.addView(docsView)
.hideTitleBar()
.setSize(DIALOG_DIMENSIONS.width - 2, DIALOG_DIMENSIONS.height - 2)
.setOAuthToken(token)
.setCallback(pickerCallback)
.setOrigin("https://docs.google.com")
.build();
picker.setVisible(true);
} else {
showError('Unable to load the folder picker.');
};
};
/*
Function used as SuccessHandler for 'pickerCallback' so will only close dialogue
box if that codes runs correctly.
*/
function folderInserted(){
google.script.host.close();
}
/**
* A callback function that extracts the chosen document's metadata from the
* response object. For details on the response object, see
* https://developers.google.com/picker/docs/result
*
* @param {object} data The response object.
*/
function pickerCallback(data) {
var action = data[google.picker.Response.ACTION];
if (action == google.picker.Action.PICKED) {
var doc = data[google.picker.Response.DOCUMENTS][0];
var id = doc[google.picker.Document.ID];
/*
Runs Apps Script Function 'insertFolderURL' and passes in item Id from Google Picker.
A failure to run will trigger below 'showError' Function.
A success run will trigger above 'folderInserted' Function and close dialogue box.
*/
google.script.run.withSuccessHandler(folderInserted).withFailureHandler(showError).getFileID(id);
} else if (action == google.picker.Action.CANCEL) {
google.script.host.close();
};
}
function showError(message) {
document.getElementById('result').innerHTML = 'Error: ' + message;
}
</script>
</head>
<body>
<div>
<p id='result'></p>
</div>
<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
</body>
</html>
/**
* Take Google Drive Folder ID passed from Google Picker.
*/
function pickerFileId(id) {
try {
logEvent("Starting 'pickerFileId' Function.");
logEvent("Google Drive folder ID from Picker is: " + id);
// create emtpy array for pushing file data into to append to Sheet at very end
var fileDetailsArray = [];
// create variable to use as Counter for index of files
var fileCounter = 0;
// get Google Drive folder of files
var sourceFolder = DriveApp.getFolderById(id);
// get files inside of folder
var files = sourceFolder.getFiles();
while (files.hasNext()) {
var childFile = files.next();
// create clickable file name link
var fileName = childFile.getName();
var fileUrl = childFile.getUrl();
var hyperlink = '=HYPERLINK("' + fileUrl + '","' + fileName + '")';
// get file ID
var fileId = childFile.getId();
// push data into beginning of array for later adding back into Google Sheet
fileDetailsArray.unshift([fileId, hyperlink]);
// increment file Counter by 1
fileCounter++;
};
logEvent("Number of files searched through is: " + fileCounter);
// get Files Sheet
var filesSheet = ss.getSheetByName('Files');
logEvent("Successfully got the 'Files' Sheet.");
// get Column and call Function to retrieve last row number, then subtract 2 for Headings
var columnToCheck = filesSheet.getRange("A:A").getValues();
var lastRowNo = getLastRowSpecial(columnToCheck);
// check no error with previous Function
if (lastRowNo !== false) {
// now append row data to Google Sheet in one go
var arrayLength = fileDetailsArray.length;
var arrayWidth = fileDetailsArray[0].length;
filesSheet.getRange(lastRowNo + 1, 1, arrayLength, arrayWidth).setValues(fileDetailsArray);
logEvent("Completed 'pickerFileId' Function.");
} else {
// error occured, do nothing as popup already displayed
};
} catch (error) {
logEvent("Error with 'pickerFileId' Function: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Error with 'pickerFileId' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
};
};
/**
* Display a dialog box to prompt user if they wish to open Google Picker.
*/
function destPickerPopup() {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'Open Destination Folder Picker',
'Select this option to pick a folder as the destination for all files that do not currently have one. Only click once to select your folder (do not double-click it). It make take up to 10 seconds to load.',
ui.ButtonSet.YES_NO
);
// Process the user's response.
if (result == ui.Button.YES) {
// User clicked "Yes".
showDestPicker();
} else {
// User clicked "No" or X in the title bar.
};
};
function showDestPicker() {
var html = HtmlService.createHtmlOutputFromFile('7) destPicker.html')
.setWidth(650)
.setHeight(500)
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi().showModalDialog(html, 'Select Folder');
};
<!DOCTYPE html>
<html>
<head>
<!-- Add the standard Google Style Sheet. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script type="text/javascript">
var DIALOG_DIMENSIONS = {
width: 650,
height: 500
};
var pickerApiLoaded = false;
function onApiLoad() {
gapi.load('picker', {
'callback': function() {
pickerApiLoaded = true;
}
});
google.script.run.withSuccessHandler(createPicker).withFailureHandler(showError).getOAuthToken();
}
function createPicker(token) {
if (pickerApiLoaded && token) {
var docsView = new google.picker.DocsView()
.setParent('root')
.setIncludeFolders(true)
.setMode(google.picker.DocsViewMode.LIST)
.setMimeTypes('application/vnd.google-apps.folder')
.setSelectFolderEnabled(true);
var picker = new google.picker.PickerBuilder()
.addView(docsView)
.hideTitleBar()
.setSize(DIALOG_DIMENSIONS.width - 2, DIALOG_DIMENSIONS.height - 2)
.setOAuthToken(token)
.setCallback(pickerCallback)
.setOrigin("https://docs.google.com")
.build();
picker.setVisible(true);
} else {
showError('Unable to load the folder picker.');
};
};
/*
Function used as SuccessHandler for 'pickerCallback' so will only close dialogue
box if that codes runs correctly.
*/
function folderInserted(){
google.script.host.close();
}
/**
* A callback function that extracts the chosen document's metadata from the
* response object. For details on the response object, see
* https://developers.google.com/picker/docs/result
*
* @param {object} data The response object.
*/
function pickerCallback(data) {
var action = data[google.picker.Response.ACTION];
if (action == google.picker.Action.PICKED) {
var doc = data[google.picker.Response.DOCUMENTS][0];
var id = doc[google.picker.Document.ID];
/*
Runs Apps Script Function 'insertFolderURL' and passes in item Id from Google Picker.
A failure to run will trigger below 'showError' Function.
A success run will trigger above 'folderInserted' Function and close dialogue box.
*/
google.script.run.withSuccessHandler(folderInserted).withFailureHandler(showError).getDestFolderID(id);
}
else if (action == google.picker.Action.CANCEL) {
google.script.host.close();
}
}
function showError(message) {
document.getElementById('result').innerHTML = 'Error: ' + message;
}
</script>
</head>
<body>
<div>
<p id='result'></p>
</div>
<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
</body>
</html>
/**
* Take Google Drive Folder ID passed from Google Picker.
* Use this as the destination folder for each File ID/URL missing one.
*/
function destPickerFolderId(id) {
try {
logEvent("Starting 'destPickerFolderId' Function.");
logEvent("Google Drive folder ID from Picker is: " + id);
// get start time so can manage duration of script and avoid timeout
var startTime = new Date().getTime();
// create variable for capturing results
var errorFlag = false;
var rowsActionedCounter = 0;
// get Google Drive folder
var destinationFolder = DriveApp.getFolderById(id);
// create clickable folder name link
var folderName = destinationFolder.getName();
var folderUrl = destinationFolder.getUrl();
var hyperlink = '=HYPERLINK("' + folderUrl + '","' + folderName + '")';
// get Files Sheet
var filesSheet = ss.getSheetByName('Files');
logEvent("Successfully got the 'Files' Sheet.");
// get Column and call Function to retrieve last row number, then subtract 2 for Headings
var columnToCheck = filesSheet.getRange("A:A").getValues();
var lastRowNo = getLastRowSpecial(columnToCheck) - 2;
// check no error with previous Function
if (lastRowNo !== false) {
// proceed as no error
// get the data in the Folders sheet
var lastCol = filesSheet.getLastColumn() - 1;
var data = filesSheet.getRange(3, 1, lastRowNo, lastCol).getValues();
var dataLength = data.length;
// loop through spreadsheet data ********************************
for (var i = 0; i < dataLength; i++) {
// log row number
var rowNo = i + 3;
logEvent("Current row number is: " + rowNo);
// get value of 'File ID or URL' and 'Folder ID or URL' and 'Status' columns to see if skipping row
var fileIDUrl = data[i][0];
var folderIDUrl = data[i][2];
var status = data[i][5];
logEvent("fileIDUrl column is: " + fileIDUrl);
logEvent("folderIDUrl column is: " + folderIDUrl);
logEvent("status column is: " + status);
if ((fileIDUrl != "") && (folderIDUrl == "") && (status == "")) {
// proceed with current row
// insert Folder ID and Name
filesSheet.getRange(rowNo, 3, 1, 2).setValues([[id, hyperlink]]);
// increment rows actioned counter by 1
rowsActionedCounter++;
} else {
logEvent("Skipping row.");
};
// perform runtime check ************************************************
// get current time
var endTime = new Date().getTime();
// find elapsed time by subtracting from start time
var elapsedTime = endTime - startTime;
// check against maximum runtime
var timeLimitExceeded = elapsedTime >= maxRuntime;
// check status
if (timeLimitExceeded) {
// runtime has been met/exceeded
logEvent('Runtime has been met/exceeded.');
// run Function to launch HTML popup
var popupTitle = "Time limit reached";
var popupMessage = "The maximum Google runtime has been reached but there are still folders to action. To continue please close this dialogue box and select to run the tool again from the Menu.";
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop to prevent script from continuing
break;
} else {
// runtime has not been met/exceeded, script can continue looping through rows
};
// perform runtime check ************************************************
};
// loop through spreadsheet data ********************************
} else {
// error occured, do nothing as popup already displayed
errorFlag = true;
};
// determine final popup message
if (errorFlag === false) {
// no errors have occured
// run Function to launch HTML popup
var popupTitle = "Successfully completed";
var popupMessage = rowsActionedCounter + " folder IDs have been added." + "<br/><br/>" + "<a href='https://www.pbainbridge.co.uk/' target='_blank'>Developed by The Gift of Script.</a>";
htmlPopup(popupTitle, popupMessage);
} else {
// errors have occured, do nothing as popup box already being displayed
};
logEvent("Completed 'destPickerFolderId' Function.");
} catch (error) {
logEvent("Error with 'destPickerFolderId' Function: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Error with 'destPickerFolderId' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
};
};
/**
* Loop through Google Sheet, collate file names and input back into Sheet.
*/
function fileNames() {
try {
logEvent("Starting 'fileNames' Function.");
// create variable for capturing errors
var errorFlag = false;
// get Files Sheet
var filesSheet = ss.getSheetByName('Files');
logEvent("Successfully got the 'Files' Sheet.");
// get Column and call Function to retrieve last row number, then subtract 2 for Headings
var columnToCheck = filesSheet.getRange("A:A").getValues();
var lastRowNo = getLastRowSpecial(columnToCheck) - 2;
// check no error with previous Function
if (lastRowNo !== false) {
// get the data in the Files sheet
var data = filesSheet.getRange(3, 1, lastRowNo, 2).getValues();
var dataLength = data.length;
// create variable to use as Counter for index of files
var fileCounter = 0;
// loop through spreadsheet data ********************************
for (var i = 0; i < dataLength; i++) {
// get value of 'File ID or URL' and 'File Name' column to see if skipping row
var fileIDUrl = data[i][0];
var existingFileName = data[i][1];
if ((fileIDUrl != "") && (existingFileName == "")) {
// proceed with current row
var rowNo = i + 3;
// check file reachable for user running script ************************
try {
// get the file
var fileID = fileIDUrl.match(/[-\w]{25,}/);
var file = DriveApp.getFileById(fileID);
var fileName = file.getName();
var fileUrl = file.getUrl();
var hyperlink = '=HYPERLINK("' + fileUrl + '","' + fileName + '")';
} catch (error) {
logEvent("Unable to get Drive file: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Google Drive file error";
var popupMessage = "Please check you have provided the correct file ID/URL.<br/><br/>Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop
break;
};
// check file reachable for user running script ************************
// append file name to Google Sheet
filesSheet.getRange(rowNo, 2).setValue(hyperlink);
// increment file Counter by 1
fileCounter++;
} else {
// skip row as blank
};
};
// determine final popup message
if (errorFlag === false) {
// no errors have occured
// run Function to launch HTML popup
var popupTitle = "Successfully completed";
var popupMessage = fileCounter + " file names have been collated." + "<br/><br/>" + "<a href='https://www.pbainbridge.co.uk/' target='_blank'>Developed by The Gift of Script.</a>";
htmlPopup(popupTitle, popupMessage);
} else {
// errors have occured, do nothing as popup box already being displayed
};
logEvent("Completed 'fileNames' Function.");
} else {
// error occured, do nothing as popup already displayed
};
} catch (error) {
logEvent("Error with 'fileNames' Function: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Error with 'fileNames' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
};
};
/**
* Loop through Google Sheet, collate folder names and input back into Sheet.
*/
function folderNames() {
try {
logEvent("Starting 'folderNames' Function.");
// create variable for capturing errors
var errorFlag = false;
// get Files Sheet
var filesSheet = ss.getSheetByName('Files');
logEvent("Successfully got the 'Files' Sheet.");
// get Column and call Function to retrieve last row number, then subtract 2 for Headings
var columnToCheck = filesSheet.getRange("A:A").getValues();
var lastRowNo = getLastRowSpecial(columnToCheck) - 2;
// check no error with previous Function
if (lastRowNo !== false) {
// get the data in the Files sheet
var data = filesSheet.getRange(3, 3, lastRowNo, 2).getValues();
var dataLength = data.length;
// create variable to use as Counter for index of folders
var folderCounter = 0;
// loop through spreadsheet data ********************************
for (var i = 0; i < dataLength; i++) {
// get value of 'Folder ID or URL' and 'Folder Name' column to see if skipping row
var folderIDUrl = data[i][0];
var existingFolderName = data[i][1];
if ((folderIDUrl != "") && (existingFolderName == "")) {
// proceed with current row
var rowNo = i + 3;
// check folder reachable for user running script ************************
try {
// get the folder
var folderID = folderIDUrl.match(/[-\w]{19,}/);
var folder = DriveApp.getFolderById(folderID);
var folderName = folder.getName();
// a Shared drive root will return 'Drive' as its name, so use a different method
if (folderName == "Drive") {
// use the Drive API to get the Shared drive
var sharedDrive = Drive.Drives.get(folderID);
// get the name of the Shared drive
var folderName = sharedDrive.name;
} else {
// do nothing
};
var folderUrl = folder.getUrl();
var hyperlink = '=HYPERLINK("' + folderUrl + '","' + folderName + '")';
} catch (error) {
logEvent("Unable to get Drive folder: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Google Drive folder error";
var popupMessage = "Please check you have provided the correct folder ID/URL.<br/><br/>Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
errorFlag = true;
// break out of loop
break;
};
// check folder reachable for user running script ************************
// append folder name to Google Sheet
filesSheet.getRange(rowNo, 4).setValue(hyperlink);
// increment folder Counter by 1
folderCounter++;
} else {
// skip row as blank
};
};
// determine final popup message
if (errorFlag === false) {
// no errors have occured
// run Function to launch HTML popup
var popupTitle = "Successfully completed";
var popupMessage = folderCounter + " folder names have been collated." + "<br/><br/>" + "<a href='https://www.pbainbridge.co.uk/' target='_blank'>Developed by The Gift of Script.</a>";
htmlPopup(popupTitle, popupMessage);
} else {
// errors have occured, do nothing as popup box already being displayed
};
logEvent("Completed 'folderNames' Function.");
} else {
// error occured, do nothing as popup already displayed
};
} catch (error) {
logEvent("Error with 'folderNames' Function: " + error.stack);
// run Function to launch HTML popup
var popupTitle = "Error with 'folderNames' Function";
var popupMessage = "Message: " + error.stack;
htmlPopup(popupTitle, popupMessage);
};
};
/**
* Function to output messages to the 'Log' sheet.
* Can be called anywhere else in script.
*/
function logEvent(action) {
// get the user running the script
var theUser = Session.getActiveUser().getEmail();
// create and format a timestamp
var dateTime = new Date();
var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yyyy HH:mm:ss");
// create array of data for pasting into log sheet
var logData = [niceDateTime, theUser, action];
// append details into next row of log sheet
logSheet.appendRow(logData);
};
/**
* Display a modal dialog box with custom HtmlService content.
* Does not suspend the script.
*/
function htmlPopup(popupTitle, popupMessage) {
var htmlOutput = HtmlService
.createHtmlOutput(popupMessage)
.setWidth(380)
.setHeight(180);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment