Search API Connector Documentation
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.
Import Facebook Ad Data to Google Sheets
Import Facebook Page Data to 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:
- Open up Google Sheets and click Extensions > API Connector > Manage Connections.
- In the list of available connections, find Facebook Pages and click Connect.
- You’ll be asked which Pages to connect. Click through to connect your account.
- 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.
- In API Connector, click Add New or Create to access the Create screen, and enter the following URL into the API URL path field:
Where it sayshttps://graph.facebook.com/v12.0/mixedanalytics
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. - From the OAuth dropdown, choose Facebook Pages.
- Choose a destination sheet and request name
- 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.
- In API Connector, click Add New or Create to access the Create screen, and enter the following URL into the API URL path field:
Where it sayshttps://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
507738226062106
, enter in your own page ID instead. - From the OAuth dropdown, choose Facebook Pages.
- Choose a destination sheet and request name
- Click Run to see your form ID(s).
Part 4: Pull Facebook Leads into Google Sheets
We’re now ready to get those leads!
- 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
- 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. - From the OAuth dropdown, choose Facebook Pages.
- Choose a destination sheet and request name
- 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:
- Pull the initial leads data into a sheet called Leads, cell C1, so you have columns A and B for your formulas
- 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)),""))
- In column A, convert the Sheets timestamps to UNIX timestamps with this formula:
=arrayformula(if(C2:C<>"",(((B2:B)-DATE(1970,1,1))*86400),""))
- You should now have all your UNIX timestamps in column A.
- Create a new sheet called Max with a single formula:
This will pull in the maximum (most recent) UNIX timestamp from your sheet.=max('Leads'!A:A)
- 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+++}]
- 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
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?
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.