Skip to content

Instantly share code, notes, and snippets.

@kurtkaiser
Created March 17, 2019 20:21
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save kurtkaiser/44a10ec4dc0db2b9f1817dfda410b338 to your computer and use it in GitHub Desktop.
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.
// 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
})
}
<!--
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>
<!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 +
'&nbsp;&nbsp;&nbsp;&nbsp;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