Facebook Ads data in Google Sheets

How to connect Facebook Ads to Google Sheets using the Facebook Marketing API and the Apipheny add-on

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 in Google Sheets

To pull data from the Facebook Marketing API to Google Sheets easily and without writing any code, first install and open the Apipheny Google Sheets add-on.

Apipheny is a no-code API integrator for Google Sheets that you can use to make unlimited API requests, connect to unlimited APIs (including the Facebook Marketing API), save API requests, schedule API requests, and more. Click here if you want to learn more about Apipheny.

1.) Install Apipheny by clicking the Install button on this page.

2. After you’ve installed Apipheny, open a Google Sheet and then click on the Add-Ons option in the top menu. In the add-ons menu, you should see Apipheny.

Click Apipheny > Import API to open the Apipheny sidebar in your Google Sheets.

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

2.) In your Developer dashboard, 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:

 

 4.) Select Tools under Marketing API: 

Facebook developer dashboard
Facebook developer dashboard

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

6.) Click the New Sandbox Account button to create a Sandbox account:

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

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

Facebook sandbox account management
Facebook sandbox account management

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

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

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/v9.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:

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/v9.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, just click Run.

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, such as:

  • Saving and scheduling your API request
  • Use the custom =APIPHENY() function to call the API request inside your spreadsheet
  • Or try creating an API request by referencing the value of a cell in the API URL with three curly braces eg. {{{Sheet1!A1}}}

You could also connect to other API data sources and create custom blends of data.

Related articles:

Related Posts

Leave a Comment