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.

Google Form to Trello

/*****************************************************************
 * 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);
  }
}