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
function initialStart() { | |
// run Function to get relevant spreadsheet data for use throughout script | |
var spreadsheet = getSpreadsheet(); | |
var spreadSheetApp = spreadsheet[0]; // used for 'Flush' in counter | |
var logSheet = spreadsheet[2]; | |
var destFolderID = spreadsheet[3]; // destination Google Drive Folder to save emails | |
var maxThreads = spreadsheet[4]; // maximum number of threads to get from Gmail | |
var sourceLabel = spreadsheet[6]; // label where emails to be saved are stored | |
// log start of running script to spreadsheet | |
logEvent(logSheet, 'User selected to run the tool'); | |
// run Function to check Google Drive Folder exists | |
var folderFlag = driveFolderPopup(destFolderID); | |
if (folderFlag == 'No') { | |
// run Function to get email data for use throughout script | |
var countEmailsArray = getEmails(sourceLabel, maxThreads); | |
/* check if var set to false as means issue with Gmail label so rest of script not | |
need to run */ | |
if (countEmailsArray) { | |
var numThreads = countEmailsArray[2]; // array of threads | |
// check IF there are emails to be saved before proceeding | |
if (numThreads > 0) { | |
// run start popup to confirm IF user wishes to proceed | |
var startConfirmation = startPopup(spreadSheetApp, sourceLabel, numThreads); | |
if (startConfirmation == 'Yes') { | |
logEvent(logSheet, "User selected 'Yes' to startPopup"); | |
// run main Function to start process for saving emails | |
saveEmailToDrive(); | |
} // end of run start popup to confirm IF user wishes to proceed | |
else { | |
logEvent(logSheet, "User selected 'No' to startPopup"); | |
logEvent(logSheet, 'Script complete'); | |
} | |
} // end of check IF there are emails to be saved before proceeding | |
else { | |
Logger.log('There are no emails to be saved'); | |
// run Function to display popup to confirm there are no emails to save | |
noEmailsPopup(); | |
logEvent(logSheet, 'No emails to save - Script complete'); | |
} | |
}/* end of check if var set to false as means issue with Gmail label so rest of script | |
not need to run */ | |
else { | |
// log error with Gmail label | |
logEvent(logSheet, "Unable to find the label '" + sourceLabel + "' in Gmail"); | |
// run Gmail label error popup to inform user of issue | |
gmailLabelPopup(sourceLabel); | |
// log script complete | |
logEvent(logSheet, 'Script complete'); | |
} | |
}/* end of check if folderFlag set to "No" as means issue with Drive Folder so rest of script | |
not need to run */ | |
else { | |
// log error with Drive Folder | |
logEvent(logSheet, "Unable to find Drive Folder '" + destFolderID); | |
// log script complete | |
logEvent(logSheet, 'Script complete'); | |
} | |
} | |
function saveEmailToDrive() { | |
// run Function to get relevant spreadsheet data for use throughout script | |
var spreadsheet = getSpreadsheet(); | |
var spreadSheetApp = spreadsheet[0]; // used for 'Flush' in counter | |
var ss = spreadsheet[1]; | |
var logSheet = spreadsheet[2]; | |
var destFolderID = spreadsheet[3]; // google drive folder under which emails are to be saved | |
var maxThreads = spreadsheet[4]; // maximum number of threads to get from Gmail | |
var moveLabels = spreadsheet[5]; // Yes/No to moving threads into another label once complete | |
var sourceLabel = spreadsheet[6]; // label where emails to be saved are stored | |
var scheduleTrigger = spreadsheet[7]; // Yes/No for scheduling the tool to run | |
var counterCell = spreadsheet[8]; // getRange of cell for email counter | |
var threadCount = spreadsheet[9]; // getRange of cell for total emails | |
var scriptStatusCell = spreadsheet[10]; // getRange of cell for script status on 'Welcome' sheet | |
var timeZone = spreadSheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); // timeZone for checking runtime | |
// log start of running script to spreadsheet | |
logEvent(logSheet, 'Main saveEmailToDrive Function has started'); | |
// get start date/time for controlling how long it runs | |
var startDate = new Date(); | |
// run Function to get email data for use throughout script | |
var countEmailsArray = getEmails(sourceLabel, maxThreads); | |
/* check if var set to false as means issue with Gmail label so rest of script not | |
need to run */ | |
if (countEmailsArray) { | |
var label = countEmailsArray[0]; // returns getUserLabelByName(sourceLabel) | |
var threads = countEmailsArray[1]; // array of threads | |
var numThreads = countEmailsArray[2]; // array of threads | |
// check IF there are emails to be saved before proceeding | |
if (numThreads > 0) { | |
logEvent(logSheet, 'Current thread total in Label is: ' + numThreads); | |
/* | |
check IF thread count cell empty before setting value - so only does this once | |
before starting the whole batch process - rather than overwriting itself if | |
the script has been scheduled to re-run for instance. | |
*/ | |
var threadValue = threadCount.getValue(); | |
if (threadValue == '') { | |
threadCount.setValue(numThreads); | |
} | |
// check IF scheduling the tool to run has been selected | |
if (scheduleTrigger == 'Yes') { | |
Logger.log('Scheduling is set to "Yes"'); | |
// check for existing triggerID script property | |
var triggerID = getScriptProperty('triggerID'); | |
if (triggerID == null) { | |
// run Function to create schedule | |
var scheduleCreated = createSchedule(logSheet); | |
// update sheet status to inform user waiting for schedule | |
scriptStatusCell.setFontColor('#ff0000'); | |
scriptStatusCell.setValue('Will start within the hour.'); | |
} | |
else { | |
Logger.log('Schedule already exists'); | |
} | |
} | |
else { | |
Logger.log('Scheduling is set to "No"'); | |
} | |
/* check IF schedule has been created: only proceed with rest of script if | |
no schedule has been created, otherwise it will start in the next | |
hour automatically so we need to terminate at this point */ | |
if (scheduleCreated != true) { | |
// log that script is running | |
logEvent(logSheet, 'Script is running.'); | |
// run Function to write running text into 'Welcome' sheet | |
runningText(); | |
// set value to default to allow script to run | |
var runtimeReached = 'No'; | |
// check option to move completed emails is true | |
if (moveLabels == 'Yes') { | |
// run Function to check completed label exists, otherwise create it | |
var completeLabel = createGmailLabel(logSheet); | |
} | |
else { } | |
// loop through threads ******************************************************************* | |
for (var j = 0; j < numThreads; j++) { | |
// check IF there is enough runtime left for the script to proceed | |
if (runtimeReached == 'No') { | |
// get number of messages within thread | |
var threadMessageCount = threads[j].getMessageCount(); | |
Logger.log('There are ' + threadMessageCount + ' messages in thread'); | |
// get an array of messages in thread | |
var threadMessages = threads[j].getMessages(); | |
// get Subject of first message in thread for naming | |
var firstMessageSubject = threadMessages[0].getSubject(); | |
// get Date of first message in thread for naming | |
var firstMessageDate = threadMessages[0].getDate(); | |
var niceFirstMessageDate = Utilities.formatDate(firstMessageDate, timeZone, 'YYYY_MM_dd'); | |
// show message for saving current thread in spreadsheet | |
ss.toast('Starting to save: ' + '"' + firstMessageSubject + '"'); | |
// empty array to push message details into | |
var messageDetails = []; | |
// loop through each message in thread ****************************** | |
for (var i = 0; i < threadMessageCount; i++) { | |
// get array of message attachments | |
var messageAttachments = threadMessages[i].getAttachments(); | |
// get other message details | |
var messageSubject = threadMessages[i].getSubject(); | |
var messageDate = threadMessages[i].getDate(); | |
var messageFrom = threadMessages[i].getFrom(); | |
var messageTo = threadMessages[i].getTo(); | |
var messageCc = threadMessages[i].getCc(); | |
var messageBcc = threadMessages[i].getBcc(); | |
var messageBody = threadMessages[i].getPlainBody(); | |
// push all message items into array | |
messageDetails.push([messageAttachments, messageSubject, messageDate, messageFrom, | |
messageTo, messageCc, messageBcc, messageBody]); | |
} //end of loop through each message in thread ****************************** | |
// if there are attachments .... | |
if (messageAttachments != '') { | |
Logger.log('Attachments found'); | |
// run Function to create folder and move attachments into it | |
var newFolderID = attachmentFolder(messageDetails, destFolderID, firstMessageSubject); | |
// new Google Doc here - so you have one doc per thread ********************************* | |
var newDocName = niceFirstMessageDate + ' - ' + firstMessageSubject; | |
var gDoc = { | |
title: newDocName, | |
parents: [{ 'id': newFolderID }], | |
mimeType: MimeType.GOOGLE_DOCS | |
}; | |
var newDocID = Drive.Files.insert(gDoc).id; | |
var newdocBody = DocumentApp.openById(newDocID).getBody(); | |
// new Google Doc here - so you have one doc per thread ********************************* | |
} | |
else { // there are no attachments .... | |
// new Google Doc here - so you have one doc per thread ********************************* | |
var newDocName = niceFirstMessageDate + ' - ' + firstMessageSubject; | |
var gDoc = { | |
title: newDocName, | |
parents: [{ 'id': destFolderID }], | |
mimeType: MimeType.GOOGLE_DOCS | |
}; | |
var newDocID = Drive.Files.insert(gDoc).id; | |
var newdocBody = DocumentApp.openById(newDocID).getBody(); | |
// new Google Doc here - so you have one doc per thread ********************************* | |
var newFolderID = 0; | |
} | |
// run Function to append thread details into Doc | |
getMessageDetails(messageDetails, newdocBody); | |
// save the open document, otherwise the PDF is blank | |
DocumentApp.openById(newDocID).saveAndClose(); | |
// run Function to reduce size of Doc | |
reduceSize(newDocID); | |
// run Function to convert Doc into PDF | |
convertDocToPDF(newDocID, newFolderID, destFolderID); | |
// replace thread labels to track completed emails if configured | |
if (moveLabels == 'Yes') { | |
completeLabel.addToThread(threads[j]); // add completed label | |
label.removeFromThread(threads[j]); // remove source label | |
} | |
// update email counter for completed emails | |
var counterValue = counterCell.getValue(); | |
counterCell.setValue(counterValue + 1); | |
spreadSheetApp.flush(); | |
// check date/time to monitor duration of script so not exceed limit ****************** | |
// run Function to check IF there is enough runtime left for script to proceed | |
var runtimeReached = checkDateTime(startDate, '1500', logSheet); | |
}// end of check IF there is enough runtime left for the script to proceed | |
else { | |
// runtime reached so the script needs to finish cleanly | |
} | |
} //end loop through threads ************************************************************** | |
// run Function to check if there are any emails left | |
var countEmailsArray = getEmails(sourceLabel, maxThreads); | |
var numThreads = countEmailsArray[2]; // array of threads | |
// check IF there are still emails left | |
if (numThreads > 0) { | |
Logger.log('Yes there are still emails left'); | |
} | |
else { | |
Logger.log('No more emails are left'); | |
// run Function to get triggerID script property and evaluate status | |
var triggerID = getScriptProperty('triggerID'); | |
if (triggerID != null) { | |
// run Function to delete corresponding trigger | |
deleteTrigger(triggerID, logSheet); | |
Logger.log('Deleted hourly trigger'); | |
// run Function to delete corresponding script property | |
deleteScriptProperty('triggerID', logSheet); | |
Logger.log('Deleted triggerID script property'); | |
} | |
else { | |
Logger.log('triggerID is: ' + triggerID); | |
} | |
// run Function to write success text into 'Welcome' sheet | |
successText(); | |
} | |
if ((numThreads > 0) && (runtimeReached == 'Yes')) { | |
logEvent(logSheet, 'Script will run again at next schedule'); | |
} | |
else { | |
logEvent(logSheet, 'Script has now completed'); | |
} | |
} // end of check IF schedule has been created | |
else { | |
Logger.log('New schedule has been created so exiting script until automated start'); | |
// run Function to display popup to confirm schedule has been created | |
scheduledPopup(); | |
} | |
} // end of check IF there are emails to be saved before proceeding | |
else { }; | |
}/* end of check if var set to false as means issue with Gmail label so rest of script | |
not need to run */ | |
else { | |
// log error with Gmail label | |
logEvent(logSheet, 'Issue with specified Gmail label'); | |
// log script complete | |
logEvent(logSheet, 'Script complete'); | |
} | |
} | |
function getEmails(sourceLabel, maxThreads) { | |
// get Gmail Label where emails are stored | |
var label = GmailApp.getUserLabelByName(sourceLabel); | |
// get an array of threads, with a limit | |
try { | |
var threads = label.getThreads(0, maxThreads); | |
var labelFlag = 'No'; | |
} | |
catch(e) { | |
Logger.log('Issue with getting Gmail label: ' + e); | |
var labelFlag = 'Yes'; | |
} | |
// evaluate status of labelFlag to determine next step | |
if (labelFlag == 'No') { | |
var numThreads = threads.length; | |
// create array of data for returning to main function | |
var countEmailsData = [label, threads, numThreads]; | |
return countEmailsData; | |
} | |
else { | |
var countEmailsData = false; | |
return countEmailsData; | |
} | |
} | |
function getSpreadsheet() { | |
// get spreadsheet | |
var spreadSheetApp = SpreadsheetApp; | |
var ss = spreadSheetApp.getActiveSpreadsheet(); | |
// get relevant sheets | |
var welcomeSheet = ss.getSheetByName('Welcome'); | |
var logSheet = ss.getSheetByName('Log'); | |
var configSheet = ss.getSheetByName('Config'); | |
// get Google Drive folder ID from what may be a Url or direct ID ********************* | |
var destFolderValue = welcomeSheet.getRange(6, 3).getValue(); | |
var destFolderID = destFolderValue.match(/[-\w]{15,}/); // use a regex to extract the ID | |
// get Google Drive folder ID from what may be a Url or direct ID ********************* | |
// get data from spreadsheet | |
var sourceLabel = welcomeSheet.getRange(8, 3).getValue(); | |
var configData = configSheet.getDataRange().getValues(); | |
var maxThreads = configData[0][1]; | |
var moveLabels = configData[1][1]; | |
var scheduleTrigger = configData[2][1]; | |
var counterCell = welcomeSheet.getRange(15, 2); | |
var threadCount = welcomeSheet.getRange(14, 2); | |
var scriptStatusCell = welcomeSheet.getRange(14, 3); | |
// create array of data for returning to main function | |
var spreadsheetData = [spreadSheetApp, ss, logSheet, destFolderID, maxThreads, moveLabels, | |
sourceLabel, scheduleTrigger, counterCell, threadCount, scriptStatusCell]; | |
return spreadsheetData; | |
} | |
function getScriptProperty(propertyValue) { | |
// access the Script Properties via the Service | |
var scriptProp = PropertiesService.getScriptProperties(); | |
// call Script Property key-value pair that want | |
var propertyValueCheck = scriptProp.getProperty(propertyValue); | |
return propertyValueCheck; | |
} | |
function createScriptProperty(propertyKey, propertyValue) { | |
// access the Script Properties via the Service | |
var scriptProp = PropertiesService.getScriptProperties(); | |
// create the Script Property key-value pair | |
scriptProp.setProperty(propertyKey, propertyValue); | |
} | |
function deleteScriptProperty(propertyValue, logSheet) { | |
// access the Script Properties via the Service | |
var scriptProp = PropertiesService.getScriptProperties(); | |
// delete variable from Script Properties | |
scriptProp.deleteProperty(propertyValue); | |
// log user action | |
logEvent(logSheet, 'deleteScriptProperty function ran successfully'); | |
} | |
function createSchedule(logSheet) { | |
var timeZone = Session.getScriptTimeZone() | |
// create a time-based trigger to run 'saveEmailToDrive' function every hour | |
var newTrigger = ScriptApp.newTrigger('saveEmailToDrive') | |
.timeBased() | |
.everyHours(1) | |
.inTimezone(timeZone) | |
.create(); | |
// get ID for trigger and add to Script Properties | |
var newTriggerID = newTrigger.getUniqueId(); | |
Logger.log('New Trigger ID is: ' + newTriggerID); | |
createScriptProperty('triggerID', newTriggerID); | |
// log user action | |
logEvent(logSheet, 'createSchedule function ran successfully, exiting script'); | |
// set acknowledgement | |
var scheduleCreated = true; | |
// return acknowledgement so script does not continue to run until scheduled | |
return scheduleCreated; | |
} | |
function createGmailLabel(logSheet) { | |
var bestLabel = 'BEST_Completed'; | |
// get current Gmail Labels | |
var labels = GmailApp.getUserLabels(); | |
var labelsLength = labels.length; | |
// empty array for pushing current Labels into | |
var labelsArray = []; | |
// loop through current Labels and get an array of their names | |
for (var i=0; i<labelsLength; i++) { | |
var labelName = labels[i].getName(); | |
labelsArray.push(labelName); | |
} | |
// reset before loop | |
var labelExists = ''; | |
// loop through current Labels looking for match ************************ | |
var labelsArrayLength = labelsArray.length; | |
for (var j=0; j<labelsArrayLength; j++) { | |
if (labelsArray[j] == bestLabel) { | |
// exit loop if match found | |
Logger.log('Found ' + bestLabel + ' Label'); | |
var labelExists = true; | |
break; | |
} | |
else { | |
//Logger.log('Cannot find ' + bestLabel + ' Label'); | |
var labelExists = false; | |
} | |
} // end of loop through current Labels looking for match **************** | |
// if Label does not exist then create it | |
if (labelExists != true) { | |
var completedLabel = GmailApp.createLabel(bestLabel); | |
Logger.log('Created Label'); | |
// log creation of Label | |
logEvent(logSheet, 'Automatically created ' + bestLabel + ' label'); | |
var labelCompleted = GmailApp.getUserLabelByName(bestLabel); | |
return labelCompleted; | |
} | |
else { | |
var labelCompleted = GmailApp.getUserLabelByName(bestLabel); | |
return labelCompleted; | |
} | |
} | |
function getMessageDetails(threadMessages, newdocBody) { | |
var aMessageSubject = threadMessages.getSubject(); | |
var aMessageDate = threadMessages.getDate(); | |
var aMessageFrom = threadMessages.getFrom(); | |
var aMessageTo = threadMessages.getTo(); | |
var aMessageCc = threadMessages.getCc(); | |
var aMessageBcc = threadMessages.getBcc(); | |
var aMessageBody = threadMessages.getPlainBody(); | |
newdocBody.appendParagraph('SUBJECT: ' + aMessageSubject); | |
newdocBody.appendParagraph('DATE: ' + aMessageDate); | |
newdocBody.appendParagraph('FROM: ' + aMessageFrom); | |
newdocBody.appendParagraph('TO: ' + aMessageTo); | |
if (aMessageCc != '') { | |
newdocBody.appendParagraph('Cc: ' + aMessageCc) | |
} | |
if (aMessageBcc != '') { | |
newdocBody.appendParagraph('Bcc: ' + aMessageBcc) | |
} | |
newdocBody.appendParagraph('BODY: ' + ' \n' + aMessageBody); | |
// add horizontal line to Google Doc to help separate individual emails | |
newdocBody.appendHorizontalRule(); | |
// add Page Break to Google Doc to help separate individual emails | |
newdocBody.appendPageBreak(); | |
} | |
function attachmentFolder(attachments, destFolderID, firstMessageSubject) { | |
// set 'true' for moving newDoc into folder with attachments later | |
var hasAttachments = true; | |
// create new folder to store attachments for thread | |
var newFolder = DriveApp.getFolderById(destFolderID).createFolder(firstMessageSubject); | |
var newFolderID = newFolder.getId(); | |
for (var b=0; b<attachments.length; b++) { | |
// create a file from the attachment | |
var file = DriveApp.getFolderById(newFolderID).createFile(attachments[b]); | |
// remove the file from the RootFolder so only exists in above new folder | |
var root = DriveApp.getRootFolder(); | |
root.removeFile(file); | |
} | |
return newFolderID; | |
} | |
function moveFile(newDocID, destFolderID) { | |
// get locations of where file currently exists | |
var doc = DriveApp.getFileById(newDocID); | |
var parents = doc.getParents(); | |
// add file to new folder location (so is ignored from previously collated 'parents' and hence won't be removed from here) | |
DriveApp.getFolderById(destFolderID).addFile(doc); | |
// once file has moved, remove it from any other locations so only 1 copy left | |
while (parents.hasNext()) { | |
var parent = parents.next(); | |
parent.removeFile(doc); | |
} | |
} | |
function reduceSize(newDocID) { | |
try { | |
// open specified Google Doc | |
var doc = DocumentApp.openById(newDocID); | |
// get Doc body | |
var docBody = doc.getBody() | |
// search and replace text with nothing | |
docBody.replaceText('>>.*>>', ''); | |
Logger.log("Ran 'reduceSize' function"); | |
} | |
catch (e) { | |
Logger.log('Error with reducing Doc size: ' + e); | |
} | |
} | |
function convertDocToPDF(newDocID, newFolderID, destFolderID) { | |
try { | |
// get current Doc | |
var source = DriveApp.getFileById(newDocID); | |
var blobFile = source.getAs('application/pdf'); | |
Logger.log('newFolderID is: ' + newFolderID); | |
if (newFolderID != 0) { | |
// save email in root folder | |
Logger.log('newFolderID is not blank'); | |
DriveApp.getFolderById(newFolderID).createFile(blobFile); | |
} | |
else { | |
// save email in new folder where attachments are now stored | |
DriveApp.getFolderById(destFolderID).createFile(blobFile); | |
} | |
// delete the original Doc version | |
source.setTrashed(true); | |
} | |
catch (e) { | |
Logger.log('Error with converting Doc to PDF: ' + e); | |
} | |
} | |
function deleteTrigger(triggerID, logSheet) { | |
// get all existing project triggers | |
var allTriggers = ScriptApp.getProjectTriggers(); | |
var allTriggersLength = allTriggers.length; | |
// loop through existing triggers *************************************** | |
for (var i=0; i<allTriggersLength; i++) { | |
// if the current trigger is the correct one, delete it and stop script | |
if (allTriggers[i].getUniqueId() == triggerID) { | |
Logger.log('TriggerID: '+ allTriggers[i] + ' matches ' + triggerID); | |
ScriptApp.deleteTrigger(allTriggers[i]); | |
break; | |
} | |
else { | |
Logger.log('No matches for TriggerID: ' + triggerID); | |
} | |
} // end of loop through existing triggers ****************************** | |
// log user action | |
logEvent(logSheet, 'deleteTrigger function ran successfully'); | |
} | |
function showResetPopup() { | |
var ui = SpreadsheetApp.getUi(); | |
var result = ui.alert( | |
'Reset spreadsheet?', | |
'Are you sure you want to continue?', | |
ui.ButtonSet.YES_NO); | |
// Process the user's response. | |
if (result == ui.Button.YES) { | |
// User clicked "Yes". | |
reset() | |
} | |
else { | |
// User clicked "No" or X in the title bar so do nothing further | |
} | |
} | |
function reset() { | |
// get spreadsheet data | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var logSheet = ss.getSheetByName('Log'); | |
// log start of running reset spreadsheet function | |
logEvent(logSheet, 'User selected to reset script'); | |
// clear relevant spreadsheet cells | |
var welcomeSheet = ss.getSheetByName('Welcome'); | |
var folderId = welcomeSheet.getRange(6, 3).clearContent(); | |
var gmailLabel = welcomeSheet.getRange(8, 3).clearContent(); | |
var emailsToSave = welcomeSheet.getRange(14, 2).clearContent(); | |
var emailsSaved = welcomeSheet.getRange(15, 2).clearContent(); | |
var scriptStatusCell = welcomeSheet.getRange(14, 3).clearContent(); | |
// run Function to get triggerID script property and evaluate status | |
var triggerID = getScriptProperty('triggerID'); | |
if (triggerID != null) { | |
// run Function to delete corresponding trigger | |
var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log'); | |
deleteTrigger(triggerID, logSheet); | |
Logger.log('Deleted scheduled trigger'); | |
// run Function to delete corresponding script property | |
deleteScriptProperty('triggerID', logSheet); | |
Logger.log('Deleted triggerID script property'); | |
} | |
else { | |
Logger.log('triggerID is: ' + triggerID); | |
} | |
// log end of running reset spreadsheet function | |
logEvent(logSheet, 'Reset script complete'); | |
// display confirmation reset popup to inform user | |
var ui = SpreadsheetApp.getUi(); | |
var response = ui.alert('Reset has successfully completed.', ui.ButtonSet.OK); | |
} | |
function scheduledPopup() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var html = HtmlService.createHtmlOutputFromFile('popupScheduled') | |
.setSandboxMode(HtmlService.SandboxMode.IFRAME) | |
.setHeight(100) | |
.setWidth(320); | |
ss.show(html); | |
} | |
function noEmailsPopup() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var html = HtmlService.createHtmlOutputFromFile('popupNoEmails') | |
.setSandboxMode(HtmlService.SandboxMode.IFRAME) | |
.setHeight(100) | |
.setWidth(320); | |
ss.show(html); | |
} | |
function logEvent(logSheet, action) { | |
// get relevant timezone for creating date | |
var timeZone = Session.getScriptTimeZone(); | |
// 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/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); | |
} | |
function onOpen() { | |
// create menu options | |
SpreadsheetApp.getUi() | |
.createMenu('Save emails') | |
.addItem('Start saving emails', 'initialStart') | |
.addItem('Reset', 'showResetPopup') | |
.addToUi(); | |
} | |
function driveFolderPopup(destFolderID) { | |
try { | |
var destFolder = DriveApp.getFolderById(destFolderID); | |
var folderFlag = 'No'; | |
} | |
catch(e) { | |
Logger.log('Issue with Drive Folder ID: ' + e); | |
var folderFlag = 'Yes'; | |
} | |
// evaluate status of driveFlag to determine next step | |
if (folderFlag == 'Yes') { | |
// show popup to inform user of error | |
var ui = SpreadsheetApp.getUi(); | |
var result = ui.alert( | |
'Google Drive Folder Error', | |
"Cannot find the Google Drive Folder with ID: " + destFolderID, | |
ui.ButtonSet.OK); | |
} | |
else {} | |
return folderFlag; | |
} | |
function gmailLabelPopup(sourceLabel) { | |
var ui = SpreadsheetApp.getUi(); | |
var result = ui.alert( | |
'Gmail Label Error', | |
"Cannot find the label '" + sourceLabel + "' in your Gmail.", | |
ui.ButtonSet.OK); | |
} | |
function startPopup(spreadSheetApp, sourceLabel, numThreads) { | |
var ui = spreadSheetApp.getUi(); | |
var result = ui.alert( | |
'Start saving emails?', | |
'Are you sure you want to save ' + numThreads + " email threads in your Gmail label '" + sourceLabel + "'?", | |
ui.ButtonSet.YES_NO); | |
// Process the user's response. | |
if (result == ui.Button.YES) { | |
// User clicked "Yes". | |
return 'Yes'; | |
} | |
else { | |
// User clicked "No" or X in the title bar so do nothing further | |
return 'No'; | |
} | |
} | |
function runningText() { | |
// get relevant spreadsheet | |
var welcomeSheet = SpreadsheetApp.getActive().getSheetByName('Welcome'); | |
// get relevant cell for updating script status | |
var scriptStatusCell = welcomeSheet.getRange(14, 3); | |
// set Font Color to red and add text | |
scriptStatusCell.setFontColor('#ff0000'); | |
welcomeSheet.getRange(15, 3).setValue('Script is running'); | |
} | |
function successText() { | |
// get relevant spreadsheet | |
var welcomeSheet = SpreadsheetApp.getActive().getSheetByName('Welcome'); | |
// get relevant cell for updating script status | |
var scriptStatusCell = welcomeSheet.getRange(14, 3); | |
// set Font Color to red and add text | |
scriptStatusCell.setFontColor('#08a102'); | |
welcomeSheet.getRange(15, 3).setValue('Successfully completed!'); | |
} |
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
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
<style> | |
h1 { | |
font-size: 120%; | |
font-weight: bold; | |
} | |
p { | |
font-size: 120%; | |
} | |
</style> | |
<script> | |
// this is the success handler when the 'Submit changes' button is clicked on the form | |
function confirmSubmit() { | |
// close form | |
google.script.host.close(); | |
} | |
</script> | |
</head> | |
<body> | |
<h1>Information</h1> | |
<p>There are no emails to save.</p> | |
</body> | |
</html> | |
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
<style> | |
h1 { | |
font-size: 120%; | |
font-weight: bold; | |
} | |
p { | |
font-size: 120%; | |
} | |
</style> | |
<script> | |
// this is the success handler when the 'Submit changes' button is clicked on the form | |
function confirmSubmit() { | |
// close form | |
google.script.host.close(); | |
} | |
</script> | |
</head> | |
<body> | |
<h1>Scheduled</h1> | |
<p>The process will start automatically within the next 1 hour.</p> | |
</body> | |
</html> | |
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
<style> | |
h1 { | |
font-size: 120%; | |
font-weight: bold; | |
} | |
p { | |
font-size: 120%; | |
} | |
</style> | |
<script> | |
// this is the success handler when the 'Submit changes' button is clicked on the form | |
function confirmSubmit() { | |
// close form | |
google.script.host.close(); | |
} | |
</script> | |
</head> | |
<body> | |
<h1>Completed</h1> | |
<p>Your emails have been saved successfully.</p> | |
</body> | |
</html> |
No comments:
Post a Comment