Hello @fredericharnois,
First of all, thank you so much for your generosity to share this! It has been super helpful in pulling reports that could have been super tedious to do via the Facebook Ad Manager UI.
The script worked out quite well after some tweaks but for some reason, I kept encountering the following issues:
- the 'request-facebook-report' script would work out smoothly but the get-facebook-report script would show the "unable to parse text at line 27" error at least a few times. It would require a few persistent manual runs before the raw data gets pushed onto Google Sheets. Wondering what could have caused this?
- once the data is generated on Google Sheet, the header does not seem to align with the "field sequence". For my case: the
Post Comments | Post Shares | Link Clicks | Landing Page Views headers would be placed before Campaign Name | Ad Name | Ad Set Name ones. Would you happen to know what could have led this and is there any way to change that?
Here's the version that I replicated on my end:
`// MODIFY YOUR REPORT HERE //
// ad account ID
const AD_ACCOUNT_ID = 'xxxxxxxx'
// ad, adset, campaign, account
const LEVEL = 'ad'
// https://developers.facebook.com/docs/marketing-api/insights/parameters#fields
const FIELDS = 'campaign_name,ad_name,adset_name,impressions,clicks,spend,cpc,actions,conversions,frequency,video_p75_watched_actions,cost_per_thruplay'
// https://developers.facebook.com/docs/marketing-api/insights/parameters#param
const DATE_RANGE = 'this_year'
// user access token linked to a Facebook app
const TOKEN = 'xxxxx'
// number of days from 1 to 90
const TIME_INCREMENT = 'monthly'
// https://developers.facebook.com/docs/marketing-api/insights/parameters#param
const FILTERING = "[{'field':'action_type','operator':'IN','value':['comment','like','post','video_view','link_click','landing_page_view']}]"
// DO NOT MODIFY ANYTHING BELOW //
function requestFacebookReport() {
// Builds the Facebook Ads Insights API URL
const facebookUrl = https://graph.facebook.com/v9.0/act_${AD_ACCOUNT_ID}/insights?level=${LEVEL}&fields=${FIELDS}&date_preset=${DATE_RANGE}&access_token=${TOKEN}&time_increment=${TIME_INCREMENT}&filtering=${FILTERING}&limit=1000;
const encodedFacebookUrl = encodeURI(facebookUrl);
const options = {
'method' : 'post'
};
// Fetches & parses the URL
const fetchRequest = UrlFetchApp.fetch(encodedFacebookUrl, options);
const results = JSON.parse(fetchRequest.getContentText());
// Caches the report run ID
const reportId = results.report_run_id;
const cache = CacheService.getScriptCache();
const cached = cache.get('campaign-report-id');
if (cached != null) {
cache.put('campaign-report-id', [], 1);
Utilities.sleep(1001);
cache.put('campaign-report-id', reportId, 21600);
} else {
cache.put('campaign-report-id', reportId, 21600);
};
Logger.log(cache.get('campaign-report-id'));
}
// MODIFY YOUR SETTINGS HERE //
// url of the google sheets where the report will be
const SPREADSHEET_URL = 'XXX'
// name of the sheet where the report will be
const TAB_NAME = 'XXX'
// DO NOT MODIFY ANYTHING BELOW //
function getFacebookReport() {
// Selects the chosen sheet and tab
const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
const sheet = ss.getSheetByName(TAB_NAME);
// Clears the sheet
sheet.clear();
// Gets the Facebook report run ID
const cache = CacheService.getScriptCache();
const reportId = cache.get('campaign-report-id');
// Fetches the report as a csv file
const url = https://www.facebook.com/ads/ads_insights/export_report?report_run_id=${reportId}&format=csv&access_token=${TOKEN};
const fetchRequest = UrlFetchApp.fetch(url);
const results = Utilities.parseCsv(fetchRequest);
// Pastes the csv file in the sheet
sheet.getRange(1,1, results.length, results[0].length).setValues(results);
}`
Appreciate your advice on this and once again thank you so much for sharing this knowledge!
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent