0

Below, I've posted the code that works for returning ad account adspend into a Google Sheets cell. I'm trying to output the ad account limits to a different cell. This way, we could calculate the remaining budget on ad accounts, by subtracting the adspend from the account limit. Below, I'll post my code that worked for returning the adspend from certain accounts and certain timeframes:

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 = 'my_access_token'


// 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);
const results = JSON.parse(fetchRequest.getContentText());


// Returns the spend

var data = [];
  
results.data.forEach(function (pieceOfData){
  data.push(Number(pieceOfData.spend));
});

if (data >= 0.01)
  return data;

else 
  return 0;


}

This is the formula that we use in Google Sheets to get the ad account spending:

=FacebookReporting("ad_acc_id,"[{since:'2022-08-01',until:'2022-08-30'}]")

The ad_acc_id and my_access_token would usually be filled in. I tried to replace the FIELDS=spend with spend_cap, which I had heard as a parameter from another GitHub post, but it didnt work. I've also posted the same issue to Facebook Developers, so If a solution arises I'll be sure to share it here too.

All and any suggestions are appreciated.

MIXD_Toms
  • 25
  • 5
  • Done, I've shortened everything as much as possible to keep the context but to get to the point. Will keep this in mind in the future. – MIXD_Toms Aug 16 '22 at 14:31
  • The formula is invalid. Check syntax. Could you link the exact part of the developer documentation that returns `spend_limit`? – TheMaster Aug 16 '22 at 14:50
  • 1
    Sorry, I'm not too clear on what exactly is the problem. What is the error that you're encountering, and the expected result? At a glance I can see a few issues, like the formula being called `FacebookLimits` and the call is `=FacebookReporting()`, the quotes in the sheet call seem misplaced, and you're trying to enter an array as a function parameter which apparently is [not supported](https://stackoverflow.com/questions/12563448/how-do-i-use-an-array-as-argument-for-my-custom-function-in-google-apps-script-i). Or is the issue the result? What is `result` supposed to look like? – Daniel Aug 17 '22 at 00:47
  • First I would recommend just rebuilding your script so it takes 3 separate inputs instead of trying to enter an array, but it's not clear if that's your issue. – Daniel Aug 17 '22 at 00:50
  • The formula that was called was indeed meant to be FacebookReporting. Just changed it in the post. With this code, Facebook is able to return a number which indicates the ad accounts spend in a certain timeframe. The array part of it is fine, doesn't change much as you only have to switch a couple numbers to change the date for which I want to see the spend. – MIXD_Toms Aug 17 '22 at 09:16
  • The issue is that I cannot find a FB parameter for the Facebook ads account limit (the total the ad account can spend, set by the user manually). I had found a parameter (spend_cap) in a Github post from 2017, but it seems to not exist in FB official documentation anymore :( – MIXD_Toms Aug 17 '22 at 09:17

0 Answers0