Search API Connector Documentation

Print

Import Facebook Leads to Google Sheets

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.

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. Select Facebook Leads from the drop-down list of applications
    facebookleads-application
  2. Under Authorization, click Connect to Facebook Leads
    facebookleads-authorization
  3. You'll be asked which Pages to connect. Click through to connect your account.
    fb-page-connect-img2
  4. 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 Page ID

We'll need your Facebook Page ID for subsequent requests, so let's get it now. To get your Facebook Page ID, navigate to the Pages section of your Facebook Business Manager account.
fb-page-connect-img5

Alternatively, you can get your page ID via the API, using the /{page_name} endpoint.
facebookleads-response1

Part 3: Get your Facebook Form IDs

Now that we have a Facebook Page ID, let's get the lead generation Form IDs associated with that Page.

  1. Select the /{page_id}/leadgen_forms endpoint to list your lead generation forms
    facebookleads-endpoints2
  2. Add your page ID into the page_id parameter
    facebookleads-parameters2
  3. Optionally select fields from the fields parameter
  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 4: Pull Facebook Leads into Google Sheets

We're 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. 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 next.
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 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 create a custom request, 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)
facebookleads-custom
To convert from a preset request to a custom API URL, tick the Add request URL box before running your preset request (under Output options). This will print out your complete API URL, which you can then copy/paste into the request URL field.

Part 7: Filter for New Leads

By default, Facebook will return all the leads data. If you'd like to filter by timestamp to get new leads only, you need to create a custom request with a filtering parameter:

https://graph.facebook.com/v14.0/532536061173218/leads?fields=created_time,id,ad_id,form_id,field_data&filtering=[{'field':'time_created','operator':'GREATER_THAN','value':1633392387}]

The filtering parameter uses UNIX timestamps but data gets returned as ISO. Neither of these date formats are natively recognized by Google Sheets, so you'll need to convert them. Here's one setup that should work:

  1. Pull the initial leads data into a sheet called Leads, cell C1, so you have columns A and B for your formulas
  2. In column B, convert the ISO timestamps 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, convert the 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.
    fb_leads_img8
  5. 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.
  6. Create a new API request that references that cell value, like this:
    https://graph.facebook.com/v14.0/532536061173218/leads?fields=created_time,id,ad_id,form_id,field_data&filtering=[{'field':'time_created','operator':'GREATER_THAN','value':+++Max!A1+++}]
  7. This will dynamically pull in new leads only. You can send the output to the same Leads sheet using Append mode so it attaches to the end and keeps all your leads data in one place, without duplicates.

Part 8: API Documentation

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

2 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 ([email protected] 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

Leave a Comment

Jump To...