Import Facebook Ads Data to Google Sheets via Apps Script
There are a few Google Sheets add-ons that allow exporting Facebook Ads reports to Sheets, however, some of them tend to be expensive or lack the support of some new metrics and dimensions. Today, I will walk you through how to get Facebook Ads data to Google Sheets without buying any add-ons and even schedule your reports to run on an ongoing basis.
I built the solution leveraging the Facebook Marketing API and the Google App scripts, it supports all report types and metrics. Follow the steps below to get started.
1- Create Facebook App
Navigate to Facebook for Developers, click ‘Create App’, then choose ‘Manage Business Integrations’. You will be asked to enter more info like App name and your contact details.
After you create the app, Navigate your business manager settings, and under Apps click ‘Add’ and link your new app to the business manager.
Next, click ‘Add Assets’ and select the ad accounts you’d like to pull reports for.
2- Generate your Access Token
Once you create the app, please head to Marketing API > Tools then select all the 3 token permissions then hit ‘Get Token’. Copy and save your token, you will need it in the following steps
3- Configure your Google App Script
Make a copy of this Google Sheet.
To configure the script, you need to add all your relevant details in the config tab:
ACCOUNT_ID: this should be you ad account ID
ACCESS_TOKEN: this is the access token we generated above from the Facebook App
START_DATE: this should be the start date of your report
END_DATE: this should be the end date of your report
FIELDS: This is all the fields you’d like to have in your report like impressions, clicks, cost..etc. For all reporting metrics and fields, please see here.
REPORT_LEVEL: this is the level of your report, it could be set to: ad, adset, campaign, account
Once you are done with your report configuration, you can just click run report, the data should be exported in the FB Ads raw tab.
To schedule the script, click Tools > Script Editor. Inside the script editor click ‘Triggers’ then ‘Add Trigger’:
Select from the ‘Choose which function to run’ dropdown menu downloadReport. The rest of the fields set per your needs, like the type of the time trigger and the actual time.
Please try it out and let me know if you have any questions in comments below.
Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest product GA4 Auditor and discover all my products here.
It’s error. have a notification
Exception: Cannot make request against https://graph.facebook.com code returned 400. Response is truncated from server: {“error”: {“message”: “Invalid OAuth access token. “,” type “:” OAuthException “,” code “: 190,” fbtrace_id “:” ADpX-lTIHCtkwNgIkHjYt0a “}} (use option muteHttpExceptions to check for full response)
please help me.
Your access token is invalid, please make sure you have access to that ad accounts and follow the steps above to generate the token.
Hello, I just tried to use this tutorial, but I had two problems, first the API version was set as 9.0 (which I managed to change to the new 10.0), but now when I try to run the report, this message appears:
Exception: Invealid argument: https://www.facebook.com/ads/ads_insights/export_report?report_run_id=779031399468832&format=csv&access_token=EAACKmWycZCwIBAPV5EsUJlNaEbzJMbjcCUC8NtvoTaVB29PN2nYlpaJLVtUhMIMJN8u6UxXDEtDU7ToNm6C0PqjY25K4TvDCT5CHvvTrW3ZCGI2DkHU0SDulUuZBKRR2XqPEg2WetzVcquNloqN4wJdC1SvAINVxS9B63LQgSttd375P
Do you know how to fix this?
Thanks for the tutorial!
Hey Lucas, I updated the script to FB v11.0, it should work now. Please try again and let me know if any issues
Hi, I’m trying to run with ad report_level without success. Can’t parse the response, here “const results = Utilities.parseCsv(fetchRequest);”. Any idea what is happen? Thanks
Hey Fred, It’s very likely the returned report was empty, please make sure to select a data range where you have active campagins.
Hi Ahmed,
how can setup this to obtain a sheet with these kind of metrics:
Campaign name, Ad set name, Ad name, Objective, Start date, End date, Spent, Impressions, Reach, Clicks, CPC, CPM, CTR, Landing page views, Cost per LP view, AtC, Initiate Checkout, Add payment info, Purchase, Revenue of purchase, ROAS.
I tried to use a few basic metrics but i got this error message: “Exception: La richiesta non riuscita per https://graph.facebook.com ha restituito il codice 400. Risposta del server troncata: {“error”:{“message”:”(#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v11.0.”,”type”:”OAuthExcepti… (usa l’opzione muteHttpExceptions per esaminare la risposta completa)”.
How can I fix it?
Thanks for your support.
Hey Luca, I updated the script to FB v11.0, it should work now. Please try again and let me know if any issues.
Hi Ahmed,
thank you for your explanation. Just followed your steps.
The step with the token, wasn’t exactly like you said and looked different. So I was on https://developers.facebook.com/tools/explorer/ (Graph API Explorer) and selected and requested an access key for the app. Is this right?
If i want to run the report, it always comes up: Google hasn’t verified this app
The app is requesting access to sensitive info in your Google Account. Until the developer ([email protected]) verifies this app with Google, you shouldn’t use it.
What should I do?
Thanks in advance
Hey Susi, The access token you generate from the Grahp Explorer is short-lived. If you follow the steps above, you can get a long-lived access token.
As for the non-verified app, you may skip this and just click advance/continue, there is no harm in that as you’re the owner of the app.
I have used this sheet liked pretty much but I am not able to find out the cost per conversion can you please help me with this
Hey Koushik, It should be “cost_per_conversion’, ‘cost_per_action_type’.
You may also just divide the Amount Spent by your relevant conversion action.