Friday, August 3, 2012

JSON to Google Docs for Trello

I want to have all Trello data into a Google Docs sheet.
So I wrote this (and with copying snippets from here and there):


// Arnold P. Siboro
// August 2012

   
function myFunction() {
  //blabla
  throw 'Congratulations, you have successfully installed XXX ';
}
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  //sheet.setActiveCell()
 
  //var object = getJSONdata();
  var object = getTrelloJSONdata();
 
  sheet.clear();

 
  for (var i=0; i    sheet.getRange(i+1,1).setValue(object.cards[i].name);
    sheet.getRange(i+1,2).setValue(object.cards[i].desc);
    //throw object.checklists.length;

  checklists="";
  checkItems="";
    for (var j=0; j     
      //throw object.cards[i].idChecklists.length;
      for (var k=0; k     
        if(object.checklists[j].id==object.cards[i].idChecklists[k]) {
          //throw object.checklists[j].name+" "+object.checklists[j].id+" "+object.cards[i].idChecklists[k];
          checklists=checklists + "["+object.checklists[j].name +"]\n";
          //throw checklists;
          //throw object.checklists[j].checkItems.length;
          for (var l=0; l            checklists=checklists+(l+1)+") "+object.checklists[j].checkItems[l].name+"\n";
          }
          checklists=checklists+"\n";
        }
      }
      //throw checklists;
    }
    sheet.getRange(i+1,3).setValue(checklists);
    //throw object.lists.length;
   
    lists="";
    for (var j=0; j      if(object.lists[j].id==object.cards[i].idList) {
        lists=lists + object.lists[j].name + "\n";
        //throw lists;
      }
      //throw lists;
    }
    sheet.getRange(i+1,4).setValue(lists);

    actions="";
    for (var j=0; j      if(object.actions[j].data.card)
        if(object.actions[j].data.card.idShort==object.cards[i].idShort) {
          if(object.actions[j].type="commentCard" && object.actions[j].data.text) actions=actions + object.actions[j].data.text + "\n";
        //throw actions;
        }
      //throw actions;
    }
    sheet.getRange(i+1,5).setValue(actions);
   
  }
 //throw object.cards.toSource();
 //throw object.cards.length;

}

//not used now, as this does not handle login into Trello
function getJSONdata() {
  var url = SERVER_BASE_URL
  var payload = "";
  var response = UrlFetchApp.fetch(url, {method:'get', payload:payload, contentType:"application/x-www-form-urlencoded"});
  if (response.getResponseCode() == 200) {
    return JSON.parse(response.getContentText());
   
  }
}
function getTrelloJSONdata() {
  //Use OAuth and Trello's API to automatically login

     
    var oauthConfig = UrlFetchApp.addOAuthService("trello");
  oauthConfig.setAccessTokenUrl("https://trello.com/1/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://trello.com/1/OAuthGetRequestToken");
  oauthConfig.setAuthorizationUrl("https://trello.com/1/OAuthAuthorizeToken");
  // Replace these with the values you get from
  // https://trello.com/1/appKey/generate
  oauthConfig.setConsumerKey("");
  oauthConfig.setConsumerSecret("");
  var requestData = {
    "method": "GET",
    "oAuthServiceName": "trello",
    "oAuthUseToken": "always"
  };
  /*
  var result = UrlFetchApp.fetch(
      "https://api.trello.com/1/members/me/boards",
      requestData);
  */
 
  //And then get the whole JSON data
   var result = UrlFetchApp.fetch(
  "https://api.trello.com/1/boards/?actions=all&actions_limit=1000&cards=all&lists=all&members=all&member_fields=all&checklists=all&fields=all",
      requestData);
 
  //throw result.getContentText();
  return JSON.parse(result.getContentText());
}

function authorizeToTrello() {
  var oauthConfig = UrlFetchApp.addOAuthService("trello");
  oauthConfig.setAccessTokenUrl("https://trello.com/1/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://trello.com/1/OAuthGetRequestToken");
  oauthConfig.setAuthorizationUrl("https://trello.com/1/OAuthAuthorizeToken");
  // Replace these with the values you get from
  // https://trello.com/1/appKey/generate
  oauthConfig.setConsumerKey("");
  oauthConfig.setConsumerSecret("");
  var requestData = {
    "method": "GET",
    "oAuthServiceName": "trello",
    "oAuthUseToken": "always"
  };
  var result = UrlFetchApp.fetch(
      "https://api.trello.com/1/members/me/boards",
      requestData);
  Logger.log(result.getContentText());
}