Skip to content

Instantly share code, notes, and snippets.


hinklefoxmail/ Secret

Created Sep 3, 2017
What would you like to do?
Event Scheduler
function myFunction() {
var ss = SpreadsheetApp.openById('1S-idIC3gabylSZhFZTbOKkGT5VBB8l0VAoKyt2ZIVaE');
var form = FormApp.openById('1DsVEYADcbprx44MGfaaUrqIBFvx40rS-tQitPOM9jZY');
//define variables
var formResponses = form.getResponses();
var lastest = formResponses[formResponses.length-1];
var responses = lastest.getItemResponses();
var user = lastest.getRespondentEmail();
var timestamp = lastest.getTimestamp();
var eventName = responses[0].getResponse()
var eventDate = responses[1].getResponse()
var startTime = responses[2].getResponse()
var endTime = responses[3].getResponse()
var department = responses[4].getResponse()
var people = responses[5].getResponse()
var location = responses[6].getResponse()
var itemsNeeded = responses[7].getResponse()
//check if there is food
var food = 'none';
var food = responses[8].getResponse();
Logger.log([eventName, eventDate, startTime, endTime, department, people, location, itemsNeeded, food]);
//put items needed in a string for emailing
var itemsList = '';
for (var i = 0; i < itemsNeeded.length; i++){
itemsList = itemsList+'\n'+itemsNeeded[i]
//open ss and get information
var ss = SpreadsheetApp.openById('1S-idIC3gabylSZhFZTbOKkGT5VBB8l0VAoKyt2ZIVaE');
var form = FormApp.openById('1DsVEYADcbprx44MGfaaUrqIBFvx40rS-tQitPOM9jZY');
var emailArray = [];
var items = ss.getSheetByName('Items').getDataRange().getValues();
var emails = ss.getSheetByName('emails').getDataRange().getValues();
var toEmail = user;
//for each respnose in items needed, see if it on the spreadsheet
for (var i = 0; i < items.length; i++){
//if it is, get info who is responsible for it
if (itemsNeeded.indexOf(items[i][0])>-1){
var role = items[i][1];
var email = getEmail(role, emails);
//If the email has not been added to the mailing list, add the email to the mailing list
if (toEmail.indexOf(email)==-1){
Logger.log('Email added to list');
toEmail = toEmail+','+email
//notify admin about thier department
for (var i = 0; i < emails.length; i++){
if (emails[i][0] == department){
var email = emails[i][1];
toEmail = toEmail+','+email;
//add secretary if there is food
if (food != 'none'){
var email = emails[i][2];
toEmail = toEmail+','+email;
itemsList = itemsList+'\n\nFood:\n'+food;
//set up email body and message
var subject = 'New event on '+eventDate;
var body = eventName+' is scheduled for '+eventDate+' from '+startTime+' to '+endTime+' in the '+location+' by '+user+'.\nThe following items will be needed for '+people+' people:\n'+itemsList+'\n\nThanks,\nJohn Hinkle';
//set up calendar event for the event
var year = eventDate.slice(0,4);
var month = eventDate.slice(5,7);
var day = eventDate.slice(8,10);
Logger.log('Year: '+year);
Logger.log('Month: '+month);
Logger.log('Day: '+day);
var newDay = month+'/'+day+'/'+year
var startString = newDay+' '+startTime;
var start = new Date(startString);
var endString = newDay+' '+endTime;
var end = new Date(endString);
var calendar = CalendarApp.getCalendarById('');
var event = calendar.createEvent(eventName, start, end);
event.setDescription('Set by: '+user+'\n\nItems Needed:\n'+itemsList);
//send emails about event and send and invitation to the event
for (var i = 0 ; i < emailArray.length; i++){
MailApp.sendEmail(toEmail, subject, body);
function testSubmit(){
var e = {values: ['3/16/2017 17:25:46', 'Test',' 3/18/2017', '9:00:00 AM', '10:00:00 AM', 1, 'MPH', 'Projector', 'Sound', 'Chairs', ,'']}
function getEmail(role, emails){
Logger.log('Getting email for '+role);
for (var i = 0; emails.length; i++){
if (role == emails[i][0]){
Logger.log('Email Found: '+emails[i][1]);
return emails[i][1];
Logger.log('Email not Found');
return '';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.