Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Conference Scheduler
function onSubmittedForm(e) {
var data = e.values; //gets the information in an arary, the last item is blank
Logger.log(data);
//open spreadsheet and get finance sheet
var thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var financeSheet = thisSpreadsheet.getSheetByName('Finances');
//put all the info into one array
var data2 = [];
for (var i = 0; i < data.length; i++){
data2.push(data[i]);
}
//and added new answers
for (var i = 0; i < 3; i++){
data2.push('No');
}
//added the information to the finance sheet
financeSheet.appendRow(data2);
teacherList(data);
}
//Takes a class name and returns an array of class information
//[0] Class Name, [1] Teacher, [2] Teacher Email, [3] Location
//var class = getClassInfo('className');
//var teacher = class[1];
function getClassInfo(className){
var ss = SpreadsheetApp.openById('1cRSmUZIZ92_h9OZqg6SvanT8rZLqA_18oh536pDqI6M');
var sheet = ss.getSheetByName('SessionInformation');
var data = sheet.getDataRange().getValues();
for (var i = 0; i<data.length; i++){
if (data[i][0] == className){
return data[i];
}
}
return [className, 'unknown class', 'unknown class', 'unknown class']
}
//Takes a class name and returns an object with class information
//Contains following properties: class.name, class.teacher, class.email, class.location
//var class = createClassObject('className');
//var teacher = class.teacher;
function createClassObject(className){
var ss = SpreadsheetApp.openById('1sZcS2efv6gYT61Ycp7SiHqnm2Gq6RKyEc2QVKEVIZ9U');
var sheet = ss.getSheetByName('SessionInformation');
var data = sheet.getDataRange().getValues();
for (var i = 0; i<data.length; i++){
if (data[i][0] == className){
var class = {name: data[i][0], teacher: data[i][1], email: data[i][2], location: data[i][3]}
return class;
}
}
var class = {name: className, teacher: 'N/A', email: 'N/A', location: 'N/A'}
return class;
}
//this example shows you how to call getClassInfo
function testClass(){
Logger.log(getClassInfo('cat'));
}
function test(){
var myArray = {values: ['3/10/2017 11:39:45', 'hinkle.macau@gmail.com', 'Hinkle Test', 'Aisabuja', 'An overview of Google Docs', 'Technology on a budget - Low cost options to teach technology','']}
var data = myArray.values;
onSubmittedForm(myArray);
}
function sendEmailUpdates(){
var sheet = SpreadsheetApp.getActive().getSheetByName('3Finances');
var table = sheet.getDataRange().getValues();
for (var i = 1; i < table.length; i++){
teacherList(table[i]);
}
}
function teacherList(data) {
var time = data[0];
var emailAddress = data[1];
var studentName = data[2];
var organization = data[3];
var className1 = data[4]
var className2 = data[5];
var paid = data[7];
if (paid !== 'Yes'){
Logger.log('Not Paid');
return;
} else {
Logger.log([emailAddress,studentName,organization,className1,className2]);
}
var class1 = createClassObject(className1);
var class2 = createClassObject(className2);
//var teacherSS = SpreadsheetApp.openById('1OzdbIv_K4_Kkk30HVJlK-kSAq8YwTzwh4PtOQix1vxs');
//var sheetData = [studentName,organization,emailAddress];
/*
try{
var sheet = teacherSS.getSheetByName(class1.name);
sheet.appendRow(sheetData);
}
catch(e){
var sheet = teacherSS.insertSheet(class1.name);
sheet.appendRow(sheetData);
}
try{
var sheet2 = teacherSS.getSheetByName(class2.name);
sheet2.appendRow(sheetData);
}
catch(e){
var sheet2 = teacherSS.insertSheet(class2.name);
sheet2.appendRow(sheetData);
}
*/
//Make schedule
var file = DriveApp.getFileById('1OKlKjKqC4MVK-kj2i0oy2A8X9uk5E35rktXspw5Aofc');
var folder = DriveApp.getFolderById('0B3QaE26f2r25S0R4X1JOS2hUTUU');
var newFile = file.makeCopy(studentName+' Updated FCT Conference Schedule', folder);
newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
var id = newFile.getId();
var doc = DocumentApp.openById(id);
var url = doc.getUrl();
var body = doc.getBody();
body.replaceText('{name}', studentName);
body.replaceText('session1.name', class1.name);
body.replaceText('session2.name', class2.name);
body.replaceText('session1.teacher', class1.teacher);
body.replaceText('session2.teacher', class2.teacher);
body.replaceText('session1.location', class1.location);
body.replaceText('session2.location', class2.location);
var footer = doc.getFooter();
var date = new Date();
var today = Utilities.formatDate(date, 'UTC+1:00', "MM-dd-yyyy");
footer.replaceText('{date}', today);
var body = "We have confirmed your registration for the FCT conference tomorrow.\nThere have been some changes to the room assignments. Please view your updated schedule.\n\nWe look forward to having you!\n\n"+url;
MailApp.sendEmail(emailAddress, 'Updated Schedule', body);
}
//append to classes
function createClassList(){
var sheet = SpreadsheetApp.getActive().getSheetByName('Finances');
var data = sheet.getDataRange().getValues();
Logger.log(data);
var newSpreadsheet = SpreadsheetApp.openById('1OzdbIv_K4_Kkk30HVJlK-kSAq8YwTzwh4PtOQix1vxs');
for (var i = 0; i < data.length; i++){
var name = data[i][2];
var email = data[i][1];
var org = data[i][3];
var paid = data[i][7];
var session1 = data[i][4];
var session2 = data[i][5];
if (paid == 'Yes'){
Logger.log(name);
try{
var sheet = newSpreadsheet.getSheetByName(session1);
sheet.appendRow([name, email, org]);
}
catch(e){
var sheet = newSpreadsheet.insertSheet(session1);
sheet.appendRow([name, email, org]);
}
try{
var sheet = newSpreadsheet.getSheetByName(session2);
sheet.appendRow([name, email, org]);
}
catch(e){
var sheet = newSpreadsheet.insertSheet(session2);
sheet.appendRow([name, email, org]);
}
}
}
}
function sendEmail(){
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet6')
var url = 'https://drive.google.com/file/d/0B3QaE26f2r25ZjFsR1hrTmhBTUNlSU1kdnhDalpVbnhEcVhZ/view?usp=sharing';
var message = 'We have prepared this pamphlet for you with more information about our program tomorrow.\n\
We are looking forward to having you!\n\n'+url;
var data = sheet.getDataRange().getValues();
MailApp.sendEmail('john.hinkle@aisabuja.com', 'Pamphlet', message)
for (var i = 0; i< data.length; i++){
var email = data[i][0];
MailApp.sendEmail(email, 'Pamphlet', message);
}
}
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.