I’m currently creating a Google Apps Script for my company. We’re a marketing agency in control of many Facebook ad accounts. Shortly, we have to update numbers on our own Google Sheet with spending from the clients ad accounts every day. I hated the robotic, manual labor, so I decided to come up with this solution.
The cell would contain the following formula:
=FacebookReporting("ad_acc_id","[{since:'2022-08-01',until:'2022-08-08'}]")
That way we could create these formulas for each of our ad accounts for whatever timeframe, and the numbers would update automatically every time we entered our sheet.
So far, my code looks like this:
function FacebookReporting(input1, input2) {
var AD_ACCOUNT_ID = input1
var TIME_RANGES = input2
// ad, adset, campaign, account
const LEVEL = 'account'
// https://developers.facebook.com/docs/marketing-api/insights/parameters#fields
const FIELDS = 'spend'
// Your user access token
const TOKEN = 'EAAORfG6N4U4BAONzrmHW18PDC0FCGgifwZCCPc692CiQ1UtGvdQoUb0usjdUpWV0cLe9g1sfeWu9XqGcVjexSWvMEkWTpFTqXjWb0skFL5ZCpknaGMZAi5WO9VNubgFL0556NHh2jqAhipZCI0zbOm8f57YCdnM3S7EXTLEZBtrFlJ7Ob0CIG'
// Builds the Facebook Ads Insights API URL
const facebookUrl = `https://graph.facebook.com/v14.0/act_${AD_ACCOUNT_ID}/insights?level=${LEVEL}&fields=${FIELDS}&time_ranges=${TIME_RANGES}&access_token=${TOKEN}&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());
// Formats only spend in the cell
var data = [];
results.data.forEach(function(pieceOfData){
data.push(pieceOfData.spend)
});
return data;
}
I’ve removed the access token, but that would usually be there. I’m able to open the website, by entering all the right variables, and then the data displays. Screenshot attached of how it looks. Now trying to figure out how to exclude only the number after the spend part and to show it in the cell where the formula is in. With the current code, the cell with the formula is empty.
Any help or suggestions is appreciated, I’m trying to save me and my coworkers about 30% of our time 😀
The data that I’m able to get from FB is the following:
{
"data": [
{
"spend": "41389.73",
"date_start": "2022-08-01",
"date_stop": "2022-08-08"
}
],
"paging": {
"cursors": {
"before": "MAZDZD",
"after": "MAZDZD"
}
}
}
The end goal is that 41389.73 shows up in the cell with the formula.
3
Answers
The solution was to remove options from
and to add
If this data structure is always returned, you should instead programmatically obtain the values of the results constant and format them yourself to an array of your desired size. The reason why I suggest this is because the structure you have is not a simple 1D or 2D array, it is the following:
To obtain the exact value of the spend property you will need to open the array, they use indexes and the first index is 0 just like @Bryan said.
I suggest you use this code after the const results = JSON.parse(fetchRequest.getContentText()); line:
Since data is an array you could receive multiple spending values, this will list them all in a different row.
If I understand correctly you’re trying to get the value of
spend
which is stored underresults
and then return it, this is how to get it based on the example:This assumes that you get only one set of
data
values.