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 Pages API
• Part 2: Get your Facebook Page ID
• Part 3: Get your Facebook Form ID
• Part 4: Pull Facebook Leads into Sheets
• Part 5: Set Column Order
• Part 6: Filter for New Leads
• Part 7: API Documentation

Before You Begin

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

Part 1: Connect to the Facebook Pages API

If you haven’t connected API Connector to Facebook Pages before, you’ll first need to initiate the connection as follows:

1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
2. In the list of available connections, find Facebook Pages and click Connect.
   
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 Pages 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.

1. In API Connector, click Add New or Create to access the Create screen, and enter the following URL into the API URL path field:

   https://graph.facebook.com/v12.0/mixedanalytics

   Where it says mixedanalytics, enter in your own page name instead. You should see your page name in the URL bar when you open up your Facebook page.
2. From the OAuth dropdown, choose Facebook Pages.
3. Choose a destination sheet and request name
4. Click Run to see your Facebook page ID
   

If you prefer, you can get your Facebook Page ID from your Facebook Business Manager account instead.

Part 3: Get your Facebook Form ID

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

1. In API Connector, click Add New or Create to access the Create screen, and enter the following URL into the API URL path field:

   https://graph.facebook.com/v12.0/507738226062106/leadgen_forms?fields=id,locale,name,status,created_time,expired_leads_count,leads_count,organic_leads_count,page,page_id

   Where it says 507738226062106, enter in your own page ID instead.
2. From the OAuth dropdown, choose Facebook Pages.
3. Choose a destination sheet and request name
4. Click Run to see your form ID(s).
   

Part 4: Pull Facebook Leads into Google Sheets

We’re now ready to get those leads!

1. Back in the Create tab, enter the following URL:
   

   https://graph.facebook.com/v12.0/532536061173218/leads?fields=created_time,id,form_id,field_data

2. Where it says 532536061173218, substitute in your own form ID. This should be one of the form IDs from the data.id column in the previous request.
3. From the OAuth dropdown, choose Facebook Pages.
4. Choose a destination sheet and request name
5. Click Run to see your leads.
   

Part 5: Set Column Order

By default, Facebook returns all the data into a column called “field data”. To give individual fields their own columns, you can use a JMESPath snippet like this.

data[].
{created_time:created_time,
form_id:form_id,
full_name:field_data[?name =='full_name'].values,
email:field_data[?name =='email'].values,
phone_number:field_data[?name =='phone_number'].values
}

Part 6: 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 add the filtering parameter like this:

https://graph.facebook.com/v12.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/v12.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 7: API Documentation

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