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:

Install the Apipheny Add-on
Create a Facebook App
Choose a Facebook API endpoint
Enter your Facebook API URL into Apipheny
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.

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

Click the Create App button:

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

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

After confirming the security check, click the Submit button:

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:

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

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:

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):

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:

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

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

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

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:

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:

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.