Deprecated: Calling get_class() without arguments is deprecated in /var/www/html/wp-includes/class-wp-http.php on line 329 Quickest way to integrate Google Sheet and Trello to make reports – Saurabh Gandhe
Saurabh Gandhe

Quickest way to integrate Google Sheet and Trello to make reports

Saurabh
google sheet and trello integration

Quickest way to Integrate Google Sheet and Trello

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.

Exit mobile version