This is a Google Apps Script Engine that imports data from Google Spreadsheet to Trello. This script was used to import SoftShake'13 conference submissions from the spreadsheets to Trello. So, it's highly specific for SoftShake and it's one-off thing. Still, may be used as a base for more complex things. The conference had 2 spreadsheets, for Fr…

Spreadsheet-to-Trello

/**
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function uploadTracksToTrello() {

  // Security settings
  // Requesting token: https://trello.com/1/authorize?key=your app key&name=softshake+upload&expiration=never&response_type=token&scope=read,write
  ScriptProperties.setProperty("appKey", "your app key");
  ScriptProperties.setProperty("token", "your token");

  // EN/FR specific parameters
  var commonBoardId, 
      language, 
      titleColumnHeader, 
      firstNameColumnHeader, 
      lastNameColumnHeader, 
      emailColumnHeader;
  
  var spreadSheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  if (spreadSheetName == "Soft-shake 2013 - Call for paper"){
    ScriptProperties.setProperty("cardColor", "blue");
    commonBoardId = "51e1d47947e06e3654003631";
    language = "EN";
    titleColumnHeader = "Title";
    firstNameColumnHeader = "First Name";
    lastNameColumnHeader = "Last Name";
    emailColumnHeader = "Email";
  }
  else if (spreadSheetName == "Soft-shake 2013 - Appel à contribution"){
    ScriptProperties.setProperty("cardColor", "green");
    commonBoardId = "51e1d461f6480cff2e002418";
    language = "FR";
    titleColumnHeader = "Titre";
    firstNameColumnHeader = "Nom";
    lastNameColumnHeader = "Prénom";
    emailColumnHeader = "Mail";
  }
  else{
    Logger.log("Unknown document name");
  }
  
  // spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var startRow = 2;
  var endRow = sheet.getLastRow();
  var totalColumns = sheet.getLastColumn();

  // Finding column indexes
  var firstRowData = sheet.getRange(1, 1, 1, totalColumns).getValues()[0];
  
  var trackColumnIndex = firstRowData.indexOf("Track");
  var titleColumnIndex = firstRowData.indexOf(titleColumnHeader);
  var descriptionColumnIndex = firstRowData.indexOf("Description");
  var firstNameIndex = firstRowData.indexOf(firstNameColumnHeader);
  var lastNameIndex = firstRowData.indexOf(lastNameColumnHeader);
  var emailIndex = firstRowData.indexOf(emailColumnHeader);

  // Loading main board (lists and tracks) and all track boards
  var commonBoard = getCompleteBoard(commonBoardId);
  var trackBoards = [];
  var boardsAndTracksLookup = getTrackToBoardLookup();
  for (var i = 0; i < boardsAndTracksLookup.length; i++){
    var board = getCompleteBoard(boardsAndTracksLookup[i].board);
    trackBoards.push(board);
  }
  
  // storing in one array all the cards from all track boards
  var cardsFromAllTracks = [];
  for (var i = 0; i < trackBoards.length; i++){
    cardsFromAllTracks = cardsFromAllTracks.concat(trackBoards[i].cards);
  }
  
  var rows=sheet.getRange(startRow, 1, endRow-startRow+1, totalColumns).getValues();

  for (var i = 0; i < rows.length; i++){
    var rowData = rows[i];
    var title = rowData[titleColumnIndex].trim();
    Logger.log("Processing talk: " + title);
    
    var description = rowData[descriptionColumnIndex];
    var track = rowData[trackColumnIndex];
    var author = "**" + rowData[firstNameIndex] + " " + rowData[lastNameIndex] + " (" + rowData[emailIndex] + ")**\n\n";
    description = author + description;
    
    // find the card in submissions board. if missing, creating it.
    var cardFound = findCard(commonBoard.cards, title);
    if (cardFound === false){
      createCard(commonBoard, track, title, description);
    }

    // finding the board for track
    var trackBoardId = getBoardForTrack(track);
    var trackBoard;
    for (var j = 0; j < trackBoards.length; j++) {
      if (trackBoards[j].id === trackBoardId){
        trackBoard = trackBoards[j];
        break;
      }
    }
    
    // check that a talk with specific title exists in any of track boards
    // When not found we'll create the card in respective track board
    var cardFoundInOneOfTrackBoards = findCard(cardsFromAllTracks, title);
    if (cardFoundInOneOfTrackBoards === false){
      var listName = "Submitted talks (" + language + ")";
      createCard(trackBoard, listName, title, description);
    }
  }
};

function createCard(board, listName, title, description){
  var listId = findList(board.lists, listName);
  if (listId == "undefined"){
    var newList = createNewListInBoard(board.id, listName);
    board.lists.push(newList);
    listId = newList.id;
  }

  createTrelloCard(title, description, listId);
}

function createTrelloCard(cardName, cardDesc, listID){
  // limiting the length of description as it throws an error
  // Error is caused by the Google's App Script engine not accepting URLs longer than 2048 chars
  var cutDescriptionAt = 1000;
  var description = cardDesc;
  if (cardDesc.length > cutDescriptionAt){
    description = cardDesc.slice(0, cutDescriptionAt) + "...";
    Logger.log("Description too long. Title: " + cardName);
  }

  var url = constructTrelloURL("cards") + "&name=" + encodeURIComponent(cardName) + "&desc=" + encodeURIComponent(description) + "&idList=" + listID + "&labels=" + ScriptProperties.getProperty("cardColor");
  try{
    var resp = UrlFetchApp.fetch(url, {"method": "post"});
  }
  catch(e){
    Logger.log("Failed to create card: " + cardName);
    Logger.log(e);
    return;
  }

  // whole description has been posted?
  if (description.length == cardDesc.length){
    return;
  }
  
  // If description is too long will add the rest of it as a comment to the card.
  var response = Utilities.jsonParse(resp.getContentText());
  var cardId = response.id;
  var comment = "*(descripton continued)*\n\n..." + cardDesc.slice(cutDescriptionAt, cardDesc.length);
  createNewComment(cardId, comment);
}

function createNewComment(cardId, comment){
    var url = constructTrelloURL("cards/"+ cardId + "/actions/comments") + "&text=" + encodeURIComponent(comment);
    var resp = UrlFetchApp.fetch(url, {"method": "post"});
}

function findCard(cardList, title){
  for(var i = 0; i < cardList.length; i++){
      if (cardList[i].name === title)
        return true;
  }
  return false;
}

function findList(listList, listName){
  for(var i = 0; i < listList.length; i++){
      if (listList[i].name === listName)
        return listList[i].id;
  }
  return;
}

function getTrackToBoardLookup(){
  // Getting track's board from track's name.
  // The boards should be created before running the script and IDs copied in this structure
  var trackToBoardLookup = [
    { board : "51e1dd4a7c544a24540019e3", names : ["Agilité (Scrum, XP, Lean)", "Agile (Scrum, XP, Lean)"]  },
    { board : "51e1ea27d4ba1d1a6f0019a0", names : ["Incubateur (NoSQL, cloud, ...)", "Incubator (NoSQL, cloud, ...)"]  },
    { board : "51e1dd5e0a2f2ebf59003fb5", names : ["Microsoft", "Microsoft"]  },
    { board : "51e1ea45f5d09728050061a9", names : ["FunProg", "FunProg"]  },
    { board : "51e1df400e9aaaea37002480", names : ["Java (Scala, Groovy, ...)", "Java (Scala, Groovy, ...)"]  },
    { board : "51e1df684056f8b80a001a66", names : ["BigData", "BigData"]  },
    { board : "51e1ea07015991931e005130", names : ["Javascript", "Javascript"]  },
    { board : "51e1e9ed42f0718954006041", names : ["Ruby", "Ruby"]  },
    { board : "51e1ea6364c4575a1f003b64", names : ["Mobilité (iPhone, iPad, Android, ...)", "Mobility (iPhone, iPad, Android)"]},
    { board : "51ed07c42935727036000ad2", names : ["Gamification", "Gamification"]},
    { board : "51e1e97267dddcc033000d1a", names : [""]}
  ];

  return trackToBoardLookup;
}

function getBoardForTrack(trackName){
  var trackToBoardLookup = getTrackToBoardLookup();

  for (var i = 0; i < trackToBoardLookup.length; i++){
    if (trackToBoardLookup[i].names.indexOf(trackName) != -1)
      return trackToBoardLookup[i].board;
  }
  
  // when not found - return fallback board
  return "51e1e97267dddcc033000d1a";
}

function createNewListInBoard(boardId, name){
  var url = constructTrelloURL("boards/"+ boardId + "/lists") + "&name=" + encodeURIComponent(name);
  var resp = UrlFetchApp.fetch(url, {"method": "post"});
  return Utilities.jsonParse(resp.getContentText());
}

function getCompleteBoard(boardId)
{
  var url = constructTrelloURL("boards/" + boardId) + "&lists=open&cards=visible";
  var resp = UrlFetchApp.fetch(url, {"method": "get"});
  var values = Utilities.jsonParse(resp.getContentText())

  return values;
}

function constructTrelloURL(baseURL){
  return "https://api.trello.com/1/"+ baseURL +"?key="+ScriptProperties.getProperty("appKey")+"&token="+ScriptProperties.getProperty("token");
}

// Helper method, used to manually discover the IDs of boards
function listOrganizationBoards(){
  var url = constructTrelloURL("organizations/softshake13/boards");
  var resp = UrlFetchApp.fetch(url, {"method": "get"});
  var values = Utilities.jsonParse(resp.getContentText())

  Logger.log(values);
}


/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    { name : "Upload to Trello", functionName : "uploadTracksToTrello" },
  ];
  sheet.addMenu("Trello", entries);
};