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