Parse facebook api to a table

igordisco · November 5, 2021, 6:09am

Hello,

I was able to return a set of photos from instagram and I would like to transform this json into a table so that I can insert it into google sheets.

I managed to get from facebook api and managed to insert in google sheets, but when I select node SET I only get one value. I would like to enter everyone.

I don’t know if I need to do it via SET or via FUNCTION.

Can anyone give me a light? Thank you very much!!!

[
  {
    "data": [
      {
        "id": "17921128354964112",
        "media_url": "https://scontent.cdninstagram.com/v/t51.29350-15/253898920_1299638197156448_2587784664488302415_n.jpg?_nc_cat=102&ccb=1-5&_nc_sid=8ae9d6&_nc_eui2=AeEto6EJKUrr_xgzZtb7xX8-RbkSJcjLeNpFuRIlyMt42uqMlQ-ZDJQD_kzx96woo3EutMX2fPgJuc6x0FJkek6I&_nc_ohc=XWUX0F_L1XoAX90qNUN&_nc_ht=scontent.cdninstagram.com&edm=AM6HXa8EAAAA&oh=91841f74539234be30eb8383e42e258a&oe=618A62D1"
      },
      {
        "id": "17938330636665142",
        "media_url": "https://scontent.cdninstagram.com/v/t51.29350-15/251604294_232875685439984_5332334763184016218_n.jpg?_nc_cat=105&ccb=1-5&_nc_sid=8ae9d6&_nc_eui2=AeEsbUKpU8jKdGTuK-gWjruRL29yRohS4Cgvb3JGiFLgKDMgBJAH6s2YqUB5H5BL0WROkQ3HrzPzrZqLjpkXGuz2&_nc_ohc=i47o_EYwNzgAX9fmGCN&_nc_oc=AQl8cxwTJybzI1ayAMqKN2HElfXmyiddZSeHTjz_ig5fbwDSSqMWJPBZrrFRyeuSTeQ&_nc_ht=scontent.cdninstagram.com&edm=AM6HXa8EAAAA&oh=3bfe61ef8e4d246f1f0069b1803a4231&oe=6189D6BF"
      },
      {
        "id": "17928114073852653",
        "media_url": "https://video.cdninstagram.com/v/t50.16885-16/251770842_3060053254264777_5631566019716844127_n.mp4?_nc_cat=100&vs=17954863972498316_1794614244&_nc_vs=HBkcFQAYJEdOcTNBUS1KMXpZckd0OEtBRjhLWWlaMVZ5ZE9idlZCQUFBRhUAAsgBACgAGAAbAYgHdXNlX29pbAExFQAAJvyEmq2Qpcw%2FFQIoAkMzLBdAGAAAAAAAABgSZGFzaF9iYXNlbGluZV8xX3YxEQB17AcA&ccb=1-5&_nc_sid=59939d&efg=eyJ2ZW5jb2RlX3RhZyI6InZ0c192b2RfdXJsZ2VuLjcyMC5pZ3R2In0%3D&_nc_eui2=AeEd-FnUEMnOBLGC7n9Ej4Eh5_rDVdDCiWXn-sNV0MKJZcPqQmWNC4jrLZOztMXQtd_94W7fHKKH1IRABC9G-gxi&_nc_ohc=FDM9dSLaDlcAX9wTRmt&_nc_ht=video.cdninstagram.com&edm=AM6HXa8EAAAA&oh=aa3b489196df6c5a050efe81aab980cd&oe=618554CC&_nc_rid=6d44f95714"
      },
      {
        "id": "17911852112157435",
        "media_url": "https://scontent.cdninstagram.com/v/t51.29350-15/251741863_1366436177125588_3894691279722723858_n.jpg?_nc_cat=111&ccb=1-5&_nc_sid=8ae9d6&_nc_eui2=AeEO9uIYl_qfUdlW-k247TqwGGPyHmV5YgkYY_IeZXliCYdZjy2G8jigAm_QTVcXNF5dneM0bOuM9TQtAQ7eUtyA&_nc_ohc=tq2QT03br9kAX88Gozm&_nc_ht=scontent.cdninstagram.com&edm=AM6HXa8EAAAA&oh=194d995c6a31b7be1b204286c95c2f03&oe=61899384"
      },
      {
        "id": "18012239110354071",
        "media_url": "https://scontent.cdninstagram.com/v/t51.29350-15/251525887_394462442371697_3805902691821153511_n.jpg?_nc_cat=102&ccb=1-5&_nc_sid=8ae9d6&_nc_eui2=AeF0dbaZ573dN2gY07S8ro1JxiVih6nIxxDGJWKHqcjHEIxHvqlcbPbpUu30d7c0BRAOJl3Lsd3lnZrPldzLme2f&_nc_ohc=bxTDPS_GzIwAX952IKM&_nc_ht=scontent.cdninstagram.com&edm=AM6HXa8EAAAA&oh=2a17c4a07d505cb77aa3c552e096eb10&oe=618988B4"
      }
    ],
    "paging": {
      "cursors": {
        "before": "QVFIUmwxTFdnMV9YQThVemZAkaFRTMEFzdmlVNmdfb1pyS2dTdUsxZAXpPZA2Y1aDRMTTBZAcFp3c1lsekdXUjlIbWZAMTWZAtbzUxckJyTXA4N1lyN3JpcjNTTUhB",
        "after": "QVFIUmUxOWlYLVMzZAmJiQmUzZAmF4bS01dUhRVjZAEY01JdW5zSW5vVUJURXo3MTF1eHhFMnd2MlQ4SlVrVzVXSWprS2RWcWxZAWjhsN2h3WXdzQy1LRE1BeHBn"
      },
      "next": "https://graph.facebook.com/v12.0/17841400180118451/media?access_token=EAAFdapbbKCIBAC8zfaYs2ZBQ06z5BxsZCdocjzeRFaHKSBh4Cx8JQxORiNkOA39asa2YJTxSnnNvsw6KoXtkfbu6hWam0XdqDOg4jmvwVoPrjxjGSm6rSPXb6YYFxBTOCIjSL3mp5kyCSIEbyErWTAIpsedAys8gi9j6014VJ6ayFMxPoSM92dZAGzSV7kRhxAZCp0T54ohSZA5ZCcNqmrmGZC4nY0vaZCpvJiJ2Ui0O0ibTT8VxxHHENScflroNe7QZD&fields=id%2Cmedia_url&limit=5&after=QVFIUmUxOWlYLVMzZAmJiQmUzZAmF4bS01dUhRVjZAEY01JdW5zSW5vVUJURXo3MTF1eHhFMnd2MlQ4SlVrVzVXSWprS2RWcWxZAWjhsN2h3WXdzQy1LRE1BeHBn"
    }
  }
]

igordisco · November 5, 2021, 6:09am

Ah, obviously I don’t want the “paging”, just the id and the media_url!

MutedJam · November 5, 2021, 7:58am

Hi @igordisco, welcome to the community

You could use the Item Lists helper node to convert the data field into individual n8n items (which can then be added to your table) like so:

Example Workflow

Is this what you had in mind?

igordisco · November 6, 2021, 5:27am

Wow, what a dummy I was. I looked for this “split” and didn’t find it.

Thanks!

There’s also something else that would help me…

Facebook, I don’t know why, separates the stats api in two. One is the one you helped, and a second called insights. The call comes through a different endpoint. And, obviously, the return is different.

The feedback from the insights api is what I post below. Is it possible to aggregate in a table (like this one) by id? Something like

(i don’t need name, title, description… only values!)

Thanks!

[
  {
    "name": "engagement",
    "period": "lifetime",
    "values": [
      {
        "value": 35
      }
    ],
    "title": "Engajamento",
    "description": "Número total de curtidas e comentários no objeto de mídia",
    "id": "17907259499211487/insights/engagement/lifetime"
  },
  {
    "name": "impressions",
    "period": "lifetime",
    "values": [
      {
        "value": 157
      }
    ],
    "title": "Impressões",
    "description": "Número total de vezes que o objeto de mídia foi visto",
    "id": "17907259499211487/insights/impressions/lifetime"
  },
  {
    "name": "reach",
    "period": "lifetime",
    "values": [
      {
        "value": 151
      }
    ],
    "title": "Alcance",
    "description": "Número total de contas únicas que viram o objeto de mídia",
    "id": "17907259499211487/insights/reach/lifetime"
  },
  {
    "name": "saved",
    "period": "lifetime",
    "values": [
      {
        "value": 0
      }
    ],
    "title": "Salvos",
    "description": "Número total de contas únicas que salvaram o objeto de mídia",
    "id": "17907259499211487/insights/saved/lifetime"
  },
  {
    "name": "engagement",
    "period": "lifetime",
    "values": [
      {
        "value": 36
      }
    ],
    "title": "Engajamento",
    "description": "Número total de curtidas e comentários no objeto de mídia",
    "id": "17921128354964112/insights/engagement/lifetime"
  },
  {
    "name": "impressions",
    "period": "lifetime",
    "values": [
      {
        "value": 1092
      }
    ],
    "title": "Impressões",
    "description": "Número total de vezes que o objeto de mídia foi visto",
    "id": "17921128354964112/insights/impressions/lifetime"
  },
  {
    "name": "reach",
    "period": "lifetime",
    "values": [
      {
        "value": 1021
      }
    ],
    "title": "Alcance",
    "description": "Número total de contas únicas que viram o objeto de mídia",
    "id": "17921128354964112/insights/reach/lifetime"
  },
  {
    "name": "saved",
    "period": "lifetime",
    "values": [
      {
        "value": 0
      }
    ],
    "title": "Salvos",
    "description": "Número total de contas únicas que salvaram o objeto de mídia",
    "id": "17921128354964112/insights/saved/lifetime"
  }
]

igordisco · November 6, 2021, 7:54pm

I did it, but I came an extremely long way.

I made a request for each instagram insights metric (impressions, reach, salvo, engagement) and split each one, saved it to the spreadsheet and added the data.

MutedJam · November 8, 2021, 8:08am

You could merge data on an ID using the Merge node and its Merge By Key mode. You’d only need to specify the name of the id field for each input.

Example Workflow

Is this what you were looking for?

digipower · August 6, 2022, 8:26am

Hi @igordisco , did you figure out a shorter workflow instead of the extremely long way?

igordisco · August 8, 2022, 2:13pm

no…

I’m doing it this way

digipower · August 13, 2022, 12:58pm

Hi All,

Following @MutedJam’s idea, I make another workflow with few IF, Set, and Merge nodes.
It’s still a “long way” but the final Merge node achieve @igordisco’s need with fewer Http Requests(Facebook) and Google Sheet(Update) nodes.

The shortcoming is using too many IF, Set and Merge nodes when transforming lots of metrics(Keys), maybe someone knows other shorter and efficient ways such as using Function node?

igordisco · August 13, 2022, 11:50pm

This is AMAZING!!!