Search API Connector Documentation

Print

Import Facebook Leads to Google Sheets

Last Updated On
byAna
< Knowledge Base
premium

In this guide, we’ll walk through how to pull Facebook leads from the Facebook API directly into Google Sheets, using the API Connector add-on for Google Sheets. We'll first get your Facebook form ID, and then use those values to fetch your leads.

Related articles:
Import Facebook Ad Data to Google Sheets
Import Facebook Page Data to Google Sheets

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Facebook Leads API

The easiest way to get started with the Facebook Leads API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Facebook Leads from the drop-down list of applications
    facebookleads-application
  3. Under Authorization, click Connect to Facebook Pages (the Facebook Leads application uses the same permissions as Facebook Pages)
    facebookleads-authorization
  4. You'll be asked which Pages to connect. Click through to connect your account.
    fb-page-connect-img2
  5. You’ll then be returned to your Google Sheet, and can verify that your Facebook Leads connection is active in the Connections screen.

Part 2: Get your Facebook Form IDs

Now that we're connected, let's get the lead generation Form IDs.

  1. Select the /{page_id}/leadgen_forms endpoint to list your lead generation forms
    facebookleads-endpoints
  2. Under the page_id parameter, select your Facebook Page from the dropdown menu.
    facebookleads-pageid
  3. Optionally select fields from the fields parameter, and increase the limit parameter to get more records.
  4. Click Run to see your form ID(s). Note your form IDs in the data.id field, as we'll use those next.
    facebookleads-response2

Part 3: Pull Facebook Leads into Google Sheets

We have our form IDs, and are now ready to get those leads!

  1. Select the /{form_id}/leads endpoint and enter your form ID into the form_id parameter
    facebookleads-parameters3
  2. Increase the limit parameter to 1000 to get more leads (use pagination if you still need more records than that).
  3. Click Run to see your leads. By default, all response data will be sent back in a single column, so see the Flatten Fields section to flatten them out.
Facebook only provides leads data for up to 90 days from the time the form is submitted (info). Therefore you may find that your available leads are lower than your total lead counts. For this reason, it's best to download your leads data often to avoid losing access to your data.

Part 4: Handle Pagination

  1. By default Facebook limits the number of results in a single response (usually to 25 records) as described here. To get more records, you can set the limit parameter to 1000.
  2. If you have more than 1000 records, then you'll need to cycle through the results in batches of 1000. With API Connector you can loop through automatically using pagination handling (paid feature).
    • Pagination type: cursor
    • Next token parameter: after
    • Next token path: paging.cursors.after
    • Run until: choose when to stop fetching data
      facebook-pagination-cursor

Part 5: Flatten Fields

By default, Facebook returns lead responses in a single column called "field data", which can be inconvenient for reporting. To give individual fields their own columns, you can flatten these fields into their own columns, like this:

  • Path to header: data.field_data.name
  • Path to value: data.field_data.values

This will produce a neat report where each response type (name, phone number, etc) gets its own field header.

Part 6: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration. To run a custom request, we'll need your Facebook Page ID, which you can get by navigating to the Pages section of your Facebook Business Manager account.
fb-page-connect-img5

Once you have the page ID, add your complete URL into the Request URL field, and choose Facebook Pages from the OAuth menu (the Leads API connection uses the same authentication as the Pages API). Here's an example request setup, just substitute in your own form ID where it says 111111111.

  • Application: Custom
  • Method: GET
  • Request URL: https://graph.facebook.com/v16.0/111111111/leads?fields=created_time,id,ad_id,form_id,field_data&limit=1000
  • OAuth: Facebook Pages


facebookleads-custom

Part 7: API Documentation

Official API documentation: https://developers.facebook.com/docs/marketing-api/guides/lead-ads/retrieving

14 thoughts on “Import Facebook Leads to Google Sheets”

  1. Hey Everyone,
    has anyone figured out how to get the Column Order sorted correctly (JMESPath as described in Step 5)? I'm getting all relevant fields with no erros, but
    it's only displayed as one single row .
    I'd rather have a compact styled Report, which command am I missing?

    Reply

    • Hey Dan, that JMESPath snippet worked for me, so I wonder if you're getting a different response than I did. Is it possible to share your JSON so I can take a look? Feel free to send via email (support@ this domain), and to remove any private data, but please keep the underlying structure. You can get the JSON by clicking Output options > More options > JMESPath > View API Response.
      Update: You can now use the flatten fields option instead.

      Reply

  3. Hey, do you know how to get the leads only for yesterday's day for example? This method picks up all leads but I'd like to add only new leads, no need to add old leads again and again.

    Reply

    • Sure, you can make a custom request using a time_created filter like this: https://graph.facebook.com/v14.0/111111111111/leads?fields=created_time,id,ad_id,form_id,field_data&filtering=[{'field':'time_created','operator':'GREATER_THAN','value':1633392387}]
      Here's one way you could automate the whole process:

      1. Pull the initial leads data into a sheet called Leads, cell C1
      2. In column B, convert the timestamps from the response into Sheets timestamps with a formula. I suggest using this array formula so you only need to enter it once instead of copying it onto each row:=arrayformula(if(C2:C<>"",DATEVALUE(MID($C$2:$C,1,10))+TIMEVALUE(MID($C$2:$C,12,8)),""))
      3. In column A, now convert those Sheets timestamps to UNIX timestamps with this formula:=arrayformula(if(C2:C<>"",(((B2:B)-DATE(1970,1,1))*86400),""))
      4. You should now have all your UNIX timestamps in column A. Create a new sheet called Max with a single formula: =max('Leads'!A:A) This will pull in the maximum (most recent) UNIX timestamp from your sheet.
      5. Create a new API request that references that cell value, like this:https://graph.facebook.com/v14.0/11111111111/leads?fields=created_time,id,ad_id,form_id,field_data&filtering=[{'field':'time_created','operator':'GREATER_THAN','value':+++Max!A1+++}]

      Once you set this up, your request will always fetch new data only and won't need to be updated again.

      Reply

      • The setup I mentioned will just pull the latest data, since it's set to pull data from greater than the existing create time. If it's pulling today's leads, that means you already have yesterday's created leads in the existing data set. If you want to ensure it only fetches yesterday's leads, you could change the setup so you put a formula for yesterday's date into your sheet, convert it to UNIX timestamps, and then use THAT value instead of =max('Leads'!A:A).

      • I'm not sure, but can you please try this? Change =max('Leads'!A:A) to =max('Leads'!A:A)+1, that way it should definitely fetch a value greater than the original create date, which will help in case their "greater_than" condition actually functions as a "greater than or equal to".

  4. Ana,

    This works: =max('Leads'!A:A)+1

    Please recommend any sources to start learning such API's requests for absolute beginners.

    Thank you!

    Reply

      • Thanks!

        Could you please take a look at the screenshot https://i.imgur.com/GqV1sRb.png and probably know how to fix this?
        Many data are located in incorrect columns. For example, the Name may be in a column with a custom question, and the phone number may appear in the Email field. In the original file with leads, everything is good but there's a mess in the spreadsheet.

      • I'm not totally sure how that can be happening, can you please make sure you've applied the field flattening mentioned above? Is this in overwrite or append mode?

Leave a Comment