CONNECTING TO TRELLO THROUGH GOOGLE APP SCRIPTS

Re-posted from old site. Original post: August 9, 2015

My game team requested that I make a way to connect a Google spreadsheet of tasks to Trello so cards could be made based on the data from the spreadsheet. This took a lot of tinkering and researching/learning the Trello API. In this post however I’m going to cover setting up the authorization code you need to start receiving and sending data to Trello. In future posts I can go into more detail about the full system I created.

Step 1: Create a Google App Script Project
See Step 1 from my previous post.

Step 2: Add the Open Authorization Library OAuth1
So in order to connect to the Trello API you need to use the OAuth1 library. In the past you could use the UrlFetchApp, but Google has since depricated the OAuth portions of this class. So to do this follow these directions in the Google libraries guide and use the library project key Mb2Vpd5nfD3Pz-_a-39Q4VfxhMjh3Sh48.

Step 3: Create an OAuth Service Object
The service object is what we use to communicate with the Trello API. You will use its fetch function combined with a specific URL (and if sending data a JSON object is used as well) to tell the Trello API what you want to do. You can remake this service object over and over again or pass it around among your functions.

Below is what the code should look like:

///////////////////////////////////////////////////////////
// Name:   createOAthService_Trello                      //
// Desc:   Uses OAuth1 class to setup a connection to    //
//         Trello                                        //
// Params: None                                          //
// Return: the object to send/recieve data through       //
///////////////////////////////////////////////////////////
function createOAthService_Trello() {
  
  var service = OAuth1.createService("trello");
  service.setAccessTokenUrl("https://trello.com/1/OAuthGetAccessToken");
  service.setRequestTokenUrl("https://trello.com/1/OAuthGetRequestToken");
  service.setAuthorizationUrl("https://trello.com/1/OAuthAuthorizeToken?scope=read,write");
  service.setPropertyStore(PropertiesService.getScriptProperties());
  service.setCallbackFunction('authCallback_Trello');
  service.setConsumerKey(consumerKey);
  service.setConsumerSecret(consumerSecret);
 
  return service;
}

There are two things here though that you will need to get from Trello through your account. They are the Consumer Key and the Consumer Secret.  To get these log into Trello, and then visit https://trello.com/1/appKey/generate.

One of these lines of code sets a Callback function. Therefore you’ll need to make a Callback function as well otherwise it will give you errors when you run your program. Here is what my Callback function looks like:

///////////////////////////////////////////////////////////
// Name:   authCallback_Trello                           //
// Desc:   Callback function for when user needs to      //
//         authorize application with Trello             //
//         (the contents are not specific to Trello)     //
// Params: None                                          //
// Return: HTML message of succes or failed              //
///////////////////////////////////////////////////////////
function authCallback_Trello(request) {
  
  var service = createOAthService_Trello();
  var isAuthorized = service.handleCallback(request);
  
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this page.');
  } 
  else {
    return HtmlService.createHtmlOutput('Denied. You can close this page');
  }
}

Also note this function calls the previous function we made createOAthService_Trello, this is fine and shouldn’t create any issues with recursion.

Step 4: Using Your Service Object
At this point you have the code you need to establish a connection, but before we can have our program interacting fully we need to run it once to get the Authorization message from Trello. (Basically it says that you allow this application to use your account for 30 days). I packaged this into a function so it’s easy to just call at the beginning of my program.

///////////////////////////////////////////////////////////
// Name:   checkServiceAccess                            //
// Desc:   Sets up access to Trello and ensures this app //
//         has authorization                             //
// Params: None                                          //
// Return: the service to send and recieve data through  //
///////////////////////////////////////////////////////////
function checkServiceAccess(){
  var service = createOAthService_Trello();
  
  if(service.hasAccess()){
    return service;
  }
  else {
    var authorizationUrl = service.authorize();
    MailApp.sendEmail(emailAddress, 'App Needs Authorization (Trello)', 'Please visit the following URL and then re-run the script: ' + authorizationUrl);
    Logger.log('Please visit the following URL and then re-run the script: ' + authorizationUrl);
  }
}

