Created
April 27, 2022 13:53
-
-
Save cwlind/28bef703e0f3ecfd8b999ff5dd2d1ad7 to your computer and use it in GitHub Desktop.
This file contains 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
/* | |
Global Variables that may need to be tweaked and are easier to access by placing here. | |
*/ | |
// Maximum runtime of script in minutes to prevent timeout (5 minutes 30 seconds) | |
var maxRuntime = 5.5 * 60 * 1000; | |
/* | |
This Function gets the necessary data from the spreadsheet. | |
*/ | |
function getSpreadsheetData() { | |
try { | |
// Log information | |
logEvent('Script has started'); | |
// get spreadsheet | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
// create toast popup to inform user | |
ss.toast('Task has now started ...', 'Start'); | |
// get 'Welcome' sheet | |
var welcomeSheet = ss.getSheetByName('Welcome'); | |
// get folder Id containing Docs | |
var docsFolderId = welcomeSheet.getRange(5, 4).getValue().trim(); | |
// get folder Id of destination folder to save PDFs into | |
var pdfFolderId = welcomeSheet.getRange(9, 4).getValue().trim(); | |
// get value for deleting Docs after conversion | |
var deleteDocs = welcomeSheet.getRange(13, 4).getValue().trim(); | |
// create name:value pair so array easier to work with | |
var sheetData = { ss: ss, docsFolderId: docsFolderId, pdfFolderId: pdfFolderId, deleteDocs: deleteDocs }; | |
// run Function to convert Docs to PDFs | |
var convertToPdfFlag = convertToPdf(sheetData); | |
if (convertToPdfFlag) { | |
// no errors occurred during process | |
// Log information | |
logEvent('Script has finished without errors'); | |
} | |
else { | |
// there were errors during process, relevant popup will already be displayed | |
// Log information | |
logEvent('Script completed with errors'); | |
} | |
} | |
catch (error) { | |
// there was a problem getting spreadsheet data | |
logEvent("Error with 'getSpreadsheetData' Function: " + error); | |
// create popup to inform user | |
var popupTitle = "'getSpreadsheetData' Function Error"; | |
var popupMessage = "Error message: " + error; | |
htmlPopup(popupTitle, popupMessage); | |
} | |
} | |
/* | |
This Function gets the Google Drive folder containing the files. | |
It iterates through each file and only handles the Google Docs. | |
It creates a PDF version of the Doc and saves this into the given Drive folder. | |
It then optionally deletes the original Google Doc if the user selected this. | |
*/ | |
function convertToPdf(sheetData) { | |
try { | |
// Log information | |
logEvent("Starting 'convertToPdf' Function"); | |
// extract Sheet data from name:value pair array | |
var ss = sheetData['ss']; | |
var docsFolderId = sheetData['docsFolderId']; | |
var pdfFolderId = sheetData['pdfFolderId']; | |
var deleteDocs = sheetData['deleteDocs']; | |
// Log information | |
logEvent("Option to delete original Google Docs is: " + deleteDocs); | |
// get Google Drive folder containing files to convert | |
var docsFolder = DriveApp.getFolderById(docsFolderId); | |
} | |
catch (error) { | |
// error getting Google Drive folder | |
logEvent('Error getting Google Drive folder containing files to convert: ' + error); | |
// create popup to inform user | |
var popupTitle = "Error getting Google Drive folder"; | |
var popupMessage = "Error message: " + error; | |
htmlPopup(popupTitle, popupMessage); | |
// return Flag to Parent Function | |
return false; | |
} | |
try { | |
// proceed IF successfully got Google Drive folder containing files to convert | |
if (docsFolder) { | |
// Log information | |
logEvent("Starting to convert files ..."); | |
// create Counter to log number of converted files | |
var convertedCounter = 0; | |
// run Function to check Script Properties for 'Continuation Token' | |
var continuationToken = getScriptProperties(); | |
// check no error getting Script Properties before proceeding ************************ | |
if (continuationToken !== false) { | |
// get start time so can manage duration of script and avoid timeout | |
var startTime = new Date().getTime(); | |
} | |
else { | |
// error occurred, return false flag to Parent Function | |
return false; | |
} | |
// check no error getting Script Properties before proceeding ************************ | |
// check if 'Continuation Token' exists to determine if continuing conversion | |
if (continuationToken === null) { | |
// no Token exists so proceed as first time running the script: | |
// log message in sheet | |
logEvent('No Continuation Token exists. Converting Docs ...'); | |
// get Google Doc sub-files only | |
// https://developers.google.com/apps-script/reference/base/mime-type | |
var subFiles = docsFolder.getFilesByType(MimeType.GOOGLE_DOCS); | |
} | |
else { | |
// Token does exist in Script Properties | |
// log message in sheet | |
logEvent('Continuation Token does exist. Converting Docs ...'); | |
// continue iterating through Drive files with Token | |
var subFiles = DriveApp.continueFileIterator(continuationToken); | |
} | |
// cycle through each sub-file | |
while (subFiles.hasNext()) { | |
// get sub-file | |
var subFile = subFiles.next(); | |
// get sub-file name | |
var subFileName = subFile.getName(); | |
// create toast popup to inform user | |
ss.toast('File: ' + subFileName, 'Converting'); | |
// create PDF version of Doc **************************************** | |
var blobFile = subFile.getAs('application/pdf'); | |
var pdfVersion = DriveApp.createFile(blobFile); | |
// get Id of PDF version | |
var pdfVersionId = pdfVersion.getId(); | |
// get PDFs destination folder | |
var pdfFolder = DriveApp.getFolderById(pdfFolderId); | |
// get PDF file and move to new location | |
DriveApp.getFileById(pdfVersionId).moveTo(pdfFolder); | |
// create PDF version of Doc **************************************** | |
// increment Counter by 1 to log number of converted files | |
convertedCounter++; | |
// check IF user selected to delete the original Google Doc | |
if (deleteDocs == 'Yes') { | |
// delete original Doc file | |
subFile.setTrashed(true); | |
} | |
else { | |
// user did not select to delete original Doc, so do nothing | |
} | |
// *************************** 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 of runtime | |
if (timeLimitExceeded) { | |
// runtime has been met/exceeded, store 'Continuation Token' so can resume later | |
// log message in sheet | |
logEvent('Runtime has been met/exceeded.'); | |
// get Token from last file | |
var continuationToken = subFiles.getContinuationToken(); | |
/* | |
run Function to add Token to Script Properies for use later. | |
capture in error variable as this is the last step in process anyway, | |
so no matter what will be using 'break' | |
*/ | |
setPropertyFlag = setScriptProperties(continuationToken); | |
if (setPropertyFlag) { | |
// successfully completed | |
// break out of loop to prevent script from continuing | |
break; | |
} | |
else { | |
// problem occurred, return false Flag | |
return false; | |
} | |
} | |
else { | |
// runtime has not been met/exceeded, script can continue looping through files | |
} | |
// *************************** perform runtime check *************************** | |
} | |
// Log information | |
logEvent("Completed converting " + convertedCounter + " Docs in folder"); | |
} | |
else { | |
// do nothing as script will have terminated at this point | |
} | |
// check if there are still files left to convert | |
if (subFiles.hasNext()) { | |
// yes there are files still left to convert | |
// log message in sheet | |
logEvent('Successfully created ' + convertedCounter + ' PDFs.'); | |
// log message in sheet | |
logEvent('There are still Docs to convert.'); | |
// no errors have occurred in the script | |
var popupTitle = 'Runtime reached - ' + convertedCounter + ' PDFs created'; | |
var popupMessage = 'No errors, but there are Docs still to create PDFs for, so you may wish to run this tool again.'; | |
htmlPopup(popupTitle, popupMessage); | |
// log message in sheet | |
logEvent('Script completed without errors.'); | |
// return success Flag | |
return true; | |
} | |
else { | |
// no all files have been converted | |
// delete Script Properties of Token as no longer required | |
deletePropertyFlag = deleteScriptProperties('CONTINUATION_TOKEN'); | |
// check no error deleting Script Property before proceeding *********** | |
if (deletePropertyFlag) { | |
// log message in sheet | |
logEvent('All Docs have been converted.'); | |
// no errors have occurred in the script | |
var popupTitle = 'Tool complete - ' + convertedCounter + ' PDFs created'; | |
var popupMessage = 'No errors. PDFs have been created for all Google Docs.'; | |
htmlPopup(popupTitle, popupMessage); | |
// return success Flag | |
return true; | |
} | |
else { | |
// error deleting Script Property - do nothing as popup will already be displayed | |
} | |
// check no error deleting Script Properties before proceeding *********** | |
} | |
} | |
catch (error) { | |
// there was a problem during PDF conversion | |
logEvent('Error during file iteration/conversion: ' + error); | |
// create popup to inform user | |
var popupTitle = "Error during PDF conversion"; | |
var popupMessage = "Error message: " + error; | |
htmlPopup(popupTitle, popupMessage); | |
return false; | |
} | |
} | |
/* | |
Function to look for 'Continuation Token' in Script Properties. | |
https://developers.google.com/apps-script/guides/properties | |
*/ | |
function getScriptProperties() { | |
try { | |
// log message in sheet | |
logEvent('Getting Script Properties.'); | |
// access Script Properties | |
var scriptProperties = PropertiesService.getScriptProperties(); | |
// look for 'Continuation Token' Property | |
var continuationToken = scriptProperties.getProperty('CONTINUATION_TOKEN'); | |
// return value to Parent Function | |
return continuationToken; | |
} | |
catch (error) { | |
// log error in sheet | |
logEvent('Problem getting Script Properties: ' + error); | |
// display user popup to inform of error | |
var popupTitle = 'Get Script Properties error'; | |
var popupMessage = 'Problem getting Script Properties: ' + error; | |
htmlPopup(popupTitle, popupMessage); | |
// return false to Parent Function | |
return false; | |
} | |
} | |
/* | |
Function to set 'Continuation Token' in Script Properties. | |
https://developers.google.com/apps-script/guides/properties | |
*/ | |
function setScriptProperties(continuationToken) { | |
try { | |
// log message in sheet | |
logEvent('Setting Script Properties.'); | |
// access Script Properties | |
var scriptProperties = PropertiesService.getScriptProperties(); | |
// set 'Continuation Token' Script Property | |
var continuationToken = scriptProperties.setProperty('CONTINUATION_TOKEN', continuationToken); | |
// log message in sheet | |
logEvent('Successfully set Script Properties.'); | |
// return true to Parent Function | |
return true; | |
} | |
catch (error) { | |
// log error in sheet | |
logEvent('Problem setting Script Property: ' + error); | |
// display user popup to inform of error | |
var popupTitle = 'Set Script Property error'; | |
var popupMessage = 'Problem setting Script Property: ' + error; | |
htmlPopup(popupTitle, popupMessage); | |
// return false to Parent Function | |
return false; | |
} | |
} | |
/* | |
Function to delete 'Continuation Token' in Script Properties. | |
https://developers.google.com/apps-script/guides/properties | |
*/ | |
function deleteScriptProperties(property) { | |
try { | |
// log message in sheet | |
logEvent('Deleting Script Properties.'); | |
// access Script Properties | |
var scriptProperties = PropertiesService.getScriptProperties(); | |
// delete 'Continuation Token' Property | |
scriptProperties.deleteProperty(property); | |
// log message in sheet | |
logEvent('Successfully deleted Script Properties.'); | |
// return true to Parent Function | |
return true; | |
} | |
catch (error) { | |
// log error in sheet | |
logEvent('Problem deleting Script Property: ' + error); | |
// display user popup to inform of error | |
var popupTitle = 'Delete Script Property error'; | |
var popupMessage = 'Problem deleting Script Property Token: ' + error; | |
htmlPopup(popupTitle, popupMessage); | |
// return false to Parent Function | |
return false; | |
} | |
} | |
/* | |
Reset all data within Google Sheet to be able to start afresh. | |
*/ | |
function reset() { | |
try { | |
// log that reset Function has been selected | |
logEvent("Initiated 'Reset' Function"); | |
// get Spreadsheet UI | |
var ui = SpreadsheetApp.getUi(); | |
// create Alert popup box | |
var result = ui.alert( | |
'Reset spreadsheet', | |
'Do you wish to remove all inputted information to start again?', | |
ui.ButtonSet.OK_CANCEL | |
); | |
// process user response from Popup Box | |
if (result == ui.Button.OK) { | |
// log selection | |
logEvent("Selected 'OK' to reset spreadsheet"); | |
// get current spreadsheet | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
// get Welcome sheet | |
var welcomeSheet = ss.getSheetByName('Welcome'); | |
// clear source folder ID box | |
welcomeSheet.getRange(5, 4).clearContent(); | |
// clear destination folder ID box | |
welcomeSheet.getRange(9, 4).clearContent(); | |
// clear delete select box | |
welcomeSheet.getRange(13, 4).clearContent(); | |
// delete Script Properties of Token | |
deletePropertyFlag = deleteScriptProperties('CONTINUATION_TOKEN'); | |
// log completion of Function | |
logEvent("Completed 'Reset' Function."); | |
// display alert popup to user to confirm completion | |
ui.alert('Successfully cleared all sheet data.'); | |
} | |
else { | |
// User clicked 'No' or 'X' in title bar so do nothing | |
logEvent("Selected 'CANCEL' to reset spreadsheet"); | |
} | |
} | |
catch (error) { | |
// problem clearing sheet data | |
logEvent("Problem with 'reset' Function" + error + error.stack); | |
// run Function to launch HTML popup | |
var popupTitle = "'reset' Function error"; | |
var popupMessage = "Unable to clear sheet data: " + error; | |
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(); | |
// get the relevant spreadsheet to output log details | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var logSheet = ss.getSheetByName('Log'); | |
// create and format a timestamp | |
var dateTime = new Date(); | |
var timeZone = ss.getSpreadsheetTimeZone(); | |
var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ 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); | |
} | |
/* | |
This Function creates a menu item to run this script. | |
*/ | |
function onOpen() { | |
SpreadsheetApp.getUi() | |
.createMenu('Admin') | |
.addItem('Reset sheet', 'reset') // label for menu item, name of function to run. | |
.addToUi(); | |
} | |
/* | |
Display a modal dialog box with custom HtmlService content. | |
Does not suspend the script. | |
*/ | |
function htmlPopup(popupTitle, popupMessage) { | |
var htmlOutput = HtmlService | |
.createHtmlOutput(popupMessage) | |
.setWidth(360) | |
.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