Monday, 13 January 2020

Bulk save emails from Gmail - the code

Following on from the overview blog post, here I have included the code itself from which the tool is built.

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!');
}

<!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