Skip to content

Instantly share code, notes, and snippets.

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