Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/schedule.gs Secret
Created Sep 3, 2017

Embed
What would you like to do?
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
You can’t perform that action at this time.