Created
March 17, 2019 20:21
-
-
Save kurtkaiser/44a10ec4dc0db2b9f1817dfda410b338 to your computer and use it in GitHub Desktop.
Google Apps Script Email System with Full UI - Allows for multiple people to be alerted on a form submissions. The program has a full user interface, menu and a sidebar that save the options a user selects.
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
// UI Sheets Email Notifications | |
// Kurt Kaiser | |
// kurtkaiser.us | |
// All Rights Reserved, 2019 | |
// Declare global variables | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getActiveSheet(); | |
var lastRow = sheet.getLastRow(); | |
var lastColumn = sheet.getLastColumn(); | |
var scriptProperties = PropertiesService.getScriptProperties(); | |
// ------------------------ Create User Interface ------------------------ | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); // Or DocumentApp or SlidesApp or FormApp. | |
ui.createMenu('Email System') | |
.addItem('Form', 'showFormSidebar') | |
.addSeparator() | |
.addSubMenu(ui.createMenu('Misc') | |
.addItem('Delete', 'deleteData')) | |
.addToUi(); | |
} | |
function showFormSidebar() { | |
var html = HtmlService.createHtmlOutputFromFile('Form') | |
.setTitle('Edit Data') | |
.setWidth(300); | |
SpreadsheetApp.getUi() | |
.showSidebar(html); | |
} | |
// ------------------------ Save Sidebar Options ------------------------ | |
function userInput(form) { | |
Logger.log('userinput'); | |
scriptProperties.deleteAllProperties(); | |
scriptProperties.setProperty('emailSubject', form.emailSubject); | |
scriptProperties.setProperty('messageTitle', form.messageTitle); | |
scriptProperties.setProperty('messageBody', form.messageBody); | |
scriptProperties.setProperty('numToEmail', form.numToEmail); | |
scriptProperties.setProperty('userEmailRadio', form.userEmailRadio); | |
if (form.userEmailRadio == 'onUserEmailRadio') | |
scriptProperties.setProperty('userEmailColumn', form.userEmailColumn); | |
scriptProperties.setProperty('emailsString', makeEmailsString(form)); | |
scriptProperties.setProperty('newEmailBoxes', addEmailBoxes(form)); | |
scriptProperties.setProperty('buttonRadio', form.buttonRadio); | |
if (form.buttonRadio == 'onButton') | |
scriptProperties.setProperty('buttonURL', ss.getUrl()); | |
// logProperties(); | |
} | |
// --------------- Returns Array of All Properties --------------------- | |
function getAllProperties() { | |
var propertiesAndKeys = {} | |
var data = scriptProperties.getProperties(); | |
for (var key in data) { | |
propertiesAndKeys[key] = scriptProperties.getProperty(key); | |
// Logger.log('Key: %s - %s', key, data[key]); | |
} | |
return propertiesAndKeys; | |
} | |
// Used to turn sidebar emails into a single string for sending | |
function makeEmailsString(form) { | |
var emailString = ''; | |
var email; | |
for (var i = 1; i < parseInt(form.numToEmail) + 1; i++) { | |
email = form['email' + i]; | |
emailString = emailString + email + ','; | |
} | |
emailString = emailString.substring(0, emailString.length - 1); | |
return emailString; | |
} | |
// Creates additional html email address boxes for the sidebar | |
function addEmailBoxes(form) { | |
var amount = parseInt(form.numToEmail); | |
var html = '' | |
for (var i = 1; i < amount + 1; i++) { | |
var value = form['email' + i] || ''; | |
html = html + i + ':<input type="text" value="' + value + '" id="email' + i + | |
'" name="email' + i + '"/><br>'; | |
} | |
return html; | |
} | |
// Limits the Saved message in the sidebar to 6 seconds | |
function waitSeconds() { | |
Utilities.sleep(6000); | |
} | |
// Deletes all properties | |
function deleteData() { | |
scriptProperties.deleteAllProperties(); | |
} | |
// ----------------- Log Script Properties ------------------ | |
function logProperties() { | |
Logger.log('Log Properties'); | |
var scriptProperties = PropertiesService.getScriptProperties(); | |
var data = scriptProperties.getProperties(); | |
for (var key in data) { | |
Logger.log('Key: %s, Value: %s', key, data[key]); | |
} | |
} | |
// ----------------- Send Email on Form Submission ------------------ | |
// Once a form, Google Form is submitted to spreadsheet | |
function onFormSubmission() { | |
var properties = getAllProperties(); | |
var submission = getSubmissionString(properties); | |
var allEmails = checkUserEmail(properties); | |
// Makes an email template, updates it with saved data | |
var htmlEmail = HtmlService.createTemplateFromFile('Email'); | |
htmlEmail.messageTitle = properties.messageTitle; | |
htmlEmail.messageBody = properties.messageBody; | |
htmlEmail = htmlEmail.evaluate().append(submission); | |
htmlEmail = htmlEmail.getContent(); | |
emailSend(properties, htmlEmail, allEmails); | |
} | |
// Get sheet header row and last row submitted for html email output | |
function getSubmissionString(properties) { | |
var submission = '<div class="box">'; | |
for (var i = 1; i < lastColumn + 1; i++) { | |
submission = submission + '<span id="label">' + sheet.getRange(1, i).getValue() + | |
'</span><br><span id="info">' + sheet.getRange(lastRow, i).getValue() + '</span><br>'; | |
} | |
// Check is button is on in sidebar | |
if (properties.buttonRadio == 'onButton') { | |
submission = submission + '<br><a href="' + properties.buttonURL + | |
'" id="button">View</a><br><br>'; | |
} | |
submission = submission + '</div><br><p style="font-size:12px;'+ | |
' text-align:center;">Created by <a href=' + | |
'"https://kurtkaiser.us/">Kurt Kaiser</a> </p>'; | |
return submission; | |
} | |
// Check if sidebar user email option, add user email from sheet if it is | |
function checkUserEmail(properties) { | |
Logger.log('in user email check'); | |
if (properties.userEmailRadio == 'onUserEmailRadio' && properties.emailsString) { | |
properties.emailsString = properties.emailsString + ',' + | |
sheet.getRange(lastRow, parseInt(properties.userEmailColumn)).getValue(); | |
} | |
return properties.emailsString; | |
} | |
// Send the email to the required parties | |
function emailSend(properties, htmlEmail, allEmails) { | |
Logger.log('in emailSend'); | |
MailApp.sendEmail({ | |
to: allEmails, | |
subject: properties.emailSubject, | |
htmlBody: htmlEmail | |
}) | |
} |
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
<!-- | |
UI Sheets Email Notifications | |
Coded by Kurt Kaiser | |
kurtkaiser.us | |
All Rights Reserved, 2019 | |
--> | |
<!DOCTYPE html> | |
<html> | |
<head> | |
<style> | |
body{ | |
text-align: center; | |
} | |
.box { | |
text-align: center; | |
font-family: Arial; | |
width: 300px; | |
border: 2px ridge blue; | |
margin: 8px; | |
margin-left: auto; | |
margin-right: auto; | |
padding: 10px; | |
border: 3px outset rgba(0, 0, 0, 0.8); | |
box-shadow: 4px 3px 19px 7px rgba(0, 0, 0, 0.55); | |
height: 100%; | |
} | |
img { | |
width: 100px; | |
} | |
#header { | |
background-color: #ececec; | |
/* gray: #ececec; blue: #d2e7d6 #e8f4ea*/ | |
} | |
#content { | |
background-color: white; | |
margin-right: auto; | |
margin-left: auto; | |
} | |
#button { | |
-webkit-border-radius: 28; | |
-moz-border-radius: 5; | |
border-radius: 5px; | |
font-family: Arial; | |
color: #ffffff; | |
font-size: 15px; | |
background: #ff7878; | |
padding: 8px 20px 8px 20px; | |
text-decoration: none; | |
} | |
#info{ | |
font-size: 14px; | |
} | |
#label{ | |
font-size: 15px; | |
font-weight: bold; | |
} | |
</style> | |
<base target="_top"> | |
</head> | |
<body > | |
<div class="box" id="header"> | |
<img src= | |
"https://upload.wikimedia.org/wikipedia/commons/thumb/a/aa/Bullhorn_font_awesome.svg/512px-Bullhorn_font_awesome.svg.png" | |
width="180" style="margin:10px 0px"><br /> | |
<h2><?= messageTitle ?></h2> | |
</div> | |
<div id="content" class="box" > | |
<h4> | |
<?= messageBody ?> | |
</h4> | |
</div> | |
</body> | |
</html> |
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> | |
<!-- | |
UI Sheets Email Notifications | |
Coded by Kurt Kaiser | |
kurtkaiser.us | |
All Rights Reserved, 2019 | |
--> | |
<html> | |
<head> | |
<!-- Style elements of the sidebar --> | |
<style> | |
html { | |
background: #bbdcf0; | |
color: black; | |
line-height: 1.5; | |
font-family: Verdana; | |
} | |
h2 { | |
-webkit-text-stroke: 1px black; | |
text-shadow: 2px 2px 0 #dadae3; | |
margin-bottom: -4px; | |
} | |
#dataInput { | |
background: #f1f2ee; | |
padding: 5px; | |
border-radius: 15px; | |
margin: 5px; | |
border-style: solid; | |
border-width: 2px; | |
border-color: #c3d5d8; | |
} | |
#instructions { | |
font-size: 12px; | |
margin-left: 10px; | |
margin-right: 20px; | |
} | |
#info { | |
font-weight: bold; | |
} | |
.myButton { | |
background-color: #428bca; | |
-moz-border-radius: 15px; | |
-webkit-border-radius: 8px; | |
border-radius: 15px; | |
border: 1px solid #0c000c; | |
display: inline-block; | |
cursor: pointer; | |
color: #ffffff; | |
font-family: Arial; | |
font-size: 16px; | |
padding: 4px 15px; | |
text-decoration: none; | |
text-shadow: 0px 1px 0px #2f6627; | |
margin: 5px; | |
} | |
.directions { | |
font-size: 13px; | |
margin-top: -2px; | |
margin-bottom: -2px; | |
} | |
.myButton:hover { | |
background-color: #94C3EA; | |
} | |
.myButton:active { | |
position: relative; | |
top: 1px; | |
} | |
input[type=text], | |
textarea { | |
width: 75%; | |
padding-left: 5px; | |
font-size: 14px; | |
} | |
</style> | |
<title>Input Info</title> | |
</head> | |
<body> | |
<center> | |
<h2>Input Information</h2> | |
<form> | |
<div id='dataInput'> | |
<div id='info'> Email Subject </div> | |
<textarea rows="2" id="emailSubject" name="emailSubject"></textarea> | |
<span id='emailSubjectBox'></span> | |
</div> | |
<div id='dataInput'> | |
<div id='info'> Message Title </div> | |
<input type="text" id="messageTitle" name="messageTitle" /> | |
<span id='messageTitleBox'></span> | |
</div> | |
<div id='dataInput'> | |
<div id='info'>Message</div> | |
<textarea rows="4" id="messageBody" name="messageBody">A new form has been submitted.</textarea> | |
<span id='messageTitleBox'></span> | |
</div> | |
<div id='dataInput'> | |
<div id='info'> Email User</div> | |
<p class="directions">Should an email also be sent to the person who submits the form?</p> | |
<input type="radio" onClick="onUserEmail()" id="onUserEmailRadio" value="onUserEmailRadio" name="userEmailRadio">Yes | |
<input type="radio" onClick="offUserEmail()" id="offUserEmailRadio" checked="checked" value="offUserEmailRadio" name="userEmailRadio">No | |
<span id='userEmailBox'></span> | |
</div> | |
<div id="dataInput"> | |
<div id="info">Number of Recipients</div> | |
<!------- Loads from script --------> | |
<span id="numOfRecipientsBox"></span> | |
</div> | |
<div id='dataInput'> | |
<div id='info'>Emails</div> | |
<span id='emailBox'> <!--1:<input type="text" id="email1" name="email1"/>--></span> | |
</div> | |
<div id='dataInput'> | |
<div id='info'> Button Link</div> | |
<p class="directions"> | |
A button linked to this spreadsheet will appear at the bottom of each email. | |
</p> | |
<input type="radio" id="onButton" value="onButton" name="buttonRadio">Yes | |
<input type="radio" id="offButton" checked="checked" value="offButton" name="buttonRadio">No | |
<span id='buttonBox'></span> | |
</div> | |
<div id="submitButton"></div> | |
<div class="myButton" onClick="formSubmit()">Save</div> | |
<div class="myButton" onClick="reload()">Reload</div> | |
<div id="savedProperties"></div> | |
</form> | |
<br><p style="font-size:12px; text-align:right;">Created by | |
<a href="https://kurtkaiser.us/">Kurt Kaiser</a> </p> | |
</center> | |
</body> | |
<!--------------------------------- Code -------------------------------------------> | |
<script type="text/javascript"> | |
var numDropMenu = returnNumberMenu(); | |
var letterDropMenu = returnLetterMenu(); | |
google.script.run.withSuccessHandler(updateFormValues).getAllProperties(); | |
document.getElementById('numOfRecipientsBox').innerHTML = | |
'<select id="numToEmail" value="1" name="numToEmail">' + numDropMenu; | |
document.getElementById('columnsBox').innerHTML = | |
'Start:<select id="startColumn" name="startColumn">' + letterDropMenu + | |
' End:<select id="endColumn" name="endColumn">' + letterDropMenu; | |
// ------------------------------------ Updating Form Values ---------------------------------- | |
function updateFormValues(properties) { | |
document.getElementById('emailSubject').value = properties.emailSubject || 'Submission Notification'; | |
document.getElementById('messageTitle').value = properties.messageTitle || 'New Submission'; | |
document.getElementById('messageBody').value = properties.messageBody || 'A new form has been submitted.'; | |
document.getElementById('numToEmail').value = properties.numToEmail || '1'; | |
document.getElementById('emailBox').innerHTML = properties.newEmailBoxes || | |
'1:<input type="text" id="email1" name="email1"/>'; | |
document.getElementById(properties.userEmailRadio).checked = true; | |
if (properties.userEmailRadio == "onUserEmailRadio") { | |
onUserEmail(); | |
document.getElementById('userEmailColumn').value = properties.userEmailColumn || ""; | |
} | |
document.getElementById(properties.buttonRadio).checked = true; | |
} | |
function formSubmit() { | |
google.script.run.userInput(document.forms[0]); | |
document.getElementById('submitButton').innerHTML = "<div id='dataInput'>Saved</div>"; | |
google.script.run.withSuccessHandler(hideSaved).waitSeconds(); | |
} | |
function onUserEmail() { | |
document.getElementById('userEmailBox').innerHTML = | |
'<hr>Email Address Column <select id="userEmailColumn" name="userEmailColumn">' + letterDropMenu; | |
} | |
function offUserEmail() { | |
document.getElementById('userEmailBox').innerHTML = ''; | |
} | |
function hideSaved() { | |
document.getElementById('submitButton').innerHTML = ""; | |
} | |
function reload() { | |
google.script.run.showFormSidebar(); | |
} | |
function returnNumberMenu() { | |
return '<option id="1" value="1">1</option><option value="2">2</option><option value="3">3</option>' + | |
'<option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option>' + | |
'<option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option>' + | |
'<option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option>' + | |
'<option value="16">16</option><option value="17">17</option><option value="18">18</option>' + | |
'<option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option>' + | |
'<option value="23">23</option><option value="24">24</option><option value="25">25</option></select>'; | |
} | |
function returnLetterMenu() { | |
return '<option id="1" value="1">A</option><option value="2">B</option><option value="3">C</option>' + | |
'<option value="4">D</option><option value="5">E</option><option value="6">F</option><option value="7">G</option>' + | |
'<option value="8">H</option><option value="9">I</option><option value="10">J</option><option value="11">K</option>' + | |
'<option value="12">L</option><option value="13">M</option><option value="14">N</option><option value="15">O</option>' + | |
'<option value="16">P</option><option value="17">Q</option><option value="18">R</option>' + | |
'<option value="19">S</option><option value="20">T</option><option value="21">U</option><option value="22">V</option>' + | |
'<option value="23">W</option><option value="24">X</option><option value="25">Y</option><option value="26">Z</option>' + | |
'</select>'; | |
} | |
</script> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment