Last active
August 11, 2018 07:29
-
-
Save peterfoxflick/767671089d661155ace7f4d0adde30fc to your computer and use it in GitHub Desktop.
This code takes a google form and makes a Trello card on each submit. It then creates a copy of a google sheet and folder. These are then linked to the Trello card and are partial filled out.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/***************************************************************** | |
* This code create a trello card for every Google Form that is | |
* filled out with information given on the form. Each trello | |
* card is linked to a sheet that resides in its own folder. | |
* for a live example see: https://trello.com/b/CQXMpx0u/demo-trello-card-create | |
* Author: Peter Fox Flick | |
****************************************************************/ | |
/***************************************************************** | |
* Trigger function for Google Forms "Form submit" event. | |
* Simple, assumes all answers are provided, no error checking. | |
****************************************************************/ | |
function onFormSubmit(e) { | |
if(e){ | |
// Get form Responses and sort it into variables | |
var formResponse = e.response; | |
var itemResponses = formResponse.getItemResponses(); | |
// All the info for Trello including APIkey and Token | |
var trello = { | |
apikey: '---', | |
token: '---', | |
templateCard: '---', // * Card that gets copied original but not always used | |
idList: '---', // * id list to copy onto | |
cardId: '', // Id of the card to be created, this is just a placeholder and will be filled out later | |
position: '' // Also a placeholder to be filled out later | |
} | |
// All the info from the responses and regarding the course | |
var data = { | |
name: "", // Proper name of everything, ex: CS 235 - SmithR | |
lastName: "", // Users last name | |
firstName: "", // Users first name | |
course: "", // course code ex: CS 234 | |
bsUrl: "", // Url for the BrightSpace course | |
bsCourseId: "", // Id of the brightspace course | |
college: "", | |
phone: "", | |
email: "", | |
} | |
// All the info for accessing google drive items | |
var gDrive = { | |
masterFolderId: '', // * Folder Id where everything lives | |
masterSheetId: '', // * Id of spreadsheet to copy | |
courseFolderId: '', // Id of the folder created - Placeholder | |
courseFolderUrl: '', // Url of the folder created - Placeholder | |
courseSheetId: '', // Id of the sheet created - Placeholder | |
courseSheetUrl: '', // Url of the sheet created - Placeholder | |
sheetName: '' // Name of sheet to alter on the sheet | |
} | |
// Scrub the data | |
getData(itemResponses, data, trello); | |
// Create Google Folder | |
createFolder(data, gDrive); | |
// Create Google Sheet | |
copySheet(data, gDrive); | |
// Create Trello Card and add Links | |
createCard(trello, data, gDrive); | |
// Fill out the sheet with data given in form | |
fillSheet(data, gDrive, trello); | |
//Add links on the trello card to the folder and sheet | |
addLink(trello, gDrive.courseFolderUrl, 'Google Drive'); | |
addLink(trello, gDrive.courseSheetUrl, 'Google Sheet'); | |
// If a Brightspace link was given add it to the trello card | |
if(data.bsUrl != "Unknown") { | |
addLink(trello, data.bsUrl, 'Brightspace'); | |
} | |
// Add default labels to the trello card | |
labels(trello, data, gDrive); | |
// Add members to the trello card (members) | |
addISR(trello, data); | |
Logger.log(data); | |
Logger.log(trello); | |
Logger.log(gDrive); | |
} | |
else | |
{ | |
// This was added for development and should not be needed in production | |
Logger.log("ERROR: no 'e'"); | |
} | |
} | |
/***************************************************************** | |
* Copy Sheet | |
* Takes the google master sheet and creates a copy in the | |
* Google master folder | |
****************************************************************/ | |
function copySheet(d, g){ | |
// get sheet | |
sheet = DriveApp.getFileById(g.masterSheetId); | |
copyInto = DriveApp.getFolderById(g.courseFolderId); | |
var courseSheet = sheet.makeCopy(d.name, copyInto); | |
g.courseSheetId = courseSheet.getId(); | |
g.courseSheetUrl = courseSheet.getUrl(); | |
} | |
/***************************************************************** | |
* Create Folder | |
* creates a folder and saves its id and url | |
****************************************************************/ | |
function createFolder(d, g){ | |
var masterFolder = DriveApp.getFolderById(g.masterFolderId); | |
var courseFolder = masterFolder.createFolder(d.name); | |
g.courseFolderId = courseFolder.getId(); | |
g.courseFolderUrl = courseFolder.getUrl(); | |
} | |
/***************************************************************** | |
* Clean URL | |
* Takes the brightspace URL and extracts the course id from it. | |
****************************************************************/ | |
function cleanUrl(url){ | |
// Search the content url first | |
if(url.search("content/") != -1){ // when on content page | |
var i = url.search("content/"); | |
url = url.substring(i+8, i+8+6); | |
} else if(url.search("home/") != -1){ //when on homepage | |
var i = url.search("home/"); | |
url = url.substring(i+5); | |
} else if(url.search("calendar/") != -1){ //when on calendar | |
var i = url.search("calendar/"); | |
url = url.substring(i+9, i+9+6); | |
} else if(url.search("le/") != -1){ //when on discussions page | |
var i = url.search("le/"); | |
url = url.substring(i+3, i+3+6); | |
} else if(url.search("ou=") != -1){ //when on quizzes page and assignments | |
var i = url.search("ou="); | |
url = url.substring(i+3, i+3+6); | |
} else if(url.search("awards/") != -1){ //when on awards | |
var i = url.search("awards/"); | |
url = url.substring(i+7, i+7+6); | |
} | |
return url; | |
} | |
/***************************************************************** | |
* Get Course Code | |
* Check the master spreadsheet and see if it has the course code | |
****************************************************************/ | |
function getCourseCode(id) { | |
if(id = "Unknown") | |
return ""; | |
var sheet = SpreadsheetApp.openById('---').getSheets()[0]; //This is the sheet with all the standardized courses in it. | |
var range = sheet.getRange('A:C'); | |
var data = range.getValues() | |
for ( var i=1; i<2888; i++) { | |
if(data[i][0] == id){ | |
return data[i][2]; | |
} | |
} | |
return ""; | |
} | |
/***************************************************************** | |
* Get Data | |
* Takes the user input and sorts it into that data var. | |
****************************************************************/ | |
function getData(r, d, t){ | |
d.lastName = r[0].getResponse(); | |
d.firstName = r[1].getResponse(); | |
d.email = r[2].getResponse(); | |
if(r[3]) { | |
d.phone = r[3].getResponse(); | |
} else { | |
d.phone = "Unknown"; | |
} | |
//Get course name and clean it up | |
d.course = r[4].getResponse(); | |
d.course = d.course.replace(/\W/g, ''); | |
firstDigit = d.course.match(/\d/) //will give you the first digit in the string | |
indexed = d.course.indexOf(firstDigit); | |
if(indexed > 0 && d.course.charAt(indexed - 1) != ' ') { | |
d.course = d.course.slice(0, indexed) + " " + d.course.slice(indexed); | |
} | |
if(r[5]) { | |
var url = r[5].getResponse(); | |
d.bsCourseId = cleanUrl(url); | |
d.bsUrl = "https://byui.brightspace.com/d2l/home/" + d.bsCourseId; | |
} else { | |
d.bsCourseId = "Unknown"; | |
d.bsUrl = "Unknown"; | |
} | |
if(r[6]) { | |
d.college = r[6].getResponse(); | |
} else { | |
d.college = "Unknown"; | |
} | |
if(r[7]) { | |
d.term = r[7].getResponse(); | |
} else { | |
d.term = "Unknown"; | |
} | |
//GET True Course if possible | |
var courseCode = getCourseCode(d.bsCourseId); | |
if(courseCode){ | |
d.course = courseCode; | |
} else { | |
d.course = d.course.toUpperCase(); | |
} | |
//Capitalize First and Last Names | |
d.lastName = d.lastName[0].toUpperCase() + d.lastName.slice(1); | |
d.firstName = d.firstName[0].toUpperCase() + d.firstName.slice(1); | |
var i = d.email.search("@"); | |
d.name = d.course + " - " + d.email.substring(0,i); | |
} | |
/***************************************************************** | |
* getCal(email to search) | |
* Check to see if the instructor has created an appointment and | |
* if they have it adds the time to the trello description | |
****************************************************************/ | |
function getCal(temail){ | |
var cals = CalendarApp.getCalendarById("----"); // Put the id of the Google calendar to search in here | |
var now = new Date(); | |
var oneMonth = new Date(now.getTime() + ( 1 * 30 * 24 * 60 * 60 * 1000)); | |
var calevents = []; | |
var events = cals.getEvents(now, oneMonth); | |
for(var i = 0; i < events.length; i++){ | |
var index = events[i].getDescription().indexOf('E-mail: '); | |
index += 8; | |
//Get the email of the teacher in the appointment | |
var email = events[i].getDescription().slice(index, index + 20); | |
index = email.indexOf('@'); | |
email = email.slice(0, index); | |
email = email.replace(/ /g, ''); | |
//Get the id of the appointment | |
var id = events[i].getId(); | |
index = id.indexOf('@'); | |
id = id.slice(0, --index); | |
//Get the URL of the acuity appointment | |
index = events[i].getDescription().indexOf('https://'); | |
var url = events[i].getDescription().slice(index); | |
index = url.search(/\n/); | |
url = url.slice(0,index); | |
// Check to make sure its the right event | |
var name = events[i].getTitle(); | |
index = name.indexOf('Faculty/TA Canvas Intro (Trainings)'); | |
if(temail == email && index == -1){ | |
return "\n" + "- [Acuity](" + url + ")\n" | |
+ "\nAppointment\n---\n" + events[i].getStartTime().toString(); | |
} | |
} | |
return false; | |
} | |
/***************************************************************** | |
* Create Card | |
* Creates a trello card and returns it's id. | |
****************************************************************/ | |
function createCard(t, d, g){ | |
var des = d.firstName + " " + d.lastName + "\n===\n" + | |
"Email: " + d.email + "\n" + | |
"Course: " + d.course + "\n" + | |
"Phone: " + d.phone + "\n" + | |
"College: " + d.college + "\n" + | |
"Links\n---\n" + | |
"- [Google Folder](" + g.courseFolderUrl + ")\n" + | |
"- [Google Sheet](" + g.courseSheetUrl + ")\n"; | |
if(d.bsUrl != "Unknown"){ | |
des += "- [BrightSpace Course](" + d.bsUrl + ")\n" ; | |
} | |
var index = d.email.indexOf("@"); | |
if(index > 0){ | |
var temail = d.email.slice(0,index); | |
var desend = getCal(temail); | |
if(desend){ | |
des += desend; | |
} | |
} | |
var url = 'https://api.trello.com/1/cards?'; | |
var trelloParams = { | |
name: d.name, // name of card | |
desc: des, // description inside of card | |
pos: 'bottom', // send all cards to the bottom | |
idList: t.idList, // id of the list to copy into | |
idCardSource: t.templateCard, // Id of the card to copy if you copy a card | |
key: t.apikey, | |
token: t.token | |
} | |
var options = { | |
method : 'post', | |
contentType: 'application/json', | |
payload : JSON.stringify(trelloParams) | |
}; | |
var trelloResponse = UrlFetchApp.fetch(url, options); | |
t.cardId = JSON.parse(trelloResponse.getContentText()).id; | |
} | |
/***************************************************************** | |
* Labels | |
* Run through the Labels that should be added based on some | |
* parameters. | |
****************************************************************/ | |
function labels(t, d, g) { | |
addLabel(t, "---"); | |
//Find if its a Foundation class | |
if(d.course.substring(0,2) == "FD") { | |
addLabel(t, "---"); | |
} | |
//Find if its a Foundation class | |
if(d.course.substring(0,5) == "FDREL") { | |
addLabel(t, "---"); | |
} | |
//Find if its 100 level course | |
if (d.course.substring(d.course.length - 3, d.course.length) < 200 ) { | |
addLabel(t, "---"); | |
} | |
//Find if its a standardized course | |
if(isStandard(d.course)){ | |
addLabel(t, "---"); | |
standardSheet(g); | |
} | |
//Find its term | |
if(d.term == "Fall 2018"){ | |
addLabel(t, '---'); | |
//find its canvas course | |
} else if (d.term == "Winter 2018") { | |
addLabel(t, '---'); | |
} | |
} | |
/***************************************************************** | |
* Add Label | |
* Adds a label to the trello card. | |
****************************************************************/ | |
function addLabel(t, l) { | |
//Creating the data to be sent to trello | |
var url = 'https://api.trello.com/1/cards/' + t.cardId + '/idLabels?value=' + l; | |
var trelloParams = { | |
key: t.apikey, | |
token: t.token | |
}; | |
//JSON options | |
var options = { | |
method: 'POST', | |
contentType: 'application/json', | |
payload: JSON.stringify(trelloParams) | |
}; | |
//Send attachment creation request | |
UrlFetchApp.fetch(url, options); | |
} | |
/***************************************************************** | |
* Is Standard | |
* Check the master spreadsheet and see if it has the course code | |
****************************************************************/ | |
function isStandard(id) { | |
var sheet = SpreadsheetApp.openById('----').getSheets()[0]; //ID of the sheet that has all of the standardized courses on it. | |
var range = sheet.getRange('B6:L'); | |
var data = range.getValues() | |
//Logger.log(data[0][0]); | |
//Logger.log(id); | |
var check = id; | |
var check = check.replace(/\s/g, ''); | |
for ( var i=0; i<100; i++) { | |
for (var j=0; j< 11; j++) { | |
//Logger.log("i = " + i + "j = " + j + " " + data[i][j]); | |
if(data[i][j].replace(/\s/g, '') == check){ | |
//Logger.log("FOUND IT"); | |
//Logger.log(data[i][j]); | |
return true; | |
} | |
} | |
} | |
return false; | |
} | |
/***************************************************************** | |
* Standard Sheet | |
* Change the spreadsheet to reflect it's a standardized course. | |
****************************************************************/ | |
function standardSheet(g){ | |
//Get the sheet | |
var sheet = SpreadsheetApp.openById(g.courseSheetId).getSheets()[0]; | |
//Change Background color | |
sheet.getRange('A1').setBackground("#EFA00B"); | |
//Add text to current title | |
sheet.getRange('A1').setValue(sheet.getRange('A1').getValue() + " - Standardized Course"); | |
} | |
/***************************************************************** | |
* Add Link | |
* Adds a link to the trello card as an attachment. | |
****************************************************************/ | |
function addLink(t, link, name) { | |
//Creating the data to be sent to trello | |
var addLinkUrl = 'https://api.trello.com/1/cards/' + t.cardId + '/attachments?'; | |
var trelloParams = { | |
name: name, // name of attachment | |
url: link, // URL to attach | |
key: t.apikey, | |
token: t.token | |
}; | |
//JSON options | |
var options = { | |
method: 'POST', | |
contentType: 'application/json', | |
payload: JSON.stringify(trelloParams) | |
}; | |
//Send attachment creation request | |
UrlFetchApp.fetch(addLinkUrl, options); | |
} | |
/***************************************************************** | |
* Add Tag | |
* Adds a tag to the Trello card. | |
****************************************************************/ | |
function addTag(t, tag) { | |
//Creating the data to be sent to Trello | |
var addTagUrl = "https://api.trello.com/1/cards/" + t.cardId + "/idLabels?"; | |
var trelloParams = { | |
value: tag, // tag Id to attach | |
key: t.apikey, | |
token: t.token | |
}; | |
//JSON options | |
var options = { | |
method: 'POST', | |
contentType: 'application/json', | |
payload: JSON.stringify(trelloParams) | |
}; | |
//Send attachment creation request | |
UrlFetchApp.fetch(addTagUrl, options); | |
} | |
/***************************************************************** | |
* Add Member | |
* Adds a member to the Trello card. | |
****************************************************************/ | |
function addMember(t, m) { | |
//Creating the data to be sent to Trello | |
var url = 'https://api.trello.com/1/cards/' + t.cardId + '/idMembers?value=' + m; | |
var trelloParams = { | |
key: t.apikey, | |
token: t.token | |
}; | |
//JSON options | |
var options = { | |
method: 'POST', | |
contentType: 'application/json', | |
payload: JSON.stringify(trelloParams) | |
}; | |
//Send attachment creation request | |
UrlFetchApp.fetch(url, options); | |
} | |
/***************************************************************** | |
* Fill Sheet | |
* Fill out some of the initial info on the spreadsheet. | |
****************************************************************/ | |
function fillSheet(d, g, t) { | |
//Get the sheet | |
var sheet = SpreadsheetApp.openById(g.courseSheetId).getSheets()[0]; | |
// C2 - Name of instructor | |
sheet.getRange('C2').setValue(d.lastName + ", " + d.firstName); | |
// C5 - Name | |
sheet.getRange('C5').setValue(d.name); | |
// C6 - BS ID | |
sheet.getRange('C6').setValue('=HYPERLINK("' + d.bsUrl + '","' + d.bsCourseId + '")'); | |
//Insert Instructors Email as givin | |
sheet.getRange('C12').setValue(d.email); | |
//Insert Phone number | |
sheet.getRange('C13').setValue(d.phone); | |
//Add Trello Card id | |
sheet.getRange('E3').setValue(t.cardId); | |
//Record the sheet in the master doc, this is later used | |
// to apply changes to every sheet created. | |
sheet = SpreadsheetApp.openById('----').getSheetByName('Sheet Ids'); | |
sheet.appendRow([SpreadsheetApp.openById(g.courseSheetId).getUrl() , d.name]) | |
} | |
/***************************************************************** | |
* Get Member Id | |
* Returns a member ID based on the college the course is in. | |
****************************************************************/ | |
function getMemId(d) { | |
if(d.college == "Agriculture and Life Sciences") { | |
//lorin | |
return "---"; | |
} else if (d.college == "Business and Communication") { | |
//peter | |
return "---"; | |
} else if (d.college == "Education and Human Development") { | |
//nate | |
return "---"; | |
} else if (d.college == "Language and Letters") { | |
//connor | |
return "---"; | |
} else if (d.college == "Physical Sciences and Engineering") { | |
//Byran | |
return "---"; | |
} else if (d.college == "Performing and Visual Arts" || d.college == "Office of Foundation and Interdisciplinary Studies") { | |
//Sam | |
return "---"; | |
} else { | |
return "---"; // If none are found give to Sam | |
} | |
} | |
/***************************************************************** | |
* Add ISR | |
* Add a ISR to a Trello card depending on its college. | |
****************************************************************/ | |
function addISR(t, d) { | |
var id = getMemId(d); | |
if(id){ | |
addMember(t, id); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment