Parent Teacher Conference Scheduler
function myFunction() { | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var sheetsArray = ss.getSheets(); | |
for (var k = 5; k < sheetsArray.length; k++){ | |
var maxRow = 2; | |
var sheet = sheetsArray[k]; | |
var row = 1; | |
for (var i = 8; i < 16; i++){ | |
var hour = i; | |
if (hour > 12){ | |
hour = hour-12; | |
} | |
for(var j = 0; j < 6; j = j +1){ | |
var minute = ':'+j; | |
var minute = minute+'0'; | |
row++; | |
sheet.getRange(row, 1).setValue("'"+hour+minute); | |
maxRow++ | |
} | |
} | |
sheet.getRange(maxRow, 1).setValue("'4:00"); | |
sheet.getRange(maxRow+1, 1).setValue("'4:10"); | |
sheet.getRange(maxRow+2, 1).setValue("'4:20"); | |
} | |
} | |
function createSheet(){ | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var data = sheet.getDataRange().getValues(); | |
for (var i = 0; i<data.length; i++){ | |
var sheetName = 'template'+((i%3)+1); | |
var templateSheet = ss.getSheetByName(sheetName); | |
ss.insertSheet(data[i][1], i+4, {template: templateSheet}); | |
} | |
} | |
function createForm(){ | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var form = FormApp.openById('1kSPrB47VtREpSq_sspmMOT9znNgmecSx_ceIyRsJ4PU'); | |
var sheetsArray = ss.getSheets(); | |
for (var i = 5; i < sheetsArray.length; i++){ | |
var sheet = sheetsArray[i]; | |
var name = sheet.getName(); | |
var data = sheet.getDataRange().getValues(); | |
var times = []; | |
for (var j = 0; j < data.length; j++){ | |
if (data[j][1].length < 1){ | |
times.push(data[j][0]); | |
} | |
} | |
Logger.log(name); | |
Logger.log(times); | |
var item = form.addListItem(); | |
item.setTitle(name); | |
item.setChoiceValues(times); | |
} | |
} | |
function testSubmit(){ | |
var object = {values: ['3/7/2017 10:54:29', , 'john.hinkle@aisabuja.com', 'John Hinkle', 'test', , , , , , , , , , , , , , , , , '8:40', , , , , , , , , , , , , , , , , , , , , , ]}; | |
onSubmittedForm(object); | |
} | |
function onSubmittedForm(e){ | |
//Get Responses from Sheet | |
var data = e.values; | |
Logger.log(data); | |
//Get Questions from Sheet | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var sheet = ss.getSheetByName('Form Responses 1'); | |
var names = sheet.getRange(1, 1, 1, data.length).getValues()[0]; | |
//Get the respondant answers | |
var scheduledTimes = []; | |
for (var i = 5; i < names.length; i++){ | |
var parentEmail = data[2]; | |
var parentName = data[3]; | |
var teacher = names[i]; | |
var time = data[i]; | |
var studentName = data[4]; | |
//If the response isn't empty | |
if (time && time.length > 1){ | |
Logger.log(time); | |
var teacherSheet = ss.getSheetByName(teacher); | |
var teacherData = teacherSheet.getDataRange(); | |
//See if the scheduled time is empty and push the schedule time or a notifcation to the array | |
scheduledTimes.push(findTime(teacher, time, parentEmail, parentName, studentName)); | |
} | |
} | |
//sort the array into chronilogical order | |
var scheduledTimes = sortArray(scheduledTimes); | |
//create new document for the parent (user) | |
var user = data[2]; | |
var file = DriveApp.getFileById('15hKLqD8zTrq_e94JuLVanjH63CETczuDtLZYe0ZHJ_U'); | |
var newFile = file.makeCopy('Parent Schedule for '+parentName); | |
newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW); | |
var id = newFile.getId(); | |
var doc = DocumentApp.openById(id); | |
var body = doc.getBody(); | |
//merge the data in | |
body.replaceText('{name}', parentName); | |
var message = ''; | |
for (var i = 0; i < scheduledTimes.length; i++){ | |
message = message + scheduledTimes[i][1]+' - '+scheduledTimes[i][0]+'\n\n'; | |
} | |
body.replaceText('{Please wait while schedule is created}', message); | |
var footer = doc.getFooter(); | |
var today = new Date(); | |
var formattedDate = Utilities.formatDate(new Date(), "UTC+01:00", "yyyy-MM-dd"); | |
Logger.log(formattedDate); | |
footer.replaceText('{date}', formattedDate); | |
//and send the data to the user | |
doc.addViewer(user); | |
var url = doc.getUrl(); | |
var contactMessage = 'If there is an error or you need to reschedule a time, please contact chinwe.obinwanne@aisabuja.com' | |
var shareUrl = 'You can view printable copy of your schedule here:\n'+url+'\n\n'; | |
MailApp.sendEmail(user, | |
'Schedule for Partnership Conference', | |
'Thank you for signing up for the March 30 partnership conference. Your schedule is as follows:\n\n'+message+shareUrl+contactMessage); | |
//then update the form removing the scheduled times | |
updateForm() | |
} | |
//Searches the spreadsheet for the teacher to double check the time is available returns an array | |
function findTime(teacher, time, parentEmail, parentName, student){ | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var sheet = ss.getSheetByName(teacher); | |
var data = sheet.getDataRange().getValues(); | |
for (var i = 0; i<data.length; i++){ | |
if (data[i][0] == time){ | |
//If the time slot is open sends a back the the parent name, teacher email, and the index for sorting | |
if (data[i][1].length < 1){ | |
sheet.getRange(i+1, 2).setValue(parentName); | |
sheet.getRange(i+1, 3).setValue(parentEmail); | |
sheet.getRange(i+1, 4).setValue(student); | |
Logger.log(['findTime',teacher,time]) | |
var email = findTeacherEmail(teacher); | |
addToCalendar(parentName, email, i); | |
return [teacher, time, i]; | |
} | |
//If the time slot is booked, emails a error message to the teacher and replaces the time with an error message with directions | |
else{ | |
var email = findTeacherEmail(teacher); | |
MailApp.sendEmail(email,'Double Booked', 'The '+time+' timeslot '+parentEmail+' requested for '+teacher+' is already booked. Please schedule another time that works.'); | |
return [teacher, 'Your requested timeslot has been is not available, please email '+email+' for another available slot.',i]; | |
} | |
} | |
} | |
//If teacher is not found, returns an error message | |
return [teacher, 'Unknown Teacher',99] | |
} | |
//Returns the teachers email or mine if it cannot find one | |
function findTeacherEmail(teacher){ | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var sheet = ss.getSheetByName('email'); | |
var data = sheet.getDataRange().getValues(); | |
for (var i = 0; i<data.length; i++){ | |
if (data[i][0] == teacher){ | |
return data[i][1]; | |
} | |
} | |
return 'john.hinkle@aisabuja.com'; | |
} | |
//updates the form to remove booked times | |
function updateForm(){ | |
var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o'); | |
var form = FormApp.openById('1kSPrB47VtREpSq_sspmMOT9znNgmecSx_ceIyRsJ4PU'); | |
var items = form.getItems(); | |
for (var i = 2; i < items.length; i++){ | |
var choices = [] | |
var item = items[i]; | |
Logger.log(item.getTitle()); | |
var sheet = ss.getSheetByName(item.getTitle()); | |
if(!sheet){ | |
Logger.log(item.getTitle()); | |
continue; | |
} | |
var data = sheet.getDataRange().getValues(); | |
for (var j = 0; j<data.length; j++){ | |
if (data[j][1].length<1){ | |
choices.push(data[j][0]); | |
} | |
} | |
item.asListItem().setChoiceValues(choices); | |
} | |
} | |
//Returns array sorted by chronilogical order | |
function sortArray(myArray){ | |
var newArray = []; | |
while (myArray.length > 0){ | |
var lowest = 100; | |
var lowestArray = []; | |
var index = 0 | |
for (var i = 0; i < myArray.length; i++){ | |
if (myArray[i][2] < lowest){ | |
lowest = myArray[i][2]; | |
lowestArray = myArray[i]; | |
var index = i; | |
} | |
} | |
newArray.push(lowestArray); | |
myArray.splice(index, 1); | |
} | |
Logger.log(newArray) | |
return newArray; | |
} | |
//Adds the 10 minute conference to the teacher's schedule | |
function addToCalendar(parentName, teacherEmail, timeInt){ | |
var march30 = 1490857200000; | |
var timeToAdd = 600000 * (timeInt-1); | |
var startTime = march30+timeToAdd | |
try { | |
var calendar = CalendarApp.getCalendarsByName(teacherEmail)[0]; | |
calendar.createEvent(parentName, new Date(startTime), new Date(startTime+600000)) | |
} | |
catch(e){ | |
Logger.log(e) | |
} | |
} | |
function changeTime(){ | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var data = sheet.getRange(2, 3, 28, 1).getValues(); | |
Logger.log(data); | |
for (var i = 0; i < data.length; i++){ | |
var text = 'March 22 ' + data[i][0] | |
sheet.getRange(i+2, 3).setValue(text); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment