Parse facebook api to a table

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"
    }
  }
]

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

Hi @igordisco, welcome to the community :slight_smile:

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
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{json: \n  {\n    \"data\": [\n      {\n        \"id\": \"17921128354964112\",\n        \"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\"\n      },\n      {\n        \"id\": \"17938330636665142\",\n        \"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\"\n      },\n      {\n        \"id\": \"17928114073852653\",\n        \"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\"\n      },\n      {\n        \"id\": \"17911852112157435\",\n        \"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\"\n      },\n      {\n        \"id\": \"18012239110354071\",\n        \"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\"\n      }\n    ],\n    \"paging\": {\n      \"cursors\": {\n        \"before\": \"QVFIUmwxTFdnMV9YQThVemZAkaFRTMEFzdmlVNmdfb1pyS2dTdUsxZAXpPZA2Y1aDRMTTBZAcFp3c1lsekdXUjlIbWZAMTWZAtbzUxckJyTXA4N1lyN3JpcjNTTUhB\",\n        \"after\": \"QVFIUmUxOWlYLVMzZAmJiQmUzZAmF4bS01dUhRVjZAEY01JdW5zSW5vVUJURXo3MTF1eHhFMnd2MlQ4SlVrVzVXSWprS2RWcWxZAWjhsN2h3WXdzQy1LRE1BeHBn\"\n      },\n      \"next\": \"https://graph.facebook.com/v12.0/17841400180118451/media?access_token=EAAFdapbbKCIBAC8zfaYs2ZBQ06z5BxsZCdocjzeRFaHKSBh4Cx8JQxORiNkOA39asa2YJTxSnnNvsw6KoXtkfbu6hWam0XdqDOg4jmvwVoPrjxjGSm6rSPXb6YYFxBTOCIjSL3mp5kyCSIEbyErWTAIpsedAys8gi9j6014VJ6ayFMxPoSM92dZAGzSV7kRhxAZCp0T54ohSZA5ZCcNqmrmGZC4nY0vaZCpvJiJ2Ui0O0ibTT8VxxHHENScflroNe7QZD&fields=id%2Cmedia_url&limit=5&after=QVFIUmUxOWlYLVMzZAmJiQmUzZAmF4bS01dUhRVjZAEY01JdW5zSW5vVUJURXo3MTF1eHhFMnd2MlQ4SlVrVzVXSWprS2RWcWxZAWjhsN2h3WXdzQy1LRE1BeHBn\"\n    }\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "data",
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Is this what you had in mind?

1 Like

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

MEDIA_ID1 | Saved | Engajament | Impressions | Reach
MEDIA_ID2 | Saved | Engajament | Impressions | Reach
MEDIA_ID3 | Saved | Engajament | Impressions | Reach

(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"
  }
]

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.

2 Likes

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
{
  "nodes": [
    {
      "parameters": {
        "functionCode": "return [{\n  json: { \n    my_id: 1\n  }\n}, {\n  json: { \n    my_id: 2\n  }\n}, {\n  json: { \n    my_id: 3\n  }\n}];"
      },
      "name": "Set Example List",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        200
      ]
    },
    {
      "parameters": {
        "url": "=https://reqres.in/api/users/{{$json[\"my_id\"]}}",
        "options": {}
      },
      "name": "HTTP Request for Each Iteam",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    },
    {
      "parameters": {
        "mode": "mergeByKey",
        "propertyName1": "my_id",
        "propertyName2": "id"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1050,
        200
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "id",
              "value": "={{$json[\"data\"][\"id\"]}}"
            }
          ],
          "string": [
            {
              "name": "name",
              "value": "={{$json[\"data\"][\"first_name\"]}} {{$json[\"data\"][\"last_name\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set Fields",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    }
  ],
  "connections": {
    "Set Example List": {
      "main": [
        [
          {
            "node": "HTTP Request for Each Iteam",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request for Each Iteam": {
      "main": [
        [
          {
            "node": "Set Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Fields": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}

Is this what you were looking for?