This way it will check whether or not I have access to Trello. If I do it will send me back the service object so I can use it otherwise it will log the URL I need to go to in order to authorize my application (I also have it email me since I use these functions via triggers so in that case I won’t be able to access the Logger). So if I use this system for the next 8 months of our project class, once a month I’ll have to go and re-authorize the app (which is unavoidable according to what I’ve read from the Trello API).

The link usually brings you to a screen that looks like this:

If you are running your program for the first time you might also have to go through Google’s authorization (it’s pretty standard with Google App Scripts and will automatically pop up).

Step 5: Start Receiving Data from Trello
So now we are all connected and just need to start asking Trello to send us things (I’ll cover telling Trello to create things in a different post).

Below is a sample of how to use the service object’s fetch method to receive data.

///////////////////////////////////////////////////////////
// Name:   getBoardsInOrganization                       //
// Desc:   Gets all the boards in an organization and    //
//         puts them into an array                       //
// Params: service - object to send/recieve data through //
//         orgName - name of the organization to get the //
//                   boards from                         //
// Return: an array of board name/id pairs               //
///////////////////////////////////////////////////////////
function getBoardsInOrganization(service, orgName){
 
  // Get the list of boards (and their info) from Trello
  var url  = "http://api.trello.com/1/organizations/" + orgName + "/boards";
  var raw  = service.fetch(url);
  var data = JSON.parse(raw);
  Logger.log(data);
  
  // Make an object pair of board name and id and add to array of boards  
  return makeArrayFromData(data, ArrayType.eBOARD);
}

The basic concept is that depending on what information you want you use a specific URL. Then you call service.fetch(URL) and that should give you back a string. Use the JavaScript JSON parser to put it all in an object. Logging this data will allow you to see exactly what you get so you can then go on to access it though something like data.boards (which is an array) and loop through all the boards in the organization.

Here is a list of some URLs you may find useful:

//  Get the boards in an organization                : "http://api.trello.com/1/organizations/" + orgName + "/boards"
//  Get all the lists on a specific board            : "http://api.trello.com/1/boards/" + boardId + "/lists"
//  Get all the cards on a specific board            : "http://api.trello.com/1/boards/" + boardId + "/cards"
//  Get all the cards on a specific list             : "http://api.trello.com/1/lists/" + listId + "/cards"
//  Get a specific card (can access checklists here) : "http://api.trello.com/1/cards/" + cardId
//  Get all the members of a specific board          : "http://api.trello.com/1/boards/" + boardId + "/members"
//  Get a specific board (can access labels here)    : "http://api.trello.com/1/boards/" + boardId
 

So that’s it. That should get you started. If you have any questions feel free to leave a comment.
I would also like to credit a fellow DigiPen student (now alumn) Riley Pannkuk. His blog post helped me get a good starting point. A link to that post is here.

Also on a side note you can use this setup to access other things as well. One example is Twitter. You just need to know the correct links for the OAuth1.createService stage.

CONNECTING GITHUB AND GOOGLE APP SCRIPTS

Re-posted from old site. Original post: June 28, 2015

About a month ago I began to make a system using Google App Scripts to help my teammates log the tasks they worked on during the week. One method was using a Google Form, but my teammates wanted to be able to skip the form and just tag commit messages in order to log their tasks. This meant I had to connect our repository on GitHub to my Google App Scripts. I quickly realized there isn’t a lot of documentation on how to do that. So I decided that once I figured it out I’d share, so here it goes.

Step 1: Making a Google App Script
Pick a document, form, spreadsheet, or some other Google file in your Drive to be the base of where your scripts will live. (I attached mine to my Google Form so that everything was in one place.)

You access the Script Editor via the Tools tab in your Google file. If this is the first time you are editing a script on a file a prompt will show up. Choose “Blank Project”. This will set you up with a file called Code.gs and it should contain a function called myFunction.

