Post Google Spreadsheet submissions as new Trello cards

postToTrello.gs

// Stored variables for POST request to Trello API
// An app authorization token needs to be requested via the URL:
// https://trello.com/1/authorize?key=YOUR_APP_KEY&name=APP_NAME&scope=read,write&expiration=never&response_type=token
var API_KEY = yourKeyHere;
var TOKEN = yourAuthTokenHere;
var ID_LIST = listToPostToHere;

// Collect data and post it as a Trello card
// Trigger set to run each time the spreadsheet has a new row added
function postToTrello(name, author, description, trelloIdList) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // Grab the range of data from the last row
  var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getValues();
  
  // vars to hold String data to post in the card
  for(var i = 0; i<data.length; i++) {
    var name = data[i][4] + " " + data[i][5];
    var bldg = data[i][6];
    var contact = "Email: " + data[i][1];
    var desc = data[i][7] + "\n\n" + data[i][8] + "\n\n" + "Availability: " + data[i][10];
  }
  
  // Build the data for the card.
  // @param name
  // @param due        all required parameters
  // @param idList
  var payload = {
    "name": name + ": " + bldg,
    "desc": desc + "\n\n" + contact,
    "pos": "top",
    "due": null,
    "idList": ID_LIST,
    "token": TOKEN,
    "key": API_KEY
  };
  var options = {
    "method": "POST",
    "payload": payload
  };
  // Post the payload to Trello
  try {
    var response = UrlFetchApp.fetch('https://trello.com/1/cards', options);
  } catch (e) {
    // Log any errors & send an email if it fails
    MailApp.sendEmail("bbennett@elkhart.k12.in.us", "postToTrell Failure", "Your script failed with an error response of: " + "\n\n" + e.message);
  }
  
  // Mark the status in the spreadsheet to "In Progress"
  sheet.getRange(sheet.getLastRow(), 3).activate().setValue("In Progress");
}