Fetching Podio Data from Chrome apps spreadsheet (Javascript)


Posted on Mar 31st, 2016


We had a requirement of generating a report by fetching data from the Podio API. This looked interesting. Its amazing to see how much powerful spreadsheets can be. Below were our requirements.

1. Get authorization token from Podio.

2. Send a request to Podio and fetch records (500 max)

3. Keep sending requests untill we recieve all the records.

4. Format the data and push it to spreadsheet

This is what I came up with:

/*************************************** 
 * @Author: Abhishek Saha 
 * @Website: http://ajaxtown.com
 * @Desc: Fetching data from Podio from Chrome-apps
 ***************************************/
var main = function() {

	var ITEMS_PER_FETCH = 300;
	var SHEET_NAME = 'RAW DATA';

    var logic = {

        /* Add all the headers. This should match Podio headers */
        headers: ["Order", "Order type", "Order source", "Country", "Tags"],

        access_token: null,
        
        data_count: 0,
        
        total_items: 0,

        init: function() 
        {
            this.access_token = this.getAccessToken();
            
            this.data_count = this.getTotalItemsFetched() - 1; //exclude the header

            if (this.data_count < 0) {
                //append headers
                this.appendToSheet([this.headers]);
            }


            var APIData = this.fetchItems(ITEMS_PER_FETCH);
            this.total_items = APIData.total;


            if (this.data_count < this.total_items) 
            {
            	// we atleast call this function once
            	// but from within the function, we might call it again
            	// untill we have all the data
                this.updateSheet(APIData);
            }
        },
        updateSheet: function(APIData) 
        {
        	//filter the data, so that we can push it to google sheets
            var filteredData = this.filterData(APIData);
			
			this.appendToSheet(filteredData);

            //kind if a recurssive function call here
            if (this.data_count + 1 < this.total_items) //-2 is kind of an error margin
            {
            	var APIData = this.fetchItems(ITEMS_PER_FETCH);
                this.updateSheet(APIData);
            }
        },
        getAccessToken: function() {
            /* Change the below configuration */
            var payload = {
                'grant_type': 'app',
                'app_id': 'ENTER_APP_ID',
                'client_id': 'ENTER_CLIENT_ID',
                'app_token': 'ENTER_APP_TOKEN',
                'redirect_uri': 'https://google.com',
                'client_secret': 'ENTER_CLIENT_SECRET'
            };
            var options = {
                "method": "post",
                "payload": payload
            };

            var response = UrlFetchApp.fetch("https://podio.com/oauth/token", options);
            var data = response.getContentText();
            var json = JSON.parse(data);
            return json.access_token;

        },

        getTotalItemsFetched: function() {

            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getSheetByName(SHEET_NAME);
            var count = sheet.getLastRow();
            Logger.log(count);
            return count;

        },

        fetchItems: function(limit) {
            var payload = {
                "limit": limit,
                "offset": this.data_count + 1
            };
          var headers = {
            "Authorization": "Token "+this.access_token,
            "content-type": "application/json;charset=utf-8"
          }
            var options = {
                "method": "post",
                "payload": JSON.stringify(payload),
                "contentType": "application/json;charset=utf-8",
                "headers": headers
            };
          
            var response = UrlFetchApp.fetch("https://api.podio.com:443/item/app/13985988/filter", options);
          
            var data = response.getContentText();
            //Logger.log(data);
            var json = JSON.parse(data);
            return json;

        },
      escape: function(str) {
        return str
        .replace(/[\\]/g, '\\\\')
        .replace(/[\"]/g, '\\\"')
        .replace(/[\/]/g, '\\/')
        .replace(/[\b]/g, '\\b')
        .replace(/[\f]/g, '\\f')
        .replace(/[\n]/g, '\\n')
        .replace(/[\r]/g, '\\r')
        .replace(/[\t]/g, '\\t');
      },
        filterData: function(data) {

        	// we will sort the data and put all items inside this as an array
            var rows = [];

            if (data.items.length > 0) 
            {
                var count = data.items.length;

                //loop through all the items
                for (var i = 0; i < count; i++) 
                {
                	//we will format each item as an object. The key will be the label.
                	//this is usefull for searching and mapping
                    var row = {};
                    var item = data.items[i];
                    var title = item.title;
                    
                    
                    //loop through all the fields of an item
                    for (var j = 0; j < item.fields.length; j++) 
                    {
                        var field = item.fields[j];
                        var label = field.label;
                        var value = field.values[0];


                        if (value.start_date) 
                        {
                            value = value.start_date;
                        } 
                        else if (value.text) 
                        {
                            value = value.text;
                        } 
                        else if (value.value.name) 
                        {
                            value = value.value.name;
                        } 
                        else if (value.value.text) 
                        {
                            value = value.value.text;
                        } 
                        else if (value.value.title) 
                        {
                            value = value.value.title;
                        } 
                        else 
                        {
                            value = value.value;
                        }

                        row[label] = value;
                    }

                    rows.push(row);
                }

            }

            //this is going to contain nice formatted data
            var data = [];
            
            // now we have awesome sorted data inside the rows[][]
            // lets map them with the headers
            
            //rows is a 2-dimensional array. We will loop to get the children (items)
            for (var i = 0; i < rows.length; i++) 
            {
                var row = rows[i];
                var item = [];

                //loop through the headers
                for (var j = 0; j < logic.headers.length; j++) 
                {
                    var label = logic.headers[j];

                    //see its so easy to search now
                    if (row[label]) 
                    {
                        var value = row[label];
                        if(typeof value == "string") {
                           //strip tags. few values have tags like <a href="">[email protected]</a>
                           value = value.replace(/<\/?[^>]+(>|$)/g, "");
                        }
                        item.push(value);
                    } 
                    else 
                    {
                        //push empty value if nothing found
                        item.push(" ");
                    }

                };
                data.push(item);
            };
            return data;

        },
        appendToSheet: function(data) 
        {

            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getSheetByName(SHEET_NAME);
            var nextRow = sheet.getLastRow() + 1;
            
            if(data.length > 0) {
              sheet.getRange(nextRow, 1, data.length, data[0].length).setValues(data);
            
            //increamanet the count
              this.data_count = this.data_count + data.length;
            }

        }
    }

    logic.init();
}

function run() {
    main();
}

Feel free to edit the code according to your use-case.


Chrome extension fix - This extension may have been corruptedAlgorithm - List Prime Numbers in Javascript

Comments
100% Complete