Step 2: The doPost Function
In order to receive data from GitHub you need to have a function called doPost. There are two functions that get used when making a Web App to handle data they are doGet and doPost. doGet is for when you want to intercept HTTP GET Requests and doPost is when you want to intercept HTTP POST Requests. Since we will be getting a POST Request from GitHub we want to use doPost. Therefore add the following to your script:

// Required to receive data from GitHub
function doPost(data) {
  GitHubGrab(data);
}

“data” will be holding the JSON data you will receive from GitHub. In my case I pass it along to a function called GitHubGrab. You can just edit your myFunction that was provided to take “data” as a parameter and put it in place of my GitHubGrab function.

Step 3: Turning your script into a Web App
In order for your script to be accessible by GitHub it has to be made into a Web App. To do this go to the Publish tab and select “Deploy as web app..”

It might prompt you to name your project but then it should bring up a couple settings you will need to change. The first being the Project version. If you make new changes to your script and want them to be reflected in your Web App (most of the time you do) ALWAYS select New as your version (it’s usually not the defaulted choice). Otherwise it doesn’t update the code being run from the Web App.

Next it will ask you “Execute the app as:”. What you choose here might also depend on what you are trying to do but if you are editing your own files just select “Me: yourEmail@gmail.com”. The last thing is asking “Who has access to this app:”. Set this to “Anyone, including anonymous”.

There are three options. Here are what they mean:

• Only myself – only you can run the app

• Anyone – anyone with a Google account and is logged in can run the app

• Anyone, even anonymous – anyone even without a Google account can run the app

GitHub isn’t a person so we don’t want it to need to login in order to run our app. That is why we choose the third option. Once you hit Update you will be provided with a link. If you were to go to this link it will run your Web App. We want to save this link.

Step 4: Getting GitHub to use your Web App
If you go to your repository on GitHub and go to the sidebar. Choose Settings. On this page there should be a box of Options. Click on “Webhooks & Services”.

Click “Add webhook”. The first box is for the Payload URL. This is where you put the link to your Web App. Also leave the Content type box as application/json. A little farther down you can choose which kind of events trigger your Webhook. For the basics just ensure “Commit comment” and “Push” are selected. Ensure “Active” is checked and click “Add Webhook” or “Update Webhook”.

If you scroll down to the bottom of the page you will see a box title “Recent Deliveries”. You may have a red icon next to the first one listed. This means it was unsuccessful.

If it is green this means it was successful. If you’ve followed the steps until now it should be green. Once you start filling in your functions and expanding your Web App you may encounter more errors.

Step 6: Debugging Delivery Info
One of the nice things about the Webhook setup is that you can Redeliver a packet. That way you don’t have to wait for someone to commit/push something or push constantly just for testing. If you need a specific type of commit message I recommend sending a test commit and then just using the Redeliver button on that commit.

The Request tab can give you some info about how the Webhook ran along with what the Payload looked like. It sends all the data in JSON so if you are looking for something specific you can just sample your commit Payload and later I’ll show how to access that in your script.

The Response tab also has helpful data. Here you can see if your Web App sent back any errors. If you go down to the Body section and look towards the bottom it will usually mention an error your script encountered like if something was undefined. If there were no errors it will look something like this:

GitHub can show your Webhook as a success even if you had errors. So it’s always good to go and check the Body section.

Step 7: Using the Payload in Your Scripts
So now you have GitHub running your Web App, but how do you access what it’s sending you. For this you will be using JavaScript’s functions for parsing JSON.

// Get the data sent from GitHub
var jsonString = data.postData.getDataAsString();
// Turn it into a JSON object so it can be accessed easier
var payload = JSON.parse(jsonString); 

The lines above take the data and turn it into a string then parses that string into a JSON object. Now you can access members as if they were members of a struct or class. For example:

// Grab Commit Message
var origMessage = payload.head_commit.message;

Now you can treat the origMessage variable as a string and parse out the things you do and don’t want.

That is the basics for connecting your Google App Scripts and GitHub repository. From here you can do whatever you want with the data like append it to a file or email it out to someone.