search

bradjasper / ImportJSON

Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet
GNU Lesser General Public License v3.0
2.11k stars 1.06k forks source link

Figure out how to handle multiple objects in Facebook API using id fields #2

Open fastfedora opened 11 years ago

fastfedora commented 11 years ago

The Facebook API allows a user to pass in a comma-separated list of IDs using the 'id' URL parameter, which returns a list of objects (see https://developers.facebook.com/docs/reference/api/request-parameters/ ).

However, the returned JSON does not use an array of objects, but a key-value map of objects where the key is the ID of each object. ImportJSON parses this as a single row of data rather than multiple rows of data.

For instance, the following is parsed as a single row with two sets of columns, one group starting with the path '/15765808054' and another starting with the path '/116335138405853':

=ImportJSON("http://graph.facebook.com/?ids=15765808054,116335138405853", "/", "rawHeaders")

This is correct behavior with the current version of ImportJSON. However, it would be useful to be able to parse this as two separate rows. To do so, we'd need a way of either a) ignoring part of the path or b) using wildcards in the path.

If we could ignore the first component in the path, then '/15765808054/about' and '/116335138405853/about' would both look like '/about' and we could treat the two objects as an array.

If we could use wildcard in paths, we could maybe define the query path as '/*/' and treat that query as saying that everything in the first component should be ignored. Alternative syntaxes might be '//' or '/.../', since we may want to use the asterisk as a wildcard character in the future.

Using paths to convert key-value maps into arrays might be a more general solution. So '///' would say: start at the third-level and return everything as if it were a first-level array. With future wildcard support, we could even do '///title*' which says to take the values of all the keys that start with title at the third level and treat them as a first level array.

stevenjk23 commented 7 years ago

This feature would be awesome. Has anyone had a go at this?

OutofscopeLabs commented 7 years ago

I would love to have a go but my code skills not up to scratch yet. Might have a look at it today to try. A dirty hack might be looking at where the pagination for a routine query is delievered and using Google script to check the returned, parsed string and loop through checking a range of cell numbers where the pagination would occur (by default there are what, 20 rows?) and creating another get URL for =importJson (set function, followed by standard URL elements followed by pagination number)?

As I say, quite a dirty hack but could work?

algotastic commented 7 years ago

I have come across this exact same issue with a spreadsheet/API I'm working with. If I can implement a fix, I will submit a solution here.

emilybe commented 6 years ago

@algotastic I have this problem to. Did you find a solution?

Would be forever grateful! I've tried everything of my capabilities to make it work, nothing has.

ofmarconi commented 6 years ago

Has anyone managed to solve this?