Using FB json data file to create spreadsheet

Question

I've looked around for this and I know the information is out there, but I'm completely ignorant on how to do this. I need to extract just the names from the json data file on Facebook's Graph API. Here's an example of the json data.

    {
   "id": "POSTID",
   "created_time": "2013-09-20T20:20:52+0000",
   "comments": {
      "data": [
         {
            "from": {
               "name": "XXXXXXX",
               "id": "XXXXXXX"
            },
            "id": "XXXXXXX"
         },
         {
            "from": {
               "name": "XXXXXXX",
               "id": "XXXXXXX"
            },
            "id": "XXXXXXX"
         }

I need to get just the names in a spreadsheet. How can I achieve this? Thanks a lot for your time.

Well you either write a custom bit of code to pull the names out, or you use one of the JSON interrogators to do the work. See: http://stackoverflow.com/questions/8481380/is-there-a-json-equivalent-of-xquery-xpath — Orbling, Sep 25 '13 at 14:58
Also, this is directly related: http://stackoverflow.com/questions/8044423/json-import-to-excel — Orbling, Sep 25 '13 at 15:12
And: http://stackoverflow.com/questions/4130849/convert-json-format-to-csv-format-for-ms-excel — Orbling, Sep 25 '13 at 15:13
I wish I had more programming knowledge. I'm just unsure of what variables to change to get it to list my data. — Daymon, Sep 25 '13 at 17:59
Well, Excel will not directly import the JSON data as is. It is not in a structure that is readily translatable to a spreadsheet at any rate. So you need to run some code over the JSON to extract the data you want before proceeding to Excel. How are you obtaining the JSON data from the Graph API? — Orbling, Sep 25 '13 at 18:43

score 0 · Accepted Answer · answered Sep 25 '13 at 20:44

OK, based on the comment discussion above I knocked up an example to pull the names from a Facebook Graph API - Comment Stream, using jQuery and JSONSelect as a JSON interrogator.

jsFiddle: http://jsfiddle.net/9nqu6/1/

Once the feed is retrieved, all the work is done by JSONSelect, using a selector '.comments .data .from .name' to pick down to the level required in the feed.

The .forEach() command allows iteration on results with a callback, here just generating a table and a CSV file (using a Data URI, file name set in Chrome via the download attribute).

NB. There's no error handling on this, so be sure to pass it the correct type of URI! eg.

https://graph.facebook.com/<postid>?fields=comments.limit(1000).fields(from)

jQuery

$('#read-graph').on('click', function() {
    var graphLink = $('#graph-link').val();

    if (!graphLink) {
        alert("Enter link");
        return false;
    }

    graphLink = graphLink + (/\?/.test(graphLink) ? "&" : "?") + "callback=?"
    $.getJSON(graphLink, function(data) {
        var nameBlock = $('#name-block');
        nameBlock.find('tr').remove();

        var csvData = "data:application/csv;charset=utf-8,Index%2CName%2C%0A";

        var cIndex = 0;
        JSONSelect.forEach('.comments .data .from .name', data, function(cName) {
            cIndex++;
            nameBlock.append('<tr><td class="index">' + cIndex + '</td><td class="name">' + cName + '</td></tr>');
            csvData = csvData + cIndex + "%2C" + encodeURIComponent('"' + cName.replace(/"/g, '""') + '"') + "%0A";
        });

        $('#download-csv').prop('href', csvData).attr('download', "FBGraph.csv").show();
    });

    return false;
});

HTML

<h3>Graph Link</h3>
<form>
    <input id="graph-link" name="graph-link" type="text" value="" />
    <input id="read-graph" name="read-graph" type="submit" value="Read Graph" />
    <a id="download-csv" href="#" style="display: none;">Download CSV</a>
</form>
<table id="name-block">
</table>

CSS

#graph-link {
    width: 400px;
}
#name-block {
    margin-top: 10px;
    border: 1px solid black;
    border-collapse: collapse;
}
#name-block tr {
    border-top: 1px dashed black;
}
#name-block .index {
    width: 50px;
}
#name-block .name {
    width: 350px;
    border-left: 1px solid black;
}

Dude, this is perfect! I can't thank you enough! THANKS!!! One more thing if you don't mind. How could I run this locally? — Daymon, Sep 25 '13 at 20:50
@Daymon: Well - it relies on the browser, Chrome for preference. You would need to put it together in an HTML file. But the CSS bit in a ` — Orbling, Sep 25 '13 at 21:32
Na, just curious for later down the road. I really appreciate it! — Daymon, Sep 25 '13 at 22:00

score 0 · Answer 2 · answered May 24 '14 at 09:44

0

I've written an Excel Add-In (XLL) that pulls JSON from the Facebook Graph API straight into Excel. It's available on GitHub:

https://github.com/spreadgit/XLfacebook

answered May 24 '14 at 09:44

Bjoern Stiel

3,918
1
21
19

Using FB json data file to create spreadsheet

2 Answers2