How to integrate Facebook Ads with Google Sheets using the Facebook Marketing API

In our previous article, we showed you how to get data from a Facebook Page into Google Sheets using the Facebook Pages API. In this tutorial, we’ll show you how to connect your Google Sheet to the Facebook Marketing API which you can use to pull data from your Facebook Ads account into Google Sheets.

The Facebook Marketing API allows developers to programmatically access Facebook’s ads platform. Developers can use Facebook’s Marketing API to get access to data for dynamic ads, ad targeting, best practices and other ad management services. 

In this tutorial, you’ll learn how to extract data from the Facebook Marketing REST API to Google Sheets in 5 steps using the Apipheny add-on:

  1. Install the Apipheny Add-on
  2. Create a Facebook App
  3. Choose a Facebook API endpoint
  4. Enter your Facebook API URL into Apipheny
  5. Run the Facebook Ads API Request in Google Sheets

Step 1.) Install and open the Apipheny add-on for Google Sheets

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

open apipheny

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Create a Facebook App

1.) Begin by navigating to https://developers.facebook.com/ and click Get Started then sign in and follow the steps to create your App

Dashboard section
Dashboard section

Click the Create App button:

My Apps section
My Apps section

Select Business as an app type and click the Continue button:

Select app type popup window
Select app type popup window

Fill out your details and click Create App. You can call your app anything, but in this example we’ll call it ApiphenyApp3:

Create an App section
Create an App section

After confirming the security check, click the Submit button:

Security Check popup
Security Check popup

2.) In the Products section of the menu on the left, select Marketing API. If you don’t see this option, click the plus (+) button next to Products and add Marketing API by clicking the Set Up button:

The Products section
The Products section

You’ve successfully added the Marketing API in the Products section of your app:

Marketing API
Marketing API

 3.) Select Tools under Marketing API and then scroll to the Sandbox Ad Account Management section. Facebook ads applications (Facebook apps with Marketing API access) allow creating and using an ad sandbox account, through which the entire ad creation and management system can be tested

Click the New Sandbox Account button to create a Sandbox account:

New Sandbox Ad Account creation
New Sandbox Ad Account creation

4.) A popup window will appear, allowing you to choose a name (1), a currency (2) and a time zone (3) for your account. You can also associate a page with your Sandbox account, by choosing a page (4) and clicking the Associate button (5):

Creating a Sandbox Ad Account
Creating a Sandbox Ad Account

Now, your page will be associated with the Sandbox account. In our case, ApiphenyPage is associated with the Sandbox. Then click the Create button to complete the action:

Sandbox Ad Account confirming creation
Sandbox Ad Account confirming creation

5.) In the Sandbox Ad Account Management section, click on the Get Access Token button to generate one:

Get Access Token
Get Access Token

6.) In the Generate Access Token popup, click the Generate button to obtain the access token:

Generate the Access Token
Generate the Access Token

7.) Copy and paste the access token somewhere safe, then click the Close button:

Access Token displayed on the screen
Access Token displayed on the screen
Note: Please note that this token will never expire.

Step 3.)  Choose an endpoint for your Facebook API URL

An endpoint is one piece of your whole API URL that tells the Facebook API what information you’re looking for (what data to query and return).

If you want to use the same example endpoint as us, skip to the next section. If you want to choose your own endpoint, keep reading.

For descriptions and examples of each Facebook Marketing API URL endpoint, you should check the relevant section of the Graph API documentation which can be found here: https://developers.facebook.com/docs/marketing-api/reference/v12.0

  • In the menu on the left, in the Reference section, you can find the list of available endpoints.
  • You can also search for an endpoint in the Search bar, if you can’t find it in the list
  • Click any of the endpoints and the corresponding documentation will open

For instance, this is what the User Adaccounts endpoint page looks like, which is the endpoint we’ll be using in the example below:

User Adaccounts Documentation
User Adaccounts Documentation

Another example is Campaign Insights endpoint, which provides insights on your advertising performance. Allows for deduped metrics across child objects, sorting of metrics, and async reporting.

The fields parameter accepts a list of comma separated values that you choose from a list. Some of these values are: cost_per_action_type, cost_per_ad_click, cost_per_conversion, cost_per_unique_action_type, cost_per_unique_click, cpc, cpm, cpp and ctr. The full list of the available values can be found by scrolling the endpoint’s documentation page down to the Fields section.

A possible URL configuration for this endpoint is:

https://graph.facebook.com/v12.0/AD_SET_ID/insights?fields=cpc%2Ccpm%2Cctr%2Ccost_per_store_visit_action%2Cstore_visit_actions&access_token=your-token-here

Don’t forget to replace your-token-here with the token that you previously obtained and the AD_SET_ID with a real value of an ad set id.

Note: Facebook changes the version of their API often. Check their documentation to confirm the latest version and update the version number in the URL if necessary.

Step 4.) Add your Facebook API URL into the Apipheny Add-on

After you find the endpoint that you need, go back to your Google Sheet, with the Apipheny app open.

At the top of the Apipheny sidebar, on the right side of the screen, select the HTTP method (GET or POST) as required by your API endpoint. For this example, we are using GET (screenshot below).

Copy and paste your complete Facebook API URL into the Apipheny add-on, where it says API URL Path, followed by any GET parameters required for your query.

One such parameter your need is your access token, which is the value you copied somewhere safe in step 2.

In this example, we’re using the Ad Account Id endpoint, which returns the ad account id, formatted as act_<numeric value>. A correctly formatted URL for this endpoint, with the required parameters, should look like this (as of November 2020):

https://graph.facebook.com/v12.0/me/adaccounts?access_token=your-token-here

You can copy and paste this example URL into the add-on if you want. Just replace your-token-here with your actual token.

You don’t need to add anything else into Apipheny.

Facebook Ads API request entered in Apipheny

Step 5.) Run the Facebook Ads API Request in Google Sheets

Click the Run button at the bottom of the Apipheny add-on and then wait for the Facebook Ads data to be imported into your Google Sheet, like this:

Facebook Ads API data in Google Sheets

After successfully calling the Facebook Marketing API, try querying a different endpoint, or try using one of the more advance features in the Apipheny add-on.


API Tutorials



API Knowledge

What is an API?
What is an API URL?
What are parameters?
What is an endpoint?
What is an API key/token?
What is basic authentication?
What are headers?
What is a GET request?
What is a POST request?