Quickest way to integrate Google Sheet and Trello to make reports

We needed to submit a report of tasks done during a set period of time.

Of course the Google Sheet was the best option to share, but Trello Free version won’t let you export a list or cards at all. You may opt for plugins, but well, Free Trello.

Technically, all you need is a JSON Parser, but using third party apps and sharing data was not an option. It must travel from TRELLO to Google, no one else involved.

Here is a Free Simple solution I came up with scribbling some Google Script Code.

The Trello JSON API is very easy to use. Go and Grab your key here:

https://trello.com/app-key (Make sure you are logged in before going to this URL.)

Once you got the key, you can generate the token, right next to it.

Go and create a new Google Sheet.

Click Tools > Script Editor.

Copy this code:

It is basically Parsing JSON data to Google Sheet. 

function onOpen() {
    addMenuOption();
}

function addMenuOption() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('MY CUSTOM MENU')
        .addItem('UPDATE REPORT', 'getTaskReport')
        .addSeparator()
        .addToUi();
}
//Refresh The Report by calling Trello and setting up the data.//
function getTaskReport(){
    var trelloAPIKey = 'YOUR TRELLO API KEY';
    var trelloToken = 'ADD YOUR TRELLO TOKEN HERE';
    var json = getTrelloCards('YOUR LIST ID TO PULL',trelloAPIKey, trelloToken);
    var ui = SpreadsheetApp.getUi();
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Report'); //This is the sheet where you will see the results.
    dataSheet.clear();
    setHeaders(dataSheet,json, 1, 1);
    setJsonData(dataSheet,json,2,1);
}

//Get Trello Board//
//Returns a list of boards for the account// name,id
//Maybe in future
function getTrelloBoards(accountId, key,token){

}

//Get Trello Lists for a given Board//
//Returns a list of all the lists in the given board// name,id
//Maybe in future
function getTrelloLists(boardId,key,token){
}

//Get Trello Board Data//
//Returns all the lists and cards for the given board id.
//Maybe in future//
function getTrelloBoardData(boardId,key,token){
}

//Get the Json Data from Trello. Call the API.
function getTrelloCards(listId,key,token) {
    var response = UrlFetchApp.fetch("https://api.trello.com/1/lists/"+listId+"/cards?key="+key+"&token="+token+"&fields=id,name,url,desc");
    var text = response.getContentText();
    return JSON.parse(text);
}

//Set the Header for the rows based on the Trello Card or Board that you pulled the data for.
function setHeaders(dataSheet, json, dataRow, dataColumn) {
    var headers = Object.keys(json[0]);
    for (header in headers) {
        dataSheet.getRange(dataRow, dataColumn).setValue((headers[header])).setWrap(true);
        dataColumn++;
    }
}

//Set the Actual JSON Data//
function setJsonData(dataSheet, json, dataRow, startColumn) {
    for (var key in json) {
        dataColumn = startColumn;
        for (var value in json[key]) {
            dataSheet.getRange(dataRow, dataColumn).setValue((json[key][value])).setWrap(true);
            dataColumn++;
        }
        dataRow++;
    }
}

All you need to do is set the List you consider as Done Cards.

Pull the report and Archive all the cards.

A quick line of code can archive the cards right from here. That’s for some one to add comment.

What Next: Archive Cards on Report Run.

Setup to run the report at specified time and send an email to you And your manager.