Search API Connector Documentation
Import Facebook Ad Data to Google Sheets
There are 2 ways to connect to the Facebook Ads API:
- Preset “Connect” button (OAuth) premium
- Personal access token. Please check the appendix for detailed instructions to retrieve your token.
- Before You Begin
- Part 1: Connect to the Facebook Ads API
- Part 2: Create your API Request URL
- Part 3: Pull Facebook API Data into Sheets
- Part 4: More Example API URLs
- Part 5: Understanding Conversion / Action Data
- Part 6: Handle Filtering
- Part 7: Handle Actions
- Part 8: Sort Data
- Part 9: Handle Pagination
- Part 10: API Request URL Builder
- Part 11: [Business Plan]: Get the Facebook Ads Report Pack
- Part 12: API Documentation & Resources
- Appendix: Connect with an Access Token
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Connect to the Facebook Ads API
To begin, we’ll initiate the connection to the Facebook Ads API.
- Open up Google Sheets and click Extensions > API Connector > Manage Connections.
- In the list of available connections, find Facebook Ads and click Connect.
- You will be directed to Facebook and asked to approve the connection. Click Done.
- You’ll then be returned to your Google Sheet, and can verify that your Facebook Ads connection is active in the Connections screen.
Part 2: Create a Facebook API Request URL
For our first request, let’s get some basic data about your Facebook ads account.
- API root: https://graph.facebook.com
- Endpoint: /v13.0/act_YOUR_ACCOUNT_ID/insights (Get your account ID as described here)
Putting it together, we get the full API Request URL:
(If you use the preset Facebook Ads connection, API Connector will automatically route requests to the most recent version when the version changes.)
Part 3: Pull Facebook API Ad Data into Sheets
Now let’s enter that URL into API Connector.
- Back in the Create Request interface, enter the Request URL we just created above.
- Under OAuth, choose Facebook Ads from the dropdown menu. You should see a badge saying Connected.
- We don’t need any headers so just leave that section blank.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you should see summary ad stats populate your Google Sheet. By default, without additional parameters the FB Advertising API will return impression and spend data for the last 30 days.
- Key metrics (reach, impressions, clicks, CPC, and spend) for the past 30 days:
- Key metrics for the past 30 days, broken down by age and gender:
- Key metrics for the past 30 days, for each day:
- Key metrics for a specific time period, for each day:
- Key metrics for a specific time period, for each day and campaign name:
- Key metrics for the past 7 days, for each day and by account name, campaign ID, campaign name, adset ID, adset name, ad ID, ad name, and account currency:
The date_preset parameter lets you choose your reporting period. Options include date_preset=today, yesterday, this_month, last_month, etc. A full list is available in the documentation. It will be ignored if the time_range parameter is used.
- Key metrics for the past 30 days, including conversions/actions (see more about getting conversion data in part 5). May 2021 update: the &use_unified_attribution_setting=true parameter has been added to account for iOS14-related attribution changes (info).
Note: this request was run in “compact” mode to make it easier to read the list of actions. The “single row” report style would display each action in its own column.
- Ad status and run dates:
Part 5: Understanding Conversion / Action Data
fields=actions in your query, as shown in the conversions URL example above. Actions will automatically be returned with their action count (the number of times the action occurred).
- offsite_conversion.custom.: Custom Conversions defined by the advertiser
- offsite_conversion.fb_pixel_add_payment_info: Adds Payment Info
- offsite_conversion.fb_pixel_add_to_cart: Adds To Cart
- offsite_conversion.fb_pixel_add_to_wishlist: Adds To Wishlist
- offsite_conversion.fb_pixel_complete_registration: Completed Registration
- offsite_conversion.fb_pixel_custom: Custom pixel events defined by the advertiser
- offsite_conversion.fb_pixel_initiate_checkout: Initiates Checkout
- offsite_conversion.fb_pixel_lead: Leads
- offsite_conversion.fb_pixel_purchase: Purchases
- offsite_conversion.fb_pixel_search: Searches
- offsite_conversion.fb_pixel_view_content: Views Content
- onsite_conversion.flow_complete: On-Facebook Workflow Completions
- onsite_conversion.messaging_block: Blocked Messaging Conversations
- onsite_conversion.messaging_conversation_started_7d: Messaging Conversations Started
- onsite_conversion.messaging_first_reply: New Messaging Conversations
- onsite_conversion.post_save: Post Saves
- onsite_conversion.purchase: On-Facebook Purchases
- outbound_click: Outbound Clicks
- photo_view: Page Photo Views
- post: Post Shares
- post_reaction: Post Reactions
- rsvp: Event Responses
- video_view: 3-Second Video Views
Part 6: Handle Filtering
If you’re using the IN operator, you can include multiple conditions like this:
offsite_conversion.custom.398028550584411 instead of
Part 7: Handle Actions
Part 8: Sort Data
sort parameter. You enter the field name you’d like to sort on, and then append “_ascending” or “_descending” to specify the sort order (the default sort order is ascending). For example, to sort data in descending order of spend amount, you would append the following to your URL:
sort=["actions:<action_type>"]. For example, to sort data in ascending order of cart adds, you would append the following to your URL:
Part 9: Handle Pagination
- 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 add the ‘limit’ parameter to the end of your URL, like this:
- There seems to be an undocumented limit of 5000, so if you need more records after that, you can loop through them using pagination handling (paid feature).
- API URL: enter your request URL, including &limit=5000
- Pagination type:
- Next token parameter:
- Next token path:
- Run until: choose when to stop running the request
Part 10: API Request URL Builder
Part 11 [Business Plan]: Get the Facebook Ads Report Pack
- Overview: A summary overview of key metrics
- Campaign Data: A report showing all your campaigns, with key metrics including impressions, frequency, clicks, spend, purchases, etc.
- Monthly Data: A report showing a summary of key metrics broken out by month
- Device Data: clicks and impressions by device
- Demographic: clicks and impressions by age and gender
Here is the link (click File > Make a Copy to get your own copy).
Part 12: API Documentation & Resources
- Official API documentation: https://developers.facebook.com/docs/marketing-api/insights/parameters
- This is a good blog article containing additional examples and information about the API.
Appendix: Connect with a Facebook API User Access Token
- Begin by navigating to https://developers.facebook.com/, and click My Apps.
- Click Add a New App.
- Facebook will ask you to choose an app type. Select Business and click Next.
- Finally, enter your app details. You can name your app anything as long as it doesn’t include terms related to Facebook. Click Create App.
- On the left sidebar, you’ll now see Marketing API listed under Products. Click Tools.
- This will produce a screen where you can select your token permissions. All the example API requests in this article use the “ads_read” permission, which allows you to read ads reports from the Ads Insights API for ad accounts you own or have been granted access to by the ad account owner. Click Get Token.
- You’ll now see your access token. Copy it down and keep it safe as they won’t show it again. Congrats, you can now access the Facebook Advertising API!
- Now that you have your token, you can use it by appending
access_token=YOUR_ACCESS_TOKENto any API URL, e.g.