Import Facebook Leads to Google Sheets

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
• Part 1: Connect to the Facebook Leads API
• Part 2: Get your Facebook Page ID
• Part 3: Get your Facebook Form ID
• Part 4: Pull Facebook Leads into Sheets
• Part 5: Flatten Fields
• Part 6: Create a Custom Request
• Part 7: Filter for New Leads
• Part 8: API Documentation

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
   
2. Under Authorization, click Connect to Facebook Leads
   
   
3. You'll be asked which Pages to connect. Click through to connect your account.
   
   
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.

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

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
   
2. Add your page ID into the page_id parameter
   
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.
   

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
   
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.
   

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)

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.
   
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