One of our colleague has prepared this step to step guide on how to extract and automate the data from facebook ads to Google sheets using google app script. With his permission, I will share what he has shared with some extra comments.
Steps we’ll go through in the process:
- Setup Facebook App with Marketing API Enabled
- Create Google App Script
- Setup Google Sheet
- Export Ad Data
- Set the triggers
If you are looking for no code tools without technical involved and save time, Try supermetrics for google sheet or excel or datastudio for free and decide whether that would help you.
Things to know before starting the process:
- The email you will use to create the facebook app and google script should be the same. It needs to have access to facebook ads accounts.
- You need to use a long term access token to run the process as the short term token expires in a few hours.
To access a long term token, follow the steps mentioned here .
- The screenshots used have the variables defined as ‘var’ but in the updated code on Github, they are now defined as ‘const’.
Setup Facebook app with Marketing API enabled
First, we will develop a Facebook App which will interact with our Facebook Ads Account and get the data. Create a new app from Facebook Developer and give it a relevant name.
Then, go to the main dashboard of the app.
Open the Basic Settings under the Settings option, and add the Google Apps Script domain as shown in the picture above. Save these settings and proceed to the next step.
The next step is to add the Marketing API to the app. You can either search for it in the dashboard or it will appear there automatically.
After that, you have to generate the app token from the Tool Option.
Save the app token so that it is accessible later . Do note that that token will not be saved, so just copy it immediately after specifying the access type to the token.
Now, our Facebook App part has been completed. Also copy the Facebook AD Account ID for which you want to get the ads data. If you manage multiple clients there will be multiple pages and multiple ad account ids. So, copy the one that you want to use for the ads data.
Create Google App Script
- create a google sheet and Copy its URL and also the tab name. “Sheet1” is the default name in every new Google Sheet.
- Now, you create two new Google apps scripts and paste the code by copying it from this GitHub repository. The files are “get-facebook-report” and “request-facebook-report”. You can copy the code from these files and paste them in the exact scripts that you have made.
Now, you have to fill out the values relevant to your process. You have to fill the credentials and other parameters in the app scripts. Things like access token, ad account id, fields, date range, etc. Make sure that you fill correct parameters in both the scripts and save the changes.
The values to be changed:
Script – (Get_facebook):
Script – (Request_facebook)
For FILTERING, add the parameters you want from the data .
You can see list of all parameter here and then use them based on your needs in the “Fields” variable(separated by commas and use inverted commas )
Now, the Google Sheets and Google App Script have been configured.
You can now run the script and get ads data right in the Google Sheet.
Do note one thing here that there is an issue in the “request-facebook-report.gs”script. But it has been resolved in this issue. So, you will have to make the changes on line number 34. This is temporary once it is added to the master branch then you will no longer have to do that.
3). Exporting Ad Data
First, run the “request-facebook-report.gs” script using the play button and it should run without any problems. Wait for about 20 seconds, and then you run the “get-facebook-report.gs” script and it will place the data in the Google Sheet whose URL you have specified in it.
Make sure that “request-facebook-report.gs” is scheduled to run before “get-facebook-report.gs”.
4). Set the triggers
To automate this process of running the scripts, you can use triggers. The scripts can be scheduled to run automatically. You can configure the app scripts to run daily by configuring the triggers. There is an icon there which you can use for it.
- Follow the instructions as given in Link 1.
- Build a Facebook app using Link 3.
- Build both the google scripts using Link 2.
- The email you used to create the facebook app and google script should be the same and it needs to have access to facebook ads accounts.
- Copy the google scripts from Link 4.
- Run the google scripts.
- If any errors occur, find the solution using Link 5.
- For adding a parameter, look for its keyword under the Fields Column in Link 6. Add that keyword in the FIELDS column of the request script.
Add the keyword in this list.
const FIELDS = ‘inline_link_clicks,campaign_name,adset_name,ad_name,unique_inline_link_clicks,unique_clicks,clicks,impressions,frequency,cpm,actions,cpc,objective,website_purchase_roas,spend,converted_product_quantity,account_currency’
- For converting the column names into English from the regional language :